FreeBSD/PostgreSQLコマンド の変更点

*PostgreSQLコマンド [#ya542f07]

[[dokuwiki.fl8.jp転載済>http://dokuwiki.fl8.jp/doku.php/02_freebsd/04_database/02_posgresql/postgresql%E3%82%B3%E3%83%9E%E3%83%B3%E3%83%89]]

#contents

**パスワード付でログインする [#h761c778]
 $ psql データベース名 -U ユーザー名 -W

**PostgreSQLのユーザ作成 [#k7be7697]
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ユーザのパスワードのプロンプトを表示します。  |


***ユーザの確認 [#udce6dc8]
 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のユーザ削除 [#la0580a3]
 dropuser ユーザ名
 
 （例）
 dropuser matsui

**PostgreSQLのデータベースの作成(createdbの使用) [#l5cf8518]
 createdb -E [エンコード] -O [オーナー] -U [接続ユーザ名] [データベース名]

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

**PostgreSQLのデータベースの削除(dropdb) [#w30948dc]
 dropdb [データベース名]

**バックアップ・リストア(pg_dump) [#i4aa29c2]
[データベース名]にてバックアップを取ることが可能。その際、データベースの所有者に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
&color(Red){※すでにdatabaseが存在する場合は、先にdropdbする必要がある。};

**バックアップ・リストア(pg_dumpall) [#m31f0ec8]
#データベースのバックアップ
 $ pg_dumpall > allbackup.db


#データベースのリストア （pg_dumpとは違いユーザなども作成してくれます）
 $ psql -f allbackup.db template1



**PostgreSQLでのゼロパディング [#f1bced1b]
 # 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)

-PostgreSQLのユーザ作成、データベース作成等の基本操作~
[[http://www.develop-memo.com/database/postgresql/postgresqloperate.html]]

-PostgreSQLデータベースのコマンド~
[[http://y-kit.jp/saba/xp/postgrecmd.htm]]


**[DEFAULTの設定・変更] [#xe8d3e10]
 ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 0;


**[DEFAULTの削除] [#m4e0ce32]
 ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

**フィールド名の変更 [#l6ebf26c]
 ALTER TABLE (テーブル名) RENAME after_fild TO (変更後フィールド名)


*PostgreSQLのコマンド [#h9ca9bbb]
 データベース作成 　 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のコマンド [#sbb59674]
 コマンド一覧 　 \?    終了 　 \q 
 データベース一覧 　 \l    組み込み関数一覧 　 \df 
 テーブル一覧 　 \d    集約関数一覧 　 \da 
 テーブル詳細 　 \d テーブル名    文字コード表示 　 \encoding 
 テーブル権限 　 \z    　 

**SQL [#ifcce372]
 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でデータ型の変更 [#n159e845]
※postgreSQLではデータ型の変更ができません。~
新たにフィールドを作ってデータを写してデータフィールドを変更するというめんどくさい処理がいります。~

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


**postgresqlチップス [#y4093422]
[[http://ash.jp/db/pg_tips.htm>http://ash.jp/db/pg_tips.htm]]