FrontPage > DataBase > PostgreSQL > PostgreSQLメモ
to_char (value, format) | 「number、date、timestamp -> char」へ変換 |
to_number (text, format) | 「char -> number」へ変換 |
to_date (text, format) | 「char -> date」へ変換 |
to_timestamp (text, format) | 「char -> timestamp」へ変換 |
trunc (number) | 小数点の切り捨て (number -> int) |
trunc (number, int) | 指定した小数点位置(int)で切り捨て |
date_trunc (format, timestamp) | タイムスタンプの指定した精度未満を切り捨て(timestamp -> dateなど) |
XXX=# COPY [テーブル名] FROM '[ファイル名].txt';
XXX=# COPY [テーブル名] FROM '[ファイル名].csv' WITH CSV;
XXX=# COPY [テーブル名] TO '[ファイル名].txt';
XXX=# COPY [テーブル名] TO '[ファイル名].csv' WITH CSV; or XXX=# COPY [テーブル名] TO '[ファイル名].csv' DELIMITERS ','; ※「WITH CSV」は、「ヘッダ」、「クウォート指定」など対応。 ※「DELIMITERS ','」は、疑似CSVファイル。
XXX=# \pset format unaligned XXX=# \pset fieldsep ',' XXX=# \o [ファイル名].csv XXX=# SELECT [カラム名, ...] FROM [テーブル名] WHERE [判定式] ORDER BY [ソート順位]; XXX=# \o
psql -t -A -F ',' -c 'select * from xxxx'
psql -t -A -F $'\t' -c 'select * from xxxx'
XXX=# SELECT PG_LOCKS.PID AS "PID", PG_DATABASE.DATNAME AS "DATABASE", PG_CLASS.RELNAME AS "NAME", PG_LOCKS.LOCKTYPE AS "LOCK TYPE", PG_LOCKS.MODE AS "LOCK MODE" FROM PG_LOCKS LEFT JOIN PG_DATABASE ON PG_LOCKS.DATABASE = PG_DATABASE.OID LEFT JOIN PG_CLASS ON PG_LOCKS.RELATION=PG_CLASS.RELFILENODE ORDER BY PG_LOCKS.PID;
XXX=# SELECT PG_CANCEL_BACKEND([PID]);
or
# kill -9 [PID]