PostgreSQL Basic #

% brew update
% brew search postgresql
% brew install postgresql@15
% echo 'export PATH="/usr/local/opt/postgresql@15/bin:$PATH"' >> ~/.zshrc
% source ~/.zshrc
% psql --version
psql (PostgreSQL) 15.4 (Homebrew)
% brew services start postgresql@15
==> Successfully started `postgresql@15` (label: homebrew.mxcl.postgresql@15)

# **** is default user name
% psql -l
                                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+-------+----------+---------+-------+------------+-----------------+-------------------
 postgres  | ****  | UTF8     | C       | C     |            | libc            |
 template0 | ****  | UTF8     | C       | C     |            | libc            | =c/****          +
           |       |          |         |       |            |                 | ****=CTc/****
 template1 | ****  | UTF8     | C       | C     |            | libc            | =c/****          +
           |       |          |         |       |            |                 | ****=CTc/****
(3 rows)

# Login to PostgreSQL
% psql -h localhost -p 5432 -U your_username -d postgres
psql (15.4 (Homebrew))
Type "help" for help.

postgres=#

# Exit PostgreSQL
postgres=# \q

% ls -la /usr/local/Cellar/postgresql@15/15.4/share/postgresql@15

psql [OPTION]... [DBNAME [USERNAME]]

$ psql --host=sample_hostname --port=sample_portnumber --username=sampleuser sample_db
or
$ psql -h sample_hostname -p sample_portnumber -U sampleuser sample_db

$ psql --file=/path/to/sample.sql
or
$ psql -f /path/to/sample.sql

$ psql --version
or
$ psql -V

export PGDATABASE=sample_db
export PGHOST=sample_host
export PGPORT=XXXX
export PGUSER=sampleuser
export PGPASSWORD=samplepassword

SELECT
  table_catalog,
  table_schema,
  table_name,
  table_type
FROM
  information_schema.tables;

SELECT
    table_name,
    ordinal_position, -- No.
    column_name,
    data_type,
    character_maximum_length, -- 文字数(文字列型)
    character_octet_length, -- バイト数(文字列型)
    numeric_precision, -- 桁数(数値型)
    numeric_scale, -- 小数部(数値型)
    datetime_precision, -- ミリ秒以下(日付型)
    interval_type, -- インターバルの単位(日付型)
    is_nullable, -- NULLを許可するか否か
    column_default -- 初期値
FROM
    information_schema.columns
WHERE
    table_name = 'sample_tbl' -- 対象テーブル名を指定
ORDER BY
    table_name,
    ordinal_position

SELECT
  schemaname AS schema_name,
  tablename AS table_name,
  indexname AS index_name,
  indexdef AS index_definition
FROM
  pg_indexes
ORDER BY
  schemaname,
  tablename,
  indexname;

SELECT
  pg_class.relname,
  (pg_class.reltuples / 1) AS count
FROM
  pg_stat_user_tables
  INNER JOIN pg_class ON pg_stat_user_tables.relname = pg_class.relname
ORDER BY
  CAST(pg_class.reltuples AS numeric) DESC;

SELECT * FROM pg_stat_activity;

$ pg_dump sample_db -U sampleuser -s -t sample_tbl

$ pg_dump -U sampleuser --format=plain --file=/path/to/sample.sql sample_db

$ pg_dump -U sampleuser --format=plain sample_db > /path/to/sample.sql

$ pg_dump -U sampleuser --format=tar --file=/path/to/sample.sql sample_db

$ psql --file=/path/to/sample.sql

$ sudo -i -u sampleuser

$ pg_dumpall --file all.sql
or
$ pg_dumpall > all.sql

EXPLAIN SELECT * FROM sample_tbl WHERE id = XXXXX;

CREATE TABLE copy_tbl (LIKE org_tbl);

CREATE TABLE sample_tbl (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP -- Update by program
) WITHOUT OIDS;

CREATE TABLE sample_tbl (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
) WITHOUT OIDS;

CREATE TRIGGER refresh_users_updated_at_step1
    BEFORE UPDATE ON users FOR EACH ROW
    EXECUTE PROCEDURE refresh_updated_at_step1();
CREATE TRIGGER refresh_users_updated_at_step2
    BEFORE UPDATE OF updated_at ON users FOR EACH ROW
    EXECUTE PROCEDURE refresh_updated_at_step2();
CREATE TRIGGER refresh_users_updated_at_step3
    BEFORE UPDATE ON users FOR EACH ROW
    EXECUTE PROCEDURE refresh_updated_at_step3();

CREATE FUNCTION refresh_updated_at_step1() RETURNS trigger AS
$$
BEGIN
  IF NEW.updated_at = OLD.updated_at THEN
    NEW.updated_at := NULL;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
    
CREATE FUNCTION refresh_updated_at_step2() RETURNS trigger AS
$$
BEGIN
  IF NEW.updated_at IS NULL THEN
    NEW.updated_at := OLD.updated_at;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION refresh_updated_at_step3() RETURNS trigger AS
$$
BEGIN
  IF NEW.updated_at IS NULL THEN
    NEW.updated_at := CURRENT_TIMESTAMP;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE sample_tbl (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP -- Update by program
) PARTITION BY RANGE (created_at) WITHOUT OIDS;

SELECT create_parent(
    p_parent_table => concat(current_schema(), '.sample_tbl'),
    p_control => 'created_at',
    p_type => 'native',
    p_interval=> 'monthly'
);

-- CREATE INDEX index_name ON table_name(column_name, ...)
CREATE INDEX sample_tbl_idx_01 ON sample_tbl(sample_column_1, sample_column_2);

SHOW TIMEZONE;

SELECT * FROM pg_timezone_names;

ALTER DATABASE exsampledb SET timezone TO 'Asia/Tokyo';

SELECT pg_reload_conf();

COPY sample_tbl FROM '/absolute/path/to/sample.tsv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER;

$ psql sample_db -c "SELECT * FROM sample_tbl" -A -F, > sample.csv

COPY sample_tbl FROM '/absolute/path/to/sample.tsv' WITH CSV DELIMITER E'\t' FORCE QUOTE * NULL AS '' HEADER;

$ psql sample_db -c "SELECT * FROM sample_tbl" -A -F $'\t' > sample.tsv

COPY sample_tbl FROM '/absolute/path/to/sample.tsv' WITH CSV DELIMITER ',';

COPY sample_tbl FROM '/absolute/path/to/sample.tsv' WITHC CSV DELIMITER E'\t';

sudo vi Library/PostgreSQL/13/data/postgresql.conf

port = 5432                             # (change requires restart)
↓
port = 5433                             # (change requires restart)

$ sudo launchctl stop com.edb.launchd.postgresql-13
$ sudo launchctl start com.edb.launchd.postgresql-13