[PostgreSQL] チートシート

SELECT * FROM version();

select * from pg_database;

-- ユーザ作成
create role user_name with superuser login password 'password';

-- データベース削除
drop database database_name;

-- データベース作成
create database database_name
  with owner user_name
    template template0
    encoding 'UTF8'
    lc_collate 'C'
    lc_ctype 'C'
;

select datname, pg_size_pretty(pg_database_size(datname)) from pg_database;

SELECT * FROM pg_available_extensions;

select * from pg_user;

SELECT * FROM information_schema.tables WHERE table_catalog = 'database_name';
 
SELECT
  t.table_name
  , pc.reltuples AS row_num
  , (pc.relpages * 8192) AS byte_size
FROM
  information_schema.tables t
  INNER JOIN pg_class pc
    ON pc.relname = t.table_name
WHERE
  t.table_catalog = 'database_name' 
  AND t.table_schema = 'public' 
  AND t.table_name NOT LIKE '\_%'
ORDER BY
  t.table_name
;

CREATE TABLE new_table_name AS SELECT * FROM old_table_name;

SELECT
  c.*
FROM
  information_schema.tables t
  INNER JOIN information_schema.columns c
    ON c.table_catalog = t.table_catalog
    AND c.table_schema = t.table_schema
    AND c.table_name = t.table_name
WHERE
  t.table_catalog = 'database_name'
  AND t.table_name = 'table_name'
ORDER BY
  t.table_name
  , c.ordinal_position

無名コードブロック
do $$declare
  -- 変数宣言
BEGIN
  -- 処理コード
END;$$

do $$declare
 
  -- 変数定義
  c_loganalyze cursor (p_logtype VARCHAR) IS
    SELECT filename, seq, logtype, message
    FROM LogAnalyze
    WHERE logtype = p_logtype
    ;
  v_rec               record;
  v_fields          CHARACTER VARYING(256)[];               -- MESSAGE列をスペースで区切った配列
 
BEGIN
 
  -- heapログのMESSAGE列に対して、スペースで分割しField1～4までデータを更新していく
  FOR v_rec IN c_loganalyze('vmstat') loop
 
    -- スペースで区切った配列をそれぞれのフィールドに格納
    v_fields := regexp_split_to_array(v_rec.message, E'\ +');
 
    -- ここまでで切り出した各FIELD用の変数を用いてUpdate
    UPDATE
      LogAnalyze
    SET
      field1 = v_fields[3],
      field2 = v_fields[4],
      field3 = v_fields[5],
      field4 = v_fields[6],
      field5 = v_fields[7],
      field6 = v_fields[8],
      field7 = v_fields[9],
      field8 = v_fields[10],
      field9 = v_fields[11],
      field10 = v_fields[12],
      field11 = v_fields[13],
      field12 = v_fields[14],
      field13 = v_fields[15],
      field14 = v_fields[16],
      field15 = v_fields[17],
      field16 = v_fields[18]
    WHERE
      filename = v_rec.filename AND
      seq = v_rec.seq AND
      logtype = v_rec.logtype
    ;
  END loop;
 
END;$$

INSERT INTO table_name (
  column_3
  , column_4
)
SELECT
  column_1
  , column_2
FROM
  table_name_1
;

UPDATE table_name SET column_name = 'value' WHERE KEY = 1;

UPDATE main_table
SET
  column1 = jt1.column1
FROM main_table a
  LEFT OUTER JOIN join_table_1 jt1
    ON jt1.key = a.key
WHERE
  a.key = main_table.key
  AND a.key = 1
;

DELETE
FROM
  table_name
WHERE
  KEY = 1

-- ファイルをリダイレクトで読み込み実行
psql -h localhost -U postgres -p 5432 -d database_name < backup.sql

-- -fオプションで実行
psql -h localhost -U postgres -p 5432 -d database_name -f backup.sql

psql -h localhost -U postgres -p 5432 -d database_name -f test.sql -A -F"," -o output.csv

psql -h localhost -U postgres -p 5432 -c "copy(select * from table_name) to stdout with csv delimiter E'\t' quote E'\b' null as '' header ;" database_name > table_name.txt

-- リダイレクトでエクスポート
pg_dump -h localhost -U postgres database_name > backup.sql

-- -fオプションでファイルを指定しエクスポート
pg_dump -f backup.sql -h localhost -U postgres database_name

pg_dump -t table_name -f backup.sql -c -h localhost -p 5432 -U user_name database_name

vacuumdb -U postgres database_name [option]

log_destination = 'stderr,syslog'
 
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
syslog_sequence_numbers = on
syslog_split_messages = on
 
log_min_messages = info

C:\WINDOWS\system32>initdb -U postgres -W -A password -D C:\pgsql\data --encoding=UTF8
データベースシステム内のファイルの所有者はユーザ"xxxxxx"となります。
このユーザをサーバプロセスの所有者とする必要があります。
 
データベースクラスタはロケール"Japanese_Japan.932"で初期化されます。
initdb: could not find suitable text search configuration for locale "Japanese_Japan.932"
デフォルトのテキスト検索構成は simple に設定されます。
 
データベージのチェックサムは無効です。
 
新しいスーパユーザのパスワードを入力してください:
再入力してください:
 
ディレクトリC:/pgsql/dataの権限を設定しています ... ok
サブディレクトリを作成しています ... ok
動的共有メモリの実装を選択しています ... windows
デフォルトのmax_connectionsを選択しています ... 100
デフォルトの shared_buffers を選択しています ... 128MB
selecting default time zone ... Asia/Tokyo
設定ファイルを作成しています ... ok
ブートストラップスクリプトを実行しています ... ok
ブートストラップ後の初期化を実行しています ... ok
データをディスクに同期しています ... ok
 
成功しました。以下のようにしてデータベースサーバを起動することができます:
 
    pg_ctl -D ^"C^:^\pgsql^\data^" -l ログファイル start

# TYPE  DATABASE        USER            ADDRESS                 METHOD
 
# IPv4 local connections:
host    all             all             127.0.0.1/32            password
# IPv6 local connections:
host    all             all             ::1/128                 password
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            password
host    replication     all             ::1/128                 password

C:\Windows\system32> psql -h localhost -U postgres
psql (12.4)
"help"でヘルプを表示します。
 
postgres=#
postgres=# \password
postgres=# alter role postgres with password 'new password';
postgres=# \q

SELECT NULL AS col
UNION
SELECT NULL AS col
UNION
SELECT 1 AS col
;

SELECT 1 AS col
UNION
SELECT NULL AS col
UNION
SELECT NULL AS col
;