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.