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

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

ユーザー一覧の表示

psql# select usename from pg_user;

パスワード設定

=# ALTER USER username PASSWORD 'password';

CSVデータのデータ入出力

これが一番ラクかも

$ psql -U USER DATABASE -c "\COPY (SELECT * FROM table) TO '/table.csv' WITH CSV HEADER"

入力

psql# \copy table from ./data.csv

出力

psql# \copy table to ./data.csv

SQLを実行してファイルに出力

  • c コマンド実行
  • A 位置揃えをしない
  • F 区切り文字
    $ psql -U USER DATABASE -c "SELECT * FROM table" -A -F, > /table.csv

文字コードの変換は以下でできます。
UTF-8からShift_JISに変換する場合。

$ iconv -f UTF8 -t cp932 table.csv > table_sjis.csv

変換できない文字が含まれている場合は処理が止まってしまうので、止めずに最後まで走らせる場合は -c オプションを付けます。

$ iconv -c -f UTF8 -t cp932 table.csv > table_sjis.csv

改行コードは iconv では出来ないようなので、以下で行います。
LF => CRLF

$ unix2dos table_sjis.csv

CRLF => LF の場合は dos2unix を使用します。
入っていない場合は yum で入れます。

テーブル構造を確認

psql# \d TableName

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

$ psql -e -f ./maketable.sql

バックアップと復元

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

COPY形式でdump

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

INSERT形式でdump

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

データを復元

$ createdb データベース名
$ psql -v ON_ERROR_STOP=1 -h localhost -U DB_USER -d DB_NAME < dump.sql

テーブルをバックアップ

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

データのみバックアップ

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

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

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

データベースをコピー

$ createdb -T ORIGINAL_DBNAME COPY_DBNAME

権限確認

=> \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
# vi /var/lib/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

GUIツール

pgAdmin


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2021-09-19 (日) 19:09:28