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.


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
\qquit psql
\llist database names
\c dbnameconnect to database
\dn *display schema names
\dt schema.*display table names in schema
\d tablenamedisplay column names in table
\dudisplay user names
\cd dirnamechange local directory
\i sqlfileinclude 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;
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

HTML hand-crafted by Roger Bowler
Last updated 17 Sep 2018