FreeBSD/PostgreSQLコマンド

PostgreSQLコマンド

dokuwiki.fl8.jp転載済

パスワード付でログインする

$ psql データベース名 -U ユーザー名 -W

PostgreSQLのユーザ作成

PostgreSQLにユーザをcreateuserコマンドを使用して作成するが
PostgreSQLにユーザ作成するのはスーパーユーザで行ないます。
※スーパーユーザとはpg_shadow テーブルで usesuper が設定されているユーザを指している。

createuser -a -d -U postgres -P ユーザー名
  • オプション
    -a(--adduser)新しいPostgreSQLユーザは、他のユーザを作成することが許可。
    ※このオプションを付けるとスーパーユーザ。
    -d(--createdb)新しいPostgreSQLユーザは、データベースの作成が許可されます。
    -U[接続ユーザ名]
    (--username)PostgreSQLに接続するユーザ名を指定します。
    上記コマンドではpostgres(スーパーユーザ)で接続しています。
    -P(--pwprompt)新しいPostgreSQLユーザのパスワードのプロンプトを表示します。

ユーザの確認

postgres=# select * from pg_shadow ;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |                passwd               | valuntil | useconfig
----------+----------+-------------+----------+-----------+-------------------------------------+----------+------------
postgres |        1 | t           | t        | t         |                                     |          |
matsui   |      100 | t           | f        | f         | md5a88cf805de7149ac382f054f03935580 |          |
(2 rows)

PostgreSQLのユーザ削除

dropuser ユーザ名

(例)
dropuser matsui

PostgreSQLのデータベースの作成(createdbの使用)

createdb -E [エンコード] -O [オーナー] -U [接続ユーザ名] [データベース名]
  • オプション
    -E(--encording)[エンコーディング名]作成するデータベース内で使用する文字エンコード方式を指定します。
    -O(--owner)[オーナ名]作成するデータベースの所有者となるユーザを指定します。
    -U(--username)[接続ユーザ名]PostgreSQLに接続するユーザ名を指定します。

PostgreSQLのデータベースの削除(dropdb)

dropdb [データベース名]

バックアップ・リストア(pg_dump)

[データベース名]にてバックアップを取ることが可能。その際、データベースの所有者にsuしておいた方がよい。

  • 主なオプション
    -a データのみをダンプ
    -b ラージオブジェクトもダンプする。(注: デフォルトではダンプされません)
    -d INSERTコマンドにてダンプ(通常はCOPYコマンド)
    -D カラム名を含めたINSERTコマンドにてダンプ
    -s テーブルのみダンプ
    -t 特定のテーブルのみダンプ
    -F アウトプットファイルの形式を指定(cがカスタム、tがtar、pがテキスト)
  • 例(バックアップ)
    pg_dump database_name > database_name.sql
  • 例(リストア)
    createdb にてdatabaseを作成し、同じユーザで以下のようにすればよい。
    psql database_name < database_name.sql
    ※すでにdatabaseが存在する場合は、先にdropdbする必要がある。

バックアップ・リストア(pg_dumpall)

#データベースのバックアップ

$ pg_dumpall > allbackup.db

#データベースのリストア (pg_dumpとは違いユーザなども作成してくれます)

$ psql -f allbackup.db template1

PostgreSQLでのゼロパディング

# select lpad(id,4,'0') as id from t_images order by id desc; 

こんな感じで表示される
  id  
------
0017
0015
0014
0012
0011
0010
0009
0008
0007
0006
0005
0003
0002
0001
(14 rows)

[DEFAULTの設定・変更]

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 0;

[DEFAULTの削除]

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

フィールド名の変更

ALTER TABLE (テーブル名) RENAME after_fild TO (変更後フィールド名)

PostgreSQLのコマンド

データベース作成   createdb [-D パス] [データベース名] 
データベース削除   dropdb [データベース名] 
インタラクティブなSQL実行   psql [データベース名] 
SQL実行   psql [データベース名] < SQLファイル 
ダンプ   pg_dumpall > ファイル名 
pg_dump [-t テーブル名] データベース名 > ファイル名 
 
レストア   psql template1 < ファイル名 
psql -d template1 -f ファイル名 
pg_restore [ -d データベース名] ファイル名 

エクスポート   psql [-d データベース名] -t -c "SQL" > ファイル名 

psqlのコマンド

コマンド一覧   \?    終了   \q 
データベース一覧   \l    組み込み関数一覧   \df 
テーブル一覧   \d    集約関数一覧   \da 
テーブル詳細   \d テーブル名    文字コード表示   \encoding 
テーブル権限   \z      

SQL

PostgreSQLのバージョン   SELECT version(); 

--------------------------------------------------------------------------------

テーブルの作成   CREATE TABLE テーブル名; 
テーブルの削除   DROP TABLE テーブル名; 
テーブル名の変更   ALTER TABLE テーブル名 RENAME TO 新テーブル名; 

--------------------------------------------------------------------------------
 
列の追加   ALTER TABLE テーブル名 ADD COLUMN 列の定義; 
列の削除   ALTER TABLE テーブル名 DROP COLUMN 列名; 
列名の変更   ALTER TABLE テーブル名 RENAME COLUMN 列名 TO 新列名; 

--------------------------------------------------------------------------------

制約の追加   ALTER TABLE テーブル名 ADD CONSTRAINT 制約名 UNIQUE ( 列名 [, 列名]... ); 
NOT-NULL制約の追加   ALTER TABLE テーブル名 ALTER COLUMN 列名 SET NOT NULL; 
制約の削除   ALTER TABLE テーブル名 DROP CONSTRAINT 制約名; 
NOT-NULL制約の削除   ALTER TABLE テーブル名 ALTER COLUMN 列名 DROP NOT NULL; 
デフォルトの追加   ALTER TABLE テーブル名 ALTER COLUMN 列名 SET DEFAULT デフォルト値; 
デフォルトの削除   ALTER TABLE テーブル名 ALTER COLUMN 列名 DROP DEFAULT;

postgreSQLでデータ型の変更

※postgreSQLではデータ型の変更ができません。
新たにフィールドを作ってデータを写してデータフィールドを変更するというめんどくさい処理がいります。

ALTER TABLE (テーブル名) ADD COLUMN after_fild (変更後データ型)
UPDATE (テーブル名) SET after_fild = (変更前フィールド名)
ALTER TABLE (テーブル名) DROP (変更前フィールド名);
ALTER TABLE (テーブル名) RENAME after_fild TO (変更後フィールド名)

postgresqlチップス

http://ash.jp/db/pg_tips.htm