【小ネタ】PostgreSQLのSQL/コマンドメモ書き

背景・目的

コマンドやSQLを忘れがちなので、メモします。今後、必要に応じて追加していく予定です。

コマンド/SQL

SQLクライアントを使用した接続

psql -h ホスト名 -d データベース名 -p DBポート番号 -U ユーザ名

スキーマ作成

CREATE SCHEMA スキーマ名;

テーブル

テーブル作成

CREATE TABLE スキーマ名.テーブル名(
id VARCHAR(64)
,year INT
,month INT
,day INT
,PRIMARY KEY(id)
);

テーブル定義を確認

select table_catalog ,table_schema , table_name,ordinal_position,column_name,is_nullable,data_type,character_maximum_length from information_schema.columns where table_name='テーブル名' order by ordinal_position;

  table_catalog   | table_schema  | table_name | ordinal_position |    column_name     | is_nullable |          data_type          | character_maximum_length
------------------+---------------+------------+------------------+--------------------+-------------+-----------------------------+--------------------------
 データベース名      | スキーマ名      |　　テーブル名   |                1 | id                 | YES         | character varying           |                       64
 データベース名      | スキーマ名      | テーブル名   |                2 | year               | YES         | integer                     |
 データベース名      | スキーマ名      | テーブル名   |                3 | month              | YES         | integer                     |
 データベース名      | スキーマ名      | テーブル名   |                4 | day                | YES         | integer                     |

(4 rows)

インデックス

インデックス作成

CREATE INDEX インデックス名 ON スキーマ名.テーブル名 (year,month,day);

インデックスの一覧確認

select schemaname,tablename,indexname from pg_indexes where tablename='テーブル名';

  schemaname   | tablename  |    indexname
---------------+------------+------------------
 スキーマ名      | テーブル名   | インデックス名

(1 rows)

データのロード

コマンドから

複数ファイルある場合にワンライナーで実行する。

for i in `seq 0 10`;do psql -d データベース名 -U ユーザ名 -c "\copy スキーマ名.テーブル名 from /home/XXXXXXX/000"$i"_part_00  delimiter '|' csv" -h DBホスト名 -p DBポート番号;done

時間計測

 \timing
Timing is on.