Archive for the ‘PostgreSQL Developer’ tag
Python-Postgres Query
As I committed to a student, here are sample programs for writing a Python query against the Postgres 11 database. The first one returns rows or tuples. The latter formats the text returned as columns.
If you’re one of many looking for the key psycopg2 driver library, you can find it in most distro repositories as: python3-psycopg2. You can use dnf or yum to install it separately or you can install pgadmin4, which includes the psycopg2 library.
The first example returns the entire row from a new_hire table with two rows:
import psycopg2 try: # Open a connection to the database. connection = psycopg2.connect( user="student" , password="student" , port="5432" , dbname="videodb") # Open a cursor. cursor = connection.cursor() # Assign a static query. query = "SELECT * FROM new_hire" # Parse and execute the query. cursor.execute(query) # Fetch all rows from a table. records = cursor.fetchall() # Read through and print the rows as tuples. for row in range(0, len(records)): print(records[row]) except (Exception, psycopg2.Error) as error : print("Error while fetching data from PostgreSQL", error) finally: # Close the database connection. if (connection): cursor.close() connection.close() |
The first example returns the rows as tuples, which is probably desired if you want to consume the result in another Python program. Here’s the output retrieved:
(1001, 'Malcolm', 'Jacob', 'Lewis', datetime.date(2018, 2, 14)) (1002, 'Henry', None, 'Chabot', datetime.date(1990, 7, 31)) |
The second one returns the rows and formats the columns into output for a csv style file:
import psycopg2 try: # Open a connection to the database. connection = psycopg2.connect( user="student" , password="student" , port="5432" , dbname="videodb") # Open a cursor. cursor = connection.cursor() # Assign a static query. query = "SELECT * FROM new_hire" # Parse and execute the query. cursor.execute(query) # Read through and print the formatted columns of each row. for (new_hire_id, first_name, middle_name, last_name, hire_date) in cursor: if (isinstance(middle_name,type(None))): print("{},'{} {}','{:%d-%b-%Y}'".format(new_hire_id, first_name, last_name, hire_date)) else: print("{},'{} {} {}','{:%d-%b-%Y}'".format(new_hire_id, first_name, middle_name, last_name, hire_date)) except (Exception, psycopg2.Error) as error : print("Error while fetching data from PostgreSQL", error) finally: # Close the database connection. if (connection): cursor.close() connection.close() |
The second one returns the rows and formatted columns for a csv style file:
1001,'Malcolm Jacob Lewis','14-Feb-2018' 1002,'Henry Chabot','31-Jul-1990' |
As always, I hope these help those looking for a starting place with Python and Postgres.
pgAdmin4 on Fedora 30
While attempting an install of pgAdmin and updating a Fedora 30 environment, I encountered a conflict on the upgrade of MySQL 8.0.17-1 to 8.0.17.2. The community-mysql-8.0.17-2.fc30.x86_64 had conflicts with:
mysql-community-client-8.0.17-1.fc30.x86_64packagemysql-community-server-8.0.17-1.fc30.x86_64package
I tried to update the system before install pgadmin4 with the following syntax:
dnf -y update && dnf -y install pgadmin4 |
The dnf utility raise the following MySQL package errors during transaction checking:
Display detailed console log →
Error: Transaction check error: file /usr/bin/mysql conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysql_config_editor conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqladmin conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqlbinlog conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqlcheck conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqldump conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqlimport conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqlpump conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqlshow conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqlslap conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/ibd2sdi conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/innochecksum conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/my_print_defaults conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/myisam_ftdump conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/myisamchk conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/myisamlog conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/myisampack conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/mysql_secure_installation conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/mysql_ssl_rsa_setup conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/mysql_tzinfo_to_sql conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/mysql_upgrade conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/mysqld_pre_systemd conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/perror conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib/systemd/system/mysqld.service conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib/systemd/system/mysqld@.service conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/adt_null.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/auth_socket.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/component_audit_api_message_emit.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/component_log_filter_dragnet.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/component_log_sink_json.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/component_log_sink_syseventlog.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/component_validate_password.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/connection_control.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/ddl_rewriter.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/group_replication.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/ha_example.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/ha_mock.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/innodb_engine.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/keyring_file.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/keyring_udf.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/libmemcached.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/locking_service.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/mypluglib.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/mysql_clone.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/mysql_no_login.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/rewrite_example.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/rewriter.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/semisync_master.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/semisync_slave.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/validate_password.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/version_token.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/sbin/mysqld conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /var/lib/mysql conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /var/lib/mysql-keyring conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/libmysqlclient.so.21.1.17 conflicts between attempted installs of community-mysql-libs-8.0.17-2.fc30.x86_64 and mysql-community-libs-8.0.17-1.fc30.x86_64 |
Since I’m not sure what’s wrong or how to fix it, I’ve put it in my queue of things to get to later. However, when I figure it out I’ll update this blog page with the solution or work around. If anybody knows the fix and would like to share, please let me know.
I removed the pending update packages with the following command:
dnf clean packages |
Then, I simply installed pgadmin4 with the following command:
dnf -y install pgadmin4 |
Display detailed console log →
The pgadmin4 configuration instructions can be found for several Linux versions at Josphat Mutai’s Computing for Geeks web page. On Fedora 30, you need to do the following:
- Install, start, and enable Apache as the
httpdservice unless you already have done that. - Copy the
/etc/httpd/conf.d/pgadmin4.conf.samplefile to/etc/httpd/conf.d/pgadmin4.conf, which is a new file. - Restart the
httpdservice to incorporate thepgadmin4configuration file.
After that, you create the following new directories as the root or sudo user:
/var/lib/pgadmin4/var/log/pgadmin4
You can make both directories with a single mkdir command, like:
mkdir -p /var/lib/pgadmin4 /var/log/pgadmin4 |
As the root or sudo user, change the ownership of these two directories to the apache user with the following syntax:
chown -R apache:apache /var/lib/pgadmin4 /var/log/pgadmin4 |
You add the following four statements to the config_distro.py file in the /usr/lib/python3.7/site-packages/pgadmin4-web directory as the root or sudo user:
LOG_FILE = '/var/log/pgadmin4/pgadmin4.log' SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db' SESSION_DB_PATH = '/var/lib/pgadmin4/sessions' STORAGE_DIR = '/var/lib/pgadmin4/storage' |
You need to setup the pgadmin user with the following python3 command:
python3 /usr/lib/python3.7/site-packages/pgadmin4-web/setup.py |
Enter the following values, a real email address and a password twice:
NOTE: Configuring authentication for SERVER mode. Enter the email address and password to use for the initial pgAdmin user account: Email address: admin@example.com Password: your_password Retype password: your_password pgAdmin 4 - Application Initialisation ====================================== |
Assuming you have an enabled firewall, you need to issue the following two commands as the root or sudo user:
rirewall-cmd --permanent --add-service=http firewall-cmd --reload |
You invoke pgAdmin4 from within a browser window with the following URL for a stand alone workstation (for a workstation on a DNS network you would enter pgadmin.domain.domain_type in lieu of localhost):
pgadmin/localhost/pgadmin4 |
You most likely will encounter an Internal Server Error, the recommended fix is reputed to be:
ausearch -c 'httpd' --raw | audit2allow -M my-httpd semodule -X 300 -i my-httpd.pp |
It didn’t work for me. At the end of the process, I have an Internal Server Error. It is something that I’ll try to fix next. The actual error message:
Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator at root@localhost to inform them of the time this error occurred, and the actions you performed just before this error. More information about this error may be available in the server error log. |
If somebody figures out the last step before I do, that’s great. Let me and everybody else know the mystery.
On a positive note, the pgadmin4 package provided the psycopg2 library. I had looked for it as a psycopg2 package but it is in python3-psycopg2 package.
Postgres & Sequences
Many developers already know about the change from int to serial in Postgres. Serial is simpler because it automatically makes the column not null and links it to a sequence. The sequence is created with the following naming convention:
table_name_column_name_seq |
The Postgres serial type always has a start with value of 1. Though, you can alter it immediately after creating the table.
You can create a serial surrogate key column like this:
CREATE TABLE new_hire ( new_hire_id SERIAL CONSTRAINT new_hire_pk PRIMARY KEY , first_name VARCHAR(20) NOT NULL , middle_name VARCHAR(20) , last_name VARCHAR(20) NOT NULL , hire_date DATE NOT NULL , UNIQUE(first_name, middle_name, hire_date)); |
If you want the sequence to start with 1001, you can ALTER the sequence after creating the table. Before you change the sequence staring value, you may want to confirm the automatic name assigned to the sequence. You can do that with the following query:
SELECT pg_get_serial_sequence('new_hire','new_hire_id'); |
It should return:
pg_get_serial_sequence --------------------------------- public.new_hire_new_hire_id_seq (1 row) |
Here’s the syntax for changing the current starting value of a Postgres sequence:
ALTER SEQUENCE new_hire_new_hire_id_seq RESTART WITH 1001; |
If you want more control, you still have it with the older syntax. The older way requires that you create the sequence before you create the table, like this:
CREATE SEQUENCE new_hire_s START WITH 1001; |
Then, you create the new_hire table like this:
CREATE TABLE new_hire ( new_hire_id INT NOT NULL DEFAULT NEXTVAL('new_hire_s') , first_name VARCHAR(20) NOT NULL , middle_name VARCHAR(20) , last_name VARCHAR(20) NOT NULL , hire_date DATE NOT NULL , PRIMARY KEY(new_hire_id) , UNIQUE(first_name, middle_name, hire_date)); |
You probably will test this from a file because tables are tedious type and usually much larger than the sample new_hire table. If you wonder how to do that, typing help from the psql> prompt will not reveal it. The key is the \i command, like this:
\i new_hire.sql |
Unfortunately, the psql> command-line shell does not allow for embedding a means to log the activity of your script file. You must do that from the Linux command-line through redirecting the console to a log file.
The rules are pretty simple for logging file results:
-a, --echo-all echo all input from script -b, --echo-errors echo failed commands -e, --echo-queries echo commands sent to the server |
Here’s an example connecting to a videodb database with a student user:
psql -d videodb -U student -W -f postgres_tables.sql -a &> postgres_tables.txt |
As always, I hope this helps those looking for how to solve the puzzle of a new environment.
Postgres 11 Video DB
Installing PostgreSQL 11 on Fedora, Version 30, requires an update to my previous instructions to create a sandboxed user. A sandboxed user can only access a non-data dictionary database with a password. In the real world, rather than a personal test instance you would configure users to include aspects of networking. However, this post is only showing you how to connect from the local server.
This post builds on my PostgreSQL Installation blog post and shows you how to create a tablespace, database, role, and user. It also shows you how to change the default configuration for how users connect to the database.
The following steps create a tablespace, database, role, and user:
- Create tablespace
The directory for the data dictionary changes with PostgreSQL has changed. You can find it with the following command:
postgres=# show data_directory; |
This will return the following:
data_directory ------------------------ /var/lib/pgsql/11/data (1 row) |
You need to create a physical videoDB subdirectory in the /var/lib/pgsql/11 directory. You can use the following syntax from the /var/lib/pgsql/11 directory to create the videoDB subdirectory:
mkdir videoDB |
- Create Tablespace
You can create a video_db tablespace with the following syntax:
CREATE TABLESPACE video_db OWNER postgres LOCATION '/var/lib/pgsql/11/videoDB'; |
This will return the following:
CREATE TABLESPACE |
You can query whether you successfully create the video_db tablespace with the following:
SELECT * FROM pg_tablespace; |
It should return the following:
spcname | spcowner | spcacl | spcoptions ------------+----------+--------+------------ pg_default | 10 | | pg_global | 10 | | video_db | 10 | | (3 rows) |
- Create a Database
You can create a videodb database with the following syntax:
CREATE DATABASE videodb WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = video_db LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1; |
You can verify the creation of the videodb with the following command:
postgres# \l |
It should show you a display like the following:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
videodb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | dba=CTc/postgres
(4 rows) |
Then, you can assign comment to the database with the following syntax:
COMMENT ON DATABASE videodb IS 'Video Database'; |
- Create a Role, Grant, and User
In this section you create a dba role, grant privileges on a videodb database to a role, and create a user with the role that you created previously with the following three statements. There are three steps in this sections.
- The first step creates a
dbarole:CREATE ROLE dba WITH SUPERUSER;
- The second step grants all privileges on a
videodbdatabase to adbarole:GRANT ALL PRIVILEGES ON DATABASE videodb TO dba;
- The third step creates a
studentuser with thedbarole:CREATE USER student WITH ROLE dba ENCRYPTED PASSWORD 'student';
- It is possible that you may (and should if this is a new instance you are building) encounter an error when you try to connect as a sandboxed user. The syntax to connect as the student user is:
psql -d videodb -U student -W
You may encounter this error:
psql: FATAL: Peer authentication failed for user "student"
You can fix this in PostgreSQL 11 by changing the user access parameters in the
pg_hba.confconfiguration file. The file is found in the/var/lib/pgsql/11/datadirectory and you need to edit it as therootorpostgresuser.The default entry is:
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer
You should replace it with the following:
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local postgres all peer local videodb student md5
After you save those changes in the
pg_hba.conffile, you need to restart the PostgreSQL (postgresql-11) service. You can do that with the following command as therootuser:service postgresql-11 restart
- Connect to the
videodbas thestudentuser
Once the postgresql-11 service is restarted, you can connect with the sandboxed student user with this syntax:
psql -d videodb -U student -W |
If you did everything correctly, you should see the following after correctly providing the student password for the student user:
psql (11.4, server 11.5) Type "help" for help. videodb=> |
After connecting to the videodb database, you can query the current database, like
SELECT current_database(); |
It should return the following:
current_database ------------------ videodb (1 row) |
This has shown you how to create a videodb database, dba role, and student user.
PostgreSQL on Fedora 30
Installing PostreSQL 11 on Fedora 30 wasn’t straight forward but there were some instructions that helped. The first step requires you to update the yum repository, like this as the root user:
rpm -Uvh https://yum.postgresql.org/11/fedora/fedora-30-x86_64/pgdg-fedora-repo-latest.noarch.rpm |
Then, you install the PostgreSQL with this command as the root user:
dnf install postgresql11-server |
After installing the PostreSQL Server I got a few errors with the symbolic links failing to resolve in the log files. Then, I realized they only failed to create symbolic links because the fresh installation deploys executables directly to the /usr/bin directory.
Display detailed console log →
Retrieving https://yum.postgresql.org/11/fedora/fedora-30-x86_64/pgdg-fedora-repo-latest.noarch.rpm warning: /var/tmp/rpm-tmp.MD4lRU: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY Verifying... ################################# [100%] Preparing... ################################# [100%] Updating / installing... 1:pgdg-fedora-repo-42.0-4 ################################# [100%] [root@localhost ~]# dnf install postgresql11-server PostgreSQL 11 30 - x86_64 215 kB/s | 585 kB 00:02 PostgreSQL 10 30 - x86_64 199 kB/s | 541 kB 00:02 PostgreSQL 9.6 30 - x86_64 295 kB/s | 515 kB 00:01 PostgreSQL 9.5 30 - x86_64 179 kB/s | 495 kB 00:02 PostgreSQL 9.4 30 - x86_64 269 kB/s | 469 kB 00:01 Last metadata expiration check: 0:00:01 ago on Mon 19 Aug 2019 02:25:56 AM MDT. Dependencies resolved. ================================================================================================================== Package Architecture Version Repository Size ================================================================================================================== Installing: postgresql11-server x86_64 11.5-1PGDG.f30 pgdg11 4.8 M Installing dependencies: postgresql11 x86_64 11.5-1PGDG.f30 pgdg11 1.7 M postgresql11-libs x86_64 11.5-1PGDG.f30 pgdg11 374 k Transaction Summary ================================================================================================================== Install 3 Packages Total download size: 6.9 M Installed size: 32 M Is this ok [y/N]: y Downloading Packages: (1/3): postgresql11-libs-11.5-1PGDG.f30.x86_64.rpm 213 kB/s | 374 kB 00:01 (2/3): postgresql11-11.5-1PGDG.f30.x86_64.rpm 698 kB/s | 1.7 MB 00:02 (3/3): postgresql11-server-11.5-1PGDG.f30.x86_64.rpm 1.5 MB/s | 4.8 MB 00:03 ------------------------------------------------------------------------------------------------------------------ Total 2.2 MB/s | 6.9 MB 00:03 warning: /var/cache/dnf/pgdg11-cde8ad453ae6cd5b/packages/postgresql11-11.5-1PGDG.f30.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY PostgreSQL 11 30 - x86_64 1.6 MB/s | 1.7 kB 00:00 Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>" Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8 From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Is this ok [y/N]: y Key imported successfully Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : postgresql11-libs-11.5-1PGDG.f30.x86_64 1/3 Running scriptlet: postgresql11-libs-11.5-1PGDG.f30.x86_64 1/3 Installing : postgresql11-11.5-1PGDG.f30.x86_64 2/3 Running scriptlet: postgresql11-11.5-1PGDG.f30.x86_64 2/3 failed to link /usr/bin/psql -> /etc/alternatives/pgsql-psql: /usr/bin/psql exists and it is not a symlink failed to link /usr/bin/clusterdb -> /etc/alternatives/pgsql-clusterdb: /usr/bin/clusterdb exists and it is not a symlink failed to link /usr/bin/createdb -> /etc/alternatives/pgsql-createdb: /usr/bin/createdb exists and it is not a symlink failed to link /usr/bin/createuser -> /etc/alternatives/pgsql-createuser: /usr/bin/createuser exists and it is not a symlink failed to link /usr/bin/dropdb -> /etc/alternatives/pgsql-dropdb: /usr/bin/dropdb exists and it is not a symlink failed to link /usr/bin/dropuser -> /etc/alternatives/pgsql-dropuser: /usr/bin/dropuser exists and it is not a symlink failed to link /usr/bin/pg_dump -> /etc/alternatives/pgsql-pg_dump: /usr/bin/pg_dump exists and it is not a symlink failed to link /usr/bin/pg_dumpall -> /etc/alternatives/pgsql-pg_dumpall: /usr/bin/pg_dumpall exists and it is not a symlink failed to link /usr/bin/pg_restore -> /etc/alternatives/pgsql-pg_restore: /usr/bin/pg_restore exists and it is not a symlink failed to link /usr/bin/reindexdb -> /etc/alternatives/pgsql-reindexdb: /usr/bin/reindexdb exists and it is not a symlink failed to link /usr/bin/vacuumdb -> /etc/alternatives/pgsql-vacuumdb: /usr/bin/vacuumdb exists and it is not a symlink Running scriptlet: postgresql11-server-11.5-1PGDG.f30.x86_64 3/3 Installing : postgresql11-server-11.5-1PGDG.f30.x86_64 3/3 Running scriptlet: postgresql11-server-11.5-1PGDG.f30.x86_64 3/3 Verifying : postgresql11-11.5-1PGDG.f30.x86_64 1/3 Verifying : postgresql11-libs-11.5-1PGDG.f30.x86_64 2/3 Verifying : postgresql11-server-11.5-1PGDG.f30.x86_64 3/3 Installed: postgresql11-server-11.5-1PGDG.f30.x86_64 postgresql11-11.5-1PGDG.f30.x86_64 postgresql11-libs-11.5-1PGDG.f30.x86_64 Complete! |
After installing the PostgreSQL Server 11, you need to initialize the database. You use the following command to initialize the database as the root user:
/usr/pgsql-11/bin/postgresql-11-setup initdb |
It should return the following:
Initializing database ... OK |
The PostgreSQL Server 11 database installs in the /var/lib/pgsql/11/data directory. You can list the contents, which should mirror these:
drwx------. 5 postgres postgres 4096 Aug 19 02:45 base drwx------. 2 postgres postgres 4096 Aug 19 02:45 global drwx------. 2 postgres postgres 4096 Aug 19 02:45 log drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_commit_ts drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_dynshmem -rw-------. 1 postgres postgres 4269 Aug 19 02:45 pg_hba.conf -rw-------. 1 postgres postgres 1636 Aug 19 02:45 pg_ident.conf drwx------. 4 postgres postgres 4096 Aug 19 02:45 pg_logical drwx------. 4 postgres postgres 4096 Aug 19 02:45 pg_multixact drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_notify drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_replslot drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_serial drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_snapshots drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_stat drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_stat_tmp drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_subtrans drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_tblspc drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_twophase -rw-------. 1 postgres postgres 3 Aug 19 02:45 PG_VERSION drwx------. 3 postgres postgres 4096 Aug 19 02:45 pg_wal drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_xact -rw-------. 1 postgres postgres 88 Aug 19 02:45 postgresql.auto.conf -rw-------. 1 postgres postgres 23895 Aug 19 02:45 postgresql.conf |
You need to enable and start the postgresql-11.service with the following commands as the root user:
systemctl enable postgresql-11.service systemctl start postgresql-11.service |
You can login to test the configuration as the root user, like this:
su - postgres -c "psql" |
You will see something like this:
psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql) psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql) psql (11.4, server 11.5) Type "help" for help. postgres=# |
The error message appear to indicate there’s a bug (at least Bug #15798 is similar). Specifically, a missing function in the libya.so.5 library. Determining that impact took some time because of what else I had in the queue.
The Bug (at least Bug #15798 gave part of the fix. The problem was figuring out where the LD_LIBRARY_PATH should really be set, and I sorted that out.
If you inspect the postgres home directory (/var/lib/pgsql), you’ll find the following .bash_profile file:
[ -f /etc/profile ] && source /etc/profile PGDATA=/var/lib/pgsql/11/data export PGDATA # If you want to customize your settings, # Use the file below. This is not overridden # by the RPMS. [ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile |
Then, you create the .pgsql_profile file in that directory. You should put the following command in the file:
export set LD_LIBRARY_PATH=/usr/lib64 needle < /dev/null |
Then, when you login as the postgres user:
psql -U postgres |
You will see:
psql (11.4, server 11.5) Type "help" for help. postgres=# |
or, you can login to test the configuration as the root user with the syntax used earlier:
su - postgres -c "psql" |
You need to put the LD_LIBRARY_PATH environment variable in the .bashrc of users who will access the PostgreSQL 11 database.
As always, I hope this helps those working it from the ground up.
PostgreSQL Calling File
Somebody asked: How do you run a script file from PostgreSQL’s psql prompt? I created two files to answer the question. Here are the two files:
Static File
SELECT 'Hello World!'; |
Dynamic File
SELECT 'Hello ['||current_user||']!'; |
It’s a simple solution, you put a \i or \include before the script file name, like:
\i helloworld.sql |
It outputs:
?column? -------------- Hello World! |
or
\include hellowhom.sql |
It outputs:
?column? ------------------ Hello [student]! |
I hope this helps those trying to call SQL script files from an interactive psql session.
PostgreSQL Identity Columns
It’s interesting to see the way different databases implement automatic numbering. Oracle Database 12c is the closest to PostgreSQL in some significant ways. However, its probably more accurate to say Oracle Database 12c copied PostgreSQL’s implementation. At least, that’s my conjecture because Oracle added a way to reset the START WITH value of the indirect sequence. However, I prefer the MySQL approach because the automatic numbering sequence is a property of the table and a simple clause of the CREATE TABLE statement.
Both PostgreSQL and Oracle Database 12c implement automatic numbering as indirect sequences. Indirect sequences are those created by a table when you designate a column as an identity column in Oracle or as a serial column in PostgreSQL. The difference is that PostgreSQL doesn’t provide a syntax version inside the CREATE TABLE semantic.
MySQL provides such syntax. You set an auto numbering column in MySQL by appending the AUTO_INCREMENT clause to the table creation statement when you want it to start with a number other than 1, like this:
CREATE TABLE auto ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , text_field VARCHAR(30) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8; |
Oracle disallows you to changing a sequence created as a background activity of the CREATE TABLE statement; and Oracle disallows you dropping an indirect sequence without changing the table that created it, which is exactly how they handle indexes created for unique constraints. Unfortunately, Oracle also disallows altering the START WITH value of any sequence.
If you want to change the START WITH value on an Oracle Database 12c indirect sequence, you must export the table, drop the table, and recreate the table with a new START WITH value before importing the data back into the table. The syntax for setting an IDENTITY column value higher than 1 is:
CREATE TABLE auto ( auto_id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1001) , text_field VARCHAR2(30) , CONSTRAINT auto_pk PRIMARY KEY (auto_id)); |
You can only create a PostgreSQL table with automatic numbering by using the SERIAL data type, which always sets the initial value to 1. You can reset the SERIAL sequence value in PostgreSQL with the ALTER statement. Unlike Oracle Database 12c, PostgreSQL does let you modify the START WITH value of any sequence. The trick is understanding how to find the sequence name. The name is always the combination of the table name, an underscore, an id string, an underscore, and a seq string. This behavior makes a great case for choosing id as the name of any auto numbering columns in a table.
CREATE TABLE auto ( id SERIAL CONSTRAINT auto_pk PRIMARY KEY , text_field VARCHAR(30)); ALTER SEQUENCE auto_id_seq RESTART WITH 1001; |
You can see the table and assigned sequence with the following command in PostgreSQL:
\d+ auto |
It should display:
Table "public.auto"
Column | Type | Modifiers | Storage | Stats target | Description
------------+-----------------------+---------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('auto_id_seq'::regclass) | plain | |
text_field | character varying(30) | | extended | |
Indexes:
"auto_pk" PRIMARY KEY, btree (id)
Has OIDs: no |
As always, I hope this helps those trying to sort through how to start identity columns above the initial value of 1.
PostgreSQL Composites
PostgreSQL like Oracle supports record data types but unlike Oracle, PostgreSQL doesn’t support collections of record data types. Here’s an example of how to define a PostgreSQL composite data type, and how to use it as a column’s data type.
CREATE TYPE address_type AS ( street_address VARCHAR , city VARCHAR , state VARCHAR , zip_code VARCHAR ); |
Then, you define an ADDRESS table, like:
CREATE TABLE address ( address_id SERIAL , address_struct ADDRESS_TYPE ); |
You can now insert rows like:
-- Insert the first row. INSERT INTO address ( address_struct ) VALUES (('52 Hubble Street','Lexington','KY','40511-1225')); -- Insert the second row. INSERT INTO address ( address_struct ) VALUES (('54 Hubble Street','Lexington','KY','40511-1225')); |
Then, you can query them like this:
SELECT * FROM address; |
It returns:
address_id | address_struct
------------+----------------------------------------------
1 | ("52 Hubble Street",Lexington,KY,40511-1225)
2 | ("54 Hubble Street",Lexington,KY,40511-1225)
(2 rows) |
You must use parentheses around the ADDRESS_STRUCT column to query individual items, like:
SELECT address_id , (address_struct).street_address , (address_struct).city , (address_struct).state , (address_struct).zip_code FROM address; |
It returns output like a table:
address_id | street_address | city | state | zip_code
------------+------------------+-----------+-------+------------
1 | 52 Hubble Street | Lexington | KY | 40511-1225
2 | 54 Hubble Street | Lexington | KY | 40511-1225
(2 rows) |
While you can define a table that holds an array of a composite type, there’s no syntax that appears to work with an array of a composite type. I hope this helps those interested in implementing record structures in PostgreSQL.
PostgreSQL Auto IDs
PostgreSQL’s approach to automatic numbering is as simple as Oracle but different than MySQL, and Microsoft SQL Server. For example, you have a two-step process with Oracle, PostgreSQL, MySQL, and Microsoft SQL Server. First, you create an Oracle table with the GENERATED AS IDENTITY clause, a PostgreSQL table with the SERIAL data type, a MySQL table with the AUTO_INCREMENT clause, and a Microsoft SQL Server table with the IDENTITY(1,1) clause. Then, you need to write an INSERT statement for Oracle, MySQL, or Microsoft SQL Server like:
- Oracle’s
INSERTstatement excludes the auto-incrementing column from the list of columns or provides aNULLvalue in theVALUES-list. You can then assign theRETURNING INTOresult from anINSERTstatement to a session-level (bind) variable. - MySQL’s
INSERTstatement excludes the auto-incrementing column from the list of columns or provides aNULLvalue in theVALUES-list. You can then assign theLAST_INSERT_ID()function value to a session-level variable, and populate a foreign key column. - Microsoft SQL Server’s
INSERTstatement excludes the auto-incrementing column from the list of columns or provides aNULLvalue in theVALUES-list. You can then assign theSCOPE_IDENTITY()function’s value to a session-level variable, and populate a foreign key column.
PostgreSQL differs because it works differently between the SQL and PL/pgSQL contexts. Let’s look at how you link the insert of data into two tables in both contexts.
The following PostgreSQL syntax creates an ADDRESS table with an auto incrementing ADDRESS_ID column that uses a SERIAL data type, which acts like an auto numbering column:
/* Create a customer table. */ CREATE TABLE customer ( customer_id SERIAL CONSTRAINT customer_pk PRIMARY KEY , first_name VARCHAR(20) , last_name VARCHAR(20)); /* Create an address table. */ CREATE TABLE address ( address_id SERIAL CONSTRAINT address_pk PRIMARY KEY , customer_id INTEGER , street_address VARCHAR(40) , city VARCHAR(30) , state VARCHAR(8) , zip_code VARCHAR(10)); |
If you want to insert one row into the CUSTOMER table and a related row in the ADDRESS table. You have two possible approaches. One works in both the SQL and PL/pgSQL contexts. That mechanism requires you to use a scalar subquery to capture the foreign key value of the CUSTOMER_ID column in the ADDRESS table, like this:
/* Insert into customer table. */ INSERT INTO customer ( first_name, last_name ) VALUES ('F. Scott','Fitzgerald'); /* Insert into address table. */ INSERT INTO address ( customer_id , street_address , city , state , zip_code ) VALUES ((SELECT customer_id FROM customer WHERE first_name = 'F. Scott' AND last_name = 'Fitzgerald') ,'599 Summit Avenue' ,'St. Paul' ,'Minnesota' ,'55102'); |
The RETURNING INTO clause of PostgreSQL only works in a PL/pgSQL context, like this:
DO $$ DECLARE lv_customer_id INTEGER; BEGIN /* Insert into customer table. */ INSERT INTO customer ( first_name, last_name ) VALUES ('Madeleine','Smith') RETURNING customer_id INTO lv_customer_id; /* Insert into address table. */ INSERT INTO address ( customer_id , street_address , city , state , zip_code ) VALUES ( lv_customer_id ,'7 Blythswood Square' ,'Glasgow' ,'Scotland' ,'G2 4BG'); /* Manage any exceptions. */ EXCEPTION WHEN OTHERS THEN RAISE NOTICE '% %', SQLERRM, SQLSTATE; END$$; |
You query the auto generated values and data from the INSERT statement to the CUSTOMER table with a scalar subquery against the natural key (the FIRST_NAME and LAST_NAME columns) from the ADDRESS table. The following is an example of such a query:
SELECT * FROM customer c INNER JOIN address a ON c.customer_id = a.customer_id; |
It returns:
customer_id | first_name | last_name | address_id | customer_id | street_address | city | state | zip_code
-------------+------------+------------+------------+-------------+---------------------+----------+-----------+----------
1 | F. Scott | Fitzgerald | 1 | 1 | 599 Summit Avenue | St. Paul | Minnesota | 55102
2 | Madeleine | Smith | 2 | 2 | 7 Blythswood Square | Glasgow | Scotland | G2 4BG
(2 rows) |
My take is that the RETURNING column_value INTO local_value clause is a better approach than using Oracle’s .NEXTVAL and .CURRVAL values. I also think the RETURNING INTO clause is a better approach than using MySQL’s LAST_INSERT_ID() or Microsoft SQL Server’s SCOPE_IDENTITY().
Initially, I felt it was odd that the PostgreSQL disallows the RETURNING INTO clause in a SQL context, because it allows the syntax in a PL/pgSQL context. After some reflection the choice makes more sense because most developers work within a procedural context when they use transactions across two or more tables. PL/pgSQL is PostgreSQL’s procedural context from managing transactions across two or more tables.
As always, I hope this helps.
A PL/pgSQL Function
Somebody wanted to know how to write a basic PostgreSQL PL/pgSQL function that returned a full name whether or not the middle name was provided. That’s pretty simple. There are principally two ways to write that type of concatenation function. One uses formal parameter names and the other uses positional values in lieu of the formal parameter names.
The two ways enjoy two techniques (SQL language and PL/pgSQL language), which gives us four possible solutions. I’ve also provided a conditional drop statement for the full_name function. If you’re new to PostgreSQL the DROP statement might make you scratch your head because you’re wondering why you need to use the formal parameter list. The DROP statement needs the parameter list because PostgeSQL lets you overload schema/database functions and procedures.
The code is for a named parameter lists using the SQL language is:
DROP FUNCTION IF EXISTS full_name ( IN pv_first_name text , IN pv_middle_name text , IN pv_full_name text); CREATE FUNCTION full_name ( IN pv_first_name text , IN pv_middle_name text , IN pv_last_name text , OUT pv_full_name text) AS 'SELECT pv_first_name || CASE WHEN pv_middle_name IS NOT NULL THEN '' '' || pv_middle_name || '' '' ELSE '' '' END || pv_last_name' LANGUAGE SQL; |
The code is for a positional parameter lists using the SQL language is:
DROP FUNCTION IF EXISTS full_name ( IN text , IN text , IN text); CREATE FUNCTION full_name ( IN text , IN text , IN text , OUT text) AS 'SELECT $1 || CASE WHEN $2 IS NOT NULL THEN '' '' || $2 || '' '' ELSE '' '' END || $3' LANGUAGE SQL; |
You would re-write the function in the PL/pgSQL language as follows (please note the named parameter list):
CREATE FUNCTION full_name ( IN pv_first_name text , IN pv_middle_name text , IN pv_last_name text) RETURNS text AS $$ DECLARE lv_output text; BEGIN IF pv_middle_name IS NULL THEN lv_output = CONCAT(pv_first_name, N' ', pv_last_name); ELSE lv_output = CONCAT(pv_first_name, N' ', pv_middle_name, N' ', pv_first_name); END IF; RETURN lv_output; END $$ LANGUAGE plpgsql IMMUTABLE; |
You can test either version of the program with the following two queries from the pseudo table dual, which isn’t require in the SELECT statement:
SELECT full_name('Henry',NULL,'Pym') AS "Ant-Man" UNION ALL SELECT full_name('Henry','''Hank''','Pym') AS "Ant-Man"; |
It prints:
Ant-Man text ---------------- Henry Pym Henry 'Hank' Pym |
As always, I hope this helps those looking for how to accomplish a concatenation function in PostgreSQL.
Subscribe via RSS