PostgreSQL (DBサーバー)/Ubuntu 8.04

以下のコマンドを実行して， postgresql をインストールする。

$ sudo apt-get install postgresql
/etc/postgresql/8.3/main/pg_hba.conf の以下の行を修正する。

local all all password
以下のコマンドを実行して， PostgreSQL を再起動する。

$ sudo /etc/init.d/postgresql-8.3 restart
以下のコマンドを実行して，ユーザアカウント postgres のパスワードを設定する。

$ sudo passwd postgres
ユーザアカウント postgres でログインする。
以下のコマンドを実行して，データベース template1 にアクセスする。

$ psql template1
以下のコマンドを実行して，データベースを終了する。

template1=# \q

データベース一覧の表示

$ psql -l
データベース template1 へのアクセス

$ psql template1
データベースの終了

template1=# \q
データベース testdb の作成

$ createdb testdb
データベース testdb の削除

$ dropdb testdb
データベース testdb のバックアップ

$ pg_dump testdb > /tmp/dbbackup
バックアップファイル dbbackup からデータベース testdb を復元

$ dropdb -U postgres testdb
$ createdb -U postgres testdb
$ psql -U postgres -e testdb < /tmp/dbbackup
ユーザ一覧を表示

template1=# SELECT usename FROM pg_user;
ユーザ abc を作成

template1=# CREATE USER abc WITH PASSWORD 'パスワード';
ユーザ abc のパスワードを変更

template1=# ALTER USER abc WITH PASSWORD '変更後のパスワード';
ユーザ abc を削除

template1=# DROP USER abc;
データベース testdb のテーブル一覧の表示

testdb=# \dt

データベース testdb にテーブル名 test2_user を作成


testdb=# CREATE TABLE test2_user (
testdb(# 	id SERIAL PRIMARY KEY,
testdb(# 	name VARCHAR(60),
testdb(# 	age INTEGER,
testdb(# 	note TEXT
testdb(# );

テーブル test2_user の名前を test_user に変更

testdb=# ALTER TABLE test2_user RENAME TO test_user;
テーブル test2_user のシーケンス id の削除

testdb=# DROP SEQUENCE test2_user_id_seq;
テーブル test_user の削除

testdb=# DROP TABLE test_user;
テーブル test_user に列名 kana を追加

testdb=# ALTER TABLE test_user ADD kana VARCHAR(80);
テーブル test_user の列名 kana を hiragana に変更

testdb=# ALTER TABLE test_user RENAME kana TO hiragana;

テーブル test_user の列名 id を削除

testdb=# SELECT name, age, note, hiragana INTO TABLE tmp_table FROM test_user;
testdb=# DROP TABLE test_user;
testdb=# ALTER TABLE tmp_table RENAME TO test_user;

テーブル test_user から CSV ファイル (/tmp/test_user.csv)) への書き出し

testdb=# COPY test_user TO '/tmp/test_user.csv' DELIMITERS ',';
CSV ファイル (/tmp/test_user.csv) からテーブル test_user への読み込み

testdb=# COPY test_user FROM '/tmp/test_user.csv' DELIMITERS ',';
テーブル test_user に対する参照のみのアクセス権をユーザー abc に設定

testdb=# GRANT SELECT ON test_user TO abc;
テーブル test_user に対する全てのアクセス権をユーザー abc に設定

testdb=# GRANT ALL ON test_user TO abc;
テーブル test_user に対する参照のみのアクセス権をユーザー abc から解除

testdb=# REVOKE SELECT ON test_user FROM abc;
テーブル test_user に対する全てのアクセス権を全てのユーザーから解除

testdb=# REVOKE ALL ON test_user FROM public;
テーブル test_user の項目一覧の表示

testdb=# \d test_user

数値型

タイプ	範囲
smallint	-32768～32767
integer	-2147483648～2147483647
bigint	-9223372036854775808～9223372036854775807
double	15桁精度
serial	1～2147483647

文字列

タイプ	範囲
char(文字数)	最大半角255文字の固定長文字列
varchar(文字数)	最大半角255文字の可変長文字列
text	無制限の可変長文字列
bytea	無制限のバイナリ列

テーブル名 test_user に名前が 'aaa' ，年齢が 31 才のレコードを追加

testdb=# INSERT INTO test_user(name, age) VALUES('aaa', 31);
testdb=# INSERT INTO test_user VALUES('aaa', 31, 'note');
テーブル名 test_user の全てのレコードの人の名前を 'bbb' に更新

testdb=# UPDATE test_user SET name='bbb';
テーブル名 test_user のレコードのうち，年齢が 31 才の人の名前を 'bbb' に更新

testdb=# UPDATE test_user SET name='bbb' WHERE age=31;
テーブル名 test_user の全てのレコードを削除

testdb=# DELETE FROM test_user;
テーブル名 test_user のレコードのうち，人の名前が 'bbb' のレコードを削除

testdb=# DELETE FROM test_user WHERE name='bbb';
テーブル名 test_user において，年齢が 30 才以上のレコードについて，年齢により昇順に並べ替えて，全ての列を表示

testdb=# SELECT * FROM test_user WHERE age>=30 ORDER BY age ASC;
テーブル名 test_user において，人の名前が 'a' で始まるレコードまたは名前が 'b' で終わるレコードについて，年齢により降順に並べ替えて，人の名前と年齢を表示

testdb=# SELECT name, age FROM test_user WHERE name LIKE 'a%' OR name LIKE '%b' ORDER BY age DESC;

テーブル名 test_user において，年齢が 30 才以上30才未満のレコードについて，人の名前を表示

testdb=# SELECT name FROM test_user WHERE age>=30 AND age<35;
testdb=# SELECT name FROM test_user WHERE age BETWEEN 30 AND 34;
testdb=# SELECT name FROM test_user WHERE age IN(30,31,32,33,34);

テーブル名 test_user において，重複したレコードを除去して，年齢を表示

testdb=# SELECT DISTINCT age FROM test_user;
テーブル名 test_user において，年齢別グループと構成数を表示

testdb=# SELECT age, COUNT(age) FROM test_user GROUP BY age;
テーブル名 test_user において，年齢が 33 才以下の年齢別グループの中で，構成数が 2 人以上のグループとその数を表示

testdb=# SELECT age, COUNT(age) FROM test_user WHERE age<=33 GROUP BY age HAVING COUNT(age)>=2;
テーブル名 test_user において，人の名前が同じグループとそのグループで一番高い年齢を表示

testdb=# SELECT name, MAX(age) FROM test_user GROUP BY name;
2 つのテーブル (test_user, uriage) について，すべてのレコードを組み合わせる結合（交差結合）

testdb=# SELECT * FROM test_user, uriage;

2 つのテーブル (test_user, uriage) に共通するカラム (name) をキーとして結合（内部結合）

testdb=# SELECT * FROM test_user, uriage WHERE test_user.name=uriage.name;
testdb=# SELECT * FROM test_user INNER JOIN uriage ON test_user.name=uriage.name;

2 つのテーブル (test_user, uriage) に共通するカラム (name, hiragana) をキーとして結合（内部結合）

testdb=# SELECT * FROM test_user, uriage WHERE test_user.name=uriage.name AND test_user.hiragana=uriage.hiragana;
testdb=# SELECT * FROM test_user INNER JOIN uriage ON test_user.name=uriage.name AND test_user.hiragana=uriage.hiragana;

2 つのテーブル (test_user, uriage) に共通するカラム (name) をキーとして，テーブル test_user を優先して結合（外部結合）

testdb=# SELECT * FROM test_user LEFT JOIN uriage ON test_user.name=uriage.name;
testdb=# SELECT * FROM uriage RIGHT JOIN test_user ON uriage.name=test_user.name;

PostgreSQL (DBサーバー)の構築

PostgreSQL のインストールと設定

PostgreSQL の操作コマンド

PostgreSQLのデータ型

SQLのコマンド

サイト内関連リンク：なし