psql -h localhost -p 5432 -U postgres -d postgres

# create db
createdb {DB_NAME} -T template1

# modify db
# (empty)

# list db
psql -l

# switch db
psql -d {DB_NAME}

# drop db
dropdb {DB_NAME}

-- create db
create database {DB_NAME} with template template1

-- modify db
alter database {DB_NAME_OLD} rename to {DB_NAME_NEW}

-- list db
\l

-- switch db
\c {DB_NAME}

-- drop db
drop database {DB_NAME}

-- create db
create schema {SCHEMA_NAME} 

-- modify db
alter schema {SCHEMA_OLD} rename to {SCHEMA_NEW}

-- list db
\dn

-- drop db
drop schema {SCHEMA_OLD}

-- create table
create table {TABLE_NAME} (column1 integer, column2 varchar(123))

-- modify table
alter table sample_table add col1 varchar(123)

-- list table
\d, \dt

-- drop table
drop table {TABLE_NAME}

# create user
createuser {USER_NAME} -l -d -r -s -P

-- create user
create role {USERNAME} with LOGIN PASSWORD 'passwd'

--list user
\du

create domain {DOMAIN_NAME} as integer check (VALUE < 5)

useradd -M pst
mkdir /var/dbcl
chown pst:pst /var/dbcl

# -D, --pgdata {DIRECTORY}
# -E, --encoding {ex: utf8, euc_jp}
# -U, --username {USERNAME}
# --no-locale
initdb -D /var/dbcl/ -E utf8 --no-locale -U pst

pg_ctl initdb -D|--pgdata {DB_CLUSTER_DIR}

pg_ctl start -D|--pgdata {DB_CLUSTER_DIR} -t {MAX_WAIT_TIME: 60sec}

# -m
#   * smart:     クライアントからの接続が全て切断されるまで待つ
#   * fast:      クライアントからの接続を即遮断. 実行中トランザクションは全てロールバック
#   * immediate: クリーンアップ処理を行わずに停止. クラッシュ相当の状態
# -t
#   待ち時間 (単位: 秒. 既定: 60sec)
# -W
#   待たずに制御を戻す
pg_ctl stop -D|--pgdata {DB_CLUSTER_DIR} -t -m s[mart]|f[ast]|i[mmediate] -W
pg_ctl restart (stop と同一)

# 設定ファイルの再読み込み
pg_ctl reload -D|--pgdata

# 起動状態
pg_ctl status

# プロセス終了
pg_ctl kill TERM|INT|QUIT|HUP {PID}

# 接続
psql -h|--host {HOST} -p|--port {PORT} -U|--user {USER}

-- 基本クエリ
select distinct on ({column_name}) {column_name} [as] {alternative_column_name} ...
from  {table_name}
where {condition}
group by {column_name}
having {column_name}
order by {column_name} {asc|desc}
offset {num}
limit {num}

-- 結合
[inner] join {table_name} on {expression}
[inner] join {table_name} using {column}
natural [inner] join {table_name}
cross join {table_name}
{left|right|full} [outer] join {table_name} on {expression}

-- 条件
where {column_name} [not] in ({value})
where {column_name} = any (query)
where {column_name} between {from} and {to}
where {column_name} is not null
where [not] exists (query)

-- 集合演算
-- 優先度は通常の論理演算と同じ
query
[union|except|intersect][all]
query

-- 更新
insert into {table_name}({column1}, {column2}) values
  ({value1}, {value1}),
  ({value2}, {value2})
;
update {table_name} set {column_name1}={value1}, {column_name2}={value2} where {expression}
delete {table_name} where {expression}

-- 配列
create table array_table (c1 text[], c2 int[])
insert into array_table(c1) values('(1, 2, 3)')
select c1[2] from array_table; -- インデックスは1始まり
select c1[2] from array_table where '1' = any(c1);

-- キャスト
'{table_name}'::regclass
cast({column_name} as integer)
{column_name}::int

-- 基本操作
create table sample_table3 (
  id int primary key,
  c1 int unique,
  c2 text not null,
  c3 text default 'abc',
  c4 timestamp default now()
)
alter table {old_table_name} rename to {new_table_name}
alter table {table_name} owner to {new_role}
alter table {table_name} rename column {old_column_name} to {new_column_name}
alter table {table_name} add column {DEFINE}
alter table {table_name} drop column {DEFINE}

-- 主キー
-- 制約名: {table_name}_pkey
create table {table_name} (c1 int primary key)
alter table {table_name} add primary key (c1)
alter table {table_name} add constraint {pri_keyname} primary key (c1)
alter table {table_name} drop constraint {table_name}_pkey

-- ユニーク
create table {table_name} (c1 int unique)
alter table {table_name} add unique (c1)
alter table {table_name} add constraint {table_name}_c1_key unique (c1)
alter table {table_name} drop constraint {table_name}_c1_key

-- not null
create table {table_name} (c1 int not null)
alter table {table_name} alter column c1 set not null
alter table {table_name} alter column c1 drop not null

-- foreign key
create table {table_name} (c1 int references {ref_table_name}({ref_column_name}) on delete cascade on update cascade)
create table {table_name} (c1 int foreign key (c1) references {ref_table_name}({ref_column_name}) on delete cascade on update cascade)
alter table {table_name} add constraint {table_name}_fkey foreign key (column_name) references {ref_table_name}({ref_column_name}) on delete cascade on update cascade

-- check
alter table {table_name} add constraint {table_name}_{column_name}_check check({expr})

-- parition
-- from 以上 to 未満
create table tbl (c1 serial, c2 text, c3 date) partition by range(c3);
create table tbl_y2011m10 partition of tbl for values from ('2011-10-01') to ('2019-10-01');
create table tbl_y2019m10 partition of tbl for values from ('2019-10-01') to ('2020-10-01');
create table tbl_y2020m10 partition of tbl for values from ('2020-10-01') to ('2022-10-01');
insert into tbl(c2, c3) values ('message', '2011-10-01');
insert into tbl(c2, c3) values ('message', '2019-10-01');
insert into tbl(c2, c3) values ('message', '2019-10-02');

alter table tbl attach partition {partition_name} for values {condition}
alter table tbl detach partition {partition_name}

create sequence sample_seq start 10 increment 2;
select nextval('sample_seq');
select currval('sample_seq');
select setval('sample_seq');

create view sample_view(id, val) as select id, c2 from sample_table1;

create index idx_name on sample_table1 [using index_type](column_name) where expr

create function sample_func(int) returns sample_table1 as $$ select * from sample_table1 limit $1 $$ LANGUAGE SQL;
create procedure sample_prc(int) as $$ select * from sample_table1 limit $1 $$ LANGUAGE SQL;

列名 like '条件 (%: 0文字以上のワイルドカード. _: 1文字のワイルドカード)'
列名 similar to '条件 (like に加えて正規表現が使用可能)'

date '2019-01-01' + integer '7'
date '2019-01-01' + interval '7 hour'
to_date('2019-01-01', 'YYYY-MM-DD')
to_timestamp('2019-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS')
to_number('123.45', '999.99')