Tutorials

Postgres queries examples

-- PostgreSQL Database and User Management Examples

-- CREATE DATABASES
CREATE DATABASE myapp_db;
CREATE DATABASE test_db WITH ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8';

-- CREATE USERS/ROLES
CREATE USER appuser WITH PASSWORD 'password123';
CREATE ROLE remoteuser WITH LOGIN PASSWORD 'securepass';
CREATE USER admin WITH SUPERUSER PASSWORD 'adminpass';

-- GRANT PERMISSIONS
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO appuser;
GRANT CONNECT ON DATABASE test_db TO remoteuser;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO remoteuser;
GRANT USAGE ON SCHEMA public TO remoteuser;

-- HOST ACCESS (pg_hba.conf entries - not SQL)
-- host    myapp_db    appuser     192.168.1.0/24    md5
-- host    all         remoteuser  0.0.0.0/0          md5

-- CREATE TABLES
CREATE TABLE users (
   id SERIAL PRIMARY KEY,
   username VARCHAR(50) NOT NULL UNIQUE,
   email VARCHAR(100) NOT NULL,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ALTER TABLES
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(150);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME TO app_users;

-- ALTER USERS
ALTER USER appuser WITH PASSWORD 'newpassword';
ALTER USER appuser RENAME TO newuser;
ALTER USER appuser WITH SUPERUSER;

-- ALTER DATABASES
ALTER DATABASE myapp_db RENAME TO new_myapp_db;
ALTER DATABASE myapp_db OWNER TO appuser;

-- DELETE/DROP OPERATIONS
DROP TABLE IF EXISTS temp_table;
DROP DATABASE IF EXISTS old_db;
DROP USER IF EXISTS unwanteduser;
REVOKE ALL PRIVILEGES ON DATABASE myapp_db FROM appuser;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM remoteuser;

-- VIEW PERMISSIONS
\du                                    -- List users/roles
\l                                     -- List databases
\dt                                    -- List tables
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'appuser';

-- Create a demo database (optional)
CREATE DATABASE all_types_demo;
\c all_types_demo;

-- Drop table if it exists
DROP TABLE IF EXISTS all_data_types;

-- Create the table with all major PostgreSQL data types
CREATE TABLE all_data_types (
    -- Numeric Types
    id               SERIAL PRIMARY KEY,     -- Auto-increment integer
    small_int_col    SMALLINT,               -- 2 bytes
    integer_col      INTEGER,                -- 4 bytes
    big_int_col      BIGINT,                 -- 8 bytes
    decimal_col      DECIMAL(10,2),          -- Exact numeric
    numeric_col      NUMERIC(8,3),           -- Alias for DECIMAL
    real_col         REAL,                   -- 4-byte floating point
    double_col       DOUBLE PRECISION,       -- 8-byte floating point
    small_serial_col SMALLSERIAL,            -- Auto-increment smallint
    big_serial_col   BIGSERIAL,              -- Auto-increment bigint
    money_col        MONEY,                  -- Currency (locale-dependent)

    -- Character Types
    char_col         CHAR(10),               -- Fixed-length
    varchar_col      VARCHAR(255),           -- Variable-length
    text_col         TEXT,                   -- Unlimited length

    -- Binary Types
    bytea_col        BYTEA,                  -- Binary data

    -- Date/Time Types
    date_col         DATE,
    time_col         TIME(3),
    time_tz_col      TIME(3) WITH TIME ZONE,
    timestamp_col    TIMESTAMP(6),
    timestamptz_col  TIMESTAMP(6) WITH TIME ZONE,
    interval_col     INTERVAL,               -- Time span

    -- Boolean
    bool_col         BOOLEAN,

    -- Enumerated Types (create before using)
    status_col       status_enum,            -- Will be defined below

    -- UUID
    uuid_col         UUID DEFAULT gen_random_uuid(), -- Needs pgcrypto or uuid-ossp

    -- JSON Types
    json_col         JSON,
    jsonb_col        JSONB,

    -- Array Types
    int_array_col    INTEGER[],
    text_array_col   TEXT[],

    -- Network Types
    cidr_col         CIDR,                   -- Network block
    inet_col         INET,                   -- IP address
    macaddr_col      MACADDR,                -- MAC address
    macaddr8_col     MACADDR8,               -- MAC address (EUI-64)

    -- Geometric Types
    point_col        POINT,
    line_col         LINE,
    lseg_col         LSEG,
    box_col          BOX,
    path_col         PATH,
    polygon_col      POLYGON,
    circle_col       CIRCLE,

    -- Range Types
    int4range_col    INT4RANGE,
    numrange_col     NUMRANGE,
    tsrange_col      TSRANGE,
    tstzrange_col    TSTZRANGE,
    daterange_col    DATERANGE,

    -- XML
    xml_col          XML,

    -- HSTORE (key-value) - requires extension
    hstore_col       HSTORE
);

-- Create ENUM type
CREATE TYPE status_enum AS ENUM ('pending','active','archived');

-- Enable extensions (if not already)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";  -- or pgcrypto for gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS hstore;

-- Add the enum column after type creation (PostgreSQL requires type first)
ALTER TABLE all_data_types
ADD COLUMN status_col status_enum DEFAULT 'pending';


INSERT INTO all_data_types (
    small_int_col, integer_col, big_int_col,
    decimal_col, numeric_col, real_col, double_col,
    money_col, char_col, varchar_col, text_col,
    bytea_col, date_col, time_col, time_tz_col,
    timestamp_col, timestamptz_col, interval_col,
    bool_col, uuid_col, json_col, jsonb_col,
    int_array_col, text_array_col, cidr_col,
    inet_col, macaddr_col, macaddr8_col,
    point_col, line_col, lseg_col, box_col,
    path_col, polygon_col, circle_col,
    int4range_col, numrange_col, tsrange_col,
    tstzrange_col, daterange_col, xml_col,
    hstore_col, status_col
) VALUES (
    32767, 2147483647, 9223372036854775807,
    12345.67, 9876.543, 3.14, 2.718281828,
    '$123.45', 'fixed', 'variable string', 'unlimited text',
    E'\\xDEADBEEF', '2025-09-27', '12:34:56.789', '12:34:56.789+02',
    '2025-09-27 12:34:56.123456', '2025-09-27 12:34:56.123456+02', '1 year 2 mons 3 days',
    TRUE, uuid_generate_v4(), '{"key":"value"}', '{"key":"value"}',
    ARRAY[1,2,3], ARRAY['a','b','c'], '192.168.0.0/24',
    '192.168.0.1', '08:00:2b:01:02:03', '08:00:2b:01:02:03:04:05',
    POINT(1.1,2.2), LINE('{1,2,3}', '{4,5,6}'), LSEG '[(0,0),(1,1)]',
    BOX '((0,0),(1,1))', PATH '((0,0),(1,1),(2,0))',
    POLYGON '((0,0),(0,1),(1,1),(1,0))',
    CIRCLE '<(1,1),2>',
    '[1,10]', '[1.5,10.5]', '[2025-01-01,2025-12-31]',
    '[2025-01-01 00:00:00+0,2025-12-31 23:59:59+0]',
    '[2025-01-01,2025-12-31]',
    'value',
    '"key1"=>"value1","key2"=>"value2"',
    'active'
);