Postgres Installation Guide and Uncommon SQL Reference for Developers

06 May, 2023 — rdbms, postgres — 3 min read

Postgres Installation Guide and Uncommon SQL Reference for Developers

PostgreSQL Installation and Connection Using Docker and Docker Compose

PostgreSQL is a powerful and widely-used open-source relational database management system (RDBMS). It is known for its reliability, robustness, and ability to handle large data sets and high levels of concurrency. This note to self, how to install and connect to PostgreSQL using Docker and Docker Compose. Just in case I forget it again.

Installation

Refer to the docker-composer.yml

docker-compose.yml

1version: '3'
2services:
3  db:
4    image: postgres:11.6
5    container_name: "postgres"
6    network_mode: bridge
7    environment:
8      - POSTGRES_USER=postgres
9      - POSTGRES_PASSWORD=postgres
10    ports:
11      - "5432:5432"
12    volumes:
13      - ./postgres-data:/var/lib/postgresql/data

I am using the official PostgreSQL image version 11.6 and setting the container name to "postgres", also setting the environment variables for the default user and password to "postgres". The ports are being exposed and mapped to the host machine and we are also creating a volume for the data to be stored.

To start the container, run the following command:

1docker-compose up -d

1docker compose up -d

This will start the container in detached mode, allowing it to run in the background.

Connecting

It's often necessary to connect to the database and run SQL commands. We'll explain how to use the docker exec command to do just that.

1. Using docker exec bash

1docker exec -it postgres bash

This command will open a terminal session inside the container. The resulting prompt will look something like this:

1root@1ab18a08c193:/#

To connect to the PostgreSQL database, we will use the psql command:

1psql -U postgres

With these steps, you should now be able to successfully install and connect to a PostgreSQL database using Docker and Docker Compose.

Using Docker exec psql

1docker exec -it postgres psql -U postgres

Infrequently used SQLs in psql

1SELECT tablename, indexname, indexdef
2  FROM pg_indexes WHERE schemaname = 'public'
3  ORDER BY tablename, indexname;

Preceding query will show all the indexes in the database. Works on cockroachdb as well. Actually I ran this on cockroachdb.

End to end Demo of using Postgres within Docker

Connect postgres using docker exec
Create Database demo_1
Create table employees with attributes "employee_id", "first_name", "last_name", and "job_title"
Insert few records in above employees table
drop table employees
drop database demo_1

Note that these commands should be executed within the psql command-line interface after connecting to the container using docker exec. Make sure to take a backup of the data before dropping any table or database.

Steps

Connect to Postgres using docker exec:

1docker exec -it postgres psql -U postgres

Create the demo_1 database:

1CREATE DATABASE demo_1;

Connect to the demo_1 database and create the employees table:

1\c demo_1
2
3-- Create Table
4CREATE TABLE employees (
5employee_id SERIAL PRIMARY KEY,
6first_name VARCHAR(255) NOT NULL,
7last_name VARCHAR(255) NOT NULL,
8job_title VARCHAR(255) NOT NULL
9);

Insert few records into the employees table:

1INSERT INTO employees (first_name, last_name, job_title)
2VALUES ('John', 'Doe', 'Manager'),
3('Jane', 'Smith', 'Developer'),
4('Bob', 'Johnson', 'Admin');

Here are a few more examples of SQL queries that can be used to retrieve data from the employees table:

1Select the distinct job titles from the employees table:

1SELECT DISTINCT job_title FROM employees;
2
3Select the first and last names of the employees who have 'John' as first name
4
5SELECT first_name, last_name FROM employees WHERE first_name='John';
6
7Select the first and last names of the employees and order the results by last name in descending order:
8
9SELECT first_name, last_name FROM employees ORDER BY last_name DESC;
10
11Select the first and last names of the employees and limit the results to 3:
12
13SELECT first_name, last_name FROM employees LIMIT 3;
14
15Select the first and last names of the employees, and the name of the department they belong to, using a join on the department_id:
16
17SELECT employees.first_name, employees.last_name, departments.name
18FROM employees
19JOIN departments ON employees.department_id = departments.id;
20
21    Select the number of employees for each job title, group the results by job title:
22
23SELECT job_title, COUNT(\*) as employee_count FROM employees GROUP BY job_title;
24
25    Select the first and last names of the employees who have been hired after a specific date:
26
27SELECT first_name, last_name FROM employees WHERE hire_date > '2022-01-01';

These are just a few examples of the types of queries that can be run on the employees table, but you can use many other SQL clauses and functions to retrieve the data you need.

Drop the table

1DROP TABLE employees;

1To drop the demo_1 database:

1DROP DATABASE demo_1;

Postgres Cheatsheet

Here are some common commands and tasks that you may need to perform when working with PostgreSQL. Connect to the Database

1psql -U [username] -d [database]

This command connects to the PostgreSQL database using the specified username and database. If no database is specified, it will connect to the default "postgres" database.

Show a list of all tables

1\dt

Preceding command will show a list of all tables in the current database.

Describe a table

\d [table_name]

This command will provide information about the specified table, including its columns and their data types. Show all data in a table

1SELECT \* FROM [table_name];

This command will show all of the data in the specified table. Create a new table

1CREATE TABLE [table_name] (
2[column_name_1] [data_type_1],
3[column_name_2] [data_type_2],
4...
5);

This command creates a new table with the specified name and columns. The columns are defined with their names and data types.

Insert data into a table

1INSERT INTO [table_name] ([column_name_1], [column_name_2], ...)
2VALUES ([value_1], [value_2], ...);

This command inserts data into the specified table. The columns that the data will be inserted into must be specified, along with the corresponding values. Update data in a table

1UPDATE [table_name]
2SET [column_name] = [new_value]
3WHERE [condition];

This command updates data in the specified table. The column to be updated and the new value must be specified, along with a condition to determine which rows should be updated. Delete data from a table

1DELETE FROM [table_name] WHERE [condition];

This command deletes data from the specified table. A condition must be specified to determine which rows should be deleted. Create a new user

1CREATE USER [username] WITH PASSWORD '[password]';

This command creates a new user with the specified username and password. Grant permissions to a user

1GRANT [permission] ON [table_name] TO [username];

This command grants the specified permission on the specified table to the specified user. Permissions can include SELECT, INSERT, UPDATE, DELETE, and others. Revoke permissions from a user

1REVOKE [permission] ON [table_name] FROM [username];

This command revokes the specified permission on the specified table from the specified user.