Postgresql

A simple setup when two Postgres databases prefilled with schema/data needed. docker-compose.yml version: '3.6' services: postgres1: image: postgres restart: always environment: - DATABASE_HOST=127.0.0.1 - POSTGRES_USER=root - POSTGRES_PASSWORD=root - POSTGRES_DB=root ports: - "15432:15432" volumes: - ./postgres1-init.sql:/docker-entrypoint-initdb.d/docker_postgres_init.sql postgres2: image: postgres restart: always environment: - DATABASE_HOST=127.0.0.1 - POSTGRES_USER=root - POSTGRES_PASSWORD=root - POSTGRES_DB=root ports: - "25432:25432" volumes: - ./postgres2-init.sql:/docker-entrypoint-initdb.d/docker_postgres_init.sql And our sample init scripts: CREATE USER tester WITH PASSWORD 'tester' CREATEDB; CREATE DATABASE testdb WITH OWNER = tester ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8' TABLESPACE = pg_default CONNECTION LIMIT = -1; ...

Get size of a table SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename')); Select unique combination of fields / tuples SELECT DISTINCT ON(field1, field2) field1, field2 FROM thetable Select rows where a combination of fields is not unique SELECT columnA, columnB, count(*) AS count FROM thetable GROUP BY columnA, columnB HAVING count(*) > 1 Search for rows with array containing value Assuming, the field appointments has the type date[] SELECT * FROM mtable WHERE appointments @> ARRAY['2023-09-19'::date] ...