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
  OR
su postgres
psql

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;
 current_user
--------------
 postgres
(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                           | my@email.com
.....

List databases

Use \list to get a list of available databases.

Leave a Reply

Your email address will not be published.