Roger Bowler’s PostgreSQL Tips for Systems Programmers

Postgres, PostgreSQL, psql, pgsql ... take your pick. Here are some handy commands if (like me) you cannot remember what you have to do to issue SQL commands against a Postgres table.

Index

• How to start the PostgreSQL server
• How to start an interactive SQL session
• Some useful psql commands
• How to create or delete a user
• How to create a database
• How to create a table
• How to access tables in a non-public schema
• How to update the PostgreSQL configuration
• How to handle EBCDIC columns in PostgreSQL tables
• How to move a PostgreSQL database to another system

PostgreSQL Tips

How to start the PostgreSQL server

sudo systemctl start postgresql

How to start an interactive SQL session

sudo -u postgres psql -d databasename

Some useful psql commands

\?	help
\q	quit psql
\l	list database names
\c dbname	connect to database
\dn *	display schema names
\dt schema.*	display table names in schema
\d tablename	display column names in table
\du	display user names
\cd dirname	change local directory
\i sqlfile	include SQL commands from a file

How to create or delete a user

sudo -u postgres createuser username
sudo -u postgres dropuser username

How to create a database

create database databasename;
alter database databasename owner to username;
grant connect on database databasename to username;
drop database databasename;

How to create a table

create table tablename
  (columnname CHAR(8), columnname VARCHAR,
  columnname INT, columnname BYTEA);
grant select on table tablename to username;
insert into tablename (columnname,
  columnname, columnname, columnname)
  values ('text', 'text', nnn, '\xC1C2C340');
select * from tablename;
update tablename set columnname = value
  where columnname = value;
drop table tablename;

How to access tables in a non-public schema

set search_path to schemaname, public;
show search_path;

or specify schemaname.tablename instead of tablename.

How to update the PostgreSQL configuration

sudo -u postgres psql -c "show config_file;"
sudo vi /var/lib/pgsql/data/postgresql.conf
sudo systemctl restart postgresql

How to handle EBCDIC columns in PostgreSQL tables

Preparatory steps

1. Install postgresql11-plpython
2. Create a file called ebcdic.sql containing these commands:

   CREATE OR REPLACE LANGUAGE plpythonu;
   CREATE OR REPLACE FUNCTION ea(IN input_str BYTEA) RETURNS TEXT
   IMMUTABLE AS $$
   from codecs import getdecoder
   codec = getdecoder('cp1140')
   result_str = codec(input_str, r'strict')
   return result_str[0]
   $$ LANGUAGE plpythonu;
   CREATE OR REPLACE FUNCTION ae(IN input_str TEXT, IN length INT) RETURNS BYTEA
   IMMUTABLE AS $$
   from codecs import getencoder
   codec = getencoder('cp1140')
   if length is None:
     str = input_str
   else:
     str = input_str.ljust(length)
   result_str = codec(str, r'strict')
   return result_str[0]
   $$ LANGUAGE plpythonu;
   

3. Add the functions to the default schema:

   sudo -u postgres psql -d databasename -f ebcdic.sql
   

Creating a table with EBCDIC columns

create table dsntab (ebcdic_dsname BYTEA, ebcdic_volser BYTEA);
insert into dsntab (ebcdic_dsname, ebcdic_volser) values
  (ae('SYS1.SVCLIB',44), ae('SYSRES',6)),
  (ae('SYS1.LINKLIB',44), ae('SYSRES',6)),
  (ae('USER.LINKLIB',44), ae('USER01',6));

Displaying EBCDIC columns from a table

select ea(ebcdic_dsname) as dsname, ea(ebcdic_volser) as volser
  from dsntab where ebcdic_volser = ae('SYSRES',6);

How to move a PostgreSQL database to another system

On the originating system

1. Dump the database to a flat file:

   pg_dump -U postgres databasename > mypgdump.sql
   

2. Transfer the flat file to the receiving system:

   scp mypgdump.sql username@ip-addr:
   

On the receiving system

1. Create an empty database:

   sudo -u postgres psql
   drop database if exists databasename;
   create database databasename;
   quit
   

2. Restore the database from the flat file:

   sudo -u postgres psql -d databasename -f mypgdump.sql
   

---