Postgres 基本コマンド

CREATE ROLE name [ WITH ] LOGIN PASSWORD 'password'
-- Example
create role saru with login password 'monkey';

CREATE SCHEMA schema_name

CREATE TABLE test
(
    a int PRIMARY KEY,
    b int,
    c int
);

create table test
(
    a int,
    b int,
    c int,
    constraint pk_test primary key (
     a
     )
);

CREATE TEMP TABLE temp_sample1 ( i int);

ALTER TABLE test
    DROP CONSTRAINT pk_test;

-- 主キーを作成するときに自分で名前を付けていなければ、テーブル名_pkeyでOK
ALTER TABLE test
    DROP CONSTRAINT test_pkey;

ALTER TABLE test ALTER COLUMN a DROP NOT NULL;

ALTER TABLE test ADD CONSTRAINT test_pkey PRIMARY KEY(a, b);

postgres=# \du

postgres=# \l

postgres=# \conninfo

postgres=# \z

postgres=# \d tablename

postgres=# \cd directory

postgres=# \copy tablename from filename DELIMITER AS ','

postgres=# \copy TABLE_NAME from 'CSV_FILE_NAME.csv' (format csv, header false, encoding utf8);

postgres=# \i filename.sql

postgres=# \! command

postgres=# \dy

postgres=# \df

postgres=# \di

postgres=# \dn

postgres=# \dt *.*

postgres=# \dT+

postgres=# \dv

create table zenkoku_chiho_kokyodantai_code (
     dantai_code     varchar(6)
    ,todofuken_kanji varchar(4)
    ,shichoson_kanji varchar(10)
    ,todofuken_kana  varchar(30)
    ,shichoson_kana  varchar(30)
)
partition by list (todofuken_kanji)
;

create table zenkoku_chiho_kokyodantai_code_1  partition of zenkoku_chiho_kokyodantai_code for values in ('北海道');
create table zenkoku_chiho_kokyodantai_code_2  partition of zenkoku_chiho_kokyodantai_code for values in ('青森県');
create table zenkoku_chiho_kokyodantai_code_3  partition of zenkoku_chiho_kokyodantai_code for values in ('岩手県');

EXPLAIN
sql文
--------------------------------------------
Seq Scan on <table_name>  (cost=0.00..1.60 rows=60 width=28)