目次

PostgreSQLコマンド

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

$ 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しておいた方がよい。

-主なオプション

  1. a データのみをダンプ
  2. b ラージオブジェクトもダンプする。(注: デフォルトではダンプされません)
  3. d INSERTコマンドにてダンプ(通常はCOPYコマンド)
  4. D カラム名を含めたINSERTコマンドにてダンプ
  5. s テーブルのみダンプ
  6. t 特定のテーブルのみダンプ
  7. 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)

-PostgreSQLのユーザ作成、データベース作成等の基本操作

http://www.develop-memo.com/database/postgresql/postgresqloperate.html

-PostgreSQLデータベースのコマンド

http://y-kit.jp/saba/xp/postgrecmd.htm

[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>http://ash.jp/db/pg_tips.htm

縦表示

mysql だと末尾に 「\G」と付けると、出力が縦表示なるけど、SQLだと「\x」を実行してからsqlを打つと縦表示になる。

通常横表示

testdb=# select pid,usename,client_addr from pg_stat_replication;
  pid  | usename  | client_addr  
-------+----------+--------------
 19549 | postgres | 172.16.0.114
(1 row)

「\x」を一回打っておく

testdb=# \x
Expanded display is on.

その後の出力は、縦表示になります。

testdb=# select pid,usename,client_addr from pg_stat_replication;
-[ RECORD 1 ]-------------
pid         | 19549
usename     | postgres
client_addr | 172.16.0.114

パスワードなしにする

export PGPASSWORD=hogehoge_password