文字コードを指定してテーブル作成

createdb -U user -E UTF-8 -T template0 dbname

ユーザー一覧の表示

psql# select usename from pg_user;

パスワード設定

=# ALTER USER username PASSWORD 'password';

CSVデータのデータ入出力

入力

psql# \copy table from ./data.csv

出力

psql# \copy table to ./data.csv

テーブル構造を確認

psql# \d TableName

ファイルからテーブル作成

$ psql -e -f ./maketable.sql

バックアップと復元

データベースまるごとバックアップ

COPY形式でdump

$ pg_dump データベース名 > 任意のファイル名

INSERT形式でdump

$ pg_dump --inserts データベース名 > 任意のファイル名

データを復元

$ createdb データベース名
$ psql -e データベース名 < 保存したファイル名

テーブルをバックアップ

$ pg_dump -t テーブル名 データベース名 > 任意のファイル名

データのみバックアップ

$ pg_dump -t テーブル名 データベース名 -a > 任意のファイル名

テーブルデータを復元(テーブルはdropされている必要あり)

$ psql -e データベース名 < 保存したファイル名

権限確認

=> \z

ユーザーのロールを確認・変更する

$ psql template1

権限確認

# \du

DB作成権限を付与

# ALTER ROLE user WITH CREATEDB;

DB作成権限をはく奪

# ALTER ROLE user WITH NOCREATEDB;

psqlコマンドのみでSELECT結果をファイルへ出力

db=> \o output.txt   [出力先をoutput.txtへ変更]
db=> \a   [桁揃えをやめ]
db=> \pset fieldsep '\t'   [デリミタをタブに変更]
db=> select * from table;   [SELECT文発行]
db=> \a   [桁揃えを戻す]
db=> \o   [出力先を戻す]

PRIMARY KEYの変更

PRIMARY KEYの削除

ALTER TABLE table DROP CONSTRAINT table_pkey;

PRIMARY KEYの作成

ALTER TABLE table ADD CONSTRAINT table_pkey PRIMARY KEY (field1, field2);

NOT NULL制約の追加と削除

追加

ALTER TABLE table ALTER COLUMN column SET NOT NULL;

削除

ALTER TABLE table ALTER COLUMN column DROP NOT NULL;

\dコマンド

テーブルの確認

\dt

テーブルとSEQUENCEの確認

\dts

SCHEMAの確認

\dn

SCHEMAに関するテーブルとSEQUENCEの確認

\dts SCHEMA.

ユーザー一覧

\du

コメントも表示

\d+

接続中ユーザーの表示

=# SELECT * FROM pg_stat_activity;

実行中問い合わせ内容を見るには設定変更が必要

stats_command_string true

実行されたSQL文を自動的に記録

# vi /usr/local/pgsql/data/postgresql.conf
log_statement = 'mod'
# /etc/rc.d/init.d/postgresql reload
  • none
    記録しない
  • ddl
    CREATE、ALTER、DROPなどのデータ定義文
  • mod
    ddl + INSERT、UPDATE、DELETなどデータに変更を加えるもの
  • all
    すべて

シーケンス値の変更

次は101からカウントしたい場合

SELECT SETVAL('usr_seq', 100);

確認

SELECT last_value FROM usr_seq;

リセットする場合

SELECT SETVAL('usr_seq', 1, false);

特定のテーブルのレコード数を知る

レコード数が少ない場合は、以下で事足りる。

SELECT count(id) FROM TABLE;

ただし、レコード数が多いと極端に時間がかかるので、以下で代用できそう。

SELECT n_live_tup FROM pg_stat_user_tables WHERE relname = 'TABLE' and schemaname = 'public';

countでのレコード数 12947413、n_live_tupでのレコード数 12947459。
微妙に数が異なるけどおおよそでいいのでこれで行こう。

並び替え時のNULL行の制御

ORDER BY id
id
---
0
0
0
1
1
1
NULL
NULL

のようになる場合、

ORDER BY id NULLS FIRST

とすると、

id
---
NULL
NULL
0
0
0
1
1
1

のように表示できます。

最後に表示したい場合は

ORDER BY id NULLS LAST

コマンドラインでの「続ける(more)」表示をやめたい

1つのフィールドに大量の文字列が詰め込まれていたりする場合はページ送りがとても面倒。
一気に表示されるように切り替えることができます。

=> \pset pager off

ページ送りにする場合は

\pset pager on

現在時刻をUNIXTIMESTAMPで得る

SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0));
date_part
------------
 1509106662
(1 row)

PostgreSQLでの複数キーワード検索の方法

http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q13115026123


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2017-10-27 (金) 21:22:42 (50d)