A man coding in front of three monitors and a laptop

How to run PostgreSQL in Docker and interact with DB

Prompted by a clash with uncooperative PostgreSQL databases, this step-by-step guide was designed to help trainee or junior level developers who just started coding or want to reinforce their knowledge with PostgreSQL.

Reading Time80 min

This is a guide on how to set up a PostgreSQL database using Docker, set up a pgAdmin 4 - feature rich Open Source administration and development platform for PostgreSQL using docker -  and interact with the PostgreSQL database using psql - a terminal-based front-end to PostgreSQL.

All of the terminal commands should work in your terminal, provided you have completed the necessary setup steps.

Please note that this guide was made to work on macOs Big Sur v11.6 and requires you to install Docker for macOs beforehand.

Setup PostgreSQL

Pull PostgreSQL docker image

Visit PostgreSQL on hub.dokcer.com page and follow the instructions to pull a PostgreSQL docker image. This can be done with the following command:

docker pull postgres

Command results:

➜  ~ docker pull postgres
Using default tag: latest
latest: Pulling from library/postgres
7d63c13d9b9b: Pull complete
cad0f9d5f5fe: Pull complete
ff74a7a559cb: Pull complete
c43dfd845683: Pull complete
e554331369f5: Pull complete
d25d54a3ac3a: Pull complete
bbc6df00588c: Pull complete
d4deb2e86480: Pull complete
cb59c7cc00aa: Pull complete
80c65de48730: Pull complete
1525521889be: Pull complete
38df9e245e81: Pull complete
380030b85e81: Pull complete
Digest: sha256:eb83331cc518946d8ee1b52e6d9e97d0cdef6195b7bf25323004f2968e91a825
Status: Downloaded newer image for postgres:latest
docker.io/library/postgres:latest

The Postgres docker page contains documentation on image usage.

Run a docker container with PostgreSQL

We can run a docker container using the above pulled (downloaded) image:

docker run --name multiverse -p 5477:5432 -e POSTGRES_DB=c137 -e POSTGRES_USER=rick -e POSTGRES_PASSWORD=plumbus -d postgres

Command results:

➜  ~ docker run --name multiverse -p 5477:5432 -e POSTGRES_DB=c137 -e POSTGRES_USER=rick -e POSTGRES_PASSWORD=plumbus -d postgres
dc5859f3a44778376f357cffb5384a13cbbfaab0648d2446c621abb136a9cacb
➜  ~ docker ps -a
CONTAINER ID   IMAGE      COMMAND                  CREATED          STATUS          PORTS                                       NAMES
dc5859f3a447   postgres   "docker-entrypoint.s…"   31 seconds ago   Up 30 seconds   0.0.0.0:5477->5432/tcp, :::5477->5432/tcp   multiverse
➜  ~ docker images
REPOSITORY   TAG       IMAGE ID       CREATED        SIZE
postgres     latest    d191afba1bb1   22 hours ago   374MB

The container's name is "multiverse", its port is forwarded to "localhost" port "5477". 

Three environment variables are passed to the container's startup, "POSTGRES_D", "POSTGRES_USER" and "POSTGRES_PASSWORD".

These mean that the newly created database inside of the docker Postgres container will be called "c137", there will be a user created called "rick" and the connection password will be "plumbus".

The container is run in detached mode, from the image "postgres".

Please note that these "variables" can change: "multiverse", "5477", "c137", "rick" and "plumbus".

Pull a pgAdmin 4 docker image

Visit pgAdmin 4 on hub.dokcer.com for instructions on how to pull and run a pgAdmin4 in a container. In short, to pull the image run:

docker pull dpage/pgadmin4

Command results:

➜  ~ docker pull dpage/pgadmin4
Using default tag: latest
latest: Pulling from dpage/pgadmin4
a0d0a0d46f8b: Pull complete
7e3f7d6e3d66: Pull complete
e9b8e3ed2c63: Pull complete
9e442dd084d6: Pull complete
35bc5a8ecfcd: Pull complete
2ab0e7f79ab2: Pull complete
ea2943343db4: Pull complete
2260de48639f: Pull complete
8a7bd2cec0d6: Pull complete
3df70e78fff9: Pull complete
72ce4104debb: Pull complete
2f4a5f004843: Pull complete
850fb29807a6: Pull complete
acbad1ac363a: Pull complete
Digest: sha256:e8d18f941264a82c6fbe81ce60503f2b00823a36e571cd383ca1f462b578f691
Status: Downloaded newer image for dpage/pgadmin4:latest
docker.io/dpage/pgadmin4:latest

Run a docker container with pgAdmin 4

We can run a pgAdmin 4 image in a docker container:

docker run --name pg_dashboard -p 5488:80 -e [email protected] -e PGADMIN_DEFAULT_PASSWORD=pickleriick -d dpage/pgadmin4

Command results:

➜  ~ docker run --name pg_dashboard -p 5488:80 -e [email protected] -e PGADMIN_DEFAULT_PASSWORD=pickleriick -d dpage/pgadmin4
c2cbda05b39ba43e2da2bbd86e8dec747c28cb808a84afdc88928959c2ee8046
➜  ~ docker ps -a
CONTAINER ID   IMAGE            COMMAND                  CREATED         STATUS         PORTS                                            NAMES
c2cbda05b39b   dpage/pgadmin4   "/entrypoint.sh"         2 seconds ago   Up 2 seconds   443/tcp, 0.0.0.0:5488->80/tcp, :::5488->80/tcp   pg_dashboard
dc5859f3a447   postgres         "docker-entrypoint.s…"   2 minutes ago   Up 2 minutes   0.0.0.0:5477->5432/tcp, :::5477->5432/tcp        multiverse
➜  ~ docker images
REPOSITORY       TAG       IMAGE ID       CREATED        SIZE
postgres         latest    d191afba1bb1   22 hours ago   374MB
dpage/pgadmin4   latest    226cfd7ea23c   6 days ago     259MB

The container's name is "pg_dashboard", its port is forwarded to a "localhost" port "5488".

Two environment variables are passed to the container's startup, "PGADMIN_DEFAULT_EMAIL" and "PGADMIN_DEFAULT_PASSWORD".

These mean that the newly created pgAdmin 4 web interface inside of the docker container will have a default user with email "[email protected]" and a password "pickleriick".

The container is run in detached mode from the image "dpage/pgadmin4".

Open your browser and navigate to http://localhost:5488/ and you should see the pgAdmin 4 web interface:

pgAdmin 4 web interface

pgAdmin 4 web interface

Here you can log into the pgAdmin4 using the above "email" and "password":
- "[email protected]"
- "pickleriick"
Which should lead you to the pgAdmin 4 dashboard.

pgAdmin 4 dashboard

pgAdmin 4 dashboard

Connect to PostgreSQL

Connect to a PostgreSQL DB inside a docker container using pgAdmin 4

To connect to a PostgreSQL DB inside the docker container, create a new server connection.

A window will appear, you can name your server connection any way you want. In this case, we'll name it "multiverse" (same as the docker container running the PostgreSQL instance, not mandatory - you can provide any name you want here).

pgAdmin 4 create a new server 1

pgAdmin 4 create a new server 1

In the "Connection" tab enter the:

  • PostgreSQL host. In this case, it is "docker.for.mac.localhost"
  • PostgreSQL port, which is in this case "5477" (port forwarded port)
  • database (under maintenance database) "c137"
  • username "rick" (postgreSQL username)
  • password "plumbus" (user rick's password, for PostgreSQL)
  • Tick the "Save password?" box

This last step is not mandatory but it’s best if you do it once and for all. 

pgAdmin 4 create a new server 2

pgAdmin 4 create a new server 2

pgAdmin 4 create a new server 3

pgAdmin 4 create a new server 3

After clicking the “Save” button, if the connection is successfully established you will see the newly defined server "multiverse" with its "Databases", "Login/Group Roles" and "Tablespaces".

pgAdmin 4 create a new server 4

pgAdmin 4 create a new server 4

Tip: Get Postgres container IPAddress (if needed):

➜  ~ docker ps -a
CONTAINER ID   IMAGE            COMMAND                  CREATED              STATUS              PORTS                                            NAMES
c2cbda05b39b   dpage/pgadmin4   "/entrypoint.sh"         About a minute ago   Up About a minute   443/tcp, 0.0.0.0:5488->80/tcp, :::5488->80/tcp   pg_dashboard
dc5859f3a447   postgres         "docker-entrypoint.s…"   4 minutes ago        Up 4 minutes        0.0.0.0:5477->5432/tcp, :::5477->5432/tcp        multiverse
➜  ~ docker inspect dc5859f3a447 | grep IPAddress
            "SecondaryIPAddresses": null,
            "IPAddress": "172.17.0.2",
                    "IPAddress": "172.17.0.2",

Connect to a PostgreSQL inside a remote Kubernetes cluster using pgAdmin 4

Port forward the Postgres container from the Kubernetes cluster.

And in the server connection settings, under host put "host.docker.internal".

Connect to PostgreSQL via psql

Install postgresql using Homebrew (macOS):

brew install postgresql

You can check the PostgreSQL version:

➜  ~ psql --version
psql (PostgreSQL) 13.3

For help run:

psql --help

Connect to a Postgres database using the following syntax:

psql -h host -p port --username="USERNAME" -W -d database

In our case, we need to provide the proper host, user and database:

psql -h localhost -p 5477 --username=rick -W -d c137

Which will connect to the Postgres database "c137" located at "localhost:5477" with username "rick" and you will be prompted to enter the password, which is "plumbus" in this case. If the connection was successful, you should see something like this:

psql - connect to a Postgres DB:

➜  ~ psql -h localhost -p 5477 --username=rick -W -d c137
Password:
psql (13.4)
Type “help” for help.

c137=#

The "c137=#" in front of the cursor means that we are connected to the database "c137".

Useful psql commands

Command

Description

\?

List help

\c

Connect to a database

Ctrl + L

Clear screen in psql

\l

List databases

\dt

Describe all tables

\d table_name

Describe a specific table

\x

Expanded display on / off

\i

Run SQL command(s) from a specified file

\copy

Copy query results to a specified output file (with options)

SQL

We can interact with PostgreSQL databases using SQL commands via psql.

Note that these raw SQL commands can be used inside pgAdmin 4 as well.

All SQL commands must end with a semicolon (";")!

You can split sql commands into multiple lines with "Enter" key in psql, the query will not execute as long as you do not enter a semicolon character (";").

SQL commands can be written all in "uppercase" or "lowercase", it does not matter. Combinations are possible as well. We will use the "uppercase" commands.

You can also run sql commands from a ".sql" file. 

Note: Asterix character ("*") in SELECT means every column.

Visit PostgreSQL Data types for more info on available data types in Postgres.

Database

Create a database syntax:

CREATE DATABASE database_name;

Let’s create a database called "test":

CREATE DATABASE test;

Command results:

c137=# CREATE DATABASE test;
CREATE DATABASE
c137=# \l
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges
-----------+-------+----------+------------+------------+-------------------
 c137      | rick  | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | rick  | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | rick  | UTF8     | en_US.utf8 | en_US.utf8 | =c/rick          +
           |       |          |            |            | rick=CTc/rick
 template1 | rick  | UTF8     | en_US.utf8 | en_US.utf8 | =c/rick          +
           |       |          |            |            | rick=CTc/rick
 test      | rick  | UTF8     | en_US.utf8 | en_US.utf8 |
(5 rows)

Drop a database syntax:

DROP DATABASE database_name;

Now drop the newly created database “test”:

DROP DATABASE test;

Command results:

c137=# DROP DATABASE test;
DROP DATABASE
c137=# \l
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges
-----------+-------+----------+------------+------------+-------------------
 c137      | rick  | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | rick  | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | rick  | UTF8     | en_US.utf8 | en_US.utf8 | =c/rick          +
           |       |          |            |            | rick=CTc/rick
 template1 | rick  | UTF8     | en_US.utf8 | en_US.utf8 | =c/rick          +
           |       |          |            |            | rick=CTc/rick
(4 rows)

Type "\l" to list all databases. You should see that the database "test" is no longer present.

Note: We already have a database we created during the PostgreSQL docker container creation called "c137", which we will use for the rest of this document, therefore we can drop the "test" database, which was only used to show you how to create and drop databases.

Table

Create a table syntax:

CREATE TABLE table_name (
  Column name + data type + constraints if any,
);

Lets create a table called "alien":

CREATE TABLE alien (
  id INT,
  first_name VARCHAR(100)
);

Command results:

c137=# CREATE TABLE alien (
  id INT,
  first_name VARCHAR(100)
);
CREATE TABLE
c137=# \dt
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | alien | table | rick
(1 row)

c137=# \d alien
                         Table "public.alien"
   Column   |          Type          | Collation | Nullable | Default
------------+------------------------+-----------+----------+---------
 id         | integer                |           |          |
 first_name | character varying(100) |           |          |

After running the command, the "CREATE TABLE" message means that the table was successfully created.

Typing in "\dt" we can list all the tables and see that our "alien" table is present.

Typing "\d alien" we can see that the table "alien" has 2 columns: "id" of type "integer" and "first_name" of type "character varying(100)".

Drop a table syntax:

DROP TABLE table_name;

Drop the table “alien”:

DROP TABLE alien;

Command results:

c137=# DROP TABLE alien;
DROP TABLE
c137=# \dt
Did not find any relations.
c137=# \d alien
Did not find any relation named "alien".

After running the command, a "DROP TABLE" message means that the table was dropped successfully.

Typing in "\dt" we get a "Did not find any relations." message, because no table exists in the database at the moment.

Typing "\d alien" we get a "Did not find any relation named "alien"." message, confirming that our table "alien" no longer exists.

Constraints

Create a table and add constraints to its columns:

CREATE TABLE alien (
  id BIGSERIAL NOT NULL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  gender VARCHAR(50) NOT NULL,
  date_of_birth DATE NOT NULL,
  email VARCHAR(150),
  species VARCHAR(100)
);

Command results:

c137=# CREATE TABLE alien (
id BIGSERIAL NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
gender VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
email VARCHAR(150),
species VARCHAR(100)
);
CREATE TABLE
c137=# \d alien
                                       Table "public.alien"
    Column     |          Type          | Collation | Nullable |              Default
---------------+------------------------+-----------+----------+-----------------------------------
 id            | bigint                 |           | not null | nextval('alien_id_seq'::regclass)
 first_name    | character varying(50)  |           | not null |
 last_name     | character varying(50)  |           | not null |
 gender        | character varying(50)  |           | not null |
 date_of_birth | date                   |           | not null |
 email         | character varying(150) |           |          |
 species       | character varying(100) |           |          |
Indexes:
    "alien_pkey" PRIMARY KEY, btree (id)

The constraints here are "NOT NULL" and "PRIMARY KEY" keywords. This basically means that the id is the table "alien"'s primary key and all of the columns that have a "NOT NULL" next to them have a constraint that says these cannot be inserted into the table row empty. More on this later in this article.

Inserting Rows

Insert rows (records, data) into tables syntax:

INSERT INTO table_name (
  column_name
) VALUES (
  column_value
);

Insert a row into the table "alien" without "email" and "species":

INSERT INTO alien (
  first_name,
  last_name,
  gender,
  date_of_birth
) VALUES (
  'John',
  'Doe',
  'Male',
  DATE '1978-05-09'
);

The "INSERT 0 1" message means the insert was successful. Typing "SELECT * FROM alien" gives us all rows from the "alien" table.

SELECT * FROM alien;

Command results:

c137=# INSERT INTO alien (
first_name,
last_name,
gender,
date_of_birth
) VALUES (
'John',
'Doe',
'Male',
DATE '1978-05-09'
);
INSERT 0 1
c137=# SELECT * FROM alien;
 id | first_name | last_name | gender | date_of_birth | email | species
----+------------+-----------+--------+---------------+-------+---------
  1 | John       | Doe       | Male   | 1978-05-09    |       |
(1 row)

Insert a row into the table "alien" with "email" and "species":

INSERT INTO alien (
  first_name,
  last_name,
  gender,
  date_of_birth,
  email,
  species
) VALUES (
  'Summer',
  'Smith',
  'Female',
  DATE '1999-12-02',
  '[email protected]',
  'Human'
);

Note: you can always "DROP" the table and create it again if something goes wrong.

Generate data for tables

For the rest of this article, we will use generated data to create and pre-populate the database tables so that we do not have to re-create the tables and insert data manually if we need a clear start.

It makes it easier to focus on the commands at hand, rather than create and drop tables every time we need a clear, new table with data.

For this purpose, we will use a site called Mockaroo.

Here we can specify our data:

  • "Field Name" - which will be our table column name
  • "Type" - data type
  • "Options" - data options, like how many % (percent) of data will be empty, special date formats, additional formulas for data transformation, etc.

Mockaroo landing page

Mockaroo landing page

Generate data for the “alien” table

To generate the data for the "alien" table, fill out the column names and their types according to the "alien" table column definitions.

Using the "ADD ANOTHER FIELD" button, add a few more fields. Name the fields to correspond to column names, and adjust their types if needed.

For "date_of_birth" make sure to select a convenient date range (from 01/01/1905 to 09/28/2021 in this example)

For "email" and "species", add a 30% blank - meaning 30% of the rows will not have this value (column value will be NULL).

Since Mockaroo does not support a "species" data type, select "Custom List" in data types and add some alien species by yourself!
Make sure that you want to generate 1000 rows, select format "SQL", name the table "alien" and tick the "include CREATE TABLE" box. At the bottom of the webpage you should see a "Preview" button. When you click it, you should see the data in sql format, with the "CREATE TABLE" command at the top and "insert" commands at the bottom.

By clicking "download data", your browser should download a file called "alien.sql".

Mockaroo

Important: For the purposes of this exercise, edit the downloaded alien.sql file’s “CREATE TABLE” command to look like this:

CREATE TABLE alien (
    id                      BIGSERIAL           NOT NULL PRIMARY KEY,
    first_name              VARCHAR(50)     NOT NULL,
    last_name               VARCHAR(50)     NOT NULL,
    gender                  VARCHAR(50)     NOT NULL,
    date_of_birth       DATE                NOT NULL,
    email                   VARCHAR(50),
    species                 VARCHAR(15)
);

We also need to remove the “id” column and its values from the insert - since we are using a BIGSERIAL data type for our id (these are automatically generated). Here is an example of how an insert command would look like (remember, the SQL commands can be in uppercase or lowercase, or both!).

INSERT INTO alien (
  first_name,
  last_name,
  gender,
  date_of_birth,
  email,
  species
) VALUES (
  'Edita',
  'Quinnell',
  'Agender',
  '1996-12-31',
  '[email protected]',
  'Cronenberg'
);

You can edit and re-use this file to create a fresh “alien” table, populated with data whenever needed.

Generate data for the “car” table

To generate the data for the "car" table, fill out the column names and their types according to the "car" table.

Remove excess fields. Create "id", "make", "model" and "price" columns.

Select 1000 rows, format "SQL", name the table "car" and tick the "include CREATE TABLE" box.

By clicking "download data", your browser should download a file called "car.sql".

Mockaroo

Important: For the purposes of this exercise, edit the downloaded car.sql file’s “CREATE TABLE” command to look like this:

CREATE TABLE car (
    id              BIGSERIAL           NOT NULL PRIMARY KEY,
    make        VARCHAR(50)        NOT NULL,
    model       VARCHAR(50)         NOT NULL,
    price           NUMERIC(19, 2)
);

We also need to remove the “id” column and its values from the insert - since we are using a BIGSERIAL data type for our id (these are automatically generated). Here is an example of how an insert command would look like (remember, the SQL commands can be in uppercase or lowercase, or both!).

INSERT INTO car (make, model, price) VALUES ('Honda', 'CR-Z', 27728.48);

You can edit and re-use this file to create a fresh “car” table, populated with data whenever needed.

Execute an “.sql” file

Execute command(s) from a “.sql” file:

\i <path_to_your_directory>/file.sql

Drop the table "alien" and run the "alien.sql" file to create the table alien and populate it with data.

\i <path_to_your_directory>/alien.sql

Tip: If using "VSCode", you can right-click on the "alien.sql" and select "Copy Path", which copies the file's full system path into your clipboard, then just paste it after the "\i" command. Alternatively, you can navigate to the directory of your downloaded file and get the current working directory with the “pwd” command, which is the “<path_to_your_directory>”.

If no errors are present, you should see a lot of "INSERT 0 1" messages, meaning the table was created and the rows have been inserted.

If all went well, run:

SELECT * FROM alien;

to see the table populated with data.

Notice that some rows are missing an "email" and "species" values, which is as we defined in the table - these are not required and can be empty or "NULL".

Note that in this table view, you can scroll down until you reach the end (mouse wheel, directional arrows, page-up / page-down), or exit with the "q" key.

Table data

Table data

Selecting data

Select specific columns from a table:

SELECT column_name_1, column_name_2 FROM table_name;

Select specific columns from the table "alien":

SELECT first_name, last_name, gender, species FROM alien;

As you can see, the results contain only the columns we selected from the table.

Select column data

Select column data

Order

Keep in mind:

  • ASC (Ascending) - 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
  • DESC (Descending) - 10, 9, 8, 7, 6, 5, 4, 3, 2, 1

Select data with order:

SELECT * FROM table_name ORDER BY column_name DESC;

Select data with order form table "alien":

SELECT * FROM alien
ORDER BY first_name DESC;

This gives us all rows ordered by "first_name" starting from the last letter of the alphabet "Z".

The default order value is "ASC", meaning we would get all rows ordered by "first_name" starting from the first letter of the alphabet "A".

Select data with order

Select data with order

Distinct

Preview unique values from a column:

SELECT DISTINCT column_name FROM table_name ORDER BY column_name;

Select all unique species from table "alien":

SELECT DISTINCT species
FROM alien ORDER BY species;

Command results:

c137=# SELECT DISTINCT species
FROM alien ORDER BY species;
     species
-----------------
 Cromulon
 Cronenberg
 Gazorpian
 Gromflomite
 Human
 Memory Parasite
 Mr. Meeseeks
 Zigerions

(9 rows)

The above command returns "DISTINCT" or "unique" values present in the table rows, under the column "species", ordered ascending by "species".

You will notice that the 9 species that we got are the 9 species used to generate the sql file (if you followed the instructions above).

Where

The WHERE” keyword allows us to filter the data based on conditions. Select all data from a table where a specific column meets a criteria:

SELECT * FROM table_name WHERE column_name = 'Value';

Select all rows from the table "alien" where the "gender" column has a value of "Female":

SELECT * FROM alien
WHERE gender = 'Female';

Select data with condition

Select data with condition

Select all rows from table where multiple columns meet specific criteria:

SELECT * FROM table_name WHERE column_name_1 = 'Value_1' AND column_name_2 = 'Value_2';

Select all rows from the table "alien" where the "gender" column has a value of "Male" and the "species" column has a value of "Gromflomite":

SELECT * FROM alien
WHERE gender = 'Male'
AND species = 'Gromflomite';

Select data with multiple conditions no.1

Select data with multiple conditions no.1

We can use the "AND" and "OR" keywords to combine select conditions. For example: Select all table rows from the table "alien" where gender is "Female" and "species" is either "Cronenberg" or "Gazorpian":

SELECT * FROM alien
WHERE gender = 'Female'
AND (species = 'Cronenberg' OR species = 'Gazorpian');

Select data with multiple conditions no.2

Select data with multiple conditions no.2

Comparisons

We can perform basic comparisons and operations using sql. A simple comparison:

SELECT 1 = 1;

returns:

  • t = True
  • f = False

Comparison example:

c137=# SELECT 1 = 1;
 ?column?
----------
 t
(1 row)

Note: The column name "?column?" is the default column name when one is not specified. We can name the column using the "AS" keyword:

SELECT 1 = 1 as comparison;

Comparison example 2:

c137=# SELECT 1 = 1 as comparison;
 comparison
------------
 t
(1 row)

Common operators in SQL:

  • > - strictly greater than
  • < - strictly less than
  • <> - not equal
  • + - addition
  • - - subtraction
  • = - equality
  • <= - less than or equal to
  • >= - greater than or equal to

Limit, offset and fetch

Use "LIMIT" and "OFFSET" keywords to skip rows when selecting data from a table and to apply an offset (meaning start getting data after skipping a certain amount of rows). This is usually used to achieve "pagination".

​​Select first "n" rows from the table:

SELECT * FROM table_name LIMIT n;

Select first "n" rows from the "alien" table:

SELECT * FROM alien LIMIT 10;

Select with limit example:

c137=# SELECT * FROM alien LIMIT 10;
 id | first_name | last_name |   gender    | date_of_birth |          email           |     species
----+------------+-----------+-------------+---------------+--------------------------+-----------------
  1 | Adelind    | Sagg      | Male        | 1993-09-30    |                          |
  2 | Edita      | Quinnell  | Agender     | 1996-12-31    | [email protected]  | Cronenberg
  3 | Bryn       | Jeannet   | Bigender    | 1924-12-13    | [email protected]    | Memory Parasite
  4 | Patty      | Asher     | Genderfluid | 1928-10-25    |                          |
  5 | Cecilius   | Stanwix   | Agender     | 2003-07-01    | [email protected]      | Gazorpian
  6 | Rufus      | Feighry   | Female      | 1949-11-07    | [email protected]   | Cronenberg
  7 | Julie      | Conduit   | Polygender  | 1907-09-22    |                          |
  8 | Nikos      | Carberry  | Non-binary  | 1909-04-02    | [email protected] | Memory Parasite
  9 | Xylia      | Kleuer    | Genderqueer | 1988-09-14    | [email protected]        | Human
 10 | Zita       | Colbourne | Male        | 1972-08-31    |                          |
(10 rows)

Select all rows after "n" rows:

SELECT * FROM table_name OFFSET n;

Select all rows after "n" rows from the table "alien":

SELECT * FROM alien OFFSET 5;

The results skipped the first 5 rows in this example (noticeable by "id" column).

Select with offset

Select with offset

Select first "n" rows after "m" rows:

SELECT * FROM table_name LIMIT n OFFSET m;

Select first "n" rows after "m" rows from the table "alien":

SELECT * FROM alien
LIMIT 10 OFFSET 5;

The results skipped the first 5 rows in this example (noticeable by the "id" column) and are "limited" to an amount of 10.

c137=# SELECT * FROM alien LIMIT 10 OFFSET 5;
 id | first_name | last_name |   gender    | date_of_birth |           email           |     species
----+------------+-----------+-------------+---------------+---------------------------+-----------------
  6 | Rufus      | Feighry   | Female      | 1949-11-07    | [email protected]    | Cronenberg
  7 | Julie      | Conduit   | Polygender  | 1907-09-22    |                           |
  8 | Nikos      | Carberry  | Non-binary  | 1909-04-02    | [email protected]  | Memory Parasite
  9 | Xylia      | Kleuer    | Genderqueer | 1988-09-14    | [email protected]         | Human
 10 | Zita       | Colbourne | Male        | 1972-08-31    |                           |
 11 | Dannie     | Powdrill  | Polygender  | 1909-11-11    | [email protected] | Cronenberg
 12 | Hasty      | Ell       | Genderfluid | 1933-03-01    | [email protected]       | Memory Parasite
 13 | Tatiania   | Pennicard | Non-binary  | 1950-12-28    |                           |
 14 | Elle       | Redmille  | Bigender    | 2021-07-17    |                           |
 15 | Reider     | Hornung   | Genderfluid | 1957-04-17    | [email protected]       | Gazorpian
(10 rows)

Official SQL way to limit items from the query is by using "FETCH" (This example uses a different combination of keywords):

SELECT * FROM table_name OFFSET n FETCH FIRST m ROW ONLY;

Skip first 5 rows and the select the next 5 rows from the table "alien":

SELECT * FROM alien
OFFSET 5
FETCH FIRST 5 ROW ONLY;

Which is equivalent to:

SELECT * FROM alien
LIMIT 5 OFFSET 5;

Select data with fetch example:

c137=# SELECT * FROM alien OFFSET 5 FETCH FIRST 5 ROW ONLY;
 id | first_name | last_name |   gender    | date_of_birth |          email           |     species
----+------------+-----------+-------------+---------------+--------------------------+-----------------
  6 | Rufus      | Feighry   | Female      | 1949-11-07    | [email protected]   | Cronenberg
  7 | Julie      | Conduit   | Polygender  | 1907-09-22    |                          |
  8 | Nikos      | Carberry  | Non-binary  | 1909-04-02    | [email protected] | Memory Parasite
  9 | Xylia      | Kleuer    | Genderqueer | 1988-09-14    | [email protected]        | Human
 10 | Zita       | Colbourne | Male        | 1972-08-31    |                          |
(5 rows)

In

Use the "IN" keyword to specify filtering conditions.

SELECT * FROM table_name WHERE column_name IN ('value1', 'value2', 'value3');

Select every row from the table "alien" whose "species" column has values "Human", "Cromulon" or "Mr. Meeseeks":

SELECT * FROM alien WHERE species = 'Human'
OR species = 'Cromulon'
OR species = 'Mr. Meeseeks';

Using "IN" keyword:

SELECT * FROM alien
WHERE species IN ('Human', 'Cromulon', 'Mr. Meeseeks');

In both cases, we should get the same results:

Select with ‘in’

Select with ‘in’

Between

Select rows from a table based on a range of column values (of dates in this case):

SELECT * FROM table_name WHERE column_name BETWEEN DATE 'value1' AND 'value2';

Select rows from the table "alien" based on the "date_of_birth" column value (being between 2 specific date values):

SELECT * FROM alien
WHERE date_of_birth
BETWEEN DATE '2000-01-01' AND '2021-01-01';

Notice that the "date_of_birth" column values are between our 2 dates from the select command: "2000-01-01" and "2021-01-01".

Select with 'between'

Select with 'between'

Like and iLike

Using the “LIKE” and “ILIKE” keywords, we can match text values against patterns using wildcards.

"%" - wildcard symbol; Any character before / after.

Find all column values ending in some value syntax:

SELECT * FROM table_name WHERE column_name LIKE '%Value';

Select all rows from the "alien" table where the "email" column value ends in ".com":

SELECT * FROM alien
WHERE email LIKE '%.com';

Notice that the "email" column values all end with ".com".

Select with 'like' no.1

Select with 'like' no.1

Select all rows from the table where column name is like the provided wildcard - “_________@%” - where the underscore (“_”) means any character, and there being 9 underscores (“_________”) in this case this means any 9 characters followed by an “@” symbol and then any character thereafter.

SELECT * FROM table_name WHERE column_name LIKE '_________@%'

Select all rows from the table where "column" values start with lowercase letter:

SELECT * FROM table_name
WHERE column_name LIKE 'p%';

Select all rows from the table where "column" values start with upper case letter:

SELECT * FROM table_name
WHERE column_name LIKE 'P%';

Select all rows from the table where "column" values start with both the upper and lower case letter:

SELECT * FROM table_name
WHERE column_name ILIKE 'p%';

Feel free to try these out by yourself. Note that this select yields no results because all "first_name" column values start with an uppercase letter:

SELECT * FROM alien
WHERE first_name LIKE 'p%';

Select with 'like' no.2

Select with 'like' no.2

Group By

When selecting table rows, we can group them by specific column and add additional columns that perform an operation for us. For example:

Select countries from the table "alien", add a "count" column that counts all of the entries, grouping by "species", meaning we will get a list of unique "species" with counts of how many times they occur in the table. "COUNT(*)" means count all occurrences.

SELECT species, COUNT(*)
FROM alien
GROUP BY species;

Select with 'group by' example:

c137=# SELECT species, COUNT(*) FROM alien GROUP BY species;
     species     | count
-----------------+-------
                 |   314
 Memory Parasite |    95
 Zigerions       |    71
 Gromflomite     |    98
 Cronenberg      |    73
 Mr. Meeseeks    |    92
 Human           |    74
 Cromulon        |    94
 Gazorpian       |    89
(9 rows)

In these results, we can see that the select command returned all of our "unique species" from the table "alien", and how many times they occur in the row data. If we were to sum all of the values in the "count" column, we would get 1000, which is the exact amount of rows present in the table "alien" at the moment (if no rows were added / removed).

Note that the "first row value" for "species" is "empty", meaning there are "X" amount of records (in this case 314) that do not have a value for the "species" column.

Group by having

"Group By Having" allows for extra filtering after the aggregation of group by.

Same as above example, except by adding a "HAVING" keyword followed by a "function" (comparison) we further filter out all rows that have a "count" greater than a specified value (number in this case):

SELECT species, COUNT(*)
FROM alien
GROUP BY species
HAVING COUNT(*) > 80
ORDER BY species;

In these results, we can see that we are missing the "Human", "Cronenberg" and "Zigerions" rows, because the "counts" of these do not satisfy the "comparison" defined after "HAVING" keyword ("COUNT(*) > 80").

Select with 'group by having' example:

c137=# SELECT species, COUNT(*)
FROM alien
GROUP BY species
HAVING COUNT(*) > 80
ORDER BY species;
     species     | count
-----------------+-------
 Cromulon        |    94
 Gazorpian       |    89
 Gromflomite     |    98
 Memory Parasite |    95
 Mr. Meeseeks    |    92
                 |   314
(6 rows)

Max, Min & Sum

For these examples, we will use a different table called "car".

First of all, create the table and insert the data using psql's "\i" command.

\i <path_to_your_directory>/car.sql

Maximum column value syntax:

SELECT MAX(column_name) FROM table_name;

Minimum column value syntax:

SELECT MIN(column_name) FROM table_name;

Sum of column values syntax:

SELECT SUM(column_name) FROM table_name;

Average of column values syntax:

SELECT AVG(column_name) FROM table_name;

Round average of column values syntax:

SELECT ROUND(AVG(column_name)) FROM table_name;

Note: "ROUND" by default rounds up to an "integer", if you pass a number "n" as a second parameter, the value will be rounded to "n" decimals (see example below).

Examples of usage on "car" table:

SELECT MAX(price) FROM car;
SELECT MIN(price) FROM car;
SELECT SUM(price) FROM car;
SELECT AVG(price) FROM car;
SELECT ROUND(AVG(price)) FROM car;

Average value rounded to 2 decimals:

SELECT ROUND(AVG(price), 2) FROM car;

Select data min, max, sum, avg, round examples:

c137=# SELECT MAX(price) FROM car;
   max
----------
 99926.79
(1 row)

c137=# SELECT MIN(price) FROM car;
   min
----------
 10022.40
(1 row)

c137=# SELECT SUM(price) FROM car;
     sum
-------------
 48301679.88
(1 row)

c137=# SELECT AVG(price) FROM car;
        avg
--------------------
 55775.611870669746
(1 row)

c137=# SELECT ROUND(AVG(price)) FROM car;
 round
-------
 55776
(1 row)

c137=# SELECT ROUND(AVG(price), 2) FROM car;
  round
----------
 55775.61
(1 row)

Alias

When performing select(s) / calculations, you can use keyword "AS" to name a column, example:

SELECT id, make, model, price,
ROUND(price * .10, 2) AS ten_percent,
ROUND(price - (price * .10), 2) AS subtracted
FROM car;

Explanation: Select "id", "make", "model" and "price", create a column "ten_percent" with a value equal to "price * 0.10", rounded to "2" decimals, create a column "subtracted" with a value equal to "price - (price * 0.10)", rounded to "2" decimals, from table "car".

The "ten_percent" column is a 10% value of price column value.

The "subtracted" column is a price subtracted by its 10% value.

You can use "AS" to override column names as well.

Select data alias

Select data alias

Coalesce

"Coalesce" keyword allows us to have a "default value" in case one is not provided.

This example will get all "emails" from the table "alien", and put the "<not_provided>" string in place of missing row values:

SELECT COALESCE(email, '<not_provided>') FROM alien;

Coalesce

Datetime & Timestamp

We can get "datetime" and "timestamp" values, as well as "cast" them when selecting.

You can cast the values by wrapping them in other functions (ex: "SELECT DATE(NOW());") or by using an operator.

Note: the casting operator here is "::", ex: in "SELECT NOW()::DATE;" - "SELECT NOW()" returns datetime in "YYYY-MM-DD HH:MM:SS+TT" format, and the "::DATE" casts that value to a date format - "YYYY-MM-DD".

This example returns date with time, including timezone - "YYYY-MM-DD HH:MM:SS+TT"

SELECT NOW();

This example returns a date in "YYYY-MM-DD" format:

SELECT DATE(NOW());

Which is the same as:

SELECT NOW()::DATE;

This example returns "Time" - "HH:MM:SS"

SELECT NOW()::TIME;

Select data datetime examples:

c137=# SELECT NOW();
              now
-------------------------------
 2021-10-13 10:45:37.476628+00
(1 row)

c137=# SELECT DATE(NOW());
    date
------------
 2021-10-13
(1 row)

c137=# SELECT NOW()::DATE;
    now
------------
 2021-10-13
(1 row)

c137=# SELECT NOW()::TIME;
      now
----------------
 10:45:51.79462
(1 row)

We can perform "addition" and "subtraction" on dates:

Add one year from now:

SELECT NOW() + INTERVAL '1 YEAR';

Subtract 1 year from now:

SELECT NOW() - INTERVAL '1 YEARS';

NOTE: Both "YEAR" and "YEARS" will work.

Add 10 months from now:

SELECT NOW() + INTERVAL '10 MONTH';

Subtract 10 months from now:

SELECT NOW() + INTERVAL '10 MONTH';

NOTE: Both "MONTH" and "MONTHS" will work.

Add 23 days from now:

SELECT NOW() + INTERVAL '23 DAY';
Subtract 23 days from now:
SELECT NOW() - INTERVAL '23 DAYS';
NOTE: Both "DAY" and "DAYS" will work.

Extract year from now:

SELECT EXTRACT(YEAR FROM NOW());

These also work:

  • DOW - day of the week (Sunday = 0)
  • CENTURY - century

Select data datetime example 2:

c137=# SELECT NOW() + INTERVAL '1 YEAR';
SELECT NOW() - INTERVAL '1 YEARS';
SELECT NOW() + INTERVAL '10 MONTH';
SELECT NOW() - INTERVAL '10 MONTHS';
SELECT NOW() + INTERVAL '23 DAY';
SELECT NOW() - INTERVAL '23 DAYS';
SELECT EXTRACT(YEAR FROM NOW());
           ?column?
-------------------------------
 2022-10-13 10:48:22.113162+00
(1 row)

           ?column?
-------------------------------
 2020-10-13 10:48:22.115195+00
(1 row)

           ?column?
-------------------------------
 2022-08-13 10:48:22.116629+00
(1 row)

           ?column?
-------------------------------
 2020-12-13 10:48:22.118124+00
(1 row)

           ?column?
-------------------------------
 2021-11-05 10:48:22.119401+00
(1 row)

           ?column?
-------------------------------
 2021-09-20 10:48:22.120769+00
(1 row)

 extract
---------
    2021
(1 row)

From the table "alien", calculate alien's age using "AGE" function, which takes 2 parameters: The starting point form which you want to subtract dates, and the date to subtract, in our case the first parameter is "NOW()" and the second is the actual "date of birth" of the alien. For better view, order by the "age", "descending":

SELECT first_name, last_name, gender, species, date_of_birth, AGE(NOW(), date_of_birth)
FROM alien
ORDER BY age DESC;

Here we can see that some of the "oldest aliens" in our table are more than "115 years old"!

Select data datetime no.3

Select data datetime no.3

If we want to order our results from youngest alien, just remove the "DESC" keyword ("ASC" is the default "GROUP BY" ordering rule.):

SELECT first_name, last_name, gender, species, date_of_birth, AGE(NOW(), date_of_birth)
FROM alien
ORDER BY age;

Here we can see that our youngest alien is only "28 days old"!

Select data datetime no.4

Select data datetime no.4

Primary Keys

A primary key is a "column" or a "group of columns" used to "identify a row uniquely in a table".

We define primary keys through "primary key constraints". Technically, a primary key constraint is the combination of a "not-null" constraint and a "UNIQUE" constraint.

"A table can have one and only one primary key". It is a good practice to add a primary key to every table. When you add a primary key to a table, PostgreSQL creates a unique B-tree index on the column or a group of columns used to define the primary key.

Drop a primary key constraint form an existing primary key syntax:

ALTER TABLE table_name DROP CONSTRAINT constraint_key;

By inspecting the table "alien" with "\d alien", we can see that the table has an "Index": "alien_pkey", which is our "constraint". After dropping the "constraint" and "describing" the table again, the constraint is no longer present.

ALTER TABLE alien
DROP CONSTRAINT alien_pkey;

Constraint example:

c137=# \d alien
                                       Table "public.alien"
    Column     |         Type          | Collation | Nullable |              Default
---------------+-----------------------+-----------+----------+-----------------------------------
 id            | bigint                |           | not null | nextval('alien_id_seq'::regclass)
 first_name    | character varying(50) |           | not null |
 last_name     | character varying(50) |           | not null |
 gender        | character varying(50) |           | not null |
 date_of_birth | date                  |           | not null |
 email         | character varying(50) |           |          |
 species       | character varying(15) |           |          |
Indexes:
    "alien_pkey" PRIMARY KEY, btree (id)

c137=# ALTER TABLE alien DROP CONSTRAINT alien_pkey;
ALTER TABLE
c137=# \d alien
                                       Table "public.alien"
    Column     |         Type          | Collation | Nullable |              Default
---------------+-----------------------+-----------+----------+-----------------------------------
 id            | bigint                |           | not null | nextval('alien_id_seq'::regclass)
 first_name    | character varying(50) |           | not null |
 last_name     | character varying(50) |           | not null |
 gender        | character varying(50) |           | not null |
 date_of_birth | date                  |           | not null |
 email         | character varying(50) |           |          |
 species       | character varying(15) |           |          |

Add primary key to a table column syntax:

ALTER TABLE table_name ADD PRIMARY KEY (id);

By inspecting the table "alien" with "\d alien", we can see that the table has no "indexes". This is because we removed the constraint with the previous command. Add the constraint to the "id" column again and describe the table. You should see the "alien_pkey" again under "Indexes" (same as before it was removed):

ALTER TABLE alien
ADD PRIMARY KEY (id);

Primary key example:

c137=# \d alien
                                       Table "public.alien"
    Column     |         Type          | Collation | Nullable |              Default
---------------+-----------------------+-----------+----------+-----------------------------------
 id            | bigint                |           | not null | nextval('alien_id_seq'::regclass)
 first_name    | character varying(50) |           | not null |
 last_name     | character varying(50) |           | not null |
 gender        | character varying(50) |           | not null |
 date_of_birth | date                  |           | not null |
 email         | character varying(50) |           |          |
 species       | character varying(15) |           |          |

c137=# ALTER TABLE alien ADD PRIMARY KEY (id);
ALTER TABLE
c137=# \d alien
                                       Table "public.alien"
    Column     |         Type          | Collation | Nullable |              Default
---------------+-----------------------+-----------+----------+-----------------------------------
 id            | bigint                |           | not null | nextval('alien_id_seq'::regclass)
 first_name    | character varying(50) |           | not null |
 last_name     | character varying(50) |           | not null |
 gender        | character varying(50) |           | not null |
 date_of_birth | date                  |           | not null |
 email         | character varying(50) |           |          |
 species       | character varying(15) |           |          |
Indexes:
    "alien_pkey" PRIMARY KEY, btree (id)

Delete

Delete everything from a table:

DELETE FROM table_name;

Note: If you wish to test this on the "alien" table, make sure to drop the "alien" table afterwards and then again create it using psql (for data consistency):

DELETE FROM alien;

Note the message "DELETE 1000", which means that the "DELETE" command ran successfully for "1000" rows.

You can then check if the table has any data with select. Here we can see that the table "alien" is still here but it's empty.

Delete example:

c137=# DELETE FROM alien;
DELETE 1000
c137=# \dt
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | alien | table | rick
 public | car   | table | rick
(2 rows)

c137=# \d alien
                                       Table "public.alien"
    Column     |         Type          | Collation | Nullable |              Default
---------------+-----------------------+-----------+----------+-----------------------------------
 id            | bigint                |           | not null | nextval('alien_id_seq'::regclass)
 first_name    | character varying(50) |           | not null |
 last_name     | character varying(50) |           | not null |
 gender        | character varying(50) |           | not null |
 date_of_birth | date                  |           | not null |
 email         | character varying(50) |           |          |
 species       | character varying(15) |           |          |
Indexes:
    "alien_pkey" PRIMARY KEY, btree (id)

c137=# SELECT * FROM alien;
 id | first_name | last_name | gender | date_of_birth | email | species
----+------------+-----------+--------+---------------+-------+---------
(0 rows)

Let's drop the table:

DROP TABLE alien;

And re-create it:

\i <path_to_your_dir>/alien.sql

Delete from a table where chosen columns have specific values:

DELETE FROM table_name
WHERE column_name = 'Value';

Try it out on the table "alien".

Delete all rows whose "species" column is null:

DELETE FROM alien
WHERE species IS NULL;

Note that we got the message "DELETE n" where "n" is the number of rows deleted, and that we no longer have rows (aliens) without "species" column value.

Delete all rows whose "gender" column is equal to "Polygender":

DELETE FROM alien
WHERE gender = 'Polygender';

Note that we got the message "DELETE n" where "n" is the number of rows deleted, and that we no longer have rows (aliens) with "gender" value of "Polygender".

Important: It is recommended that you drop the table "alien" again and recreate it.

Delete example 2

Delete example 2

Unique Constraints

Add a constraint:

ALTER table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

With default constraint name:

ALTER table_name ADD UNIQUE (column_name);

In the table "alien":

ALTER TABLE alien
ADD CONSTRAINT email_constraint
UNIQUE (email);

Note that the "ALTER TABLE" message after the command means that the command executed successfully.

Unique constraint example:

c137=# ALTER TABLE alien ADD CONSTRAINT email_constraint UNIQUE (email);
ALTER TABLE
c137=# \d alien
                                       Table "public.alien"
    Column     |         Type          | Collation | Nullable |              Default
---------------+-----------------------+-----------+----------+-----------------------------------
 id            | bigint                |           | not null | nextval('alien_id_seq'::regclass)
 first_name    | character varying(50) |           | not null |
 last_name     | character varying(50) |           | not null |
 gender        | character varying(50) |           | not null |
 date_of_birth | date                  |           | not null |
 email         | character varying(50) |           |          |
 species       | character varying(15) |           |          |
Indexes:
    "alien_pkey" PRIMARY KEY, btree (id)
    "email_constraint" UNIQUE CONSTRAINT, btree (email)

With default constraint name:

ALTER TABLE alien
ADD UNIQUE (email);

Note the format the constraint key is in if we let the postgres name it: "<TABLE>_<COLUMN>_key", in this case "alien_email_key".

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

In the table "alien" (depending on which command you ran, provide the email constraint key here... it should be either "email_constraint" or "alien_email_key")

Unique constraint example 2:

c137=# ALTER TABLE alien
ADD UNIQUE (email);
ALTER TABLE
c137=# \d alien
                                       Table "public.alien"
    Column     |         Type          | Collation | Nullable |              Default
---------------+-----------------------+-----------+----------+-----------------------------------
 id            | bigint                |           | not null | nextval('alien_id_seq'::regclass)
 first_name    | character varying(50) |           | not null |
 last_name     | character varying(50) |           | not null |
 gender        | character varying(50) |           | not null |
 date_of_birth | date                  |           | not null |
 email         | character varying(50) |           |          |
 species       | character varying(15) |           |          |
Indexes:
    "alien_pkey" PRIMARY KEY, btree (id)
    "alien_email_key" UNIQUE CONSTRAINT, btree (email)
    "email_constraint" UNIQUE CONSTRAINT, btree (email)

Drop the constraint on the "email" column in the "alien" table:

ALTER TABLE alien DROP CONSTRAINT alien_email_key;

Depending on your constraint name, the constraint should now be removed from the table.

Unique constraint example 3:

c137=# ALTER TABLE alien DROP CONSTRAINT alien_email_key;
ALTER TABLE
c137=# \d alien
                                       Table "public.alien"
    Column     |         Type          | Collation | Nullable |              Default
---------------+-----------------------+-----------+----------+-----------------------------------
 id            | bigint                |           | not null | nextval('alien_id_seq'::regclass)
 first_name    | character varying(50) |           | not null |
 last_name     | character varying(50) |           | not null |
 gender        | character varying(50) |           | not null |
 date_of_birth | date                  |           | not null |
 email         | character varying(50) |           |          |
 species       | character varying(15) |           |          |
Indexes:
    "alien_pkey" PRIMARY KEY, btree (id)

Check constraints

Add a constraint based on a condition:

ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (condition);

We can alter the table "car" by adding a "check constraint" on "price" by checking if it is greater than "10 000".

If we inspect the "car" table we can see that the price is of type "numeric(19, 2)" (19 - digit number; fixed precision, 2 - rounded to 2 decimal places; The number of decimal digits that are stored to the right of the decimal point). That means at this moment we can enter a row with a price as low as "13.99". Pretty low price for a car...

INSERT INTO car (model, make, price)
VALUES ('Jotun', 'Toaster', '13.99');

Check constraint example:

c137=# \d
            List of relations
 Schema |     Name     |   Type   | Owner
--------+--------------+----------+-------
 public | alien        | table    | rick
 public | alien_id_seq | sequence | rick
 public | car          | table    | rick
 public | car_id_seq   | sequence | rick
(4 rows)

c137=# \d car
                                   Table "public.car"
 Column |         Type          | Collation | Nullable |             Default
--------+-----------------------+-----------+----------+---------------------------------
 id     | bigint                |           | not null | nextval('car_id_seq'::regclass)
 make   | character varying(50) |           | not null |
 model  | character varying(50) |           | not null |
 price  | numeric(19,2)         |           |          |
Indexes:
    "car_pkey" PRIMARY KEY, btree (id)
c137=# INSERT INTO car (model, make, price) VALUES ('Jotun', 'Toaster', '13.99');
INSERT 0 1
c137=# SELECT * FROM car WHERE id > 1000;
  id  |  make   | model | price
------+---------+-------+-------
 1001 | Toaster | Jotun | 13.99
(1 row)

Add a check constraint on the price column that says we cannot enter a price below "10 000":

ALTER TABLE car
ADD CONSTRAINT price_constraint
CHECK (price > 10000);

This will, in our case, return an "error" saying that some of "existing column price values violate this constraint". This is because we just inserted a "row" with a price of "13.99".

Check constraint example 2:

c137=# ALTER TABLE car
ADD CONSTRAINT price_constraint
CHECK (price > 10000);
ERROR:  check constraint "price_constraint" of relation "car" is violated by some row

Since we know the "id" of the newly created "row" is "1001", we can delete it:

DELETE FROM car
WHERE id = 1001;

Check constraint example 3:

c137=# DELETE FROM car WHERE id = 1001;
DELETE 1

Then we can add the check constraint:

ALTER TABLE car
ADD CONSTRAINT price_constraint
CHECK (price > 10000);

Note: postgres automatically added a "type conversion" from our "CHECK" condition to a "numeric" value on the newly created "price_constraint".

Check constraint example 4:

c137=# ALTER TABLE car
ADD CONSTRAINT price_constraint
CHECK (price > 10000);
ALTER TABLE
c137=# \d car
                                   Table "public.car"
 Column |         Type          | Collation | Nullable |             Default
--------+-----------------------+-----------+----------+---------------------------------
 id     | bigint                |           | not null | nextval('car_id_seq'::regclass)
 make   | character varying(50) |           | not null |
 model  | character varying(50) |           | not null |
 price  | numeric(19,2)         |           |          |
Indexes:
    "car_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "price_constraint" CHECK (price > 10000::numeric)

Now we can see that by "trying to insert a row with a price of value below or equal to 10 000" will return an "error" saying that the new row for relation "car" violates check constraint "price_constraint".

Check constraint example 5:

c137=# INSERT INTO car (model, make, price) VALUES ('Jotun', 'Toaster', '13.99');
ERROR:  new row for relation "car" violates check constraint "price_constraint"
DETAIL:  Failing row contains (1002, Toaster, Jotun, 13.99).
c137=# INSERT INTO car (model, make, price) VALUES ('Jotun', 'Toaster', '10000');
ERROR:  new row for relation "car" violates check constraint "price_constraint"
DETAIL:  Failing row contains (1003, Toaster, Jotun, 10000.00).

We can insert a row with a price greater than "10 000":

INSERT INTO car (model, make, price)
VALUES ('Jotun', 'Toaster', '13500.00’)

Note that we can select all of our newly (or manually) added rows if we used the "car.sql" file to create the table, by adding a "WHERE id > 1000" condition in the select command. Also note that our new row's "id" is "1004" and this is the "BIGSERIAL" data type increments its "BIGINT" counter every time it is "invoked", even if the "operation failed".

Since we deleted a row with "id" "1001" and we failed to insert 2 rows, the "BIGSERIAL's" counter was incremented to "1002" and "1003" respectively. After the final insert, the "id" of the new row is "1004". More on this on [Serial & Sequences](link here).

Check constraint example 6:

c137=# INSERT INTO car (model, make, price) VALUES ('Jotun', 'Toaster', '13500.00');
INSERT 0 1
c137=# SELECT * FROM car WHERE id > 1000;
  id  |  make   | model |  price
------+---------+-------+----------
 1004 | Toaster | Jotun | 13500.00
(1 row)

Update

Update an entry using “UPDATE” and "WHERE" keyword syntax:

UPDATE table_name SET column_name = 'Value' WHERE column_name = 'Value';

Update multiple columns:

UPDATE table_name SET column1 = 'Value_1', column2 = 'Value_2' WHERE column_name = 'Value';

Update a "car" row where a "price" is lower than "10 000" (to check the previously added constraint).

First, let's find a rows that meet our criteria and take the first one available:

SELECT * FROM car
WHERE price IS NULL;

Update_data

Update data example

In this case it is the row with "id" "2". Attempt to update it to a price of "13.99":

UPDATE car
SET price = '13.99'
WHERE id = 2;

Update data example 2:

c137=# UPDATE car SET price = '13.99' WHERE id = 2;
ERROR:  new row for relation "car" violates check constraint "price_constraint"
DETAIL:  Failing row contains (2, Lincoln, Mark VII, 13.99).

And we will get an "error" saying our "price violates the price_constraint" we defined above. Update properly now:

UPDATE car
SET price = '167800.25'
WHERE id = 2;

Update data example 3:

c137=# UPDATE car SET price = '167800.25' WHERE id = 2;
UPDATE 1
c137=# SELECT * FROM car WHERE id = 2;
 id |  make   |  model   |   price
----+---------+----------+-----------
  2 | Lincoln | Mark VII | 167800.25
(1 row)

Here is an example of updating multiple row values:

UPDATE car
SET price = '199999.99', make = 'Rick'
WHERE id = 2;

Update data example 4:

c137=# UPDATE car
SET price = '199999.99', make = 'Rick'
WHERE id = 2;
UPDATE 1
c137=# SELECT * FROM car WHERE id = 2;
 id | make |  model   |   price
----+------+----------+-----------
  2 | Rick | Mark VII | 199999.99
(1 row)

On conflict do nothing

Handle "errors" \ "exceptions" \ "conflicts", example: inserting a table row with a duplicate column value in a unique column.

Use "ON CONFLICT" only on "columns" that have a "constraint". Syntax:

INSERT INTO table_name (column1, column2) VALUES (val1, val2)
ON CONFLICT (column) DO NOTHING;

Here is an example: Alter the table "alien" to have a "UNIQUE" "constraint" called "email_constraint" on column "email":

ALTER TABLE alien
ADD CONSTRAINT email_constraint UNIQUE(email);

On conflict example:

c137=# ALTER TABLE alien ADD CONSTRAINT email_constraint UNIQUE(email);
ALTER TABLE
c137=# \d alien
                                       Table "public.alien"
    Column     |         Type          | Collation | Nullable |              Default
---------------+-----------------------+-----------+----------+-----------------------------------
 id            | bigint                |           | not null | nextval('alien_id_seq'::regclass)
 first_name    | character varying(50) |           | not null |
 last_name     | character varying(50) |           | not null |
 gender        | character varying(50) |           | not null |
 date_of_birth | date                  |           | not null |
 email         | character varying(50) |           |          |
 species       | character varying(15) |           |          |
Indexes:
    "alien_pkey" PRIMARY KEY, btree (id)
    "email_constraint" UNIQUE CONSTRAINT, btree (email)

Then "insert a row with an email that already exists". Find one first by getting every row that has an email (or email is not null), in this case row with "id = 2" has an email: "[email protected]":

SELECT * FROM alien
WHERE email IS NOT NULL;

On conflict no.2

If we attempt to insert a row with the existing email "[email protected]", we will get an error that our new row violates the "email_constraint":

INSERT INTO alien (
  first_name, last_name, gender, date_of_birth, email, species
) VALUES (
  'Squnky', 'Swash', 'Omegagender', '1548-08-25', '[email protected]', 'Memory Parasite'
);

On conflict example 3:

c137=# INSERT INTO alien (
  first_name, last_name, gender, date_of_birth, email, species
) VALUES (
  'Squnky', 'Swash', 'Omegagender', '1548-08-25', '[email protected]', 'Memory Parasite'
);
ERROR:  duplicate key value violates unique constraint "email_constraint"
DETAIL:  Key (email)=([email protected]) already exists

If we add the "ON CONFLICT" keyword, we do not get an "error", rather an "INSERT 0 0" message, meaning the insert failed ("gracefully"):

INSERT INTO alien (
  first_name, last_name, gender, date_of_birth, email, species
) VALUES (
  'Squnky', 'Swash', 'Omegagender', '1548-08-25', '[email protected]', 'Memory Parasite'
) ON CONFLICT (email) DO NOTHING;

On conflict example 4:

c137=# INSERT INTO alien (
  first_name, last_name, gender, date_of_birth, email, species
) VALUES (
  'Squnky', 'Swash', 'Omegagender', '1548-08-25', '[email protected]', 'Memory Parasite'
) ON CONFLICT (email) DO NOTHING;
INSERT 0 0

Foreign keys, joins and relationships

We can alter our alien table, adding a column "car_id" that references the table car's "id" column - "Foreign Key".

ALTER TABLE alien
ADD COLUMN car_id
BIGINT REFERENCES car(id);

Foreign key example:

c137=# ALTER TABLE alien ADD COLUMN car_id BIGINT REFERENCES car(id);
ALTER TABLE
c137=# \d car
                                   Table "public.car"
 Column |         Type          | Collation | Nullable |             Default
--------+-----------------------+-----------+----------+---------------------------------
 id     | bigint                |           | not null | nextval('car_id_seq'::regclass)
 make   | character varying(50) |           | not null |
 model  | character varying(50) |           | not null |
 price  | numeric(19,2)         |           |          |
Indexes:
    "car_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "price_constraint" CHECK (price > 10000::numeric)
Referenced by:
    TABLE "alien" CONSTRAINT "alien_car_id_fkey" FOREIGN KEY (car_id) REFERENCES car(id)

Make the "car_id" column unique, meaning only each alien can have only one car at a time:

ALTER TABLE alien
ADD CONSTRAINT unique_car_id UNIQUE(car_id);

Foreign key example 2:

c137=# ALTER TABLE alien ADD CONSTRAINT unique_car_id UNIQUE(car_id);
ALTER TABLE
c137=# \d alien
                                       Table "public.alien"
    Column     |         Type          | Collation | Nullable |              Default
---------------+-----------------------+-----------+----------+-----------------------------------
 id            | bigint                |           | not null | nextval('alien_id_seq'::regclass)
 first_name    | character varying(50) |           | not null |
 last_name     | character varying(50) |           | not null |
 gender        | character varying(50) |           | not null |
 date_of_birth | date                  |           | not null |
 email         | character varying(50) |           |          |
 species       | character varying(15) |           |          |
 car_id        | bigint                |           |          |
Indexes:
    "alien_pkey" PRIMARY KEY, btree (id)
    "email_constraint" UNIQUE CONSTRAINT, btree (email)
    "unique_car_id" UNIQUE CONSTRAINT, btree (car_id)
Foreign-key constraints:
    "alien_car_id_fkey" FOREIGN KEY (car_id) REFERENCES car(id)

Assign a "car" to an "alien" by updating the alien table row's "car_id" value to match the id of the row from the car table (do that a few times):

UPDATE alien
SET car_id = 1
WHERE id = 1;
UPDATE alien
SET car_id = 3
WHERE id = 2;
UPDATE alien
SET car_id = 10
WHERE id = 5;

Check it out (selecting only the 3 "alien" table rows that we affected, you can list all rows if you want.):

SELECT * FROM alien
WHERE id IN (1, 2, 5);

Foreign key example 3:

c137=# SELECT * FROM alien WHERE id IN (1, 2, 5);
 id | first_name | last_name | gender  | date_of_birth |          email          |  species   | car_id
----+------------+-----------+---------+---------------+-------------------------+------------+--------
  1 | Adelind    | Sagg      | Male    | 1993-09-30    |                         |            |      1
  2 | Edita      | Quinnell  | Agender | 1996-12-31    | [email protected] | Cronenberg |      3
  5 | Cecilius   | Stanwix   | Agender | 2003-07-01    | [email protected]     | Gazorpian  |     10
(3 rows)

Inner join

The "INNER JOIN" selects rows that have matching values in both tables. Syntax:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

This example selects all "aliens" that have a "car" ("car_id" assigned):

SELECT * FROM alien
JOIN car ON alien.car_id = car.id;

Note: The "INNER JOIN" selects all rows from both tables as long as there is a match between the columns. If there are rows in the table "alien" that do not have matches in the table "car", these rows will not be shown!

Here is an extra example where we select only certain columns:

SELECT alien.first_name, car.make, car.model, car.price FROM alien
JOIN car ON alien.car_id = car.id;

Inner join example:

c137=# SELECT * FROM alien JOIN car ON alien.car_id = car.id;
 id | first_name | last_name | gender  | date_of_birth |          email          |  species   | car_id | id |  make  | model  |  price
----+------------+-----------+---------+---------------+-------------------------+------------+--------+----+--------+--------+----------
  5 | Cecilius   | Stanwix   | Agender | 2003-07-01    | [email protected]     | Gazorpian  |     10 | 10 | Nissan | Sentra |
  1 | Adelind    | Sagg      | Male    | 1993-09-30    |                         |            |      1 |  1 | Honda  | CR-Z   | 27728.48
  2 | Edita      | Quinnell  | Agender | 1996-12-31    | [email protected] | Cronenberg |      3 |  3 | Lexus  | SC     | 19231.39
(3 rows)
Left join

The "LEFT JOIN" returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

Combine 2 tables (like in an inner join), the result contains all of the rows from table 1 (LEFT TABLE) and only the rows from table 2 (RIGHT TABLE) that have a corresponding relationship.

This includes everyone that has a car and those aliens that do not have a car.

SELECT * FROM alien
LEFT JOIN car ON alien.car_id = car.id
ORDER BY alien.id;

We can see that we got all of the "alien" rows (left table) joined with their "car" that have a corresponding "car_id".

psql left join example

Left join example

Right join

The "RIGHT JOIN" returns all rows from the "right" table (table2), and the matching rows from the "left" table (table1). The result is 0 rows from the left side, if there is no match. Syntax:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Note: In some databases “RIGHT JOIN" is called "RIGHT OUTER JOIN".

Example right join between "alien" and "car" tables:

SELECT * FROM alien
RIGHT JOIN car
ON alien.car_id = car.id.

We can see that we got all of the "car" rows (right table) joined with their "alien" owners that have a corresponding "car_id".

Right join example

Right join example

Full join

The "FULL OUTER JOIN" keyword returns all rows when there is a match in left (table1) or right (table2) table rows.

Note: "FULL OUTER JOIN" and "FULL JOIN" are the same.

Note: "FULL OUTER JOIN" can potentially return very large result-sets! Syntax:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Full join on "alien" and "car" tables:

SELECT *
FROM alien
FULL OUTER JOIN car
ON alien.car_id = car.id
ORDER BY alien.id;

Note: The "FULL OUTER JOIN" returns "all matching records from both tables whether the other table matches or not". So, if there are rows in "alien" that do not have matches in "car", or if there are rows in "car" that do not have matches in "alien", those rows will be listed as well.

Deleting records with foreign keys

Deleting an entry that is referenced from another table will return an error.

You need to remove the foreign key constraint from the related table first.

Export query results to a file

Export query results to a file, set delimiter, set type as "csv", include headers.

\copy (
  SELECT * FROM alien
  LEFT JOIN car ON car.id = alien.car_id
  ORDER BY alien.id
) TO '/Users/username/Desktop/results.csv'
DELIMITER ',' CSV HEADER;

The message "COPY 1000" means that the 1000 results generated have been copied to the destination file.

Export query results example:

c137=#
\copy (
  SELECT * FROM alien
  LEFT JOIN car ON car.id = alien.car_id
  ORDER BY alien.id
) TO '/Users/username/Desktop/results.csv'
DELIMITER ',' CSV HEADER;
COPY 1000
c137=#

Serial and Sequences

Inspect a "BIGSERIAL" sequence table. The table was automatically created when the table "alien" was created:

SELECT * FROM alien_id_seq;

Serial / Sequence example:

c137=# \d
            List of relations
 Schema |     Name     |   Type   | Owner
--------+--------------+----------+-------
 public | alien        | table    | rick
 public | alien_id_seq | sequence | rick
 public | car          | table    | rick
 public | car_id_seq   | sequence | rick
(4 rows)

c137=# SELECT * FROM alien_id_seq;
 last_value | log_cnt | is_called
------------+---------+-----------
       1002 |      23 | t
(1 row)

Here we can see that the BIGSERIAL’s "id" column's last value was "1000", its log count and is it called.

Select the next val from the BIGSERIAL's sequence. This consumes the function and auto-increments the number.

SELECT nextval('alien_id_seq'::regclass);

Serial / Sequence example 2:

c137=# SELECT nextval('alien_id_seq'::regclass);
 nextval
---------
    1001
(1 row)

Restart the sequence with a given value:

ALTER SEQUENCE alien_id_seq
RESTART WITH 10;

Serial / Sequence example 3:

c137=# ALTER SEQUENCE alien_id_seq
RESTART WITH 10;
ALTER SEQUENCE
c137=# SELECT * FROM alien_id_seq;
 last_value | log_cnt | is_called
------------+---------+-----------
         10 |       0 | f
(1 row)

Extensions

List all available extensions:

SELECT * FROM pg_available_extensions;

Install an extension:

CREATE EXTENSION if NOT EXISTS 'extension_name';

Install the "uuid-ossp" extension:

CREATE EXTENSION if NOT EXISTS "uuid-ossp";

"\df" - list all available functions.

Extensions example:

c137=# \df
                                 List of functions
 Schema |        Name        | Result data type |    Argument data types    | Type
--------+--------------------+------------------+---------------------------+------
 public | uuid_generate_v1   | uuid             |                           | func
 public | uuid_generate_v1mc | uuid             |                           | func
 public | uuid_generate_v3   | uuid             | namespace uuid, name text | func
 public | uuid_generate_v4   | uuid             |                           | func
 public | uuid_generate_v5   | uuid             | namespace uuid, name text | func
 public | uuid_nil           | uuid             |                           | func
 public | uuid_ns_dns        | uuid             |                           | func
 public | uuid_ns_oid        | uuid             |                           | func
 public | uuid_ns_url        | uuid             |                           | func
 public | uuid_ns_x500       | uuid             |                           | func
(10 rows)

UUID

Generate universally unique identifier (uuid; uuid4 in this case):

SELECT uuid_generate_v4();

UUID example:

c137=# SELECT uuid_generate_v4();
           uuid_generate_v4
--------------------------------------
 75d1ac92-561a-4f20-92cd-e4547e7f33f3
(1 row)
UUID as primary key

We can use uuid(s) as primary keys. Example table definitions are below (Drop the existing tables or rename them to test this). Note that we first must "create a car table before creating the alien table because the alien table references the car table":

CREATE TABLE car (
car_uid             UUID             NOT NULL PRIMARY KEY,
make                VARCHAR(100)     NOT NULL,
model                 VARCHAR(100)     NOT NULL,
price                 NUMERIC(19, 2)     NOT NULL CHECK (price > 0)
);
 
CREATE TABLE alien (
alien_uid             UUID             NOT NULL PRIMARY KEY,
first_name             VARCHAR(50)     NOT NULL,
last_name             VARCHAR(50)        NOT NULL,
gender             VARCHAR(50)     NOT NULL,
date_of_birth             DATE             NOT NULL,
email                 VARCHAR(150),
species             VARCHAR(100),
car_uid            UUID             REFERENCES car(car_uid),
UNIQUE(car_uid),
UNIQUE(email)
);

UUID as primary key example:

c137=# CREATE TABLE car (
car_uid                         UUID                    NOT NULL PRIMARY KEY,
make                            VARCHAR(100)    NOT NULL,
model                           VARCHAR(100)    NOT NULL,
price                           NUMERIC(19, 2)  NOT NULL CHECK (price > 0)
);

CREATE TABLE alien (
alien_uid                       UUID                    NOT NULL PRIMARY KEY,
first_name                      VARCHAR(50)     NOT NULL,
last_name                       VARCHAR(50)             NOT NULL,
gender                  VARCHAR(50)     NOT NULL,
date_of_birth                   DATE                    NOT NULL,
email                           VARCHAR(150),
species                         VARCHAR(100),
car_uid                 UUID                    REFERENCES car(car_uid),
UNIQUE(car_uid),
UNIQUE(email)
);
CREATE TABLE
CREATE TABLE

Insert values. Note that you must manually generate uuids. After that, inspect the tables.

INSERT INTO alien (
  alien_uid, first_name, last_name, gender, email, date_of_birth, species
) VALUES (
  uuid_generate_v4(), 'Jessica', 'Smith', 'Female', '[email protected]', '1999-12-02', 'Human'
);
 
INSERT INTO alien (
  alien_uid, first_name, last_name, gender, email, date_of_birth, species
) VALUES (
  uuid_generate_v4(), 'John', 'Doe', 'Male', '[email protected]', '1978-05-09', 'Human'
);
 
INSERT INTO alien (
  alien_uid, first_name, last_name, gender, email, date_of_birth, species
) VALUES (
  uuid_generate_v4(), 'Noob', 'Noob', 'Male', '[email protected]', '2012-11-11', '???'
);
 
INSERT INTO car (
  car_uid, make, model, price
) VALUES (
  uuid_generate_v4(), 'Jotun', 'Toaster', '13500'
);
 
INSERT INTO car (
  car_uid, make, model, price
) VALUES (
  uuid_generate_v4(), 'DMT', 'Toaster', '56500'
);
 
INSERT INTO car (
  car_uid, make, model, price
) VALUES (
  uuid_generate_v4(), 'Plumbuss', 'Future', '104000'
);

UUID as primary key example 2:

c137=# INSERT INTO alien (
  alien_uid, first_name, last_name, gender, email, date_of_birth, species
) VALUES (
  uuid_generate_v4(), 'Jessica', 'Smith', 'Female', '[email protected]', '1999-12-02', 'Human'
);

INSERT INTO alien (
  alien_uid, first_name, last_name, gender, email, date_of_birth, species
) VALUES (
  uuid_generate_v4(), 'John', 'Doe', 'Male', '[email protected]', '1978-05-09', 'Human'
);

INSERT INTO alien (
  alien_uid, first_name, last_name, gender, email, date_of_birth, species
) VALUES (
  uuid_generate_v4(), 'Noob', 'Noob', 'Male', '[email protected]', '2012-11-11', '???'
);

INSERT INTO car (
  car_uid, make, model, price
) VALUES (
  uuid_generate_v4(), 'Jotun', 'Toaster', '13500'
);

INSERT INTO car (
  car_uid, make, model, price
) VALUES (
  uuid_generate_v4(), 'DMT', 'Toaster', '56500'
);

INSERT INTO car (
  car_uid, make, model, price
) VALUES (
  uuid_generate_v4(), 'Plumbuss', 'Future', '104000'
);
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1

Update the table "alien" with "car_uid" values from the table "car" (Assign a car to an alien):

UPDATE alien SET car_uid = <YOUR_CAR_UUID_HERE>
WHERE alien_uid = <YOUR_ALIEN_UUID_HERE>

In this example:

UPDATE alien SET car_uid = '5f4b9b07-b9ed-4f01-82bb-48e015252fc3'
WHERE alien_uid = 'a91da577-5edd-40fa-8fca-a46babf1149e';

UPDATE alien SET car_uid = 'beaa3376-28fa-47c7-8a2c-36c1eae0ca92'
WHERE alien_uid = 'af4c04ec-789c-4657-aec3-4209ba6ba50f';

UUID as primary key example 3:

c137=# UPDATE alien SET car_uid = '5f4b9b07-b9ed-4f01-82bb-48e015252fc3'
WHERE alien_uid = 'a91da577-5edd-40fa-8fca-a46babf1149e';

UPDATE alien SET car_uid = 'beaa3376-28fa-47c7-8a2c-36c1eae0ca92'
WHERE alien_uid = 'af4c04ec-789c-4657-aec3-4209ba6ba50f';
UPDATE 1
UPDATE 1

Performing a FULL JOIN and selecting specific columns, as shown below, returns a table with aliens and their corresponding cars, but also cars without their assigned aliens:

SELECT first_name, last_name, gender, date_of_birth, email, species,
car.make AS car_make, car.model AS car_model, car.price AS car_price
FROM alien
FULL OUTER JOIN car
ON alien.car_uid = car.car_uid
ORDER BY alien.alien_uid;

UUID as primary key example 4:

c137=#
  SELECT first_name, last_name, gender, date_of_birth, email, species,
car.make AS car_make, car.model AS car_model, car.price AS car_price
FROM alien
FULL OUTER JOIN car
ON alien.car_uid = car.car_uid
ORDER BY alien.alien_uid;
 first_name | last_name | gender | date_of_birth |         email          | species | car_make | car_model | car_price
------------+-----------+--------+---------------+------------------------+---------+----------+-----------+-----------
 Noob       | Noob      | Male   | 2012-11-11    | [email protected] | ???     | Plumbuss | Future    | 104000.00
 John       | Doe       | Male   | 1978-05-09    | [email protected]         | Human   |          |           |
 Jessica    | Smith     | Female | 1999-12-02    | [email protected]       | Human   | Jotun    | Toaster   |  13500.00
            |           |        |               |                        |         | DMT      | Toaster   |  56500.00
(4 rows)

Conclusion

This guide is intended for junior developers or anyone who wants to learn more about interacting with PostgreSQL. I hope it will be useful!

Your take on the subject

They say knowledge has power only if you pass it on - we hope our blog post gave you valuable insight.

If you want to share your opinion or learn more about PostgreSQL, feel free to contact us. We'd love to hear what you have to say!