A PostgreSQL console cheat sheet

Learn to move your first steps with the PostgreSQL console with this cheat sheet.

A PostgreSQL command line cheat sheet

PostgreSQL is a fantastic database isn't it? However for all those starting out, PostgreSQL console could be confusing to say the least.

Let's see in this cheat sheet what are the basic commands for moving your first steps it the PostgreSQL console.

A PostgreSQL console cheat sheet: first steps

First of all you want to login into the console. To do so become "postgres" from the command line with:

sudo su - postgres

Once inside enter the PostgreSQL console with:

psql

If you see this prompt you're in:

postgres=#

A PostgreSQL console cheat sheet: interacting with databases

Once inside the PostgreSQL console you can interact with databases. From now on I'll omit the prompt postgres=#. To create a new database:

CREATE DATABASE family;

To list all the databases in the server:

\l

In addition to the shortcut there is also the extended version:

\list

To delete a database (warning zone):

DROP DATABASE family;

To connect to a database run:

\c family

or:

\connect family

and you should see:

You are now connected to database "family" as user "postgres"

Note that outside the PostgreSQL console you can also create a database with createdb:

createdb my_test_db

A PostgreSQL console cheat sheet: interacting with tables

Once connected to a database you can list all its tables with:

\dt

This command will give you a representation of all the tables in the database:

          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | daughter | table | postgres
 public | mother   | table | postgres
(2 rows)

My example assumes a simple database with two tables connected through a many to one relationship. To describe a table, that is, to see its columns, run:

\d table_name

This command will give you a complete description of the table:

                                      Table "public.daughter"
   Column   |          Type          | Collation | Nullable |               Default                
------------+------------------------+-----------+----------+--------------------------------------
 id         | smallint               |           | not null | nextval('daughter_id_seq'::regclass)
 first_name | character varying(254) |           | not null | 
 last_name  | character varying(254) |           | not null | 
 mother_id  | integer                |           | not null | 
Indexes:
    "daughter_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_mother_id" FOREIGN KEY (mother_id) REFERENCES mother(id)

You can see every column in the table with the corresponding type. If there is any primary or foreign key you'll see them as well.

A PostgreSQL console cheat sheet: roles

Database users can interact, modify, update, and see databases. In PostgreSQL they're called roles. To create a new user from the PostgreSQL console run:

CREATE ROLE my_user WITH LOGIN PASSWORD 'my_password';

PostgreSQL roles may own one or more database. Once you create a role you can assign an entire database to it:

CREATE DATABASE my_database WITH OWNER my_user;

When you're done with databases, roles, and tables you can finally exit the console with:

\q

A PostgreSQL console cheat sheet: getting help

The PostgreSQL console has a lot more commands, and there are also a lot of SQL instructions in the PostgreSQL dialect. To see the complete list of all the SQL command available in PostgreSQL you can run:

\h

To see a complete list of PostgreSQL command run:

\?

Need to refresh your JavaScript skills? Check out The Little JavaScript Book!

I WANT THE BOOK
Valentino Gagliardi

Hi! I’m Valentino! Educator and consultant, I help people learning to code with on-site and remote workshops. Looking for JavaScript and Python training? Let’s get in touch!