PostgreSQL tutorial by examples
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
andbar
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:
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.