Postgres and psql for beginners
Computing
Actually I am not sure whether I can put myself in the beginners pot, having run Postgresql for many years and built several products and service upon it. However a round of using PSQL for common use cases is needed I think. So this basically is for that usual situation where you have logged in to the server that postgres is running on and have access to the user postgres or have the rights to use psql.
Startup psql command line interface
psql
Meta commands
Meta commands are excellent short commands processed by psql itself. Some are shortcuts to longer SQL statements that get executed, others are system commands such as change directory.
List databases on the system
<br />
\l<br />
List of databases<br />
Name | Owner | Encoding | Collation | Ctype | Access privileges<br />
-----------+----------+----------+-------------+-------------+-----------------------<br />
database1 | tim | UTF8 | en_US.UTF-8 | en_US.UTF-8 |<br />
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |<br />
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres<br />
: postgres=CTc/postgres<br />
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres<br />
: postgres=CTc/postgres<br />
database2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |<br />
(5 rows)<br />
List users on the system
<br />
postgres=# \du<br />
List of roles<br />
Role name | Attributes | Member of<br />
-----------+-------------+-----------<br />
tim | Superuser | {}<br />
: Create role<br />
: Create DB<br />
postgres | Superuser | {}<br />
: Create role<br />
: Create DB<br />
another | | {}<br />
List help
<br />
\?<br />
List help
<br />
\?<br />
Change to database called “databasename”
<br />
\c databasename<br />