Common PostgreSQL Commands

These are some common PostgreSQL commands for things that I need to remember.

Opening session

This assumes linux authentication (default for Debian Linux).

Run the psql command as the postgres user:

sudo -u postgres psql
su postgres

Open a database with \c DBNAME:

postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".

Showing Table Info

Use \dt to show table information (same as show tables; from MySQL):

mydb=# \dt
             List of relations
 Schema |       Name       | Type  | Owner
 public | change           | table | myuser 
 public | changeset        | table | myuser 
 public | config           | table | myuser 

Insert Select

Select queries can be used in INSERT queries:

INSERT into table (id, username, description) VALUES ('1', (select user from public.user where id = '1'), 'some description');

Load queries from file

Use \i /tmp/filename to execute all queries from the file.

Selecting from tables that conflict with PGSQL naming

Some tables have a name that conflicts with internal PostgreSQL naming, as an example:

mydb=# select user_id from user;
ERROR:  column "user_id" does not exist
LINE 1: select user_id from user;
mydb=# select * from user;
(1 row)

Instead, first run \dt to get the schema name of the table then prefix that to the table name to select from. The correct query in this case would be:

mydb=# select * from public.user;
 id |         uid         |             name             |                        email 
  1 | someone             | Me                           |

List databases

Use \list to get a list of available databases.

Leave a Reply

Your email address will not be published. Required fields are marked *