■（補足）postgresパッケージのインストール用シェルの起動
・・・クラスタの初期化が不可だったため再インストールを行った
echo $PATH
ps aux | grep postgres
cd /tmp/setup
sh setup-intro.sh
■フォルダ構造
※PostgresSQLのインストール先
[root@localhost pgsql]# ls /usr/local/
bin etc games include lib libexec pgsql sbin share src
※インストールされたPostgresSQLフォルダの中身
[root@localhost pgsql]# ls /usr/local/pgsql/
bin data include lib share
※PostgresSQLの実行ファイル
[root@localhost pgsql]# ls /usr/local/pgsql/bin/
clusterdb droplang pg_archivecleanup pg_dumpall pgbench vacuumdb
createdb dropuser pg_config pg_resetxlog postgres vacuumlo
createlang ecpg pg_controldata pg_restore postmaster
createuser initdb pg_ctl pg_standby psql
dropdb oid2name pg_dump pg_upgrade reindexdb
※C言語用のincludeファイル
[root@localhost pgsql]# ls /usr/local/pgsql/include/
ecpg_config.h libpq pgtypes_error.h sqlca.h
ecpg_informix.h libpq-events.h pgtypes_interval.h sqlda-compat.h
ecpgerrno.h libpq-fe.h pgtypes_numeric.h sqlda-native.h
ecpglib.h pg_config.h pgtypes_timestamp.h sqlda.h
ecpgtype.h pg_config_manual.h postgres_ext.h
informix pg_config_os.h server
internal pgtypes_date.h sql3types.h
※C言語用のライブラリファイル
[root@localhost pgsql]# ls /usr/local/pgsql/lib/
_int.so libecpg.so pgrowlocks.so
adminpack.so libecpg.so.6 pgstattuple.so
ascii_and_mic.so libecpg.so.6.2 pgxs
auto_explain.so libecpg_compat.a plpgsql.so
autoinc.so libecpg_compat.so refint.so
btree_gin.so libecpg_compat.so.3 seg.so
btree_gist.so libecpg_compat.so.3.2 tablefunc.so
chkpass.so libpgport.a test_parser.so
citext.so libpgtypes.a timetravel.so
cube.so libpgtypes.so tsearch2.so
cyrillic_and_mic.so libpgtypes.so.3 unaccent.so
dblink.so libpgtypes.so.3.1 utf8_and_ascii.so
dict_int.so libpq.a utf8_and_big5.so
dict_snowball.so libpq.so utf8_and_cyrillic.so
dict_xsyn.so libpq.so.5 utf8_and_euc2004.so
earthdistance.so libpq.so.5.3 utf8_and_euc_cn.so
euc2004_sjis2004.so libpqwalreceiver.so utf8_and_euc_jp.so
euc_cn_and_mic.so lo.so utf8_and_euc_kr.so
euc_jp_and_sjis.so ltree.so utf8_and_euc_tw.so
euc_kr_and_mic.so moddatetime.so utf8_and_gb18030.so
euc_tw_and_big5.so pageinspect.so utf8_and_gbk.so
fuzzystrmatch.so passwordcheck.so utf8_and_iso8859.so
hstore.so pg_buffercache.so utf8_and_iso8859_1.so
insert_username.so pg_freespacemap.so utf8_and_johab.so
isn.so pg_stat_statements.so utf8_and_sjis.so
latin2_and_win1250.so pg_trgm.so utf8_and_sjis2004.so
latin_and_mic.so pg_upgrade_support.so utf8_and_uhc.so
libecpg.a pgcrypto.so utf8_and_win.so
※各種共有ファイル
[root@localhost pgsql]# ls /usr/local/pgsql/share/
contrib pg_service.conf.sample snowball_create.sql
conversion_create.sql postgres.bki sql_features.txt
doc postgres.description system_views.sql
information_schema.sql postgres.shdescription timezone
man postgresql.conf.sample timezonesets
pg_hba.conf.sample psqlrc.sample tsearch_data
pg_ident.conf.sample recovery.conf.sample
※追加提供されるモジュール類
[root@localhost pgsql]# ls /usr/local/pgsql/share/contrib/
_int.sql pg_freespacemap.sql uninstall_dict_xsyn.sql
adminpack.sql pg_stat_statements.sql uninstall_earthdistance.sql
autoinc.sql pg_trgm.sql uninstall_fuzzystrmatch.sql
btree_gin.sql pgcrypto.sql uninstall_hstore.sql
btree_gist.sql pgrowlocks.sql uninstall_int_aggregate.sql
chkpass.sql pgstattuple.sql uninstall_isn.sql
citext.sql refint.sql uninstall_lo.sql
cube.sql seg.sql uninstall_ltree.sql
dblink.sql tablefunc.sql uninstall_pageinspect.sql
dict_int.sql test_parser.sql uninstall_pg_buffercache.sql
dict_xsyn.sql timetravel.sql uninstall_pg_freespacemap.sql
earthdistance.sql tsearch2.sql uninstall_pg_stat_statements.sql
fuzzystrmatch.sql unaccent.sql uninstall_pg_trgm.sql
hstore.sql uninstall__int.sql uninstall_pgcrypto.sql
insert_username.sql uninstall_adminpack.sql uninstall_pgrowlocks.sql
int_aggregate.sql uninstall_btree_gin.sql uninstall_pgstattuple.sql
isn.sql uninstall_btree_gist.sql uninstall_seg.sql
lo.sql uninstall_chkpass.sql uninstall_tablefunc.sql
ltree.sql uninstall_citext.sql uninstall_test_parser.sql
moddatetime.sql uninstall_cube.sql uninstall_tsearch2.sql
pageinspect.sql uninstall_dblink.sql uninstall_unaccent.sql
pg_buffercache.sql uninstall_dict_int.sql
※HTMLのドキュメント類
[root@localhost pgsql]# ls /usr/local/pgsql/share/doc
contrib html
※man用のマニュアルファイル
[root@localhost pgsql]# ls /usr/local/pgsql/share/man/
man1 man3 man7
■インストール済みpostgresSQLの設定内容の確認
pg_config --configure
■データベースクラスタの初期化
initdb --encoding=UTF8 --no-locale
■postgresSQLの環境変数の確認
echo $PGDATA
＞/usr/local/pgsql/data
■
EXIT
xhost +
su - postgres
gedit /usr/local/pgsql/data/postgresql.conf
※設定ファイルの修正
pg_ctl -w restart
exit
useradd kudo
passwd kudo
■データベースデーモン(posgreDB)の起動
pg_ctl -w restart
■posgreスーパーユーザでの一般ユーザ作成＆権限設定
su - postgres
createuser kudo
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
exit
■一般ユーザIDでのDB作成
su - kudo
createdb userDB
psql -l
■
psql userDB
※psql userDB -U postgres
userDB=> select * from pg_class
\q
※もしくはCtrl+D
■
userDB=> CREATE TABLE fruit(
userDB(> id INTEGER,
userDB(> name TEXT,
userDB(> price INTEGER
userDB(> );
\d
List of relations
Schema | Name | Type | Owner
--------+-------+-------+-------
public | fruit | table | kudo
(1 row)
■
userDB=> \d fruit
Table "public.fruit"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
price | integer |
■
INSERT INTO fruit VALUES (1,'mikan',100);
INSERT INTO fruit(name,price,id) VALUES ('ringo',150,3);
select * from fruit order by price;
select price,name from fruit order by price;
select * from fruit where price > 100;
userDB=> select name as 名前 from fruit;
名前
-------
mikan
melon
ringo
(3 rows)
■
userDB=> select avg(price) from fruit ;
avg
----------------------
916.6666666666666667
update fruit set price = price - 10 where name='mikan';
DELETE from fruit where name='mikan';
■テーブルからファイルのエクスポート(バックアップ)
\copy fruit to fruit.dat CSV
lv fruit.dat
■
\copy fruit_2 from fruit.dat CSV
[root@localhost setup]# su - postgres
[postgres@localhost ~]$ psql userDB
psql (9.0.0)
Type "help" for help.
userDB=# COPY fruit(id,name) TO '/tmp/fruit.dat';
COPY 2
[postgres@localhost ~]$ cat /tmp/fruit.dat
2 melon
3 ringo
copy fruit_3 from '/tmp/fruit.dat';
drop TABLE fruit_3 ;
su - kudo
psql userDB
userDB=> create table kaiin(
userDB(> id serial,
userDB(> name text
userDB(> );
insert into kaiin (name) VALUES ('user1');
insert into kaiin (name) VALUES ('user2');
userDB=> select * from kaiin;
id | name
----+-------
1 | user1
2 | user2
3 | user3
(3 rows)
userDB=> \ds
List of relations
Schema | Name | Type | Owner
--------+--------------+----------+-------
public | kaiin_id_seq | sequence | kudo
(1 row)
userDB=> select nextval('kaiin_id_seq');
nextval
---------
4
(1 row)
userDB=> select round(25.6,0);
round
-------
26
(1 row)
userDB-> \df random();
userDB-> \df+ random();
userDB-> \x
※型の表示を横ではなく、縦に表示する。
userDB=> select current_date;
date
------------
2011-05-16
(1 row)
userDB=> select current_timestamp;
now
------------------------------
2011-05-16 15:39:08.94946+09
(1 row)
userDB=> select current_timestamp(0);
timestamptz
------------------------
2011-05-16 15:39:30+09
(1 row)
userDB=> SHOW port ;
port
------
5432
(1 row)
userDB=> SHOW client_encoding ;
client_encoding
-----------------
UTF8
(1 row)
select '01-15-2011' ::date;
set datestyle to Postgremes;
※１TRNのみ有効の値セット
■現在の「日付・時間(TIMESTAMPデータ型)」の表示
userDB=> select current_timestamp;
now
-------------------------------
2011-05-16 15:52:46.317798+09
(1 row)
■指定した日数を現在時刻に足して計算
userDB=> select current_timestamp
userDB-> + '100 day'::interval;
?column?
-------------------------------
2011-08-24 15:55:25.022655+09
(1 row)
■
userDB=> \lo_import '/bin/ls' 'ls command'
lo_import 16413
userDB=> \lo_list
Large objects
ID | Owner | Description
-------+-------+-------------
16413 | kudo | ls command
(1 row)
userDB=> \lo_export 16413 'ls_copy'
lo_export
userDB=> \q
[kudo@localhost ~]$ ls
fruit.dat ls_copy
[kudo@localhost ~]$ chmod a+x ls_copy
※ファイルに実行権限をあたえる。
[kudo@localhost ~]$ ./ls_copy
fruit.dat ls_copy
■null値の格納と表示確認
userDB=> CREATE TABLE nulltest(
userDB(> id int,
userDB(> name text
userDB(> );
userDB=> insert into nulltest values (1,'null');
userDB=> insert into nulltest values (2,'');
userDB=> insert into nulltest values (3,null);
userDB=> select * from nulltest ;
id | name
----+------
1 | null
2 |
3 |
(3 rows)
userDB=> select * from nulltest where name is null;
id | name
----+------
3 |
(1 row)
userDB=> select * from nulltest where name is not null;
id | name
----+------
1 | null
2 |
(2 rows)
userDB=> \pset null (NULL)
Null display is "(NULL)".
※null値を「NULL」と表示するように一時的なセット
userDB=> select * from nulltest ;
id | name
----+--------
1 | null
2 |
3 | (NULL)
(3 rows)
■テーブル構築時の変数に対する制約
userDB=> CREATE TABLE products(
userDB(> id int,
userDB(> name text not null,
userDB(> price int
userDB(> );
CREATE TABLE
userDB=> insert into products VALUES (1,'mikan',100);
INSERT 0 1
userDB=> insert into products VALUES (2,NULL,100);
ERROR: null value in column "name" violates not-null constraint
userDB=> insert into products VALUES (3,'NULL',100);
INSERT 0 1
userDB=> insert into products VALUES (4,'',100);
INSERT 0 1
userDB=> select * from products ;
id | name | price
----+-------+-------
1 | mikan | 100
3 | NULL | 100
4 | | 100
(3 rows)
■
userDB=> CREATE TABLE media(
userDB(> id int,
userDB(> name text,
userDB(> price int check(price > 100)
userDB(> );
CREATE TABLE
userDB=> insert into media VALUES (1,'flopy1',100);
ERROR: new row for relation "media" violates check constraint "media_price_check"
userDB=> insert into media VALUES (2,'flopy2',150);
INSERT 0 1
userDB=> select * from media ;
id | name | price
----+--------+-------
2 | flopy2 | 150
(1 row)
■
userDB=> CREATE TABLE cable(
userDB(> id int UNIQUE,
userDB(> name text,
userDB(> price int
userDB(> );
NOTICE: CREATE TABLE / UNIQUE will create implicit index "cable_id_key" for table "cable"
CREATE TABLE
userDB=> \di
List of relations
Schema | Name | Type | Owner | Table
--------+--------------+-------+-------+-------
public | cable_id_key | index | kudo | cable
(1 row)
userDB=> INSERT INTO cable VALUES (1,'CAT5',100);
userDB=> INSERT INTO cable VALUES (1,'CATe',150);
ERROR: duplicate key value violates unique constraint "cable_id_key"
DETAIL: Key (id)=(1) already exists.
userDB=> INSERT INTO cable VALUES (2,'CATe',150);
userDB=> INSERT INTO cable VALUES (NULL,'CATe',150);
userDB=> select * from cable ;
id | name | price
----+------+-------
1 | CAT5 | 100
2 | CATe | 150
| CATe | 150
(3 rows)
userDB=> \d cable
Table "public.cable"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
price | integer |
Indexes:
"cable_id_key" UNIQUE, btree (id)
■
userDB=> CREATE TABLE drink(
userDB(> id int PRIMARY KEY,
userDB(> name text,
userDB(> price int
userDB(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "drink_pkey" for table "drink"
CREATE TABLE
userDB=> insert into drink values(1,'tea',250);
INSERT 0 1
userDB=> insert into drink values(2,'coffee',300);
INSERT 0 1
userDB=> insert into drink values(2,'test',300);
ERROR: duplicate key value violates unique constraint "drink_pkey"
DETAIL: Key (id)=(2) already exists.
userDB=> insert into drink values(NULL,'',30);
ERROR: null value in column "id" violates not-null constraint
userDB=> \d drink
Table "public.drink"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
name | text |
price | integer |
Indexes:
"drink_pkey" PRIMARY KEY, btree (id)
■データ入力の際に設定するデフォルト値の設定
userDB=> CREATE TABLE cellphone(
id int,
name text,mp
reg timestamp default current_timestamp
);
userDB=> INSERT INTO cellphone (id,name) VALUES (1,'HT03A');
userDB=> INSERT INTO cellphone (id,name,reg) VALUES (1,'HT03A',now());
userDB=> select * from cellphone ;
id | name | reg
----+-------+----------------------------
1 | HT03A | 2011-05-16 17:06:25.996802
1 | HT03A | 2011-05-16 17:07:28.863403
(2 rows)
■主キー(被参照テーブル)と外部キー(参照テーブル)の設定
userDB=> CREATE TABLE kudamono(
item_id int primary key,
name text,
price int
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "kudamono_pkey" for table "kudamono"
CREATE TABLE
userDB=>
userDB=> CREATE TABLE hanbai(
userDB(> id int,
userDB(> item_id int references kudamono(item_id),
userDB(> number int,
userDB(> saletime timestamp default current_timestamp
userDB(> );
CREATE TABLE
userDB=> INSERT INTO kudamono VALUES (1,'mikan',100);
userDB=> INSERT INTO kudamono VALUES (2,'melon',2500);
userDB=> INSERT INTO kudamono VALUES (3,'ringo',150);
userDB=> insert into hanbai values (1,2,8);
userDB=> insert into hanbai values (2,20,80);
ERROR: insert or update on table "hanbai" violates foreign key constraint "hanbai_item_id_fkey"
DETAIL: Key (item_id)=(20) is not present in table "kudamono".
userDB=> select * from kudamono ;
item_id | name | price
---------+-------+-------
1 | mikan | 100
2 | melon | 2500
3 | ringo | 150
(3 rows)
userDB=> select * from hanbai ;
id | item_id | number | saletime
----+---------+--------+----------------------------
1 | 2 | 8 | 2011-05-16 17:18:34.216762
(1 row)
■
userDB=>
CREATE TABLE nyuuka(
id int,
item_id int references kudamono(item_id) on update cascade,
num int,
nyuukabi date
);
userDB=> INSERT INTO nyuuka VALUES (1,3,100,'2009-12-27'::date);
userDB=> UPDATE kudamono SET item_id =103 where item_id = 3;
userDB=> select * from nyuuka;
id | item_id | num | nyuukabi
----+---------+-----+------------
1 | 103 | 100 | 2009-12-27
(1 row)
■
userDB=> DROP TABLE kudamono ;
ERROR: cannot drop table kudamono because other objects depend on it
DETAIL: constraint hanbai_item_id_fkey on table hanbai depends on table kudamono
constraint nyuuka_item_id_fkey on table nyuuka depends on table kudamono
HINT: Use DROP ... CASCADE to drop the dependent objects too.
userDB=> DROP TABLE kudamono CASCADE;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to constraint hanbai_item_id_fkey on table hanbai
drop cascades to constraint nyuuka_item_id_fkey on table nyuuka
DROP TABLE
userDB=> \d
List of relations
Schema | Name | Type | Owner
--------+--------------+----------+-------
public | cable | table | kudo
public | cellphone | table | kudo
public | drink | table | kudo
public | fruit | table | kudo
public | fruit_2 | table | kudo
public | hanbai | table | kudo
public | kaiin | table | kudo
public | kaiin_id_seq | sequence | kudo
public | media | table | kudo
public | nulltest | table | kudo
public | nyuuka | table | kudo
public | products | table | kudo
(12 rows)
userDB=> \d hanbai
Table "public.hanbai"
Column | Type | Modifiers
----------+-----------------------------+---------------
id | integer |
item_id | integer |
number | integer |
saletime | timestamp without time zone | default now()
userDB=> \d nyuuka
Table "public.nyuuka"
Column | Type | Modifiers
----------+---------+-----------
id | integer |
item_id | integer |
num | integer |
nyuukabi | date |
■DOMAINの定義
userDB=> CREATE DOMAIN examscore as int check (value >=0 and value <= 100);
CREATE DOMAIN
userDB=> CREATE TABLE score (
userDB(> id int,
userDB(> math examscore,
userDB(> science examscore
userDB(> );
CREATE TABLE
userDB=> INSERT INTO score VALUES (1,80,101);
ERROR: value for domain examscore violates check constraint "examscore_check"
userDB=> select * from score ;
id | math | science
----+------+---------
(0 rows)
■一時的(DBへの接続中)なテーブル作成
userDB=> CREATE TEMP TABLE shukei( id int,name text);
CREATE TABLE
userDB=> \d shukei
Table "pg_temp_3.shukei"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
userDB=> \q
[kudo@localhost ~]$ psql userDB
psql (9.0.0)
Type "help" for help.
userDB=> \d shukei
Did not find any relation named "shukei".
■検索結果から新テーブルの作成
userDB=> CREATE TABLE fruit_name as
SELECT name from fruit;
SELECT 2
userDB=> SELECT * from fruit_name ;
name
-------
melon
ringo
(2 rows)
■仮想的なテーブル（カラム毎のアクセス制限、問い合わせの簡易化のため）の作成
userDB=> CREATE VIEW fruit_view AS SELECT name from fruit;
CREATE VIEW
userDB=> SELECT * from fruit_view ;
name
-------
melon
ringo
(2 rows)
userDB=> \d+ fruit;
Table "public.fruit"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+----------+-------------
id | integer | | plain |
name | text | | extended |
price | integer | | plain |
Has OIDs: no
userDB=> \d+ fruit_view;
View "public.fruit_view"
Column | Type | Modifiers | Storage | Description
--------+------+-----------+----------+-------------
name | text | | extended |
View definition:
SELECT fruit.name
FROM fruit;
userDB=> \d fruit_view ;
View "public.fruit_view"
Column | Type | Modifiers
--------+------+-----------
name | text |
■VIEWコマンドを使用したカラムアクセス(参照)制限 ?例：ユーザID・PWを管理している「pg_roles」
userDB=> \d+ pg_roles
View "pg_catalog.pg_roles"
Column | Type | Modifiers | Storage | Description
---------------+--------------------------+-----------+----------+-------------
rolname | name | | plain |
rolsuper | boolean | | plain |
rolinherit | boolean | | plain |
rolcreaterole | boolean | | plain |
rolcreatedb | boolean | | plain |
rolcatupdate | boolean | | plain |
rolcanlogin | boolean | | plain |
rolconnlimit | integer | | plain |
rolpassword | text | | extended |
rolvaliduntil | timestamp with time zone | | plain |
rolconfig | text[] | | extended |
oid | oid | | plain |
View definition:
SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, s.setconfig AS rolconfig, pg_authid.oid
FROM pg_authid
LEFT JOIN pg_db_role_setting s ON pg_authid.oid = s.setrole AND s.setdatabase = 0::oid;
userDB=> \x
Expanded display is on.
userDB=> SELECT * from pg_roles;
-[ RECORD 1 ]-+---------
rolname | postgres
rolsuper | t
rolinherit | t
rolcreaterole | t
rolcreatedb | t
rolcatupdate | t
rolcanlogin | t
rolconnlimit | -1
rolpassword | ********
rolvaliduntil |
rolconfig |
oid | 10
-[ RECORD 2 ]-+---------
rolname | kudo
rolsuper | f
rolinherit | t
rolcreaterole | f
rolcreatedb | t
rolcatupdate | f
rolcanlogin | t
rolconnlimit | -1
rolpassword | ********
rolvaliduntil |
rolconfig |
oid | 16384
userDB=> \q
[kudo@localhost ~]$ psql -U postgres userDB
psql (9.0.0)
Type "help" for help.
userDB=# \x
Expanded display is on.
userDB=# SELECT * from pg_roles;
-[ RECORD 1 ]-+---------
rolname | postgres
rolsuper | t
rolinherit | t
rolcreaterole | t
rolcreatedb | t
rolcatupdate | t
rolcanlogin | t
rolconnlimit | -1
rolpassword | ********
rolvaliduntil |
rolconfig |
oid | 10
-[ RECORD 2 ]-+---------
rolname | kudo
rolsuper | f
rolinherit | t
rolcreaterole | f
rolcreatedb | t
rolcatupdate | f
rolcanlogin | t
rolconnlimit | -1
rolpassword | ********
rolvaliduntil |
rolconfig |
oid | 16384
■ルール(RULEコマンド)によるSQL文の書き換え ?例：自動的にログ取得など
userDB=> SELECT * from fruit_view ;
name
-------
melon
ringo
(2 rows)
userDB=> SELECT * from fruit;
id | name | price
----+-------+-------
2 | melon | 2500
3 | ringo | 150
(2 rows)
userDB=> CREATE RULE update_fruit_view AS ON UPDATE
userDB-> to fruit_view do instead update fruit
userDB-> SET name = NEW.name where name = OLD.name;
CREATE RULE
userDB=> UPDATE fruit_view SET name='メロン' where name='melon';
UPDATE 1
userDB=> SELECT * from fruit_view ;
name
--------
ringo
メロン
(2 rows)
userDB=> SELECT * from fruit;
id | name | price
----+--------+-------
3 | ringo | 150
2 | メロン | 2500
(2 rows)
■継承（親テーブル定義の子テーブルへの継承）…子テーブル情報(一部)も親テーブルへ自動書き込み
userDB=> CREATE TABLE emp(name text);
CREATE TABLE
userDB=> CREATE TABLE manager (title text) inherits (emp);
CREATE TABLE
userDB=> \d emp
Table "public.emp"
Column | Type | Modifiers
--------+------+-----------
name | text |
Number of child tables: 1 (Use \d+ to list them.)
userDB=> \d manager
Table "public.manager"
Column | Type | Modifiers
--------+------+-----------
name | text |
title | text |
Inherits: emp
userDB=> INSERT INTO emp VALUES ('Bob');
INSERT 0 1
userDB=> INSERT INTO manager VALUES ('John','chief');
INSERT 0 1
userDB=> SELECT * from emp ;
name
------
Bob
John
(2 rows)
userDB=> SELECT * from manager;
name | title
------+-------
John | chief
(1 row)
userDB=> \d+ emp
Table "public.emp"
Column | Type | Modifiers | Storage | Description
--------+------+-----------+----------+-------------
name | text | | extended |
Child tables: manager
Has OIDs: no
userDB=> \d+ manager
Table "public.manager"
Column | Type | Modifiers | Storage | Description
--------+------+-----------+----------+-------------
name | text | | extended |
title | text | | extended |
Inherits: emp
Has OIDs: no
■スキーマ(DB内の名前空間)の設定
※包括イメージ
データベースクラスタ(template0,template1,)
→データベース（userDB）
→スキーマ（public,foo）
userDB=> CREATE SCHEMA foo;
CREATE SCHEMA
userDB=> CREATE TABLE t1(v int);
CREATE TABLE
userDB=> CREATE TABLE t1(v int);
ERROR: relation "t1" already exists
userDB=> CREATE TABLE t1(vfoo int);
ERROR: relation "t1" already exists
userDB=> CREATE TABLE foo.t1(vfoo int);
CREATE TABLE
userDB=> SHOW search_path \;
userDB-> ;
search_path
----------------
"$user",public
(1 row)
userDB=> \d
List of relations
Schema | Name | Type | Owner
--------+--------------+----------+-------
public | cable | table | kudo
public | cellphone | table | kudo
public | drink | table | kudo
public | emp | table | kudo
public | fruit | table | kudo
public | fruit_2 | table | kudo
public | fruit_name | table | kudo
public | fruit_view | view | kudo
public | hanbai | table | kudo
public | kaiin | table | kudo
public | kaiin_id_seq | sequence | kudo
public | manager | table | kudo
public | media | table | kudo
public | nulltest | table | kudo
public | nyuuka | table | kudo
public | products | table | kudo
public | score | table | kudo
public | t1 | table | kudo
(18 rows)
userDB=> SELECT * from t1;
v
---
(0 rows)
userDB=> \d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-----------
v | integer |
userDB=> \d foo.t1
Table "foo.t1"
Column | Type | Modifiers
--------+---------+-----------
vfoo | integer |
userDB=> ※パスを変更し、foo名前空間を優先度化
userDB->
userDB-> ;
ERROR: syntax error at or near "※パスを変更し、foo名前空間を優先度化"
LINE 1: ※パスを変更し、foo名前空間を優先度化
^
userDB=> \dn
List of schemas
Name | Owner
--------------------+----------
foo | kudo
information_schema | postgres
pg_catalog | postgres
pg_toast | postgres
pg_toast_temp_1 | postgres
pg_toast_temp_3 | postgres
public | postgres
(7 rows)
userDB=> ↑ スキーマ一覧の表示;
ERROR: syntax error at or near "↑"
LINE 1: ↑ スキーマ一覧の表示;
^
userDB=> SELECT current_schema();
current_schema
----------------
public
(1 row)
userDB=> SET search_path TO foo, public;
SET
userDB=> SELECT * from t1 ;
vfoo
------
(0 rows)
■新しいデータベースの作成
[kudo@localhost ~]$ createdb bookstore
■テーブルの一括作成(SQL文を記述したファイルを実行)
[kudo@localhost ~]$ psql bookstore -f /tmp/pgsql90-intro/bookstore.sql
BEGIN
SET
…
※作成テーブルの確認
[kudo@localhost ~]$ psql bookstore
psql (9.0.0)
Type "help" for help.
bookstore=> \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+-------
public | customer | table | kudo
public | customer2 | table | kudo
public | employee | table | kudo
public | item | table | kudo
public | j1 | table | kudo
public | j2 | table | kudo
public | sales | table | kudo
public | t_score | table | kudo
public | t_survey | table | kudo
(9 rows)
?５章SELECT文?
■昇順並び替えの表示
bookstore=> SELECT id,name,price FROM item ORDER BY price;
id | name | price
----+----------------------------------------------+-------
9 | SQLポケットリファレンス | 1980
11 | Database-SQL-Rdbms Howto | 2634
8 | SQLクイックリファレンス | 2800
7 | PHP x PostgreSQLで作る最強Webシステム | 2880
15 | PHP4徹底攻略 実戦編 | 3800
14 | PHP4徹底攻略 改訂版 | 3800
bookstore=> SELECT id,name,price FROM item ORDER BY price,id;
id | name | price
----+----------------------------------------------+-------
9 | SQLポケットリファレンス | 1980
11 | Database-SQL-Rdbms Howto | 2634
8 | SQLクイックリファレンス | 2800
7 | PHP x PostgreSQLで作る最強Webシステム | 2880
14 | PHP4徹底攻略 改訂版 | 3800
15 | PHP4徹底攻略 実戦編 | 3800
■降順並び替えの表示
bookstore=> SELECT id,name,price FROM item ORDER BY price DESC;
id | name | price
----+----------------------------------------------+-------
12 | トランザクション処理〈上〉 | 6800
13 | トランザクション処理〈下〉 | 6800
■検索結果（昇順・降順並び替え後）の範囲指定の表示
bookstore=> SELECT id,name,price FROM item ORDER BY price,id LIMIT 20 OFFSET 10;
id | name | price
----+---------------------------------------+-------
1 | 改定第3版 PostgreSQL完全攻略ガイド | 4980
3 | PostgreSQLオフィシャルマニュアル | 4980
6 | PostgreSQL: Introduction and Concepts | 5758
12 | トランザクション処理〈上〉 | 6800
13 | トランザクション処理〈下〉 | 6800
(5 rows)
■相関名(仮の別名)でのテーブル表示 ※テーブル同士の結合も
bookstore=> SELECT s.id, i.name, s.number FROM
bookstore-> sales AS s, item AS i WHERE i.id=s.item_id;
id | name | number
-----+----------------------------------------------+--------
1 | PostgreSQLオフィシャルマニュアル | 1
2 | SQLポケットリファレンス | 1
3 | 実践PostgreSQL | 1
4 | PostgreSQL: Introduction and Concepts | 10
■検索結果から重複を除く
bookstore=> SELECT DISTINCT ON (sales.item_id) item.name
bookstore-> FROM sales,item WHERE item.id = sales.item_id;
name
----------------------------------------------
改定第3版 PostgreSQL完全攻略ガイド
実践PostgreSQL
PostgreSQLオフィシャルマニュアル
エキスパートから学ぶPostgreSQL活用テクニック
はじめてのPostgreSQL
PostgreSQL: Introduction and Concepts
PHP x PostgreSQLで作る最強Webシステム
SQLクイックリファレンス
SQLポケットリファレンス
Postgresql Essential Reference
Database-SQL-Rdbms Howto
トランザクション処理〈上〉
トランザクション処理〈下〉
PHP4徹底攻略 改訂版
PHP4徹底攻略 実戦編
(15 rows)
■
bookstore=> SELECT item_id,sum(number) FROM sales GROUP BY item_id
bookstore-> HAVING sum(number) > 10 ORDER BY sum(number);
item_id | sum
---------+-----
10 | 15
15 | 19
7 | 24
3 | 29
9 | 31
6 | 34
2 | 54
1 | 64
(8 rows)
■条件文の記述
bookstore=> SELECT item_id,
CASE WHEN sum(number) > 10 THEN 'GOOD!'
ELSE 'BAT!!!'
END
FROM sales GROUP BY item_id;
item_id | case
---------+--------うr
6 | GOOD!
14 | BAT!!!
■テーブル内にあるNULL値のカラムを変換
bookstore=> \d t_survey
Table "public.t_survey"
Column | Type | Modifiers
---------+---------+-----------
id | integer |
comment | text |
bookstore=> SELECT * from t_survey ;
id | comment
----+---------------------
1 | uuum, This is good.
2 | So-so
3 |
4 | good
5 | worse !
(5 rows)
bookstore=> SELECT COALESCE (comment,'no coment') from t_survey ;
coalesce
---------------------
uuum, This is good.
So-so
no coment
good
worse !
(5 rows)
■値が条件に一致する際にNULL値を返却?例：特定の数字を省いて平均値計算
bookstore=> \d t_score
Table "public.t_score"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
point | integer |
bookstore=> SELECT * FROM t_score ;
id | point
----+-------
1 | 90
2 | 50
3 | 55
4 | 77
5 | 85
6 | 999
7 | 12
8 | 32
9 | 42
10 | 60
(10 rows)
bookstore=> SELECT avg(point) FROM t_score ;
avg
----------------------
150.2000000000000000
(1 row)
bookstore=> SELECT avg(NULLIF(point,999)) FROM t_score ;
avg
---------------------
55.8888888888888889
(1 row)
■テーブル同士の条件付の結合１
bookstore=> SELECT * FROM sales JOIN customer ON sales.customer_id = customer.id;
id | customer_id | item_id | number | time | id | name | email | pho
ne | address
-----+-------------+---------+--------+---------------------+----+------------+----------------+-------
-------+---------
1 | 1 | 3 | 1 | 2008-01-07 04:05:06 | 1 | 守屋一郎 | moriya@foo.bar | 03-111
1-2222 | 東京都
2 | 3 | 9 | 1 | 2008-01-08 09:30:25 | 3 | 宮崎三郎 | miyaz@foo.bar | 048-33
3-4444 | 埼玉県
3 | 9 | 2 | 1 | 2008-01-08 09:31:40 | 9 | 村上九朗 | mura@foo.bar | 043-11
1-2222 | 千葉県
4 | 4 | 6 | 10 | 2008-01-08 12:22:30 | 4 | 寺本四郎 | tm@baz.bar | 048-44
4-5555 | 埼玉県
■テーブル同士の条件付の結合２
bookstore=> \d j1
Table "public.j1"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
j1_val | text |
bookstore=> \d j2
Table "public.j2"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
j2_val | text |
bookstore=> SELECT * FROM j1;
id | j1_val
----+--------
1 | 一
1 | 壱
2 | 二
3 | 三
5 | 五
(5 rows)
bookstore=> SELECT * FROM j2;
id | j2_val
----+--------
0 | zero
1 | one
2 | two
3 | three
4 | four
(5 rows)
bookstore=> SELECT * FROM j1,j2 WHERE j1.id = j2.id;
id | j1_val | id | j2_val
----+--------+----+--------
1 | 一 | 1 | one
1 | 壱 | 1 | one
2 | 二 | 2 | two
3 | 三 | 3 | three
(4 rows)
■テーブル同士の条件付の結合４?左のテーブル(j1)を起点とする
bookstore=> SELECT * FROM j1 LEFT OUTER JOIN j2 USING (id);
id | j1_val | j2_val
----+--------+--------
1 | 一 | one
1 | 壱 | one
2 | 二 | two
3 | 三 | three
5 | 五 |
(5 rows)
■テーブル同士の条件付の結合５?右のテーブル(j2)を起点とする
bookstore=> SELECT * FROM j1 RIGHT OUTER JOIN j2 USING (id);
id | j1_val | j2_val
----+--------+--------
0 | | zero
1 | 一 | one
1 | 壱 | one
2 | 二 | two
3 | 三 | three
4 | | four
(6 rows)
■テーブル同士の条件付の結合６?両方のテーブル(j1、j2)を起点とする
bookstore=> SELECT * FROM j1 FULL OUTER JOIN j2 USING (id);
id | j1_val | j2_val
----+--------+--------
0 | | zero
1 | 一 | one
1 | 壱 | one
2 | 二 | two
3 | 三 | three
4 | | four
5 | 五 |
(7 rows)
■テーブル同士の条件付の結合７?両方のテーブル(j1、j2)の総当たり表示
bookstore=> SELECT * FROM j1 CROSS JOIN j2;
id | j1_val | id | j2_val
----+--------+----+--------
1 | 一 | 0 | zero
1 | 一 | 1 | one
1 | 一 | 2 | two
1 | 一 | 3 | three
1 | 一 | 4 | four
1 | 壱 | 0 | zero
1 | 壱 | 1 | one
1 | 壱 | 2 | two
1 | 壱 | 3 | three
1 | 壱 | 4 | four
2 | 二 | 0 | zero
2 | 二 | 1 | one
2 | 二 | 2 | two
■サブクエリー …条件に当てはまる行を検索表示
bookstore=> \d item
Table "public.item"
Column | Type | Modifiers
-------------+---------+---------------------------------------------------
id | integer | not null default nextval('item_id_seq'::regclass)
name | text | not null
price | integer |
description | text |
Indexes:
"item_pkey" PRIMARY KEY, btree (id)
Check constraints:
"item_price_check" CHECK (price > 0)
Referenced by:
TABLE "sales" CONSTRAINT "item_id_fk" FOREIGN KEY (item_id) REFERENCES item(id)
bookstore=> SELECT * FROM item;
id | name | price | description
----+----------------------------------------------+-------+-------------------------------------------
--------------------------
1 | 改定第3版 PostgreSQL完全攻略ガイド | 4980 | 技術評論社 石井達夫 著
2 | 実践PostgreSQL | 4800 | オライリー・ジャパン ジョン・ウォースレイ
著
bookstore=> SELECT min(price) FROM item;
min
------
1980
(1 row)
bookstore=> SELECT name,price FROM item
bookstore-> WHERE item.price = (SELECT min(price) FROM item);
name | price
-------------------------+-------
SQLポケットリファレンス | 1980
(1 row)
■サブクエリー…複数行の場合の検索結果の表示
bookstore=> SELECT item_id FROM sales
WHERE EXTRACT (MONTH FROM time) =2 ;
item_id
---------
1
6
3
9
12
bookstore=> SELECT id,name,price FROM item
WHERE item.id IN(SELECT item_id FROM sales WHERE EXTRACT (MONTH FROM time) =2 ) ORDER BY id;
id | name | price
----+----------------------------------------------+-------
1 | 改定第3版 PostgreSQL完全攻略ガイド | 4980
2 | 実践PostgreSQL | 4800
3 | PostgreSQLオフィシャルマニュアル | 4980
4 | エキスパートから学ぶPostgreSQL活用テクニック | 3980
6 | PostgreSQL: Introduction and Concepts | 5758
7 | PHP x PostgreSQLで作る最強Webシステム | 2880
8 | SQLクイックリファレンス | 2800
■サブクエリー…？？？
bookstore=> SELECT name FROM item WHERE EXISTS(
SELECT * FROM sales WHERE sales.item_id = item.id AND EXTRACT (MONTH FROM time) = 1)
ORDER BY id;
name
----------------------------------------------
改定第3版 PostgreSQL完全攻略ガイド
実践PostgreSQL
PostgreSQLオフィシャルマニュアル
■サブクエリー…データ数をカウントし、それにマッチングするユーザIDを表示
bookstore=> SELECT * FROM sales;
id | customer_id | item_id | number | time
-----+-------------+---------+--------+---------------------
1 | 1 | 3 | 1 | 2008-01-07 04:05:06
2 | 3 | 9 | 1 | 2008-01-08 09:30:25
bookstore=> SELECT customer_id, count(*) FROM sales GROUP BY customer_id;
customer_id | count
-------------+-------
6 | 11
8 | 9
1 | 11
2 | 9
3 | 20
10 | 8
4 | 14
5 | 10
9 | 4
7 | 4
(10 rows)
bookstore=> SELECT c.name, v.count FROM (
SELECT customer_id, count(*) FROM sales GROUP BY customer_id) AS v
JOIN customer c ON v.customer_id = c.id ORDER BY id DESC;
name | count
------------+-------
紺野十兵衛 | 8
村上九朗 | 4
細川八朗 | 9
神田七朗 | 4
渡辺六朗 | 11
前川五郎 | 10
寺本四郎 | 14
宮崎三郎 | 20
山形二郎 | 9
守屋一郎 | 11
(10 rows)
■SELECT表示結果のまとめ
bookstore=> SELECT * FROM customer ;
id | name | email | phone | address
----+------------+----------------+--------------+---------
1 | 守屋一郎 | moriya@foo.bar | 03-1111-2222 | 東京都
2 | 山形二郎 | yamaga@foo.bar | 03-2222-3333 | 東京都
3 | 宮崎三郎 | miyaz@foo.bar | 048-333-4444 | 埼玉県
4 | 寺本四郎 | tm@baz.bar | 048-444-5555 | 埼玉県
5 | 前川五郎 | maek@baz.bar | 03-5555-6666 | 東京都
6 | 渡辺六朗 | wat@baz.bar | 03-6666-7777 | 東京都
7 | 神田七朗 | kan@hoge.bar | 03-7777-8888 | 東京都
8 | 細川八朗 | hoso@hoge.bar | 03-8888-9999 | 東京都
9 | 村上九朗 | mura@foo.bar | 043-111-2222 | 千葉県
10 | 紺野十兵衛 | kon@foo.bar | 043-222-3333 | 千葉県
(10 rows)
bookstore=> SELECT * FROM customer2 ORDER BY id;
id | name | email | phone | address
----+------------+----------------+--------------+---------
1 | 守屋一郎 | moriya@foo.bar | 03-1111-2222 | 東京都
20 | 佐々木二葉 | futaba@foo.bar | 03-0992-2345 | 東京都
30 | 湯元三恵 | mie@foo.bar | 048-999-8888 | 埼玉県
(3 rows)
bookstore=> SELECT * FROM customer UNION ALL SELECT * FROM customer2 ORDER BY id;
id | name | email | phone | address
----+------------+----------------+--------------+---------
1 | 守屋一郎 | moriya@foo.bar | 03-1111-2222 | 東京都
1 | 守屋一郎 | moriya@foo.bar | 03-1111-2222 | 東京都
2 | 山形二郎 | yamaga@foo.bar | 03-2222-3333 | 東京都
3 | 宮崎三郎 | miyaz@foo.bar | 048-333-4444 | 埼玉県
4 | 寺本四郎 | tm@baz.bar | 048-444-5555 | 埼玉県
5 | 前川五郎 | maek@baz.bar | 03-5555-6666 | 東京都
6 | 渡辺六朗 | wat@baz.bar | 03-6666-7777 | 東京都
7 | 神田七朗 | kan@hoge.bar | 03-7777-8888 | 東京都
8 | 細川八朗 | hoso@hoge.bar | 03-8888-9999 | 東京都
9 | 村上九朗 | mura@foo.bar | 043-111-2222 | 千葉県
10 | 紺野十兵衛 | kon@foo.bar | 043-222-3333 | 千葉県
20 | 佐々木二葉 | futaba@foo.bar | 03-0992-2345 | 東京都
30 | 湯元三恵 | mie@foo.bar | 048-999-8888 | 埼玉県
(13 rows)
■検索結果の共通部分（積）の表示
bookstore=> SELECT * FROM customer INTERSECT SELECT * FROM customer2 ORDER BY id;
id | name | email | phone | address
----+----------+----------------+--------------+---------
1 | 守屋一郎 | moriya@foo.bar | 03-1111-2222 | 東京都
(1 row)
■検索結果の共通部分を除いたA側「customer」の結果
bookstore=> SELECT * FROM customer EXCEPT SELECT * FROM customer2 ORDER BY id;
id | name | email | phone | address
----+------------+----------------+--------------+---------
2 | 山形二郎 | yamaga@foo.bar | 03-2222-3333 | 東京都
3 | 宮崎三郎 | miyaz@foo.bar | 048-333-4444 | 埼玉県
4 | 寺本四郎 | tm@baz.bar | 048-444-5555 | 埼玉県
5 | 前川五郎 | maek@baz.bar | 03-5555-6666 | 東京都
6 | 渡辺六朗 | wat@baz.bar | 03-6666-7777 | 東京都
7 | 神田七朗 | kan@hoge.bar | 03-7777-8888 | 東京都
8 | 細川八朗 | hoso@hoge.bar | 03-8888-9999 | 東京都
9 | 村上九朗 | mura@foo.bar | 043-111-2222 | 千葉県
10 | 紺野十兵衛 | kon@foo.bar | 043-222-3333 | 千葉県
(9 rows)
■
bookstore=> DECLARE item_cursor CURSOR WITH HOLD
bookstore-> FOR SELECT * FROM item ORDER BY id;
DECLARE CURSOR
※検索結果の表示
bookstore=> FETCH IN item_cursor;
id | name | price | description
----+-------------------------------------+-------+-------------------------
1 | 改定第3版 PostgreSQL完全攻略ガイド | 4980 | 技術評論社 石井達夫 著
(1 row)
※検索結果のカーソルを２つ進め表示
bookstore=> FETCH FORWARD 2 FROM item_cursor;
id | name | price | description
----+----------------------------------+-------+----------------------------------------------------
2 | 実践PostgreSQL | 4800 | オライリー・ジャパン ジョン・ウォースレイ 著
3 | PostgreSQLオフィシャルマニュアル | 4980 | インプレス PostgreSQL Global Development Group 著
(2 rows)
※検索結果のカーソルを１つ戻り表示
bookstore=> FETCH BACKWARD 1 FROM item_cursor;
id | name | price | description
----+----------------+-------+-----------------------------------------------
2 | 実践PostgreSQL | 4800 | オライリー・ジャパン ジョン・ウォースレイ 著
(1 row)
※検索結果のカーソルを１つ進める
bookstore=> MOVE NEXT IN item_cursor;
MOVE 1
※検索結果のカーソルを１つ進め表示
bookstore=> FETCH IN item_cursor;
id | name | price | description
----+----------------------------------------------+-------+-------------------------------------------
----------------
4 | エキスパートから学ぶPostgreSQL活用テクニック | 3980 | インプレス リチャード・ストーンズ／ニール
・マシュー 共著
(1 row)
※検索結果のカーソルを最後に進める
bookstore=> MOVE ALL IN item_cursor;
MOVE 11
bookstore=> FETCH IN item_cursor;
id | name | price | description
----+------+-------+-------------
(0 rows)
※検索結果のクローズ（確保領域の開放）
bookstore=> CLOSE item_cursor;
CLOSE CURSOR
■トランザクションの接続確認?BEGIN→COMMIT
※Ctrl+Lで表示画面のクリア
userDB=> BEGIN;
userDB=> insert INTO city VALUES (2,'tiba');
INSERT 0 1
userDB=> UPDATE city SET name='tiba2' WHERE id =2;
UPDATE 1
userDB=> COMMIT;
※「BEGIN」後の「COMMIT」前の表示確認
userDB=> SELECT * FROM city ;
id | name
----+--------
1 | Tokyo2
(1 row)
※「COMMIT」後の表示確認
userDB=> SELECT * FROM city ;
id | name
----+--------
1 | Tokyo2
2 | tiba2
(2 rows)
■トランザクションの接続確認?BEGIN→ROLLBACK
userDB=> insert INTO city VALUES (3,'hukusima');
userDB=> BEGIN;
userDB=> insert INTO city VALUES (3,'hukusima');
ERROR: duplicate key value violates unique constraint "city_pkey"
DETAIL: Key (id)=(3) already exists.
userDB=> insert INTO city VALUES (4,'hukusima2');
ERROR: current transaction is aborted, commands ignored until end of transaction block
userDB=> SELECT * FROM city;
ERROR: current transaction is aborted, commands ignored until end of transaction block
userDB=> ROLLBACK ;
※「ROLLBACK」後の表示確認
userDB=> SELECT * FROM city;
id | name
----+----------
1 | Tokyo2
2 | tiba2
3 | hukusima
(3 rows)
■トランザクションの接続確認?BEGIN→SAVEPOINT→ROLLBACK→COMMIT
userDB=> BEGIN;
userDB=> insert INTO city VALUES (5,'ooita');
INSERT 0 1
userDB=> SAVEPOINT sp1;
userDB=> foo;
ERROR: syntax error at or near "foo"
LINE 1: foo;
^
userDB=> SELECT * FROM city;
ERROR: current transaction is aborted, commands ignored until end of transaction block
userDB=> ROLLBACK TO sp1;
userDB=> SELECT * FROM city;
id | name
----+----------
1 | Tokyo2
2 | tiba2
3 | hukusima
5 | ooita
(4 rows)
userDB=> COMMIT;
userDB=> SELECT * FROM city;
id | name
----+----------
1 | Tokyo2
2 | tiba2
3 | hukusima
5 | ooita
(4 rows)
?７章関数?
■登録済み手続き言語の確認
[kudo@localhost ~]$ createlang --list userDB
Procedural Languages
Name | Trusted?
---------+----------
plpgsql | yes
■手続き言語の登録…NGだったが。
[kudo@localhost ~]$ createlang plperl userDB
createlang: language installation failed: ERROR: could not access file "$libdir/plperl": No such file or directory
■ユーザ関数の定義
userDB=> CREATE FUNCTION simple() RETURNS int
LANGUAGE sql AS
'SELECT 1';
userDB=> SELECT simple();
simple
--------
1
(1 row)
userDB=> \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------+------------------+---------------------+--------
public | simple | integer | | normal
(1 row)
userDB=> \x
Expanded display is on.
userDB=> \df+ simple();
List of functions
-[ RECORD 1 ]-------+---------
Schema | public
Name | simple
Result data type | integer
Argument data types |
Type | normal
Volatility | volatile
Owner | kudo
Language | sql
Source code | SELECT 1
Description |
?８章運用管理基礎?
■セキュリティ管理（クライアント認証）
[postgres@localhost ~]$ echo $PGDATA
/usr/local/pgsql/data
[postgres@localhost ~]$ ls $PGDATA
PG_VERSION pg_hba.conf pg_notify pg_twophase postmaster.log
base pg_ident.conf pg_stat_tmp pg_xlog postmaster.opts
global pg_log pg_subtrans postgresql.conf postmaster.pid
pg_clog pg_multixact pg_tblspc postgresql.conf~
[postgres@localhost ~]$ less /usr/local/pgsql/data/pg_hba.conf
↓
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
…
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
■
[postgres@localhost ~]$ psql
psql (9.0.0)
Type "help" for help.
postgres=# \x
Expanded display is on.
postgres=# SELECT * from pg_authid ;
-[ RECORD 1 ]-+---------
rolname | postgres
rolsuper | t
rolinherit | t
rolcreaterole | t
rolcreatedb | t
rolcatupdate | t
rolcanlogin | t
rolconnlimit | -1
rolpassword |
rolvaliduntil |
-[ RECORD 2 ]-+---------
rolname | kudo
rolsuper | f
rolinherit | t
rolcreaterole | f
rolcreatedb | t
rolcatupdate | f
rolcanlogin | t
rolconnlimit | -1
rolpassword |
rolvaliduntil |
■パスワードの変更と確認
userDB=> \password kudo
Enter new password:
Enter it again:
userDB=> \password postgres
Enter new password:
Enter it again:
ERROR: must be superuser to alter superusers
[kudo@localhost ~]$ psql -U postgres -d postgres
psql (9.0.0)
Type "help" for help.
postgres=# SELECT * from pg_authid ;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnl
imit | rolpassword | rolvaliduntil
----------+----------+------------+---------------+-------------+--------------+-------------+---------
-----+-------------------------------------+---------------
postgres | t | t | t | t | t | t |
-1 | |
kudo | f | t | f | t | f | t |
-1 | md594d6275d0cb1cd8f0bc9109dd6546766 |
(2 rows)
postgres=# \q
[kudo@localhost ~]$ psql
psql: FATAL: database "kudo" does not exist
[kudo@localhost ~]$ psql -d postgres
psql (9.0.0)
Type "help" for help.
postgres=> SELECT * from pg_authid ;
ERROR: permission denied for relation pg_authid
postgres=>
■クライアント認証「pg_hba.conf」の編集と動作確認
[root@localhost pgsql]# gedit /usr/local/pgsql/data/pg_hba.conf
↓
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# TEST-kudo
host userDB all 0.0.0.0/0 md5
※設定ファイルの再設定
[postgres@localhost ~]$ pg_ctl reload
server signaled
※パスワード認証になるかの動作確認
[postgres@localhost ~]$ psql -h 127.0.0.1 -U kudo -d userDB
Password for user kudo:
psql (9.0.0)
Type "help" for help.
userDB=> \d
List of relations
Schema | Name | Type | Owner
--------+--------------+----------+-------
public | cable | table | kudo
public | cellphone | table | kudo
public | city | table | kudo
public | drink | table | kudo
public | emp | table | kudo
public | fruit | table | kudo
public | fruit_2 | table | kudo
public | fruit_name | table | kudo
public | fruit_view | view | kudo
public | hanbai | table | kudo
public | kaiin | table | kudo
public | kaiin_id_seq | sequence | kudo
public | manager | table | kudo
public | media | table | kudo
public | nulltest | table | kudo
public | nyuuka | table | kudo
public | products | table | kudo
public | score | table | kudo
public | t1 | table | kudo
(19 rows)
※テーブル内の権限の確認
userDB=> \z fruit
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-------+-------+-------------------+--------------------------
public | fruit | table | |
(1 row)
※「PUBLIC」に「SELECT」の権限を設定
userDB=> GRANT SELECT ON fruit TO PUBLIC ;
GRANT
userDB=> \z fruit
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-------+-------+-------------------+--------------------------
public | fruit | table | kudo=arwdDxt/kudo+|
| | | =r/kudo |
(1 row)
■データベースのバックアップ(ファイルにリダイレクト)
[postgres@localhost ~]$ pg_dump userDB > BKUPuserDB.out
[postgres@localhost ~]$ ls
BKUPuserDB.out
■バックアップ済みのファイルのリストア
※バックアップ先のデータベースの作成
[postgres@localhost ~]$ createdb re_userDB
[postgres@localhost ~]$ psql -f BKUPuserDB.out re_userDB
SET
SET
…
※リストア結果の確認
[postgres@localhost ~]$ psql re_userDB
psql (9.0.0)
Type "help" for help.
re_userDB=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+----------+-------
public | cable | table | kudo
public | cellphone | table | kudo
public | city | table | kudo
public | drink | table | kudo
public | emp | table | kudo
■DB-ALLバックアップのリストア(復元)の失敗
[postgres@localhost ~]$ pg_ctl start
server starting
[postgres@localhost ~]$ \d
-bash: d: command not found
[postgres@localhost ~]$ psql
psql (9.0.0)
Type "help" for help.
postgres=# \d
No relations found.
postgres=# \q
[postgres@localhost ~]$
[postgres@localhost ~]$
[postgres@localhost ~]$ ls
BKUPall.out BKUPuserDB.out test
[postgres@localhost ~]$ psql -f BKUPall.out template1
You are now connected to database "postgres".
SET
SET
[postgres@localhost ~]$ pg_ctl stop
[postgres@localhost ~]$ initdb -E UTF8 --locale=C
[postgres@localhost ~]$ pg_ctl start
[postgres@localhost ~]$ psql -f BKUPall.out template1
…
COMMIT
SET
psql:BKUPall.out:1846: ERROR: relation "cable_id_key" already exists
psql:BKUPall.out:1854: ERROR: multiple primary keys for table "city" are not allowed
psql:BKUPall.out:1862: ERROR: multiple primary keys for table "drink" are not allowed
psql:BKUPall.out:1869: ERROR: rule "update_fruit_view" for relation "fruit_view" already exists
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
[postgres@localhost ~]$ psql
psql (9.0.0)
Type "help" for help.
postgres=# \d
No relations found.
最終更新：2011年06月16日 22:38
