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 guide. To first connect to the PostgreSQL server you could use the following command:

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

Table of content

1. PostgreSQL databases
2. PostgreSQL tables
3. PostgreSQL insert
4. PostgreSQL select
5. PostgreSQL dates
6. PostgreSQL string functions
7. PostgreSQL aggregate functions
8. PostgreSQL update
9. PostgreSQL delete
10. PostgreSQL truncate
11. PostgreSQL join
12. PostgreSQL where
13. PostgreSQL union
14. PostgreSQL views
15. PostgreSQL describe
16. PostgreSQL alter
17. PostgreSQL encryption and hashing
18. PostgreSQL version
19. PostgreSQL and Docker
20. PostgreSQL running queries
21. PostgreSQL functions

1. PostgreSQL databases

1.1. 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=#

1.2. 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.

1.3. PostgreSQL select current database

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

1.4. PostgreSQL drop database

Syntax:

DROP DATABASE [IF EXISTS] database_name;

$ psql -U postgres foo
foo=# DROP IF EXISTS DATABASE bar;

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

$ dropdb -U postgres bar

1.5. PostgreSQL copy database

Syntax:

CREATE DATABASE new_database WITH TEMPLATE old_database OWNER postgresql_user;

$ psql -U postgres foo
foo=# SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'foo' AND pid <> pg_backend_pid();
foo=# CREATE DATABASE bar WITH TEMPLATE foo OWNER postgres;

Note: As we did in previous example we need to make sure that there are not active connection in old_database before duplicating it by running pg_terminate_backend function.

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

$ createdb -U postgresql_user -T old_database new_database
$ createdb -U postgres -T foo bar

Up until now, we’ve assumed that we were copying databases across the same Postgres RDBMS server. But we should point out that it is also possible to copy PostgreSQL databases across different servers as well. We can use their binaries pg_dump and psql to achieve it:

$ pg_dump -C -h origin_host -U postgresql_user database_name | psql -h remote_host -U postgresql_user database_name

2. PostgreSQL tables

2.1. PostgreSQL create table

Syntax:

# Create a brand new PostgreSQL table
CREATE TABLE [IF NOT EXISTS] table_name (field1 type, field2 type, ..., fieldN type);

# or create a table from existing PostgreSQL tables
CREATE TABLE [IF NOT EXISTS] table_name AS SELECT field1, field2, ..., fieldN FROM table_names [WHERE conditions];

Create a new PostgreSQL table users:

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

2.2. 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

2.3. PostgreSQL drop table

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

2.4. 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.

3. PostgreSQL insert

Syntax:

# To insert one row at a time
INSERT INTO table_name (field1, field2, ..., fieldN) VALUES ('value1', 'value2', ..., 'valueN');

# To insert multiple rows
INSERT INTO table_name (field1, field2, ..., fieldN) VALUES ('value1', 'value2', ..., 'valueN'), ('value1', 'value2', ..., 'valueN') ... ('value1', 'value2', ..., 'valueN');

# To insert rows by selecting them from another table in the same database
INSERT INTO table_name (field1, field2, ..., fieldN) \
SELECT (field1, field2, ..., fieldN) \
FROM table_name2;

# To insert rows by selecting them from a remote database table.
# * Note: dblink is a PostgreSQL extension that allow us to connect to other remote databases and perform different types of cross-database queries.
CREATE EXTENSION dblink;

INSERT INTO table_name
SELECT *
FROM dblink (
    'dbname=<dbname> port=<port> host=<host> user=<user> password=<password>',
    'SELECT (field1, field2, ..., fieldN) FROM remote_table_name;'
) AS name(field1 type1, field2 type2, ..., fieldN typeN);

Insert into table users:

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

Insert multiple values into table users:

$ psql -U postgres foo
foo=# INSERT INTO users (id, name, surname) VALUES
(4, 'Janie', 'Doe'), (5, 'Johnny', 'Doe'),
(6, 'John', 'Smith'), (7, 'Luther', 'Blissett'),
(8, 'Jean', 'Dupont'), (9, 'Jos', 'Bleau'),
(10, 'Nanashi', 'No Gombe'), (11, 'Vasya', 'Pupkin'),
(12, 'Mario', 'Rossi'), (13, 'Fred', 'Nurk'),
(14, 'Joe', 'Bloggs'), (15, 'Nax', 'Mustermann'),
(16, 'Erika', 'Mustermann'), (17, 'John', 'Roe'),
(18, 'Richard', 'Roe'), (19, 'Jane', 'Roe');

4. PostgreSQL select

Syntax:

SELECT field1, field2, ..., fieldN FROM table_name [WHERE conditions] [LIMIT N[,K]];

Select users limiting the result to 3:

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

Previous LIMIT SQL statement can be combined with RANDOM() PostgreSQL function to select only one single random result like:

$ psql -U postgres foo
postgres=# SELECT name, surname FROM users ORDER BY RANDOM() LIMIT 1;
 name | surname
------+---------
 Baby | Doe
(1 row)

5. PostgreSQL dates

5.1. 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

6. PostgreSQL string functions

6.1. PostgreSQL concat

$ psql -U postgres foo
foo=# SELECT CONCAT(name, ' ', surname) AS fullname FROM users WHERE id < 4;
 fullname
----------
 Jane Doe
 John Doe
 Baby Doe
(3 rows)

Note: To concatenate strings, operator || can be used instead of concat function as well.

foo=# SELECT name || ' ' || surname AS fullname FROM users WHERE id < 4;
 fullname
----------
 Jane Doe
 John Doe
 Baby Doe
(3 rows)

7. PostgreSQL aggregate functions

7.1. PostgreSQL count

foo=# SELECT COUNT(1) AS num_users, COUNT(DISTINCT name) AS num_dist_names, COUNT(DISTINCT surname) AS num_dist_surnames FROM users;
num_users | num_dist_names | num_dist_surnames
-----------+----------------+-------------------
       18 |             16 |                12
(1 row)

Previous query shows the number of users and the distinct number of names and surnames.

8. PostgreSQL update

Syntax:

UPDATE table_name SET field1=value1, ..., fieldN=valueN [WHERE conditions];

Update users:

foo=# UPDATE users SET name='Marie' WHERE id=1;
UPDATE 1
foo=# SELECT id, name, surname FROM users WHERE id=1;
 id | name  | surname
----+-------+---------
  1 | Marie | Doe
(1 row)

foo=# UPDATE users SET name='Jane' WHERE id=1;
UPDATE 1
foo=# SELECT id, name, surname FROM users WHERE id=1;
 id | name | surname
----+------+---------
  1 | Jane | Doe
(1 row)

9. PostgreSQL delete

Syntax:

DELETE FROM table_name [WHERE conditions];

Delete users:

$ psql -U postgres foo
foo=# DELETE FROM users WHERE id=19;
DELETE 1
foo=# SELECT COUNT(1) AS num_users FROM users;
 num_users
-----------
        18

10. PostgreSQL truncate

10.1 PostgreSQL truncate table

Syntax:

TRUNCATE TABLE [ONLY] table_name [RESTART IDENTITY] [CASCADE];

In the following example we are creating a backup table users_backup before actually truncating the users table.

$ psql -U postgres foo
foo=# CREATE TABLE users_backup AS SELECT * FROM users;
SELECT 18
foo=# TRUNCATE TABLE users;
TRUNCATE TABLE
foo=# SELECT COUNT(1) AS num_users FROM users;
 num_users
-----------
         0

And because we cleared out the origin table we should re-insert the values again using users_backup:

$ psql -U postgres foo
foo=# INSERT INTO users SELECT * FROM users_backup;
INSERT 0 18

10.1.1 PostgreSQL truncate table restart identity

Truncate table resetting auto increment values:

$ psql -U postgres foo
foo=# CREATE TABLE IF NOT EXISTS planets (id SERIAL PRIMARY KEY, name VARCHAR);
foo=# INSERT INTO planets (name) VALUES
('Earth'), ('Jupiter'), ('Neptune'), ('Mars'), ('Venus'), ('Saturn');
foo=# SELECT id, name FROM planets ORDER BY id DESC LIMIT 1;
 id |  name
----+--------
  6 | Saturn
(1 row)
foo=# INSERT INTO planets (name) VALUES ('Mercury');
INSERT 0 1
foo=# SELECT id, name FROM planets ORDER BY id DESC LIMIT 1;
 id |  name
----+---------
  7 | Mercury
foo=# TRUNCATE TABLE planets RESTART IDENTITY;
foo=# INSERT INTO planets (name) VALUES ('Uranus');
foo=# SELECT id, name FROM planets ORDER BY id DESC LIMIT 1;
 id |  name
----+--------
  1 | Uranus

Uranus has id=1 after resetting auto-increment values.

11. 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');

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:

11.1. 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.

11.2. 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
 Vasya   |
 John    |
 Mario   |
 Nanashi |
 Richard |
 John    |
 Nax     |
 Fred    |
 Johnny  |
 Jean    |
 John    |
 Erika   |
 Janie   |
 Baby    |
 Joe     |
 Jos     |
 Luther  |
(19 rows)

PostgreSQL LEFT JOIN and LEFT OUTER JOIN are the same.

11.3. 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.

12. PostgreSQL where

$ psql -U postgres foo
foo=# SELECT id, name, surname FROM users WHERE surname='Doe';
 id |  name  | surname
----+--------+---------
  2 | John   | Doe
  3 | Baby   | Doe
  4 | Janie  | Doe
  5 | Johnny | Doe
  1 | Jane   | Doe
(5 rows)

12.1. PostgreSQL in list

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

foo=# SELECT id, name, surname FROM users WHERE id NOT IN (2);
 id |  name   |  surname
----+---------+------------
  3 | Baby    | Doe
  4 | Janie   | Doe
  5 | Johnny  | Doe
  6 | John    | Smith
  7 | Luther  | Blissett
  8 | Jean    | Dupont
  9 | Jos     | Bleau
 10 | Nanashi | No Gombe
 11 | Vasya   | Pupkin
 12 | Mario   | Rossi
 13 | Fred    | Nurk
 14 | Joe     | Bloggs
 15 | Nax     | Mustermann
 16 | Erika   | Mustermann
 17 | John    | Roe
 18 | Richard | Roe
  1 | Jane    | Doe
(17 rows)

12.2. PostgreSQL like

$ psql -U postgres foo
foo=# SELECT id, name, surname FROM users WHERE name LIKE 'J%';
 id |  name  | surname
----+--------+---------
  2 | John   | Doe
  4 | Janie  | Doe
  5 | Johnny | Doe
  6 | John   | Smith
  8 | Jean   | Dupont
  9 | Jos    | Bleau
 14 | Joe    | Bloggs
 17 | John   | Roe
  1 | Jane   | Doe
(9 rows)

foo=# SELECT id, name, surname FROM users WHERE name NOT LIKE 'J%';
 id |  name   |  surname
----+---------+------------
  3 | Baby    | Doe
  7 | Luther  | Blissett
 10 | Nanashi | No Gombe
 11 | Vasya   | Pupkin
 12 | Mario   | Rossi
 13 | Fred    | Nurk
 15 | Nax     | Mustermann
 16 | Erika   | Mustermann
 18 | Richard | Roe
(9 rows)

13. PostgreSQL union

Syntax:

SELECT field(s) FROM table_name1 UNION [ALL|DISTINCT] SELECT field(s) FROM table_name2 [UNION [ALL|DISTINCT] SELECT field(s) FROM table_nameN];

$ 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
------------------
 John Doe
 Baby Doe
 Janie Doe
 ...
 Batwoman
 Batman
 Batman
(21 rows)

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
------------------
 John Doe
 Baby Doe
 Janie Doe
 ...
 Batwoman
 Batman
(20 rows)

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

14. PostgreSQL views

14. PostgreSQL create view

Syntax:

CREATE [OR REPLACE] VIEW view_name AS SELECT field1, field2, ..., fieldN FROM table_names [WHERE conditions]

Create a new view called people as a union from users and superheroes tables:

$ psql -U postgres foo
postgres=# CREATE OR REPLACE VIEW people AS SELECT CONCAT(name, ' ', surname) AS name FROM users UNION SELECT name FROM superheroes;
CREATE VIEW
foo=# SELECT name FROM PEOPLE;
       name
------------------
 John Roe
 Johnny Doe
 Batwoman
 ...
 Luther Blissett
 John Doe
 Jane Doe
(20 rows)

15. PostgreSQL describe

15.1. PostgreSQL describe table

In psql we can use the syntax \d to describe a table and because it is equivalent to \dtvs can describe views and sequences as well:

database_name=# \d [table_name or pattern];

For example, to describe the table users:

$ 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)

Another way to describe a Postgres table is by querying the view information_schema.columns:

SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'table_name';

Example:

$ psql -U postgres foo
foo=# SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'users';
 column_name |     data_type
-------------+-------------------
 id          | integer
 name        | text
 surname     | text
(3 rows)

16. PostgreSQL alter

16.1. PostgreSQL alter table 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) |           |          |
 to_delete   | character varying(15) |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

16.2. PostgreSQL alter table drop column

$ psql -U postgres foo
foo=# ALTER TABLE users DROP COLUMN to_delete;
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)

16.3. PostgreSQL alter table 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)

17. PostgreSQL encryption and hashing

17.1. 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('jane_doe@example.com', 'my_encryption_key') WHERE id=1;
foo=# UPDATE users SET email=pgp_sym_encrypt('john_doe@example.com', 'my_encryption_key') WHERE id=2;
foo=# UPDATE users SET email=pgp_sym_encrypt('baby_doe@example.com', 'my_encryption_key') WHERE id=3;

foo=# SELECT name, surname, email FROM users WHERE id < 4;
 name | surname |            email
------+---------+---------------------------------
 Jane | Doe     | \xc30d04070302d9c949fc06947eb..
 John | Doe     | \xc30d040703028d31085c4b1745d..
 Baby | Doe     | \xc30d04070302e82cb932530188f..
(3 rows)

17.2. PostgreSQL decrypt column

$ psql -U postgres foo
foo=# SELECT name, surname, pgp_sym_decrypt(email::bytea, 'my_encryption_key') AS email FROM users WHERE id < 4;
 name | surname |        email
------+---------+----------------------
 Baby | Doe     | baby_doe@example.com
 Jane | Doe     | jane_doe@example.com
 John | Doe     | john_doe@example.com
(3 rows)

17.3. PostgreSQL hash 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('bf', 8)) WHERE id=1;
foo=# UPDATE users SET password=crypt('my_password_for_john_doe', gen_salt('bf', 8)) WHERE id=2;
foo=# UPDATE users SET password=crypt('my_password_for_babe_doe', gen_salt('bf', 8)) WHERE id=3;
foo=# SELECT name, surname, password FROM users WHERE id < 4;
 name | surname |                           password
------+---------+--------------------------------------------------------------
 Jane | Doe     | $2a$08$ZeKoQM/oSrUllkyjDGKkbuiDyomBfmVF31nN7ME2rha2M5ERomk12
 John | Doe     | $2a$08$GZoiAkghuIJg/hDjTbqpD.LwibQYuXZR4Go8JwKWpY/a18gzOqDNm
 Baby | Doe     | $2a$08$aLq6MzJbF3uUQoN51hPMa.lqduXaI8tWHuLIlXLdspYTsbcb.UmJW
(3 rows)

17.4. PostgreSQL check hashed 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)

18. PostgreSQL version

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

19. PostgreSQL and Docker

19.1 PostgreSQL using Docker

Syntax (only testing purposes):

$ docker run -d \
  --name my_postgres \
  -e POSTGRES_HOST_AUTH_METHOD=trust \
  postgres:12

POSTGRES_PASSWORD environment variable will be required if POSTGRES_HOST_AUTH_METHOD=trust is not set.

To access the psql shell:

$ docker exec -it my_postgres psql -U postgres
psql (12.3 (Debian 12.3-1.pgdg100+1))
Type "help" for help.

postgres=#

19.1 PostgreSQL using Docker-compose

Syntax (only testing purposes):

version: '3'
services:
  db:
    image: postgres:12
    environment:
      POSTGRES_HOST_AUTH_METHOD: "trust"

To run PostgreSQL using previous Docker-compose file:

$ docker-compose up -d

20. PostgreSQL running queries

20.1 PostgreSQL display running queries

Displays all Postgres active queries including its start time and pid:

$ psql -U postgres
postgres=# SELECT query_start, query, pid
FROM pg_stat_activity
WHERE query != '<IDLE>'
AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Shows all Postgres insert/delete/update running queries including its start time and pid:

$ psql -U postgres
postgres=# SELECT query_start, query, pid
FROM pg_stat_activity
WHERE query ~* '^(insert|update|delete).*'
ORDER BY query_start desc;

20.2 PostgreSQL kill a hanging query

Once you have identified a hanging query to be killed you can use pg_terminate_backend:

$ psql -U postgres
postgres=# SELECT pg_terminate_backend(pid);

Note: pid can be found querying pg_stat_activity table like we did in the previous section.

21. PostgreSQL functions

21.1 PostgreSQL create function

Syntax:

CREATE [OR REPLACE] FUNCTION function_name (param1 type, param2 type, ..., paramN type)
 RETURNS type AS $$
[DECLARE]
 -- declaration
BEGIN
 -- body
END;
$$ LANGUAGE language_name;

PostgreSQL create function example:

$ psql -U postgres foo
foo=# CREATE OR REPLACE FUNCTION users_nator ()
RETURNS TABLE (name TEXT) AS $$
BEGIN
  RETURN QUERY EXECUTE('SELECT CONCAT(name, ''nator'') FROM users');
END;
$$ LANGUAGE plpgsql;

users_nator function adds suffix -nator to the users’ names. Example calling the previous postgres function:

foo=#  SELECT users_nator();
 users_nator
-------------
 Janenator
 Johnnator
 Babynator

Recommended books to expand your PostgreSQL knowledge:

Loading Comments

DevOps books:

Cloud providers:

DigitalOcean offers affordable pricing for VMs and many other public cloud services. You can sign up for DigitalOcean and receive a $100 free credit using this referral link.