PostgreSQL tutorial by examples

PostgreSQL tutorial

In this tutorial, you’ll see several PostgreSQL examples and tips that will help jump start your journey of becoming a PostgreSQL database developer.

Before you begin

A GNU Linux/Mac OS/Windows machine with a PostgreSQL command line client is good enough to follow this tutorial. To first connect to the PostgreSQL server you could use the following command:

$ psql -h <POSTGRESQL_SERVER> -U <POSTGRESQL_USER> <POSTGRESQL_DATABASE_NAME?>

PostgreSQL create database

$ psql -U postgres
postgres=# CREATE DATABASE foo;
postgres=# \c foo;
You are now connected to database "foo" as user "postgres".
foo=#

Another option to create a PostgreSQL database is to use its binary createdb:

$ createdb -U postgres bar
$ psql -U postgres bar
bar=#

PostgreSQL list all databases

$ psql -U postgres foo
foo=# SELECT datname FROM pg_database;
datname
-----------
postgres
foo
bar
template1
template0

foo and bar are the databases that we’ve created before.

postgres is the default database when there is now database selected in a connection.

template1 database contains the structure that will have all the new databases created in the future including extensions, tables, etc.

template0 is a database with original settings that cannot be connected and can be used as starting point to recreate all others databases.

PostgreSQL select current database

$ psql -U postgres foo
foo=# SELECT current_database();
 current_database
------------------
 foo

PostgreSQL drop database

$ psql -U postgres postgres
postgres=# DROP DATABASE bar;

Another option to drop a PostgreSQL database is to use its binary dropdb:

$ dropdb -U postgres bar

PostgreSQL create table

Syntax:

CREATE TABLE [IF NOT EXISTS] table_name (field1 type, field2 type, ..., fieldN type);

Create table users:

$ psql -U postgres foo
foo=# CREATE TABLE IF NOT EXISTS users (id int PRIMARY KEY, name TEXT, surname TEXT);

PostgreSQL show tables

$ psql -U postgres foo
foo=# \dt
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | users | table | postgres

In Postgres, it is also possible to list all tables from a database using a SQL query:

$ psql -U postgres foo
foo=# SELECT table_schema || '.' || table_name AS table FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema');
   table
--------------
public.users

PostgreSQL drop table

$ psql -U postgres foo
foo=# DROP TABLE IF EXISTS users;
foo=# \q

PostgreSQL drop all tables

$ psql -U postgres foo
foo=#  CREATE OR REPLACE FUNCTION delete_all_tables() RETURNS VOID AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
    EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(rec.tablename) || ' CASCADE';
  END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT delete_all_tables();

Deletes all PostgreSQL tables from current database foo and public schema. delete_all_tables() function can be called later on if it’s necessary.

PostgreSQL insert

Syntax:

INSERT INTO table_name (field1, field2, ..., fieldN) VALUES ('value1', 'value2', ..., 'valueN');

Insert into table users:

$ psql -U postgres foo
foo=# INSERT INTO users (id, name, surname) VALUES (1, 'John', 'Doe');

PostgreSQL select

Syntax:

SELECT field1, field2, ..., fieldN FROM table_name;

Select users:

$ psql -U postgres foo
foo=# SELECT id, name, surname FROM users;
 id | name | surname
----+------+---------
  1 | John | Doe

PostgreSQL concat

$ psql -U postgres foo
foo=# SELECT CONCAT(name, ' ', surname) as fullname FROM users WHERE id=1;
 fullname
----------
 John Doe

PostgreSQL datetime

Date and time in UTC:

$ psql -U postgres foo
foo=# SELECT now() AT TIME ZONE 'UTC';
          timezone
----------------------------
 2020-01-06 07:26:02.734403

Date and time in local time:

$ psql -U postgres foo
foo=# SET TIME ZONE 'Africa/Casablanca';
foo=# SELECT now();
              now
-------------------------------
 2020-01-06 08:26:32.121129+01

foo=# SET TIME ZONE 'UTC';
foo=# SELECT now();
              now
-------------------------------
 2020-01-06 07:27:06.872359+00

PostgreSQL update

Syntax:

UPDATE table_name SET field1=value1, ..., fieldN=valueN [WHERE field2=value2 ... AND fieldK=fieldK];

Update users:

$ psql -U postgres foo
foo=# UPDATE users SET name='Jane' WHERE id=1;

PostgreSQL delete

Syntax:

DELETE FROM table_name [WHERE field1=value1 ... AND fieldN=valueN];

Delete users:

$ psql -U postgres foo
foo=# DELETE FROM users WHERE id=1;

PostgreSQL truncate

$ psql -U postgres foo
foo=# TRUNCATE TABLE users;

PostgreSQL where

$ psql -U postgres foo
foo=# SELECT * FROM users WHERE surname='Doe';
 id |                        name                        |                      surname
----+----------------------------------------------------+----------------------------------------------------
  1 | Jane                                               | Doe
  2 | Baby                                               | Doe
  3 | John                                               | Doe

PostgreSQL in list

$ psql -U postgres foo
foo=# SELECT id, name, surname FROM users WHERE id IN (1,3);
 id |                        name                        |                      surname
----+----------------------------------------------------+----------------------------------------------------
  1 | Jane                                               | Doe
  3 | John                                               | Doe

foo=# SELECT id, name, surname FROM users WHERE id NOT IN (2);
 id |                        name                        |                      surname
----+----------------------------------------------------+----------------------------------------------------
  1 | Jane                                               | Doe
  3 | John                                               | Doe

PostgreSQL like

$ psql -U postgres foo
foo=# SELECT id, name, surname FROM users WHERE name LIKE 'J%';
 id |                        name                        |                      surname
----+----------------------------------------------------+----------------------------------------------------
  1 | Jane                                               | Doe
  3 | John                                               | Doe

foo=# SELECT id, name, surname FROM users WHERE name NOT LIKE 'J%';
 id |                        name                        |                      surname
----+----------------------------------------------------+----------------------------------------------------
  2 | Baby                                               | Doe

PostgreSQL join

$ psql -U postgres foo
foo=# CREATE TABLE IF NOT EXISTS hobbies (id SERIAL PRIMARY KEY, userid int, description TEXT);
foo=# INSERT INTO hobbies (userid, description) VALUES (1, 'Hiking');
foo=# INSERT INTO hobbies (userid, description) VALUES (1, 'Astronomy');
foo=# INSERT INTO hobbies (userid, description) VALUES (99, 'Chess');
foo=# INSERT INTO users (id, name, surname) VALUES (2, 'Baby', 'Doe');

Note: We are using PostgreSQL SERIAL to avoid the need to pass hobbies.id for each record.

Once we have two tables we can perform the different PostgreSQL JOIN queries:

PostgreSQL join or inner join

$ psql -U postgres foo
foo=# SELECT u.name, h.description AS hobby FROM users u, hobbies h WHERE u.id = h.userid;
foo=# SELECT u.name, h.description AS hobby FROM users u JOIN hobbies h ON u.id = h.userid;
foo=# SELECT u.name, h.description AS hobby FROM users u INNER JOIN hobbies h ON u.id = h.userid;
name |   hobby
------+-----------
Jane | Hiking
Jane | Astronomy

All three previous queries perform a inner join giving the same result.

PostgreSQL left join

$ psql -U postgres foo
foo=# SELECT u.name, h.description as hobby FROM users u LEFT JOIN hobbies h ON u.id = h.userid;
foo=# SELECT u.name, h.description as hobby FROM users u LEFT OUTER JOIN hobbies h ON u.id = h.userid;
name |   hobby
------+-----------
Jane | Hiking
Jane | Astronomy
Baby |

PostgreSQL LEFT JOIN and LEFT OUTER JOIN are the same.

PostgreSQL right join

$ psql -U postgres foo
foo=# SELECT u.name, h.description as hobby FROM users u RIGHT JOIN hobbies h ON u.id = h.userid;
foo=# SELECT u.name, h.description as hobby FROM users u RIGHT OUTER JOIN hobbies h ON u.id = h.userid;
name |   hobby
------+-----------
Jane | Hiking
Jane | Astronomy
     | Chess

PostgreSQL RIGHT JOIN and RIGHT OUTER JOIN are the same.

PostgreSQL union

$ psql -U postgres foo
foo=# CREATE TABLE IF NOT EXISTS superheroes (id INT PRIMARY KEY, name TEXT);
foo=# INSERT INTO superheroes (id, name) VALUES (1, 'Batwoman');
foo=# INSERT INTO superheroes (id, name) VALUES (2, 'Batman');
foo=# INSERT INTO superheroes (id, name) VALUES (3, 'Batman');
foo=# SELECT CONCAT(name, ' ', surname) as name FROM users UNION ALL SELECT name FROM superheroes;
  name
----------
 Jane Doe
 Baby Doe
 Batwoman
 Batman
 Batman

foo=# SELECT CONCAT(name, ' ', surname) as name FROM users UNION SELECT name FROM superheroes;
foo=# SELECT CONCAT(name, ' ', surname) as name FROM users UNION DISTINCT SELECT name FROM superheroes;
  name
----------
 Batwoman
 Baby Doe
 Batman
 Jane Doe

Note: PostgreSQL UNION ALL includes duplicate records, UNION or UNION DISTINCT don’t.

PostgreSQL describe table

$ psql -U postgres foo
foo=# \d users;
                Table "public.users"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 id      | integer |           | not null |
 name    | text    |           |          |
 surname | text    |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

PostgreSQL alter table

PostgreSQL add column

$ psql -U postgres foo
foo=# ALTER TABLE users ADD COLUMN phone VARCHAR(15);
ALTER TABLE
foo=# \d users;
                       Table "public.users"
 Column  |         Type          | Collation | Nullable | Default
---------+-----------------------+-----------+----------+---------
 id      | integer               |           | not null |
 name    | text                  |           |          |
 surname | text                  |           |          |
 phone   | character varying(15) |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

PostgreSQL rename table

$ psql -U postgres foo
foo=# ALTER TABLE users RENAME TO users_foo;
ALTER TABLE
foo=# \dt users*
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | users_foo | table | postgres
(1 row)

foo=# ALTER TABLE users_foo RENAME TO users;
ALTER TABLE
foo=# \dt users*
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | users | table | postgres
(1 row)

PostgreSQL encryption

PostgreSQL encrypt column

$ psql -U postgres foo
foo=# CREATE EXTENSION pgcrypto;
foo=# ALTER TABLE users ADD COLUMN email VARCHAR;
foo=# UPDATE users SET email=pgp_sym_encrypt('jdoe@example.com', 'my_encryption_key') WHERE id=1;
foo=# UPDATE users SET email=pgp_sym_encrypt('bdoe@example.com', 'my_encryption_key') WHERE id=2;
foo=# SELECT name, surname, email FROM users;
 name | surname |              email
------+---------+----------------------------------
 Jane | Doe     | \xc30d04070302778113d8a355068a..
 Baby | Doe     | \xc30d040703022ac11838f1518c9f..

PostgreSQL decrypt column

$ psql -U postgres foo
foo=# SELECT name, surname, pgp_sym_decrypt_bytea(email::bytea, 'my_encryption_key') AS email FROM users;
 name | surname |      email
------+---------+------------------
 Jane | Doe     | jdoe@example.com
 Baby | Doe     | bdoe@example.com

PostgreSQL encrypt password

$ psql -U postgres foo
foo=# CREATE EXTENSION IF NOT EXISTS pgcrypto;
foo=# ALTER TABLE users ADD COLUMN password VARCHAR;
foo=# UPDATE users SET password=crypt('my_password_for_jane_doe', gen_salt('md5')) WHERE id=1;
foo=# UPDATE users SET password=crypt('my_password_for_babe_doe', gen_salt('md5')) WHERE id=2;
foo=# SELECT name, surname, password FROM users;
 name | surname |              password
------+---------+------------------------------------
 Jane | Doe     | $1$OCx6C/8u$.rJqi1FEifmhlpqzo1l/Y.
 Baby | Doe     | $1$5WXm1dLx$2lJqQlzXH.zpPvrL0t1Vm.

PostgreSQL check encrypted password

$ psql -U postgres foo
foo=#  SELECT name, surname FROM users WHERE password=crypt('my_password_for_jane_doe', password);
 name | surname
------+---------
 Jane | Doe
(1 row)

foo=#  SELECT name, surname FROM users WHERE password=crypt('my_wrong_password', password);
 name | surname
------+---------
(0 rows)

PostgreSQL version

$ psql -U postgres
postgres=# SELECT version();
version
-------------------
PostgreSQL 11.6 ...



Loading Comments