PostgreSQL commands

PostgreSQL.txt

//switch user to postgres
sudo su postgres

//Reach psql
postgres@elcportal-dev:/var/www/local/hafiz/cli$ psql

//psql with params
psql -h hostname -U postgres

//clear screen
ctrl+L

//show  databses
postgres=# \l
                                    List of databases
      Name      |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
----------------+----------+----------+-------------+-------------+-----------------------
 mdl23-ncel-dev | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                |          |          |             |             | postgres=CTc/postgres
 template1      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                |          |          |             |             | postgres=CTc/postgres

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

//Switch to our DB
postgres=# \c mdl23-ncel-dev
You are now connected to database "mdl23-ncel-dev" as user "postgres".

//show tables
mdl23-ncel-dev=# \d

//search table with regexp
mdl23-ncel-dev=# \dt *user*

//Show table schema
mdl23-ncel-dev=# \dS mdl_user;

//pretty display - On/Off - same command
mdl23-ncel-dev=# \x

//Sample query
mdl23-ncel-dev=# SELECT id  FROM "mdl_user" WHERE username='8000000019';

//help
mdl23-ncel-dev=# \?

//exit from other window, eg:schema display
q (same as vi)

//exit from psql
\q