Skip to content

Postgres


psql postgresql://postgres:postgres@127.0.0.1:5432/postgres

Raw Install

TODO

Default username and password

User: postgres Pass: postgres Database: postgres

Reminders

\l                    # list databases
CREATE DATABASE_NAME  # Create a new database
\c DATABASE_ NAME     # connect to database
\dt                   # Show Tables in Database
\df                   # List all functions
\d+ TABLE_NAME        # Show what is in the table

Connect docker


docker run --name postgres -e POSTGRES_PASSWORD=postgres -d -p 5432:5432 postgres

Example Databases

Functions

Cursor

BEGIN;
DECLARE 
    curs1 CURSOR FOR
    SELECT * FROM rental;
FETCH 10 FROM curs1;
FETCH PRIOR FROM curs1;
FETCH PRIOR FROM curs1;
FETCH PRIOR FROM curs1;
FETCH 3 FROM curs1;
COMMIT;

Triggers

Restore from backup

pg_restore --host localhost --port 5432 --password user -c -U user -d dvdrental -v "/home/paul/Downloads/dvdrental.tar" -W

Data Types

PostgreSQL: Documentation: 9.5: Data Types

Raw Install

sudo -u postgres psql

docker-compose templates

  • What is searchPath in postgres
psql postgresql://postgres:postgres@127.0.0.1:5432/postgres

psql postgresql://user:password@127.0.0.1:5432/db_name

Postgres docker-compose

e408f86de7261a516af9bb43234ae343

Drop All Tables

DO $$ 
DECLARE 
    table_name text;
BEGIN
    -- Loop through and drop all tables in all schemas
    FOR table_name IN (SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema') 
    LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || table_name || ' CASCADE;';
    END LOOP;
END $$;

Sources