PostgreSQL �ɂ� �s�� �z��ɕς��� ARRAY_AGG �Ƃ�����������܂��B ����Ɣz�����ɕς��� ARRAY_TO_STRING ��g�ݍ��킹�邱�Ƃ� �s���J���}���Ȃǂ̕�����ɂ��邱�Ƃ��ł��܂��B
�Ⴆ�� ����ȃe�[�u���ɑ��āD�D�D
db=# SELECT id, name FROM fruit;
id | name
----+--------
1 | ���
2 | �݂���
3 | �Ԃǂ�
���̂悤�Ȍ��ʂ邱�Ƃ��ł��܂��B
db=# SELECT ARRAY_TO_STRING(ARRAY_AGG(name order by id), ',') AS names FROM fruit;
names
----------------------
���,�݂���,�Ԃǂ�
����� Oracle �łȂ�Ƃ��ł��Ȃ��Ǝv���Ă�����ł��� Oracle�ł�11g R2 ���� LISTAGG �Ƃ��������lj�����Ă��܂����B
SQL> SELECT LISTAGG(name, ',') WITHIN GROUP (order by id) AS names FROM fruit;
names
----------------------
���,�݂���,�Ԃǂ�
LISTAGG ���� ARRAY_AGG + ARRAY_TO_STRING �̂悤�ȓ��������܂��B
�����ł��B
�i�g�p���Ă��� PostgreSQL �� PostgreSQL 9.3.12 �ł��j
PostgreSQL �� �z����e�[�u���̂悤�Ɉ������@�ł��B �f�[�^�̈ꊇ�o�^��A���̃e�X�g�Ȃǂɕ֗��ł��B
�܂��A�z��̒�`�͎��̂悤�ɂȂ�܂��B
'{value1,value2,value3}'
ARRAY ���Z�\�����g���Ǝ��̂悤�ɂȂ�܂��B
ARRAY['value1','value2','value3']
����͂܂� ARRAY ���Z�\���̕��� SQL �Ŏg�p���܂��B
SELECT field1::VARCHAR
FROM UNNEST(ARRAY['value1','value2','value3']) AS field1;
�g���܂킵���ǂ��悤�� FROM ��Ŏg�p���Ă��܂��B
�|�C���g�� UNNEST ���ł��B UNNEST �����g�p����� �z����s�W���ɓW�J���邱�Ƃ��ł��܂��B
[�Q�l]
9.18. �z����Ɖ��Z�q - PostgreSQL 9.3.2����
SQL �̎��s���ʂ͎��̂悤�ɂȂ�܂��B
db=# SELECT field1::VARCHAR db-# FROM UNNEST(ARRAY['value1','value2','value3']) AS field1; field1 -------- value1 value2 value3 (3 rows)
������g���� ���悤�� SQL �Ŏg�p������̃e�X�g�p SQL ���ȒP�ɍ��܂��B
SELECT field1::VARCHAR, test_function(field1::VARCHAR) AS result
FROM UNNEST(ARRAY['value1','value2','value3']) AS field1;
�����܂ł͈ꎟ���z��ł����B
�z����e�[�u���̂悤�Ɉ����̂� �� 1 �ł͍���܂��ˁB
�����A2 �����z��ɂ���ƈ������ʓ|�ɂȂ邽�� �z��̕������`�� ARRAY ���Z�\����g�ݍ��킹�� ���̂悤�ɒ�`���܂��B
ARRAY['{100,value1,2018/10/01}','{200,value2,2018/10/02}']
����� SQL �Ŏg���܂��B
SELECT (fields::VARCHAR[])[1]::NUMERIC AS field1 , (fields::VARCHAR[])[2]::VARCHAR AS field2 , (fields::VARCHAR[])[3]::DATE AS field3 FROM UNNEST(ARRAY['{100,value1,2018/10/01}' ,'{200,value2,2018/10/02}']) AS fields;
�|�C���g�� "fields" �ɔz��̕������`������̂� ����� "VARCHAR[]" �ŃL���X�g���܂��B
SQL �̎��s���ʂ͎��̂悤�ɂȂ�܂��B
db=# SELECT (fields::VARCHAR[])[1]::NUMERIC AS field1 db-# , (fields::VARCHAR[])[2]::VARCHAR AS field2 db-# , (fields::VARCHAR[])[3]::DATE AS field3 db-# FROM UNNEST(ARRAY['{100,value1,2018/10/01}' db-# ,'{200,value2,2018/10/02}']) AS fields; field1 | field2 | field3 --------+--------+------------ 100 | value1 | 2018-10-01 200 | value2 | 2018-10-02 (2 rows)
�l�ɃJ���}���g�p����ꍇ�� ���̂悤�ɃG�X�P�[�v���܂��B
ARRAY['{100,val\,ue1,2018/10/01}','{200,value2,2018/10/02}']
�����ł��B
�i�g�p���Ă��� PostgreSQL �� PostgreSQL 9.3.12 �ł��j
��̌^�� "NUMERIC(10,4)" �̂悤�ȏꍇ ���̂܂� SQL �Ńf�[�^���擾���� "123456" �� "123456.0000" �̂悤�Ɏ擾����܂��B
����́A�����_�ȉ��� 0 �������@�ł��B
�ȉ��� SQL �� �e�[�u���ƃf�[�^���쐬���܂��B
CREATE TABLE table1 (
field1 NUMERIC(10,4)
);
INSERT INTO table1 (field1)
VALUES (123456.0000)
, (123456.5000)
, (123456.0500)
, (123456.0050)
, (123456.0005);
SQL �����s�B
db=# CREATE TABLE table1 ( db(# field1 NUMERIC(10,4) db(# ); CREATE TABLE
db=# INSERT INTO table1 (field1) db-# VALUES (123456.0000) db-# , (123456.5000) db-# , (123456.0500) db-# , (123456.0050) db-# , (123456.0005); INSERT 0 5
�܂��� ���ʂɃf�[�^���擾���Ă݂܂��B
db=# SELECT field1 db-# FROM table1; field1 ------------- 123456.0000 123456.5000 123456.0500 123456.0050 123456.0005 (5 rows)
�����_�ȉ��� 0 �� �����Ă��܂��ˁB
�ϐ��x�� "DOUBLE PRECISION" �^�� �L���X�g���܂��B
PostgreSQL �̃L���X�g�� �Z�~�R���� 2 �ł��B
xxxx::DOUBLE PRECISION
���� SQL �� ���̂܂܂̎擾�ifield1�j�� �L���X�g���Ď擾�ifield1+�j���r���Ă݂܂��B
db=# SELECT field1 db-# , field1::DOUBLE PRECISION AS "field1+" db-# FROM table1; field1 | field1+ -------------+------------- 123456.0000 | 123456 123456.5000 | 123456.5 123456.0500 | 123456.05 123456.0050 | 123456.005 123456.0005 | 123456.0005
�����_�ȉ��� 0 �� ���Ă��܂��ˁB
����� �v���O��������ł� �悭������@�ł��B
�������A"DOUBLE PRECISION" �� �s���m�ȉϐ��x�̐��l�f�[�^�^�ł��̂� ���x�̖��ɂ͒��ӂ���K�v������܂��B
�����ł��B
�i�g�p���Ă��� PostgreSQL �� PostgreSQL 9.3.12 �ł��j
PostgreSQL �� �ł��邾�����ɉe����^���Ȃ��悤�� �ꎞ�I�ɑS�Ẵg���K�[���ɂ������Ǝv���Ă����� ���̂悤�ȕ��@������܂����B
[�Q�l]
PostgreSQL�ňꎞ�I��trigger��S�Ė���������@ - Qiita
SET session_replication_role = replica;
�N���C�A���g�ڑ��̕ϐ��usession_replication_role�v��ύX������@�� ���̕ϐ��́A�g���K����у��[���̔��s�𐧌䂷�邻���ł��B
[�Q�l]
18.11. �N���C�A���g�ڑ��f�t�H���g - PostgreSQL 9.3.2����
���ۂɎ����Ă݂܂��B
�܂��Atable1 �� table2 ���쐬�B
CREATE TABLE table1 (
field1 INTEGER
, field2 INTEGER
);
CREATE TABLE table2 (
field1 INTEGER
);
table1 �� INSERT ���� field1 �̒l�� field2 �ɃZ�b�g���� BEFORE �g���K�[���쐬�B
CREATE FUNCTION before_func1()
RETURNS TRIGGER AS $$
BEGIN
NEW.field2 := NEW.field1;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER before_trigger1
BEFORE INSERT ON table1
FOR EACH ROW EXECUTE PROCEDURE before_func1();
table1 �� INSERT ���� table2 �Ƀ��R�[�h�� INSERT ���� AFTER �g���K�[���쐬�B
CREATE FUNCTION after_function1()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO table2 (field1) VALUES (NEW.field1);
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER after_trigger1
AFTER INSERT ON table1
FOR EACH ROW EXECUTE PROCEDURE after_function1();
�e�[�u���ƃg���K�[���쐬������A���� SQL �����s���܂��B
INSERT INTO table1 (field1) VALUES (1);
table1 �� INSERT �B
db=# INSERT INTO table1 (field1) VALUES (1);
INSERT 0 1
table1 ���m�F�B
db=# SELECT * FROM table1;
field1 | field2
--------+--------
1 | 1
(1 row)
BEFORE �g���K�[�� field2 �ɒl���Z�b�g����Ă��܂��B
table2 ���m�F�B
db=# SELECT * FROM table2;
field1
--------
1
(1 row)
AFTER �g���K�[�� ���R�[�h�� INSERT ����Ă��܂��B
���ɃN���C�A���g�ڑ��̕ϐ��usession_replication_role�v�������܂��� �܂������l���m�F�B
db=# SHOW session_replication_role;
session_replication_role
--------------------------
origin
(1 row)
�����l�́uorigin�v�ł����B
�N���C�A���g�ڑ��̕ϐ��usession_replication_role�v�� �ureplica�v��ݒ肵�܂��B
db=# SET session_replication_role = replica;
SET
db=# SHOW session_replication_role;
session_replication_role
--------------------------
replica
(1 row)
�����ƕύX����Ă܂��B
�������Ɠ��� INSERT �������s�B
�i�e�[�u���̃��R�[�h�̓N���A�ςł��j
db=# INSERT INTO table1 (field1) VALUES (1);
INSERT 0 1
table1 ���m�F�B
db=# SELECT * FROM table1;
field1 | field2
--------+--------
1 |
(1 row)
BEFORE �g���K�[�����s����Ȃ��������� field2 �ɒl���Z�b�g����Ă��܂���B
table2 ���m�F�B
db=# SELECT * FROM table2;
field1
--------
(0 rows)
AFTER �g���K�[�����s����Ȃ��������� ���R�[�h�� INSERT ����Ă��܂���B
�g���K�[�͎��s����܂���ł����B
����͕֗��ł��ˁB
������̃Z�b�V�����ł͒ʏ�ʂ�̓�������܂��B
�������A���������ۂ���Ȃ��Ȃ�\��������̂� ���R�Ȃ���T�d�Ɏ��s����K�v������܂��B
�O���APostgreSQL �̃p�X���[�h�����ϐ��Őݒ肷��̂� PostgreSQL �I�ɂ͔��Ə����܂����� ��������Ă�����@�ɂ��ď����Ă����܂��B
[�Q�l]
���ϐ� - PostgreSQL 9.3.2����
�ȉ��̂悤�ȓ��e���u~/.pgpass�v�ɋL�ڂ��܂��B
hostname:port:database:username:password
�t�@�C�����́A���ϐ� PGPASSFILE �ŕς��邱�Ƃ��ł��܂��B
�����L�ڂ��邱�Ƃ��ł��A �ォ�珇�Ƀ}�b�`�������̂��g�p���Ă���܂��B ���ϐ��ƈ���ĕ����ݒ肵�Ă�����͕̂֗��ł��ˁB
localhost:*:testdb1:testuser1:testpassword1 localhost:*:testdb1:testuser2:testpassword2 localhost:*:testdb2:testuser3:testpassword3
"*" ���g�p�ł��܂��B �z�X�g���́AUnix�h���C���\�P�b�g�̏ꍇ �ł� "localhost" �ŗǂ������ł��B
[�Q�l]
�p�X���[�h�t�@�C�� - PostgreSQL 9.3.2����
$ chmod 600 ~/.pgpass
�t�@�C���̌����� 600 �ɂ��Ă��Ȃ��ƃG���[�ɂȂ�܂��B
�ȑO�APostgreSQL �̃p�X���[�h��
���ϐ��Őݒ肷��L���������܂�����
PostgreSQL �I�ɂ͔��̂悤�ł��B
�i�ꕔ�̃I�y���[�e�B���O�V�X�e���ł�root�ȊO�̃��[�U�Ŋ��ϐ���������ꍇ���邽�߂Ƃ̂��Ɓj
[�Q�l]
���ϐ� - PostgreSQL 9.3.2����
�ꕔ�̃I�y���[�e�B���O�V�X�e�����ǂ���w���̂��킩��܂��A �v���Z�X���g���Ă���ϐ��̌������C�ɂȂ����̂� ������Ɗm�F���Ă݂܂����B
�܂����ϐ���ݒ肵�܂��B
$ export PGPASSWORD=testpassword
PostgreSQL �̃f�[�^�x�[�X�ɐڑ�����v���Z�X���쐬���܂��B
$ psql -U testuser -d testdb
psql (9.3.10, server 9.3.12)
Type "help" for help.
testdb=#
[Ctrl]+[z] �v���Z�X�点�܂��B
[1]+ Stopped psql -U testuser -d testdb
�N�����̃v���Z�X���m�F���܂��B
$ ps PID TTY TIME CMD 28796 pts/0 00:00:00 bash 29754 pts/0 00:00:00 psql 29834 pts/0 00:00:00 ps
�Q�l�T�C�g�ɂ��ƁA���ϐ��́u/proc/%{PID}/environ�v�� �i�[����Ă��邻���ł��B
[�Q�l]
Linux/Unix�v���Z�X�N�����̊��ϐ����_���v���� | �M�[�N��ڎw����
�m�F���Ă݂܂��B
$ cat /proc/29754/environ | sed -e 's/\x0/\n/g' | grep PGPASSWORD PGPASSWORD=testpassword
�������Ɋm�F�ł��܂����B
�g�p���Ă�����ł� root ���[�U�łȂ���� �����ȊO�̊��ϐ��͌���܂���ł������A �����̊��ϐ����iroot�̌������g����A���̃v���Z�X�̊��ϐ����j�m�F���������Ƃ�����Ǝv���̂� �o���Ă����Ɨǂ��̂�������܂���B
PostgreSQL ���g���Ă��� ���P�[���̐ݒ�ɂ���Ă� �\�[�g�����z��Ƃ͈Ⴄ���ʂɂȂ邱�Ƃ�����܂��B
�\�[�g���Ɋւ��郍�P�[���� LC_COLLATE �� �f�[�^�x�[�X�쐬���ɂ����ύX�ł��Ȃ��Ǝv���Ă��܂����B
[�Q�l]
PostgreSQL�̃��P�[��(lc collate,lc ctype)�̊m�F�ƕύX
���̂悤�� SELECT ���ɐݒ肵����B
db=# SELECT * FROM tab1 ORDER BY col1 COLLATE "C" DESC;
�ォ���P�ʂŕύX���ł���悤�ł��B
db=# ALTER TABLE tab1 ALTER COLUMN col1 TYPE VARCHAR COLLATE "C";
�}�j���A���ɓo�ꂷ��̂� 9.1 ����Ȃ̂� �����炭���̕ӂ�őΉ����ꂽ�̂ł͂Ȃ����ƁB
[�Q�l]
SELECT - PostgreSQL 9.1.5����
ALTER TABLE - PostgreSQL 9.1.5����
�ȑO�ALinux �� watch �R�}���h�ɂ��������܂����� PostgreSQL �ɂ� 9.3 ���� \watch ���^�R�}���h���lj�����܂����B
\watch [SEC] execute query every SEC seconds
psql=# SELECT CURRENT_TIME; timetz -------------------- 23:02:53.492804+09 (1 row) psql=# \watch 1 Watch every 1s Mon Oct 17 23:02:57 2016 timetz ------------------- 23:02:57.03674+09 (1 row) Watch every 1s Mon Oct 17 23:02:58 2016 timetz -------------------- 23:02:58.038192+09 (1 row)
���̂悤�� �O�Ɏ��s���� SQL �����Ԋu�Ŏ��s���Ă���܂��B
�I���� [Ctrl]+[c]�ł��B
�f�t�H���g�� watch �R�}���h�Ɠ����悤�� 2�b�Ԋu�̂悤�ł��B
psql=# SELECT CURRENT_TIME; timetz -------------------- 23:01:20.790009+09 (1 row) psql=# \watch Watch every 2s Mon Oct 17 23:01:25 2016 timetz -------------------- 23:01:25.005323+09 (1 row) Watch every 2s Mon Oct 17 23:01:27 2016 timetz -------------------- 23:01:27.007919+09 (1 row)
INSERT �� UPDATE ���J��Ԃ����s�ł��܂��B
psql=# INSERT INTO test_a VALUES ('a', 'b'); INSERT 0 1 psql=# \watch 1 Watch every 1s Mon Oct 17 23:09:25 2016 INSERT 0 1 Watch every 1s Mon Oct 17 23:09:26 2016 INSERT 0 1
psql=# UPDATE test_a SET field2 = 'c' WHERE field1 = 'a'; UPDATE 10 psql=# \watch 1 Watch every 1s Mon Oct 17 23:10:05 2016 UPDATE 10 Watch every 1s Mon Oct 17 23:10:06 2016 UPDATE 10
�f�[�^�̊Ď��� �����̌J��Ԃ��Ȃǂ� ���p�ł������ł��ˁB
PostgreSQL 9.3 ���� SQL �� LATERAL �Ƃ����L�[���[�h���L�q�ł���悤�ɂȂ��Ă��܂��B �܂��Ǝ��̊g�����Ǝv������A���� LATERAL �� SQL�W�� �ŋK�肳��Ă��邻���ł��B Oracle �ł� 12c ����T�|�[�g����Ă���悤�ł��B
[�Q�l]
LATERAL���g���Ă݂悤 ? Let's Postgres
�g�����Ƃ��Ă� �x���]���H �݂����Ȋ����ł��傤���B PostgreSQL �ł́ALATERAL �₢���킹����ɑ��Ă��Ďw��ł���̂ł����A FROM �Ɏw�肵���₢�����̌��ʂ��A���₢���킹�̒��Ŏg�p���邱�Ƃ��ł��܂��B
���Ƃ��A���̂悤�� SQL �̓G���[�ɂȂ�܂��B
psql=# SELECT table1.field1, subquery1.field2 psql-# FROM table1 psql-# LEFT JOIN ( psql-# SELECT field2 FROM table2 psql-# WHERE table2.field1 = table1.field1) AS subquery1 psql-# ON TRUE;
����� �Ԏ��̕����₢���킹�Ŏg�p���Ă��邽�߂ł��B
�{���́A���̂悤�ɕ��₢���킹����O�ɏo���K�v������܂��B
psql=# SELECT table1.field1, subquery1.field2 psql-# FROM table1 psql-# LEFT JOIN ( psql-# SELECT field2 FROM table2) AS subquery1 psql-# ON (subquery1.field1 = table1.field1);
�Ƃ��낪 LATERAL ���g���� ���̂悤�ȏ��������ł��܂��B
psql=# SELECT table1.field1, subquery1.field2 psql-# FROM table1 psql-# LEFT JOIN LATERAL ( psql-# SELECT field2 FROM table2 psql-# WHERE table2.field1 = table1.field1) AS subquery1 psql-# ON TRUE;
LATERAL ���w�肵�����₢���킹�� �ォ��]�������̂� ���₢���킹�̒��� FROM ��̖₢���킹�̗���g�p���邱�Ƃ��ł��܂��B
���ꂾ������ �P�ɕςȏ��������ł��� �����Ȃ̂ł��� ��ŕ]������邱�Ƃ� �`���[�j���O�ɖ𗧂ꍇ������܂��B
�Ⴆ�� �����i100���j�ƌo��ׁi500�����j���� �������Ƃ̌o����擾���܂��B
psql=# SELECT ALL psql-# ����.����ID psql-# , �����o��.���v���z psql-# , �����o��.������ psql-# FROM psql-# ���� psql-# LEFT JOIN psql-# ( psql-# SELECT ALL psql-# ����ID psql-# , SUM(���z) AS ���v���z psql-# , COUNT(*) AS ������ psql-# FROM psql-# �o��� psql-# GROUP BY psql-# ����ID psql-# ) AS �����o�� psql-# ON psql-# ( psql-# �����o��.����ID = ����.����ID psql-# );
�����炭����� SQL �ɂȂ�Ǝv���܂��B
���������ł� 5,391ms �ł����B
LATERAL ���g���Ǝ��̂悤�ɏ����܂��B
psql=# SELECT ALL psql-# ����.����ID psql-# , ����.������ psql-# , �����o��.���v���z psql-# , �����o��.������ psql-# FROM psql-# ���� psql-# LEFT JOIN psql-# LATERAL psql-# ( psql-# SELECT ALL psql-# SUM(���z) AS ���v���z psql-# , COUNT(*) AS ������ psql-# FROM psql-# �o��� psql-# WHERE psql-# �o���.����ID = ����.����ID psql-# ) AS �����o�� psql-# ON psql-# TRUE;
���͂��̏ꍇ�͋t�ɒx���Ȃ��� 21,614 ms �ł����B 1�s���]������������Ԃ�������悤�ł��B
�L�[���ڂł͂Ȃ��������������ɒlj����܂��B
�܂� LATERAL ���g��Ȃ� SQL ����B
psql=# SELECT ALL psql-# ����.����ID psql-# , �����o��.���v���z psql-# , �����o��.������ psql-# FROM psql-# ���� psql-# LEFT JOIN psql-# ( psql-# SELECT ALL psql-# ����ID psql-# , SUM(���z) AS ���v���z psql-# , COUNT(*) AS ������ psql-# FROM psql-# �o��� psql-# GROUP BY psql-# ����ID psql-# ) AS �����o�� psql-# ON psql-# ( psql-# �����o��.����ID = ����.����ID psql-# ) psql-# WHERE psql-# ����.������ = '�V�X�e���P��';
Hash Right Join (actual time=3314.571..3314.574 rows=1 loops=1)
Hash Cond: ((t_kj_keihi_meisai.����ID)::text = (����.����ID)::text)
-> HashAggregate (actual time=3314.411..3314.441 rows=141 loops=1)
-> Seq Scan on t_kj_keihi_meisai (actual time=0.121..716.138 rows=5021645 loops=1)
-> Hash (actual time=0.089..0.089 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on r_kj_jigyousho ���� (actual time=0.022..0.085 rows=1 loops=1)
Filter: (������ = '�V�X�e���P��'::text)
Rows Removed by Filter: 99
����� 3,314ms �ł����B
���� LATERAL �o�[�W�����B
psql=# SELECT ALL psql-# ����.����ID psql-# , ����.������ psql-# , �����o��.���v���z psql-# , �����o��.������ psql-# FROM psql-# ���� psql-# LEFT JOIN psql-# LATERAL psql-# ( psql-# SELECT ALL psql-# SUM(���z) AS ���v���z psql-# , COUNT(*) AS ������ psql-# FROM psql-# �o��� psql-# WHERE psql-# �o���.����ID = ����.����ID psql-# ) AS �����o�� psql-# ON psql-# TRUE psql-# WHERE psql-# ����.������ = '�V�X�e���P��';
Nested Loop (actual time=221.481..221.523 rows=1 loops=1)
-> Seq Scan on r_kj_jigyousho ���� (actual time=0.029..0.070 rows=1 loops=1)
Filter: (������ = '�V�X�e���P��'::text)
Rows Removed by Filter: 99
-> Aggregate (actual time=221.448..221.449 rows=1 loops=1)
-> Bitmap Heap Scan on t_kj_keihi_meisai �o��� (actual time=217.928..219.075 rows=4633 loops=1)
Recheck Cond: ((����ID)::text = (����.����ID)::text)
-> Bitmap Index Scan on t_yj_keihi_meisai_idx1 (actual time=217.882..217.882 rows=4633 loops=1)
Index Cond: ((����ID)::text = (����.����ID)::text)
������� 221ms �ł����B 10�{�ȏ㑬���Ȃ��Ă��܂��B
���̂悤�ɏꍇ�ɂ���Ă� ���Ȃ葬���Ȃ邱�Ƃ�����܂��B
�܂� LATERAL ���w�肵���ꍇ FROM �̌��ʂ̍s�ɑ��� 1�s���]������邽�� INNER JOIN ���w�肵���ꍇ�ł��O�������̂悤�� ���̌��ʂ̍s��S�ĕԂ��̂Œ��ӂ��K�v�ł��B
PostgreSQL �ł� 9.2 ���� �C���f�b�N�X�E�I�����[�E�X�L�������g����悤�ɂȂ��Ă��܂��B
���̋@�\�� �f�[�^���擾����ۂɁA�C���f�b�N�X�����Ŏ������ꍇ�� �C���f�b�N�X�݂̂��g�p����Ƃ������̂ł��B Oracle �ł͐̂��炠��܂����� PostgreSQL �ł� 9.2 �����������܂����B
�ʏ�́A�����ɃC���f�b�N�X���g�p���Ă��A�e�[�u������f�[�^���擾���邽�߂� �ʂ̗̈�i�q�[�v�j��ǂ݂ɍs���킯�ł����A���ꂪ�s�v�ɂȂ邽�߃p�t�H�[�}���X�̌���� ���Ȃ���҂ł��镔��������܂��B
�����APostgreSQL�͒NjL�^�̃f�[�^�x�[�X�Ȃ̂� �C���f�b�N�X�����ł� �Â��f�[�^���ǂ������f�ł��Ȃ����߁A�C���f�b�N�X�E�I�����[�E�X�L�����̎����� ����Ƃ���Ă��܂����B ����� 8.4 �Ńo�L���[���̂��߁H�ɒlj����ꂽ �r�W�r���e�B�}�b�v �Ƃ����f�[�^���g���āu�C���f�b�N�X�����ő��v���v�f���Ă��邻���ł��B
�Ȃ̂ŁA�C���f�b�N�X�E�I�����[�E�X�L�����Ƃ����Ă��A�ꍇ�ɂ���Ă̓e�[�u���̃f�[�^��ǂ݂ɍs���K�v������܂��B �X�V�������e�[�u���ł͓����������܂���B
�O�Ōo�����ł����肪�����ł����A�r�W�r���e�B�}�b�v���Y��ɁH�Ȃ�悤�Ƀo�L���[�����d�v�ł��B
���s�v�������Ǝ��̂悤�ɂȂ�܂��B
�i field1 ���e�[�u���̎�L�[�j
psql=# EXPLAIN psql-# SELECT field1 FROM table1 psql-# WHERE field1 > 300000 Index Only Scan using table1_pkey on table1 (cost=0.43.. Index Cond: (field1 > 300000)
Index Only Scan �Əo�͂���܂��B
PostgreSQL �� ���ݎ��s���� psql �ŁA�p�����[�^��\������ɂ� "SHOW" �Ƃ����R�}���h���g�p���܂��B
psql=# SHOW checkpoint_segments;
checkpoint_segments
---------------------
32
(1 row)
"SHOW ALL" ���ƈꗗ��\�����邱�Ƃ��ł��܂��B
psql=# \x psql=# SHOW ALL; -[ RECORD 1 ]------------------------------------------------------------------- name | allow_system_table_mods setting | off description | Allows modifications of the structure of system tables. -[ RECORD 2 ]------------------------------------------------------------------- name | application_name setting | psql description | Sets the application name to be reported in statistics and logs. -[ RECORD 3 ]------------------------------------------------------------------- name | archive_command setting | (disabled) description | Sets the shell command that will be called to archive a WAL file.
�����ł��B
SELECT table_schema
, table_name
, constraint_name
FROM information_schema.table_constraints
WHERE constraint_type = 'FOREIGN KEY';
����Ɏ��̂悤�ɂ���� �������邽�߂� SQL�������ł��܂��B
SELECT 'ALTER TABLE ' || table_schema || '.' || table_name
|| ' DROP CONSTRAINT ' || constraint_name || ';'
FROM information_schema.table_constraints
WHERE constraint_type = 'FOREIGN KEY';
����̓f�B���N�g���҂̍Ō�A���O�Ȃǂ̃f�[�^���i�[�����f�B���N�g���ɂ��āB
�Ƃ͂����p�b�P�[�W�ŃC���X�g�[�����Ă���̂�
Ubuntu �x�[�X�̘b�ɂȂ�܂��B
Ubuntu �ł͊�{�I�Ƀ��O�t�@�C���� /var/log �̒����� �z���Ƀp�b�P�[�W���̃f�B���N�g���Ŋi�[����܂��B PostgreSQL �̏ꍇ /var/lib/postgresql �ɂȂ�܂��B
/var/log/postgresql
�ݒ�t�@�C����f�[�^�̃f�B���N�g���̂悤�� �f�[�^�x�[�X�N���X�^�̃T�u�f�B���N�g���͂���܂���B /var/log/postgresql �̒����Ƀ��O�t�@�C��������܂��B
$ ls -F /var/log/postgresql
/var/log/postgresql/postgresql-9.1-main.log
����Ƀ��O�t�@�C���̃t�@�C������ "9.1-main" �̂悤�Ƀf�[�^�x�[�X�N���X�^�Ɠ����L�[���[�h�������Ă��܂��B �i���O�t�@�C�����͐ݒ�ŕύX�ł��܂��j
����̓e�[�u���Ȃǂ̃f�[�^���i�[�����f�B���N�g���ɂ��āB
�Ƃ͂����p�b�P�[�W�ŃC���X�g�[�����Ă���̂�
Ubuntu �x�[�X�̘b�ɂȂ�܂��B
Ubuntu �ł́A/var/lib/postgresql �ȉ��Ɋi�[����܂��B
/var/lib/postgresql
Ubuntu �̏ꍇ�A���̃f�B���N�g���̒����ɂ� �O�X��̐ݒ�t�@�C���Ɠ��l�� �f�[�^�x�[�X�N���X�^���Ƃ̃T�u�f�B���N�g��������܂��B
/var/lib/postgresql/9.1/main
$ ls -F /var/lib/postgresql 9.1/ $ ls -F /var/lib/postgresql/9.1 main/ $ ls -F /var/lib/postgresql/9.1/main base/ pg_stat_tmp/ postmaster.opts global/ pg_subtrans/ postmaster.pid pg_clog/ pg_tblspc/ server.crt@ pg_multixact/ pg_twophase/ server.key@ pg_notify/ PG_VERSION pg_serial/ pg_xlog/
����̓R�}���h�i���s�t�@�C���j���i�[�����f�B���N�g���ɂ��āB
�Ƃ͂����p�b�P�[�W�ŃC���X�g�[�����Ă���̂�
Ubuntu �x�[�X�̘b�ɂȂ�܂��B
initdb �� vacuumdb�Apsql �Ȃ� PostgreSQL �̎�ȃR�}���h�� �ȉ��̃f�B���N�g���Ɋi�[����܂��B
/usr/lib/postgresql/9.1/bin
$ ls -F /usr/lib/postgresql/9.1/bin
clusterdb* pg_basebackup* pg_upgrade*
createdb* pg_controldata* postgres*
createlang* pg_ctl* postmaster@
createuser* pg_dump* psql*
dropdb* pg_dumpall* reindexdb*
droplang* pg_resetxlog* vacuumdb*
dropuser* pg_restore*
initdb* pg_test_fsync*
�����̃o�[�W������ 1 �̃}�V���ʼnғ����邱�Ƃ����邽�� �R�}���h�������̃o�[�W�������i�[�ł���悤�� �T�u�f�B���N�g����������܂��B
�����A���ۂ� /usr/lib/postgresql/9.1/bin �ɂ� �p�X���ʂ��Ă��炸 /usr/bin �ȉ��ɂ��郊���N�t�@�C�����N�����邱�ƂɂȂ�܂��B
/usr/bin
$ ls -F /usr/bin (PostgreSQL �֘A�̂ݔ���) clusterdb@ pg_basebackup@ pg_lsclusters@ createdb@ pg_config@ pg_restore@ createlang@ pg_createcluster@ pg_upgradecluster@ createuser@ pg_ctlcluster@ psql@ dropdb@ pg_dropcluster@ reindexdb@ droplang@ pg_dump@ vacuumdb@ dropuser@ pg_dumpall@ vacuumlo@
���̂悤�� /usr/bin/ �ȉ��̃R�}���h�̓����N�ɂȂ��Ă��܂��B
�i��O�Ƃ��� initdb �̓����N������܂���j
$ readlink -f /usr/bin/vacuumdb /usr/share/postgresql-common/pg_wrapper
�܂��A�e�R�}���h�͒��� /usr/lib/postgresql/9.1/bin �ȉ��� �t�@�C���Ƀ����N����̂ł͂Ȃ� �ȉ��̃t�@�C���Ƀ����N���Ă��܂��B
/usr/share/postgresql-common/pg_wrapper
pg_wrapper �� Perl �̃X�N���v�g�ł��B
$ file /usr/share/postgresql-common/pg_wrapper
/usr/share/postgresql-common/pg_wrapper: Perl script, ASCII text executable
�O�q�����悤�� 1�̃}�V�����ŕ����̃o�[�W�������ғ����邱�Ƃ��ł��邽�� ��U���� pg_wrapper �ŁA�R�}���h���āA���ϐ��Ȃǂ���K�ȃo�[�W������ �R�}���h���N������悤�ɂȂ��Ă���킯�ł��B
����͐ݒ�t�@�C�����i�[�����f�B���N�g���ɂ��āB
�Ƃ͂����p�b�P�[�W�ŃC���X�g�[�����Ă���̂�
Ubuntu �x�[�X�̘b�ɂȂ�܂��B
Ubuntu �ł͊�{�I�� /etc �̒����� �z���Ƀp�b�P�[�W���̃f�B���N�g���Ŋi�[����܂��B
/etc/postgresql
������ PostgreSQL �̏ꍇ /etc/postgresql �̉��ɂ� �i�C���X�g�[�����_�ł́j 9.1 �Ƃ����T�u�f�B���N�g�������Ȃ� ����ɂ��̒��ɂ� main �Ƃ����T�u�f�B���N�g����������܂���B
/etc/postgresql/9.1/main
������ /etc/postgresql/9.1/main �̉��� �ݒ�t�@�C�����i�[����Ă��܂��B
$ ls -F /etc/postgresql/9.1/main/
environment pg_hba.conf postgresql.conf
pg_ctl.conf pg_ident.conf start.conf
���� 9.1/main �ł����A ������ PostgreSQL �́g�f�[�^�x�[�X�N���X�^�h�������Ă��܂��B
[�Q�l�T�C�g]
�f�[�^�x�[�X�N���X�^�̍쐬 - PostgreSQL 9.1.5����
�f�[�^�x�[�X�N���X�^�́A���[����e�[�u���X�y�[�X�����L����f�[�^�x�[�X�̉�ł��B Ubuntu �ł́A1�̃T�[�o���ɕ����̃f�[�^�x�[�X�N���X�^���쐬�ł���悤�� ���炩���ߐݒ�t�@�C���Ȃǂ̃f�B���N�g���Ƀf�[�^�x�[�X�N���X�^�� �T�u�f�B���N�g�����쐬���Ă���Ă���킯�ł��B
�p�b�P�[�W�̃C���X�g�[������ �f�[�^�x�[�X�N���X�^���쐬���Ă���邽�� 1�����f�[�^�x�[�X�N���X�^���g�p���Ȃ��ꍇ�ł� �ŏ����� 9.1/main �̂悤�Ƀf�[�^�x�[�X�N���X�^�����ʂ���f�B���N�g���ɂȂ��Ă��܂��B
���Ȃ݂ɃC���X�g�[�����Ƀf�[�^�x�[�X�N���X�^�������ō쐬���Ȃ��f�B�X�g���r���[�V������ �R���p�C�����ăC���X�g�[�������ꍇ��A�lj��Ńf�[�^�x�[�X�N���X�^���쐬����ꍇ�� ��L�Q�l�T�C�g�ɋL�ڂ��Ă���悤�� initdb �R�}���h���g�p���� �f�[�^�x�[�X�N���X�^���쐬���܂��B
$ initdb -D /usr/lib/postgresql/9.1/main
PostgreSQL ����{�I�Ȃ��Ƃ���Ċm�F���Ă����܂��B
�g�p������� Ubuntu12.10 �� PostgreSQL �� 9.1 �ł��B
����̓p�b�P�[�W�̃C���X�g�[���Ȃ̂� ���Ȃ� Ubuntu ���̘b�ɂȂ�܂��B �iUbuntu �D���Ȃ̂ŁE�E�E�j
�܂��AUbuntu12.10 �� [OpenSSH Server] �݂̂�I������ �C���X�g�[���������̏ꍇ postgresql �p�b�P�[�W�� apt-get ����� ���̃p�b�P�[�W���C���X�g�[������܂��B
postgresql postgresql-9.1 postgresql-common postgresql-client-9.1 postgresql-client-common libpq5 sgml-base xml-core libxml2:i386 ssl-cert
sgml-base �Ȃ� PostgreSQL ��p����Ȃ��p�b�P�[�W�ȊO������̂� �����͐�ɃC���X�g�[�����Ă��܂��܂��B
$ sudo apt-get install sgml-base xml-core libxml2 ssl-cert
���̏�Ԃ� postgresql �p�b�P�[�W���C���X�g�[�����܂��B
$ sudo apt-get install postgresql
����ƁA���̃p�b�P�[�W������܂��B
postgresql postgresql-9.1 postgresql-common postgresql-client-9.1 postgresql-client-common libpq5
���̂Ƃ��A���̃f�B���N�g�����쐬����܂��B
/etc/postgresql /etc/postgresql-common /run/postgresql /usr/share/postgresql /usr/share/postgresql-common /var/cache/postgresql /var/lib/postgresql /var/log/postgresql
���̃f�B���N�g���Ƀt�@�C����T�u�f�B���N�g�����i�[����܂��B
/usr/bin /usr/lib /usr/sbin /usr/share/doc /usr/share/man
postgresql �p�b�P�[�W�̃C���X�g�[������ �lj����ꂽ�t�@�C���ƃf�B���N�g���̈ꗗ�� ���������Q�Ƃ��Ă��������B
����́A��ȃf�B���N�g���ɂ��Č��Ă����܂��B
PostgreSQL �� SELECT �������s�����ꍇ�ł� write failed �Ƃ����G���[���o�邱�Ƃ�����܂��B �i�Ƃ������o�܂����j
���̏ꍇ�A PostgreSQL ���ғ����Ă���}�V���� �f�B�X�N�̋e�ʂ��Ȃ��Ȃ��Ă�\��������܂��B
�f�[�^���Q�Ƃ��邾���� SELECT ���ł� �ċA�����Ȃǂ��g���Ė����ɉ���Ă��܂��� ���̃G���[���o�邱�Ƃ�����܂��B
PostgreSQL �ɂ̓f�[�^�x�[�X�̃f�t�H���g�̕\�̈悪����܂��B �e�[�u���Ȃǂ̃f�[�^�x�[�X�̃I�u�W�F�N�g���쐬����Ƃ��� �\�̈���w�肵�Ȃ���A���̃f�[�^�x�[�X�̃f�t�H���g�̕\�̈�ɍ쐬����܂��B
���̏ꍇ�A�f�[�^�x�[�X database1 �� tablespace1 �� �f�t�H���g�̕\�̈�Ƃ��Đݒ肵�Ă��܂��B
db=# \x Expanded display is on. db=# \l+ List of databases -[ RECORD 1 ]-----+-------------------------- Name | database1 Owner | postgres Encoding | UTF8 Collation | en_US.UTF-8 Ctype | en_US.UTF-8 Access privileges | Size | 51 MB Tablespace | tablespace1 Description |
���̃f�t�H���g�̕\�̈�̕ύX�ł��� PostgreSQL �� 8.4 ����� ALTER DATABASE ���� �s�����Ƃ��ł��܂��B
db=# ALTER DATABASE database1 SET TABLESPACE tablespace2;
����ŕ\�̈悪 tablespace2 �ɂȂ�܂��B
db=# \l+ List of databases -[ RECORD 1 ]-----+-------------------------- Name | database1 Owner | postgres Encoding | UTF8 Collation | en_US.UTF-8 Ctype | en_US.UTF-8 Access privileges | Size | 51 MB Tablespace | tablespace2 Description |
�f�[�^�x�[�X�N���X�^�̃f�t�H���g�̕\�̈�ɕύX����ꍇ�� ���̂悤�ɕ\�̈�� pg_default ���w�肵�܂��B
db=# ALTER DATABASE database1 SET TABLESPACE pg_default;
8.4 �܂ł� createdb �R�}���h���g���Ď��̂悤�� �ύX�����肵�Ă�����ł����A�o�[�W�����A�b�v�̓��e�� �ǂ������Ă����Ȃ��� �V�����o�[�W�������g���Ă郁���b�g������ł��Ȃ��ł��ˁB
$ createdb -T database1 -D pg_default database2;
PostgreSQL �� PL/pgSQL �̃g���K�[�� �ȉ��̂悤�ȃG���[���ł邱�Ƃ�����܂��B
ERROR: cross-database references are not implemented: test.table1.column1 CONTEXT: compilation of PL/pgSQL function "test_af_chg" near line 4
�����̂ЂƂƂ��āAPL/pgSQL �v���O�������� �ϐ��̒�`�� %TYPE �������Ă�ꍇ������܂��B
DECLARE wk_column1 test.table1.column1;
���̏ꍇ�A��L�̃G���[���ł܂��B
�������́A���̂悤�ɂȂ�܂��B
DECLARE
wk_column1 test.table1.column1%TYPE;
�ӊO�ƋC�Â��ɂ����ł��B
ORACLE �ɂ� ROWNUM �Ƃ����[�������� ���ʃZ�b�g�ɔԍ���U���Ď擾���邱�Ƃ��ł��܂��B �iORDER BY �ɉe������̂ŏ��Ԃɔԍ����t���Ȃ����Ƃ�����܂����j
PostgreSQL �ł̓E�B���h�E���iwindow function�j���g���� �����悤�Ȃ��Ƃ��ł��܂��B
���̂悤�Ȋ����ł��B
db=# SELECT (ROW_NUMBER() OVER()) AS rownum, f1 FROM table1; rownum | f1 --------+---- 1 | A 2 | B 3 | C 4 | D 5 | E (5 rows)
���Ȃ݂ɃE�B���h�E���� �W��SQL �ɋK�肳��Ă�����̂� PostgreSQL �݂̂ɂ���l�����ł͂���܂���B
PostgreSQL �ɂ� ORACLE �� ROWID �̂悤�� �s��f�����w�肷�邽�߂̗� ctid ������܂��B
db=# SELECT ctid, * FROM test.table1; ctid | f1 | f2 ----------+-------+--------------- (0,1) | A143H | test@test.com (0,2) | A393G | test@test.com (0,3) | A380E | test@test.com (0,4) | A147K | test@test.com
ctid �́A�s�o�[�W�����̕����I�ʒu�Ȃ̂� �s���X�V������AVACUUM FULL �ŕς���Ă��܂��܂��B
�����A���� ctid ���g�p����Ƃ��Ȃ葁���s���擾�ł��܂��B ���̂Ƃ��̎��s�v��ɏo�Ă��鉉�Z�q�� Tid Scan �Ȃ̂ł��B
���ۂɎ��s�v����擾���Ă݂܂��B
db=# EXPLAIN db-# SELECT * FROM test.table1 WHERE ctid = '(0,2)'; QUERY PLAN ------------------------------------------------------------ Tid Scan on table1 (cost=0.00..4.01 rows=1 width=218) TID Cond: (ctid = '(0,2)'::tid)
Tid Scan �Əo�܂����B
PRIMARY KEY �Ō��������ꍇ�̎��s�v����擾���Ă݂܂��B
db=# EXPLAIN db-# SELECT * FROM test.table1 WHERE field1 = 1; QUERY PLAN --------------------------------------------------------------------------- Index Scan using table1_pkey on table1 (cost=0.00..8.27 rows=1 width=50) Index Cond: ((field1)::text = '1'::text)
Index Scan ������� Tid Scan �̕����R�X�g���������ł��ˁB
�O�� psql �̕ϐ��ɂ��ď����܂����� �ϐ��ւ̒l�̑���� psql ���N������R�}���h���C��������ł��܂��B �Ƃ������A�o�b�`�����Ȃǂ��l����Ƃ����������C���ɂȂ�Ǝv���܂��B �i�q�A�h�L�������g�Ƃ����������܂����j
SQL �́A�O���Ɠ������̂��g�p���܂��B
SELECT * FROM generate_series(1, 100) WHERE generate_series between :start AND :end;
start �� end �Ƃ��� 2 �̕ϐ����g�p���Ă��܂��B
�R�}���h���C���Ŏw�肷��ɂ́Av �I�v�V�������g�p���܂��B
$ psql -f test.sql -v start=3 -v end=9 db generate_series ----------------- 3 4 5 6 7 8 9 (7 rows)
����̓��t����Ԃ� SQL �����s�������ꍇ�ȂǂɎg���܂��B
ORACLE �� sql*plus �ȂǂƓ����悤�� psql �ł��ϐ����������Ƃ��ł��܂��B
�ϐ��� set �R�}���h�Œl�������܂��B
db=# \set value 2
��̗�ł́Avalue �Ƃ����ϐ��� 2 �����܂����B
�O��� echo �R�}���h�ŁA�l���o�͂ł��܂��B
db=# \echo :value 2
SQL �̒��Ŏg�p���邱�Ƃ��ł��܂��B
db=# SELECT :value; ?column? ---------- 2 (1 row)
�ϐ����g���� SQL �̏�����ݒ肷�邱�Ƃ��ł��܂��B
db=# \set start 3 db=# \set end 10 db=# \echo :start - :end 3 - 10
�ϐ���ݒ肵�āAWHERE ��Ŏg�p���܂��B
db=# SELECT * FROM generate_series(1, 100) db-# WHERE generate_series between :start AND :end; generate_series ----------------- 3 4 5 6 7 8 9 10 (8 rows)
�Ӗ��̖��� SQL �ł����A3 ���� 10 �ɂ��ڂ�Ă��܂��B
�R�}���h�̌��ʂ��ϐ��ɑ���ł��܂����A ������������ꍇ�A�G�X�P�[�v��������Ƃ�₱�����ł��B
db=# \set today ''''`date +"%Y/%m/%d"`'''' db=# select :today; ?column? ------------ 2012/12/15
psql �ɂ� \echo \qecho �Ƃ����R�}���h������܂��B
db=# \echo hello
hello
���̂悤�ɕ�������o�͂��邱�Ƃ��ł��܂��B
�܂��R�}���h�����s�ł��܂��B
db=# \echo `date +"%Y/%m/%d"`
2012/12/15
�R�}���h�̓o�b�N�N�H�[�g�ň݂͂܂��B
db=# \echo `pwd`
/tmp
���̂悤�� psql ���g�p���Ă���Ƃ��� ���O�t�@�C���� �F�X�Əo�͂ł���̂ŕ֗��ł��B
$ psql -f test.sql db > test.log
��Ђŕ����ꂽ �uUNION ���܂܂��r���[��������ǁA�C���f�b�N�X�g���Ă����H�v�Ƃ��� ����ɑ��� �u�f�[�^�x�[�X���������̕����������ǂ��Ɣ��f������g���Ă���邵�A���s�v�������Ă݂�H�v�Ƃ��� ��������ł����A�O�̂��ߎ����Ă݂܂��B
�܂��A�e�X�g�p�̃e�[�u���Ƀf�[�^��o�^���܂��B
INSERT INTO table1 (f1) SELECT generate_series FROM generate_series(1, 10000);
INSERT INTO table2 (f1) SELECT generate_series FROM generate_series(1, 10000);
generate_series ���́A�֗��ł��ˁB
�܂��������ɒP���� SQL �Ŏ��s�v����擾�B ������O�Ƀe�[�u���� Analyze ���Ă����܂��B
EXPLAIN SELECT * FROM table1 WHERE f1 = 1
������
Index Scan using table1_pkey on table1 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
�C���f�b�N�X table1_pkey ���g�p����Ă��܂��B
���ɕ��₢���킹�� UNION ALL ���Ď��s�v����擾�B
EXPLAIN
SELECT *
FROM (SELECT * FROM table1
UNION ALL
SELECT * FROM table2) AS t1
WHERE f1 = 1
������
Result (cost=0.00..16.54 rows=2 width=8)
-> Append (cost=0.00..16.54 rows=2 width=8)
-> Index Scan using table1_pkey on table1 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
-> Index Scan using table2_pkey on table2 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
����������ƁA�C���f�b�N�X table1_pkey �� table2_pkey ���g�p����Ă��܂��B
���Ȃ݂� UNION ALL ����Ȃ��� UNION �̏ꍇ�B
EXPLAIN
SELECT *
FROM (SELECT * FROM table1
UNION
SELECT * FROM table2) AS t1
WHERE f1 = 1
������
Unique (cost=16.57..16.58 rows=2 width=8)
-> Sort (cost=16.57..16.57 rows=2 width=8)
Sort Key: table1.f1, table1.f2
-> Append (cost=0.00..16.56 rows=2 width=8)
-> Index Scan using table1_pkey on table1 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
-> Index Scan using table2_pkey on table2 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
��������C���f�b�N�X�͎g���Ă���܂��B
�O�̂��߁A�r���[���쐬���Ď����Ă݂܂��B
CREATE VIEW view1 AS
SELECT *
FROM (SELECT * FROM table1
UNION ALL
SELECT * FROM table2) AS t1
�r���[�ɑ��Ď��s�v����擾�B
EXPLAIN SELECT * FROM view1 WHERE f1 = 1
������
Result (cost=0.00..16.54 rows=2 width=8)
-> Append (cost=0.00..16.54 rows=2 width=8)
-> Index Scan using table1_pkey on table1 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
-> Index Scan using table2_pkey on table2 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
���ʂ͕��₢���킹�Ɠ����ł����B
���������̂́A�����̎�Ŏ����Ă����ƈ��S�ł��ˁB
PostgreSQL �ɂ� generate_series �Ƃ����W����Ԃ����Ƃ��ł��� �������֗��Ȋ�������܂��B
[�Q�l�T�C�g]
�W����Ԃ��� - PostgreSQL 8.4.4����
���̂悤�Ɏg�����Ƃ��ł��܂��B
db=# SELECT * FROM generate_series(1, 5); generate_series ----------------- 1 2 3 4 5
�X�e�b�v��������܂��B
db=# SELECT * FROM generate_series(1, 5, 2); generate_series ----------------- 1 3 5
TIMESTAMP �^�������܂��B
db=# SELECT * FROM generate_series( db-# '2013/01/01'::TIMESTAMP, '2013/01/03', '1 day'); generate_series --------------------- 2013-01-01 00:00:00 2013-01-02 00:00:00 2013-01-03 00:00:00
FROM ��ɏ����Ȃ��g�������ł��܂��B
db=# SELECT generate_series(1, 3); generate_series ----------------- 1 2 3
�W����Ԃ��A�Ƃ����Ƃ��낪�������Ƃ���� ���̂悤�ɕʂ̏W���ƈꏏ�Ɏg���� ���ςȂǂ̏W���̉��Z���ʂ��ȒP�Ɏ擾���邱�Ƃ��ł��܂��B
db=# SELECT f1 FROM tab1; f1 ---- 1 2 3 db=# SELECT f1, generate_series(1, 3) FROM tab1; f1 | generate_series ----+----------------- 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2 2 | 3 3 | 1 3 | 2 3 | 3
�e�X�g�f�[�^�����Ƃ��Ȃǂ��Ȃ���ɗ����܂��B
PostgreSQL �֘A�̋L�����T�[�t�B�����Ă����Ƃ��� ���̋L���ɗ��ꒅ���܂����B
[�Q�l]
POSTORO: postgres�� Covering Index�ɂ�鍂���������ł��Ȃ��炵��
�Ȃ�ƁB
index-only �Ƃ� covering index �Ƃ����̂� �w�f�[�^�x�[�X����f�[�^���擾����Ƃ��� �C���f�b�N�X�ɕK�v�ȗS�Ċ܂܂�Ă���� �e�[�u���̃f�[�^��ǂ܂Ȃ��Ă��ςށx�Ƃ��� �p�t�H�[�}���X�����߂���@�ł��B
�_���Ďg�p�������Ƃ͂���܂��� �Ȃ�ƂȂ� PostgreSQL �ł����ʂɎg����Ǝv���Ă��܂����B
HOT������̂ŁA�C���f�b�N�X�̐v����Ƃ��ɂ� �C�����Ȃ��Ă͂Ȃ�Ȃ��ł��ˁB
PostgreSQL �� PRIMARY KEY ���폜���� SQL �� Oracle �ƈ���Ă����̂Ń����B
PRIMARY KEY �̍쐬�� PostgreSQL, Oracle, MySQL, SQLServer �������� ���̂悤�ɂȂ�܂��B
ALTER TABLE "�e�[�u����" ADD PRIMARY KEY ("�L�[��"...)
�폜�̏ꍇ�͈Ⴂ�܂��B PostgreSQL �� SQLServer �ł͎��̂悤�ɂȂ�܂��B
ALTER TABLE "�e�[�u����" DROP CONSTRAINT "primary key ����"
PRIMARY KEY �̍쐬�łł���������폜���邱�Ƃ� PRIMARY KEY ���폜���܂��B
Oracle, MySQL �͎��̂悤�ɂȂ�܂��B
ALTER TABLE "�e�[�u����" DROP PRIMARY KEY
������̕����V���v���ł��ˁB
[�Q�l]
�e�[�u����`�̕ύX�i��̒lj��A�ύX�A�폜�j - �I���N���EOracle SQL ����
MySQL�N�C�b�N�E���t�@�����X
�J���ł̃���:SQL Server�Ŏ�L�[��lj�����ɂ́H - livedoor Blog�i�u���O�j
PostgreSQL �� SEQUENCE �ɂ� CACHE �Ƃ����ݒ肪����܂��B (Oracle �Ȃǂɂ�����܂�)
���̋@�\�͍������̂��߂̂��̂ŁA���O�� SEQUENCE ���܂Ƃ߂� �擾���Ă����A��ʂ� SEQUENCE �s����Ƃ��� ���x�� SEQUENCE ���X�V���Ȃ��悤���܂��B
���ۂ̓��������Ă݂܂��B
db=# CREATE SEQUENCE test_seq CACHE 3;
CACHE �̂ݐݒ肵�āi���̓f�t�H���g�Łj�쐬���܂����B
���̂悤�ɐݒ肳��Ă��܂��B
db=# SELECT last_value, cache_value FROM test_seq; -[ RECORD 1 ]-- last_value | 1 cache_value | 3
nextval() �Œl���擾���܂��B
db=# SELECT nextval('test_seq'); �� 1 ���Ԃ�
SEQUENCE �͎��̂悤�ɂȂ�܂��B
db=# SELECT last_value, cache_value FROM test_seq; -[ RECORD 1 ]-- last_value | 3 cache_value | 3
1 �� nextval() ���Ă��܂��� last_value �� 3 �ɂȂ��Ă��܂��B ����� 1 ���擾�����Ƃ��� 2, 3 ���L���b�V���Ƃ��Ď擾���Ă��邽�߂ł��B
������� nextval() �𑱂���� 2, 3 ���擾�ł��܂��B
db=# SELECT nextval('test_seq'); �� 2 ���Ԃ� db=# SELECT nextval('test_seq'); �� 3 ���Ԃ�
����ŃL���b�V�����������g�����Ă��܂����̂ł��� ����� nextval() ���Ă݂܂��B
db=# SELECT nextval('test_seq'); �� 4 ���Ԃ�
���̏�Ԃ� SEQUENCE �͎��̂悤�ɂȂ�܂��B
db=# SELECT last_value, cache_value FROM test_seq; -[ RECORD 1 ]-- last_value | 6 cache_value | 3
���̂悤�ɁA�L���b�V�����g����ƐV�����L���b�V�����擾���Ȃ����܂��B �f�t�H���g�ł� CACHE �� 1 �Ȃ̂Ŏ擾���邲�ƂɃL���b�V�����g�����Ă���킯�ł��B
�L���b�V���̓Z�b�V�������ł̂ݗL���ł��B ���̏�Ԃ́A���̃Z�b�V�����ȊO����� 6 �܂Ŏg�p�ς݂Ƃ��Ĉ������� �ʂ̃Z�b�V������ nextval() ����Ǝ��̂悤�� 7 ���Ԃ�܂��B
[�ʃZ�b�V����] db=# SELECT nextval('test_seq'); �� 7 ���Ԃ� [���̃Z�b�V����] db=# SELECT nextval('test_seq'); �� 5 ���Ԃ�
���̂��߃L���b�V�����g�p����� SEQUENCE �̒l�����n��� ���Ȃ����Ƃ�����̂Œ��ӂ��K�v�ł��B
PostgreSQL �� SEQUENCE �ɒl��ݒ肷��ɂ� ALTER SEQUENCE �� setval() �� 2 �̕��@������܂��B
����� setval() �ɂ� ������ 2 �̂��̂� 3 �̂��̂�����܂��B ���ꂼ��̎��s���ʂ����Ă����܂��B
�܂� ALTER SEQUENCE �B
db=# ALTER SEQUENCE test_sequence RESTART WITH 1;
���̂Ƃ��A�����̒l�͎��̂悤�ɂȂ��Ă��܂��B
db=# SELECT last_value, is_called FROM test_sequence; -[ RECORD 1 ]-- last_value | 1 is_called | f
last_value �� RESTART WITH �Őݒ肵�� 1 �ɂȂ�A is_called �� f (�U) �ɂȂ�܂��B
is_called �Ƃ����̂� last_value �����ɌĂꂽ���ǂ����̃t���O�� is_called ���U�ł���� �܂��Ă�Ă��Ȃ��̂� nextval() �����Ƃ��� last_value �� 1 ��Ԃ��܂��B
���� ������ 2 �� setval() �ł��B
db=# SELECT setval('test_sequence', 1);
���̂Ƃ��A�����̒l�͎��̂悤�ɂȂ��Ă��܂��B
db=# SELECT last_value, is_called FROM test_sequence; -[ RECORD 1 ]-- last_value | 1 is_called | t
������ 2 �� setval() �ł� last_value �͓����ł��� is_called �� t (�^) �ɂȂ�܂����B last_value �� 1 �͊��ɌĂ�Ă���Ƃ������e�Ȃ̂� ���̏ꍇ nextval() �����Ƃ��� 1 + increment_by (�f�t�H���g 1) ��Ԃ��܂��B
�����悤�� 1 ��ݒ肵�Ă� ALTER SEQUENCE �� ������ 2 �� setval() �ł� ���ɕԂ��l���ς���Ă��܂��܂��B
������ ������ 3 �� setval() �̏o�ԂɂȂ�܂��B
db=# SELECT setval('test_sequence', 1, false);
��̂悤�� ������ 3 �� setval() �ł� ��O������ is_called �̒l���w�肷�邱�Ƃ��ł��܂��B
����� ALTER SEQUENCE �����Ƃ��Ɠ����ɂȂ�܂��B
db=# SELECT last_value, is_called FROM test_sequence; -[ RECORD 1 ]-- last_value | 1 is_called | f
SEQUENCE �̓f�[�^�̈�Ӑ�������Ă����厖�ȋ@�\�Ȃ̂� �ڍs���Ȃǂɂ���Ă��܂�Ȃ��悤���ӂ��K�v�ł��B
PostgreSQL �� SEQUENCE �̎��̂̓e�[�u���ł��B CREATE SEQUENCE �����Ƃ��� SEQUENCE �Ɠ������O�� 1 �s�����̓���ȃe�[�u�����쐬���܂��B
���̂悤�� \d �� SEQUENCE �̏�Ԃ��m�F���邱�Ƃ��ł��܂��B
db=# \d test_sequence
Sequence "public.test_sequence"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | test_sequence
last_value | bigint | 9
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 26
is_cycled | boolean | f
is_called | boolean | t
SEQUENCE �̎��̂̓e�[�u���Ȃ̂� ���̂悤�� SQL �� FROM ��� SEQUENCE �����w�肵�Ă� ���g�����邱�Ƃ��ł��܂��B
db=# \x Expanded display is on. db=# SELECT * FROM public.test_sequence; -[ RECORD 1 ]-+-------------------- sequence_name | test_sequence last_value | 9 start_value | 1 increment_by | 1 max_value | 9223372036854775807 min_value | 1 cache_value | 1 log_cnt | 26 is_cycled | f is_called | t
PostgreSQL �� view ���e�[�u���ƃ��[�����g���Ď������Ă��܂��� ���Ȃ������ő����̋@�\����������Ƃ����̂̓J�b�R�����ł��ˁB
PostgreSQL �� psql �ɂ̓w���v��\������֗��ȃR�}���h \h ������܂��B
�g�����͊ȒP�ł��B
�����Ȃ��Ŏ��s����� SQL �̃R�}���h�ꗗ���\������܂��B
db=# \h
ABORT CREATE LANGUAGE
ALTER AGGREGATE CREATE OPERATOR
ALTER CONVERSION CREATE OPERATOR CLASS
ALTER DATABASE CREATE OPERATOR FAMILY
ALTER DOMAIN CREATE ROLE
ALTER FOREIGN DATA WRAPPER CREATE RULE
ALTER FUNCTION CREATE SCHEMA
ALTER GROUP CREATE SEQUENCE
ALTER INDEX CREATE SERVER
ALTER LANGUAGE CREATE TABLE
�����ɃR�}���h��t����ƃR�}���h�̐�����\�����܂��B
db=# \h COPY
Command: COPY
Description: copy data between a file and a table
Syntax:
COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ]
SQL �R�}���h�̎g������Y�ꂽ�Ƃ��� �茳�Ƀ��t�@�����X���Ȃ��Ă� ���̏�Ŋm�F�ł���̂� �֗��ł��B
PostgreSQL �� psql �ł� \timing ���g���� ���s���Ԃ��v�����邱�Ƃ��ł��܂��B
db=# \timing
Timing is on.
����Ōv���� on �ɂȂ�܂����B
��͒ʏ�ʂ� SQL �����s���܂��B
db=# select 1;
?column?
----------
1
(1 row)
Time: 2.422 ms
���� 1 �x���s����� off �ɂȂ�܂��B
db=# \timing
Timing is off.
PostgreSQL �� PL/pgSQL ���g���� ORACLE �� PL/SQL �̂悤�� ������邱�Ƃ��ł��܂��� �葱������� SQL �ł��ȒP�Ɋ�����邱�Ƃ��ł��܂��B
���̂悤�ɒ�`���܂��B
CREATE FUNCTION test_func (
i1 integer,
i2 integer
)
RETURNS integer
AS
$$
SELECT $1 + $2
$$
LANGUAGE sql IMMUTABLE STRICT;
�Ăяo���Ƃ��͓����ł��B
db=# SELECT test_func(4, 3);
7
PostgreSQL ���g���Ă��ĔF�� md5 �ȂǂɂȂ��Ă���ꍇ psql �Őڑ����邽�тɃp�X���[�h���Ă��܂��B
���ꂪ�ʓ|�ȏꍇ�� ���ϐ� PGPASSWORD �Ƀp�X���[�h��ݒ肵�Ă������Ƃ��ł��܂��B
$ echo "select * from pg_tables" | psql sample_db
Password:
�ʏ�́��̂悤�ɂȂ�܂��B
$ export PGPASSWORD=xxxxxx $ echo "select * from pg_tables" | psql sample_db
�p�X���[�h���Ă��Ȃ��Ȃ�܂��B
�i xxxxxx �́A�p�X���[�h�ł��j
�o�b�`�����Ȃǂł����̕��@���g���܂��B
�ȉ��� SQL �� 'xxxxx' �̕�����ɕύX���܂��B
SELECT schemas.nspname AS schema_name
, tables.relname AS table_name
FROM pg_attribute AS colmuns
INNER JOIN pg_class AS tables
ON colmuns.attrelid = tables.oid
INNER JOIN pg_namespace AS schemas
ON tables.relnamespace = schemas.oid
WHERE colmuns.attname = 'xxxxx'
ORDER BY schema_name, table_name
�e�[�u�����č쐬�����Ƃ��ȂǁA�e�[�u���� serial �̗���ꍇ �����̔ԍ��Əd�Ȃ�Ȃ��悤�ɏ����i sequence �j�̌��݂̒l��ݒ肷��K�v������܂��B
�����̌��݂̒l��ݒ肷��ɂ͎��� setval() ���g�p���܂��B
# SELECT setval('������', 3, true);
�ݒ肵���l���m�F����ɂ� currval() ���g�p���܂��B
# SELECT currval('������');
�������Acurrval() �� setval() �� nextval() �������Z�b�V�������ł��� �g�p�ł��܂���B
PostgreSQL �Ɋi�[����Ă��� PL/pgSQL ���̃\�[�X���m�F���郁���ł��B
���� test �Ƃ������Ŋm�F���Ă݂܂��B
CREATE OR REPLACE FUNCTION test(f1 TEXT, f2 TEXT) RETURNS TEXT AS $$ DECLARE BEGIN RETURN 'This is a pen!'; END; $$ LANGUAGE PLPGSQL;
���̏�i�[����Ă��� pg_proc �J�^���O����擾���܂��B prosrc ��Ƀ\�[�X���i�[����Ă��܂��B
db=# SELECT prosrc FROM pg_proc WHERE proname = 'test'; prosrc ------------------------------ DECLARE BEGIN RETURN 'This is a pen!'; END; (1 row)
pg_proc �J�^���O�ɂ� ���ɂ��F�X�ȏ�i�[����Ă��܂��B
db=# \x db=# SELECT * FROM pg_proc WHERE proname = 'test'; -[ RECORD 1 ]--+----------------------------- proname | test pronamespace | 27944 proowner | 16384 prolang | 34946 procost | 100 prorows | 0 proisagg | f prosecdef | f proisstrict | f proretset | f provolatile | v pronargs | 2 prorettype | 25 proargtypes | 25 25 proallargtypes | proargmodes | proargnames | {f1,f2} prosrc | : DECLARE : BEGIN : RETURN 'This is a pen!'; : END; : probin | - proconfig | proacl |
proargnames �������̖��O�� proargtypes �������̃f�[�^�^�ł��B prorettype �͖߂�l�̃f�[�^�^�ł��B
�y�Q�l�T�C�g�z
PostgreSQL 8.1.0 ���� 41.24. pg_proc
�����Ȃ� PREPARE �Ə����Ă� �m��Ȃ��l�́u������H�v�ƂȂ�Ǝv���܂��B PREPARE �� PostgreSQL �ɂ����� SQL ���̗\������Ă���郁�\�b�h�ł��B SQL ���̈ꎞ�I�Ȋ����ƍl���Ă��ǂ���������܂���B
���ۂ̎g�����ł����A���̂悤�ɐ錾���܂��B
db=# PREPARE sql_1 AS SELECT tablename from pg_tables; PREPARE
����� sql_1 �Ƃ������O�œo�^�ł��܂����B
�\�� SQL �������s����ɂ� EXECUTE ���g�p���܂��B
db=# EXECUTE sql_1; tablename ------------------------- sql_features sql_implementation_info sql_languages sql_packages sql_sizing sql_sizing_profiles
PREPARE �ɂ� SELECT,INSERT,UPDATE,DELETE ���g�p�ł��� �ڑ������܂ŗL���ł��B
SQL �����̒l�������ŕς��邱�Ƃ��ł��܂��B
db=# PREPARE sql_2 (varchar) AS SELECT tablename db-# from pg_tables db-# where tablename = $1; PREPARE
������ 1�߂��珇�Ԃ� �ϐ� $1,$2,$3... �Ŏ܂��B
EXECUTE �Ŏ��s����Ƃ��ɒl���w�肵�܂��B
db=# EXECUTE sql_2('pg_index');
tablename
-----------
pg_index
(1 row)
�傫�ȃ����b�g�Ƃ��� EXECUTE �����Ƃ��Ɏ��s�v��Ȃǂ̏��������Ȃ����� �i PREPARE �����Ƃ��ɏ�������܂��j ���� SQL �������x���g�p����ꍇ�A���x���オ��܂��B
�O�� VACUUM �̊m�F�ɂ��ď����܂����� ����� ANALYZE �̊m�F�ɂ��ď��������Ǝv���܂��B
�܂��̓e�[�u�� analyze_test ���쐬���܂��B
db=# CREATE TABLE analyze_test (f1 VARCHAR(10), f2 INTEGER);
CREATE TABLE
ANALYZE ������ pg_stat_all_tables ����擾�ł��܂��B
db=# SELECT * FROM pg_stat_all_tables db-# WHERE relname = 'analyze_test'; -[ RECORD 1 ]----+------------------------------ relid | 16469 schemaname | public relname | analyze_test seq_scan | 2 seq_tup_read | 9 idx_scan | idx_tup_fetch | n_tup_ins | 5 n_tup_upd | 1 n_tup_del | 1 n_tup_hot_upd | 1 n_live_tup | 4 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze |
ANALYZE ����O�͂��̂悤�ȏ�Ԃł��B
�܂��Apg_stat_all_tables �̑��� �Ƃ̓��v��� pg_stats �r���[����擾�ł��܂��B
db=# SELECT * FROM pg_stats WHERE tablename = 'analyze_test';
(No rows)
ANALYZE ����O�� ���R�[�h���擾�ł��܂���B
����ł� ANALYZE �����s���܂��B
db=# ANALYZE analyze_test;
ANALYZE
��Ԃ��m�F���܂��B
db=# SELECT * FROM pg_stat_all_tables db-# WHERE relname = 'analyze_test'; -[ RECORD 1 ]----+------------------------------ relid | 16469 schemaname | public relname | analyze_test seq_scan | 2 seq_tup_read | 9 idx_scan | idx_tup_fetch | n_tup_ins | 5 n_tup_upd | 1 n_tup_del | 1 n_tup_hot_upd | 1 n_live_tup | 4 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | 2010-04-24 15:49:12.383112+09 last_autoanalyze |
last_analyze �� ANALYZE �����������i�[����܂����B
�Ƃ̓��v�����m�F���܂��B
db=# SELECT * from pg_stats WHERE tablename = 'analyze_test'; -[ RECORD 1 ]-----+-------------------------- schemaname | public tablename | analyze_test attname | f1 null_frac | 0 avg_width | 6 n_distinct | -1 most_common_vals | most_common_freqs | histogram_bounds | {00001,00003,00004,00005} correlation | -0.2 -[ RECORD 2 ]-----+-------------------------- schemaname | public tablename | analyze_test attname | f2 null_frac | 0 avg_width | 4 n_distinct | -1 most_common_vals | most_common_freqs | histogram_bounds | {15,30,40,50} correlation | -0.2
ANALYZE �����e�[�u���̗�̃��R�[�h���lj�����Ă��܂��B
histogram_bounds �ɗ�̒l�̃T���v���������Ă��܂��B
�i�s�����Ȃ��̂őS�Ă̒l�������Ă��܂��j
PostgreSQL �� �f�[�^���X�V�����Ƃ��� �폜�����Ƃ��� ���R�[�h�Ɂu�s�v�v�}�[�N��t����Ƃ����������s�Ȃ��܂��B
�u�s�v�v�}�[�N�̕t�������R�[�h�i�ȉ��A�s�v�ȃ��R�[�h�j�� ���̂܂܂ł͍ė��p�ł��Ȃ��̂Ńf�[�^��}�����Ȃ��Ă� �X�V���J��Ԃ������� �e�[�u���̎g�p����X�y�[�X�͍L�����Ă����܂��B
VACUUM �́A�s�v�ɂȂ������R�[�h�̃X�y�[�X�� �ė��p�\�ɂ��܂��B ����́A���̓������m�F���Ă݂����Ǝv���܂��B
�܂��͐V�����e�[�u�� vacuum_test ���쐬���܂��B
db=# CREATE TABLE vacuum_test (f1 VARCHAR(10), f2 INTEGER);
CREATE TABLE
���� pg_stat_all_tables �J�^���O����擾���܂��B
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------ n_live_tup | 0 n_dead_tup | 0 last_vacuum | last_autovacuum |
�e�[�u�����쐬�������_�ł͏�̂悤�ɂȂ��Ă��܂��B
VACUUM �Ɋ֘A�����ȗ�̂ݎ擾���Ă��܂��B
���ꂼ��̗�̈Ӗ��͎��̂悤�ɂȂ�܂��B
| �� | ���� |
|---|---|
| n_live_tup | �L���ȃ��R�[�h�� |
| n_dead_tup | �s�v�ɂȂ������R�[�h�� |
| last_vacuum | ��� VACUUM �����s�������� |
| last_autovacuum | �Ō�� ���� VACUUM �����s���ꂽ���� |
���R�[�h��}�����܂��B
INSERT INTO vacuum_test VALUES ('00001', 10);
INSERT INTO vacuum_test VALUES ('00002', 20);
INSERT INTO vacuum_test VALUES ('00003', 30);
INSERT INTO vacuum_test VALUES ('00004', 40);
INSERT INTO vacuum_test VALUES ('00005', 50);
��Ԃ��m�F���܂��B
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------ n_live_tup | 5 n_dead_tup | 0 last_vacuum | last_autovacuum |
5 ���R�[�h�}�������̂ŁA�L���ȃ��R�[�h���� 5 �ɂȂ�܂����B
���� 1 ���R�[�h�폜���܂��B
db=# DELETE FROM vacuum_test WHERE f1 = '00002';
DELETE 1
��Ԃ��m�F���܂��B
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------ n_live_tup | 4 n_dead_tup | 1 last_vacuum | last_autovacuum |
�L���ȃ��R�[�h���� 4 �ɂȂ� �s�v�ȃ��R�[�h���� 1 �ɂȂ�܂����B
���łɍX�V�����܂��B
db=# UPDATE vacuum_test SET f2 = 15 WHERE f1 = '00001';
UPDATE 1
��������Ԃ��m�F���܂��B
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------ n_live_tup | 4 n_dead_tup | 2 last_vacuum | last_autovacuum |
�s�v�ȃ��R�[�h���� 1 �����܂����B
�����܂ł͏����ł��B �悤�₭ VACUUM �����s���܂��B
db=# VACUUM vacuum_test;
VACUUM
��Ԃ��m�F���܂��B
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------------------------ n_live_tup | 4 n_dead_tup | 0 last_vacuum | 2010-04-20 22:20:20.560647+09 last_autovacuum |
�s�v�ȃ��R�[�h���� 0 �ɂȂ�܂����B
last_vacuum �� VACUUM �����s���������������Ă��܂��B
���̂悤�� VACUUM �ŁA�s�v�ɂȂ������R�[�h�� �X�y�[�X���ė��p�\�ɂ��邱�Ƃ��ł��܂��B
PostgreSQL �� ���ݎ��s���̃v���Z�X�� ���s���̃N�G������s���Ԃ��擾���܂��B
�y�Q�l�T�C�g�z
PostgreSQL�̂�����ƃe�N - �悭����Wiki
SELECT
procpid AS "�v���Z�XID",
start AS "�J�n����",
now() - start AS "���s����",
current_query AS "���s�N�G��"
FROM
(SELECT
backendid,
pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity_start(S.backendid) AS start,
pg_stat_get_backend_activity(S.backendid) AS current_query
FROM
(SELECT pg_stat_get_backend_idset() AS backendid) AS S
) AS S
WHERE
current_query <> '<IDLE>'
AND
procpid <> pg_backend_pid()
ORDER BY
"���s����" DESC;
�������g�̃v���Z�X�� idle ���̃v���Z�X�����O���Ă��܂��B
���������Ȃ��Ƃ��́A"\x" �œW�J�\�� ���Ă������ ���� SQL �ł����₷���Ȃ�܂��B
db=# \x db=# SELECT .... (�ȗ�) -[ RECORD 1 ]----------------------------- �v���Z�XID | 19848 �J�n���� | 2010-03-25 22:17:48.459064+09 ���s���� | 00:00:18.431077 ���s�N�G�� | <IDLE> in transaction
�v���Z�X���~�߂����ꍇ�́A���̂悤�ɂ��܂��B
db=# SELECT pg_cancel_backend("�v���Z�XID");
"�v���Z�X ID" �ɑΏۂ̃v���Z�X ID �����Ă��������B
PostgreSQL �� SQL �ł͐F�X�ȎZ�p���Z�q���g���܂��B
����̓o�C�i�����Z�q�����������̂��Љ�����Ǝv���܂��B
�܂��͎l�����Z�B
db=# -- �a db=# SELECT 1 + 1; db=# -- �� db=# SELECT 3 - 2; db=# -- �� db=# SELECT 4 * 6; db=# -- �� db=# SELECT 7 / 2;
���́A�����̊���Z�̏ꍇ�A��̂ĂɂȂ�܂��B
���ɂ��F�X����܂��B
�����v�Z��������ƕ����Ăǂ����B
db=# -- �ׂ��� db=# SELECT 2 ^ 5; db=# SELECT POWER(2, 5); db=# -- ��]�i����Z�̗]��j db=# SELECT 13 % 5; db=# SELECT MOD(13, 5); db=# -- ������ db=# SELECT |/ 9; db=# SELECT sqrt(9); db=# -- ������ db=# SELECT ||/ 8; db=# SELECT cbrt(8); db=# -- ��Βl db=# SELECT @ -4; db=# SELECT abs(-4);
���͂���܂��A�K��̉��Z�q������܂��B
�K��̉��Z�q�͍��E�� 2 �o�[�W����������܂��B
db=# -- �K��i���j db=# SELECT !! 4; db=# -- �K��i�E�j db=# SELECT 4 !;
db=# SELECT DATE_TRUNC('month', now() + '1 months') + '-1 days';
?column?
------------------------
2010-02-28 00:00:00+09
(1 row)
�����̌��������� 1 �������܂��B
PostgreSQL �ɂ� �ʏ�� DISTINCT �ł͂Ȃ� DISTINCT ON �Ƃ������̂�����܂��B
���̂悤�ȃe�[�u��������Ƃ��܂��B
db1=# SELECT * FROM distinct_test;
field1 | field2 | field3
--------+--------+--------
1 | 4 | 1
1 | 4 | 2
2 | 2 | 1
2 | 2 | 2
3 | -1 | 1
3 | -1 | 2
(6 rows)
�܂��͒ʏ�� DISTINCT �ł��B
db1=# SELECT DISTINCT field1, field2
FROM distinct_test;
field1 | field2
--------+--------
1 | 4
2 | 2
3 | -1
(3 rows)
���̏ꍇ�A��Ɏw�肵�� field1, field2 �̏��ԂŃ\�[�g����܂��B
��̎w��̏��Ԃ�ς��܂��B
db1=# SELECT DISTINCT field2, field1
FROM distinct_test;
field2 | field1
--------+--------
-1 | 3
2 | 2
4 | 1
(3 rows)
���x�� field2, field1 �Ń\�[�g����܂��B
����͂܂��A�����������̂ł��B
DISTINCT ON ���g���Ă݂܂��B
DISTINCT ON (field1, field2) �̃J�b�R�̒���
��̎w�� "field1, field2, field3" ��
�Ⴄ���Ƃɒ��ӂ��Ă��������B
db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test; field1 | field2 | field3 --------+--------+-------- 1 | 4 | 1 2 | 2 | 1 3 | -1 | 1 (3 rows)
�s���� DISTINCT �� field1, field2 ���w�肵���Ƃ��Ɠ����ł��B
�܂� DISTINCT ON ���g�p����� �J�b�R�̒��Ŏw�肵����� �܂Ƃ߂��A�J�b�R�̒��Ŏw�肵�Ă��Ȃ���i���̗Ⴞ�� field3 �j�� �ŏ��Ɍ����������̂��o�͂���܂��B
�u DISTINCT �ł܂Ƃ߂������ǁA���̗���~�����I�v�Ƃ����Ƃ��ɕ֗��ł��ˁB �����A���̂܂܂��� DISTINCT ON �Ɏw�肵�Ă��Ȃ���� �ǂ̒l���o��̂��ۏႳ��Ȃ����߁A�\�[�g�������Ă��K�v������܂��B
ORDER BY �ɂ��\�[�g�͎��̂悤�ɂȂ�܂��B
db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test ORDER BY field1; field1 | field2 | field3 --------+--------+-------- 1 | 4 | 1 2 | 2 | 1 3 | -1 | 1 (3 rows) db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test ORDER BY field2; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test ORDER BY field3; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
DISTINCT ON �̐擪�Ŏw�肳��Ă��� field1 �ȊO�� ��ł� �G���[�ɂȂ��Ă��܂��܂��B
ORDER BY �Ń\�[�g����ɂ́A DISTINCT ON �Ŏw�肵����i�w�菇�j�̌�ɁADISTINCT ON �ȊO�̗�������K�v������܂��B
db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test ORDER BY field1, field2, field3 DESC; field1 | field2 | field3 --------+--------+-------- 1 | 4 | 2 2 | 2 | 2 3 | -1 | 2 (3 rows)
�������邱�Ƃɂ���� GROUP BY �� MAX() ���g�����̂� �����l���擾�����Ƃ��ł��܂����B
�u���O�Ȃǂ̃T�C�g��� SQL �̎��s���ʂ��ڂ��邱�Ƃ�����܂��� ���̂悤�ɏo�͂����� ������ƌ��h�����̂�����܂��B
db1=# select * from pg_database where datname = 'postgres';
datname | datdba | encoding | datistemplate | datallowconn | datco
nnlimit | datlastsysoid | datvacuumxid | datfrozenxid | dattablespac
e | datconfig | datacl
----------+--------+----------+---------------+--------------+------
--------+---------------+--------------+--------------+-------------
--+-----------+--------
postgres | 10 | 6 | f | t |
-1 | 10792 | 499 | 499 | 16
63 | |
(1 row)
���������Ƃ��ɓW�J�\�����g���܂��B
db1=# \x
Expanded display is on.
���̐ݒ�ɂ���� ���̂悤�Ɍ��ʂ��c�ɓW�J����܂��B
db1=# select * from pg_database where datname = 'postgres';
-[ RECORD 1 ]-+---------
datname | postgres
datdba | 10
encoding | 6
datistemplate | f
datallowconn | t
datconnlimit | -1
datlastsysoid | 10792
datvacuumxid | 499
datfrozenxid | 499
dattablespace | 1663
datconfig |
datacl |
psql �̋N��������ݒ肷��Ƃ��� -x �I�v�V�������g�p���܂��B
$ psql -x db1
PostgreSQL �ł� pg_hba.conf ���g���Đڑ��ݒ���s���܂��� ���̑O�� �l�b�g���[�N�ڑ��̐ݒ�� postgresql.conf �ɍs���K�v������܂��B �i�f�t�H���g�ł������Ǝv���܂����j
PostgreSQL �́ATCP/IP �\�P�b�g �� UNIX �h���C���\�P�b�g ���g���Đڑ����܂��B �킩��Ȃ���ATCP/IP �\�P�b�g �́A�l�b�g���[�N�AUNIX �h���C���\�P�b�g �� ���[�J�� �� ���[�J���̔�l�b�g���[�N�ʐM���ƍl���Ă��������B �O������ڑ�����ꍇ�ATCP/IP �\�P�b�g ���g���܂����A ���[�J������g���ꍇ�� TCP/IP �\�P�b�g �ł� UNIX �h���C���\�P�b�g �ł��ʐM�ł��܂��B
�ݒ�� postgresql.conf �� listen_addresses �p�����[�^���g�p���܂��� CentOS �� Redhat �ł� �f�t�H���g�ł� ���̂悤�ɃR�����g�A�E�g����Ă��܂��B �R�����g�A�E�g����Ă���ꍇ�̃f�t�H���g�l�� localhost �ł��B
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
���̏ꍇ�� PostgreSQL �� LISTEN ���Ă���A�h���X�� netstat �R�}���h�� �m�F���Ă݂܂��B
localhost �Ȃ̂� ���̂悤�� 127.0.0.1 �� LISTEN ���Ă��܂����B �܂��A���ł� UNIX �h���C���\�P�b�g�������܂��B
# netstat -an | grep LISTEN | grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN unix 2 [ ACC ] STREAM LISTENING 343193 /tmp/.s.PGSQL.5432
���̐ݒ�ł� IP �A�h���X�� 127.0.0.1 �Ƃ��� �A�N�Z�X���ꂽ���̂����ڑ��ł��܂���B �O������ 192.168.1.92 �Ƃ����� IP �A�h���X�ŃA�N�Z�X���ꂽ�ꍇ�� �ڑ��ł��Ȃ��킯�ł��B
�O������ڑ�������ɂ� �ʏ� listen_addresses �p�����[�^�� '*' (�A�X�^���X�N) ��ݒ肵�܂��B
listen_addresses = '*'
���̏ꍇ�� netstat �̌��ʂ� 0.0.0.0 �ƂȂ�܂��B
# netstat -an | grep LISTEN tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN unix 2 [ ACC ] STREAM LISTENING 343193 /tmp/.s.PGSQL.5432
0.0.0.0 �̏ꍇ 127.0.0.1 �ł� 192.168.1.92 �ł��ڑ����邱�Ƃ��ł��܂��B
���ɂ� �J���}�ŋ���Ďw�肷�邱�Ƃ��ł��܂��B
listen_addresses = 'localhost,192.168.1.92'
���̏ꍇ�� netstat �̌��ʂ� ���̂悤�ɕ����o�Ă��܂��B
# netstat -an | grep LISTEN tcp 0 0 192.168.1.92:5432 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN unix 2 [ ACC ] STREAM LISTENING 343193 /tmp/.s.PGSQL.5432
TCP/IP �\�P�b�g ���g�p���� UNIX �h���C���\�P�b�g�݂̂Ŏg�p����ꍇ�� listen_addresses �p�����[�^�� ����ݒ肵�܂��B
listen_addresses = ''
���̏ꍇ�� netstat �̌��ʂ� ���̂悤�ɕ����o�Ă��܂��B
# netstat -an | grep LISTEN
unix 2 [ ACC ] STREAM LISTENING 343193 /tmp/.s.PGSQL.5432
(Web �T�[�o�Ȃǂ�) �A�v���P�[�V�����T�[�o�� PostgreSQL �T�[�o�������}�V���� �O������ڑ�������K�v���Ȃ��̂ł���A�������Ă����� �l�b�g���[�N���g�p�����ڑ����֎~�ł��܂��B
PostgreSQL �� ���� WARNING ���������邱�Ƃ�����܂��B
WARNING: nonstandard use of escape in a string literal HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
����� SQL �̒��Ƀo�b�N�X���b�V���̕������e������ ����ꍇ�ɋN����̂ł��� HINT �̒ʂ� 'E' ��t���邱�ƂőΉ��ł��܂��B
���̂悤�� SQL �Ŕ������܂��B
�x���͏o�܂����A���ʂ��擾�ł��܂��B
db1=# SELECT REPLACE(field1, '\n', '\\n') FROM table1;
WARNING: nonstandard use of escape in a string literal
LINE 1: SELECT REPLACE(field1, '\n', '\\n') FROM table1
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
WARNING: nonstandard use of \\ in a string literal
LINE 1: SELECT REPLACE(field1, '\n', '\\n') FROM table1
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
replace
------------------------------------------------------
test "test!"\ntest "test!"\ntest "test!"\n����ɂ���
HINT �ɏ]���� �������e������ 'E' ��t���܂��B
db1=# SELECT REPLACE(field1, E'\n', E'\\n') FROM table1; replace ------------------------------------------------------ test "test!"\ntest "test!"\ntest "test!"\n����ɂ���
����� WARNING ���o�Ȃ��Ȃ�܂����B
PostgreSQL �̓��O�����[�e�[�V��������@�\�������Ă��܂��B
Redhat �ł���f�t�H���g�� ���̂悤��Mon��Wed�Ȃǂ̗j���̕����� �t�^����āA���[�e�[�V��������`�ɂȂ��Ă��܂��B
$ ls /var/lib/pgsql/data/pg_log
postgresql-Mon.log
postgresql-Tue.log
postgresql-Wed.log
����� postgres.conf �� log_filename �p�����[�^�� �ݒ肷�邱�Ƃ��ł��܂��B Redhat �̃f�t�H���g�i�j���j�͎��̂悤�ɐݒ肳��Ă��܂��B
log_filename = 'postgresql-%a.log'
�N������t�^����ɂ́A���̂悤�ɐݒ肵�܂��B
log_filename = 'postgresql-%Y%m%d.log'
����ŁA���̂悤�ȃ��O�t�@�C�����ł��܂��B
$ ls /var/lib/pgsql/data/pg_log
postgresql-20091005.log
postgresql-20091006.log
postgresql-20091007.log
PostgreSQL �ɂ� SQL �ŃZ�b�V�����̏����擾������� �F�X�ƒ�`����Ă��܂��B �ȒP�ɏЉ�Ă��������Ǝv���܂��B
�ڑ����� PostgreSQL �T�[�o�̃o�[�W�����̕������Ԃ��܂��B
db1=# SELECT version();
version
-------------------------------------------------------------------
PostgreSQL 8.3.5 on i386-redhat-linux-gnu, compiled by GCC gcc (GC
(1 row)
�ڑ����� �f�[�^�x�[�X�̖��O��Ԃ��܂��B
$ psql db1 postgres db1=# SELECT current_database(); current_database ------------------ db1 (1 row)
���݂̃X�L�[�}����Ԃ��܂��B
�ڑ���������́Apostgresql.conf �� search_path �p�����[�^�� ��`�Ɉˑ������l��Ԃ��܂��B �����p�X����̏ꍇ�� NULL ��Ԃ��܂��B
db1=# SELECT current_schema();
current_schema
----------------
public
(1 row)
CREATE TABLE �Ȃǂ�����ۂɁA�X�L�[�}�����I�� ��`���Ȃ��ꍇ�A���̃X�L�[�}�����ݒ肳��܂��B
�����p�X�� set �ŕς��邱�Ƃ��ł��܂��B
db1=# set search_path = 'hogehoge'; db1=# SELECT current_schema(); current_schema ---------------- hogehoge (1 row) db1=# set search_path = 'public','hogehoge'; db1=# SELECT current_schema(); current_schema ---------------- public (1 row)
�����p�X�ɃX�L�[�}���w�肵���ꍇ�́A�擪�̃X�L�[�}�� ���݂̃X�L�[�}�ƂȂ�܂��B
�����p�X���̃X�L�[�}��Ԃ��܂��B boolean �� true ���Z�b�g����ƈÖق̃X�L�[�}���Ԃ��܂��B
db1=# SELECT current_schemas(false); current_schemas ----------------- {postgres,public} (1 row) db1=# SELECT current_schemas(true); current_schemas ----------------- {pg_catalog,postgres,public} (1 row)
�f�[�^�x�[�X�ɐڑ��������[�U����Ԃ��܂��B
$ psql db1 postgres db1=# SELECT session_user; session_user -------------- postgres (1 row) db1=# \q $ psql db1 hogehoge db1=# SELECT session_user; session_user -------------- hogehoge (1 row)
current_user �� user �͓����ł��B
current_user �́A���ݎ��s���Ă��郆�[�U����Ԃ��܂��B �ʏ�� session_user �Ɠ����ł����A SECURITY DEFINER �������t�����������s�����ꍇ�� �Ԃ��l������Ă��܂��B SECURITY DEFINER �����́A�����쐬�������[�U�̌����� ���s����I�v�V�����ł��B
$ psql db1 postgres db1=# SELECT session_user, current_user; session_user | current_user --------------+-------------- postgres | postgres (1 row)
SECURITY DEFINER ������t������ test1() �� �t���Ă��Ȃ� test2() �� hogehoge ���[�U�ō쐬���Ă݂܂��B
$ psql db1 hogehoge db1=# SCREATE FUNCTION test1() RETURNS name AS 'SELECT current_user;' SECURITY DEFINER LANGUAGE SQL ; CREATE FUNCTION db1=# SCREATE FUNCTION test2() RETURNS name AS 'SELECT current_user;' LANGUAGE SQL ; CREATE FUNCTION
����� postgres ���[�U�Ŏ��s���܂��B
$ psql db1 postgres db1=# select test1(); test1 ----------- hogehoge (1 row) db1=# select test2(); test1 ----------- postgres (1 row)
�قȂ�l��Ԃ��܂����B ���̂悤�Ɏ��ۂɎ��s���Ă��錠�����m�F����ꍇ�� current_user ���g�p���܂��B
�܂��Asession_user ���܂߁A���[�U�n�͓���ŁA���� () ��t������ �Ăяo���K�v������̂Œ��ӂ��܂��傤�B
CentOS �� Redhat �� PostgreSQL ����ꂽ���� ���{��̃\�[�g�������������Ȃ邱�Ƃ�����܂��B
item_nm
----------------------
����
�Ȃ�
����
�݂���
���
��������
��������
50 �����ł͂Ȃ��������̏��Ԃ� ����ł��܂����Ƃ�����܂��B
$ pg_controldata /var/lib/pgsql/data
Debian �n�� /usr/lib/postgresql/x.y/bin/pg_controldata �� ����܂��B (x.y �� 8.1 �Ȃǂ̃o�[�W�����ł�)
$ /usr/lib/postgresql/x.y/bin/pg_controldata \
/var/lib/postgresql/x.y/main/
���̌��ʂŁALC_COLLATE �� LC_CTYPE �� ���̂悤�ɏo�͂����ꍇ�A ���{��̃\�[�g�����������Ȃ�܂��B
LC_COLLATE: en_US.UTF-8 LC_CTYPE: en_US.UTF-8
CentOS �Ȃǂł́A/etc/init.d/pgsql �����s�����Ƃ��� $PGDATA (/var/lib/pgsql/data) �����݂��Ȃ��� initdb �����s����̂ł��� ���̂Ƃ��̃I�v�V������ --no-local �̎w�肪�Ȃ��̂� OS �̊������̂܂܃Z�b�g����Ă��܂��킯�ł��B
���Ȃ݂� PostgreSQL CE �̎����ł� ���{����g�p����ꍇ�� --no-local ���w�肷��̂��ǂ��Ƃ���Ă��܂����B
�����Ȃ��Ă���ꍇ�Ainitdb ������Ȃ����K�v������܂��B �����̃f�[�^�̓_���v������Ă������X�g�A���邱�Ƃ��ł��܂��B
$PGDATA (/var/lib/pgsql/data) �����l�[������ �蓮�� "initdb --no-local" �����s���邱�Ƃ� pg_controldata �̌��ʂ����̂悤�ɂȂ�܂��B
LC_COLLATE: C LC_CTYPE: C
�\�[�g�����������Ȃ�܂��B
item_nm
----------------------
��������
����
��������
�Ȃ�
�݂���
����
���
OS �S�̂�ς��Ă����Ȃ��ꍇ�́A /etc/sysconfig/i18n ���C������Ƃ����������悤�ł��B
LANG="ja_JP.UTF-8" # (�C����)
���̏ꍇ --no-local ��ݒ肷��K�v���Ȃ��Ȃ�̂� ������ $PGDATA �������� /etc/init.d/pgsql �����s���邾���ł��B
������ɂ���A�����̃f�[�^������ꍇ initdb �����s����O�� �ޔ������Ă����Ȃ��Ƃ����Ȃ��̂� ���ӂ��Ă��������B
�^�C�g���̒ʂ�ł����APostgreSQL �ł́ATRUNCATE TABLE �� �g�����U�N�V�������Ȃ̂ŁA���[���o�b�N����� �f�[�^�͌��ɖ߂�܂��B
���̂悤�ɂȂ�܂��B
postgres=# select * from test1; f1 | f2 ----+---- 1 | 1 2 | 2 (2 rows) postgres=# begin; BEGIN postgres=# truncate table test1; TRUNCATE TABLE postgres=# select * from test1; f1 | f2 ----+---- (0 rows) postgres=# rollback; ROLLBACK postgres=# select * from test1; f1 | f2 ----+---- 1 | 1 2 | 2 (2 rows)
�ʔ����̂́ACREATE TABLE �Ȃǂ��g�����U�N�V�������ł��B
postgres=# begin; BEGIN postgres=# create table test2 (f1 varchar(1), f2 int); CREATE TABLE postgres=# select * from test2; f1 | f2 ----+---- (0 rows) postgres=# rollback; ROLLBACK postgres=# select * from test2; ERROR: relation "test2" does not exist
���R�A���̃g�����U�N�V�������R�~�b�g����܂ł́A �ʂ̃g�����U�N�V��������� test2 �e�[�u���͌����܂���B
�ʂ̃g�����U�N�V����������ł���Ə�����₱�����Ȃ�܂��B
�g�����U�N�V���� A ���g�����U�N�V�������Ƀe�[�u�����쐬���Ă���Ƃ��� ���̃g�����U�N�V���� B �������e�[�u�����Ńe�[�u�����쐬���悤�Ƃ���� �g�����U�N�V���� B �� A �̌��ʑ҂��ɂȂ�܂��B
�g�����U�N�V���� A �����[���o�b�N���� �e�[�u���̍쐬���L�����Z�����ꂽ�ꍇ �g�����U�N�V���� B ���e�[�u�����쐬�ł���̂ŁA �g�����U�N�V���� B �̓g�����U�N�V�������p���ł��܂��B
�������A�g�����U�N�V���� A ���R�~�b�g���� �e�[�u�����쐬���m�肵�Ă��܂����ꍇ�́A �g�����U�N�V���� B �̓e�[�u���̍쐬���ł��Ȃ��̂��� B ���́A���̃G���[���������܂��B
ERROR: duplicate key value violates
unique constraint "pg_type_typname_nsp_index"
DROP TABLE �����l�̂��Ƃ��N����܂��B
���܂肱���������P�[�X�͂Ȃ��Ǝv���܂����A ���̕ӂ�̓����� Oracle �ƈقȂ�̂Œ��ӂ��K�v�ł��B
���̂悤�� PostgreSQL �� SQL �G���[������܂��B
SELECT DISTINCT, ORDER BY expressions must appear in select list
����� SELECT DISTINCT ���g�p���Ă���ꍇ�ɁA ORDER BY ��Ɋ܂܂�Ă��鍀�ڂ� SELECT ��ɂȂ��ꍇ�ɔ������܂��B
�Ⴆ�A���̂悤�� SQL �ł��B
SELECT DISTINCT
field_1
, field_2
FROM test_table
ORDER BY field_3
SELECT DISTINCT ����Ă���̂� field_1 �� field_2 �ł��� �\�[�g�� field_3 ���w�肵�Ă��܂��B
����ł͌��ʂ̕��я���ۏ��邱�Ƃ��ł��Ȃ����� �G���[����������킯�ł��B
���Ȃ݂� Oracle �̏ꍇ�́A���̂悤�ȃG���[�ɂȂ�܂��B
ORA-01791: SELECT���������ł�
�����AOracle �� ����Ɍ����悤�ŁA���̂悤�ȏꍇ�̓G���[�ɂȂ�܂���B �i PostgreSQL �̓G���[�ɂȂ�܂��j
SELECT DISTINCT
field_1
, field_2
FROM test_table
WHERE field_3 = '0'
ORDER BY field_3
���̏ꍇ�́Afield_3 �� 1�ɌŒ肳��邽�߁A "�\�[�g�w��Ȃ�" �Ɠ�����ԂɂȂ邽�߃G���[�ɂȂ�܂���B
�����B
=# SELECT attr.attname AS column_name -# FROM pg_attribute AS attr -# INNER JOIN pg_stat_user_tables AS stat -# ON attr.attrelid = stat.relid -# AND stat.schemaname = '�X�L�[�}��' -# AND stat.relname = '�e�[�u����' -# INNER JOIN pg_constraint cons -# ON attr.attnum = ANY (cons.conkey) -# AND cons.contype = 'p' -# AND cons.conrelid = stat.relid;
��L�[���ڂ������o�͂���܂��B
���̂悤�ȃe�[�u���� �f�[�^�x�[�X�ɂ���Ƃ��܂��B
| group_name | food_name |
|---|---|
| fruits | orange |
| fruits | apple |
| fruits | pears |
| fish | sardines |
| fish | tuna |
| fish | bream |
��ʂɕ\������ꍇ�́A ���̂悤�ɃO���[�v�����ĕ\���������Ȃ邱�Ƃ� �����Ȃ����Ǝv���܂��B ���͂����ł��B
| group_name | food_name |
|---|---|
| fruits | orange |
| apple | |
| pears | |
| fish | sardines |
| tuna | |
| bream |
���̂��߂ɂ́A�O���[�v���ƂɌ������擾����Ȃ� �lj��ŏ������쐬����K�v������̂ł����A ����͏W���������ċ[���I�ɑΉ����Ă݂܂��B
�W����Ƃ� max(*) �� count(*) �̂悤�� �����̃��R�[�h�ɑ��ď������s�Ȃ����ł��B
�܂���`�ł��B
CREATE AGGREGATE �����g�p���܂��B
�ڂ����́A�ȉ����Q�Ƃ��Ă��������B
PostgreSQL 8.0.2 ���� - CREATE AGGREGATE
����Ȋ����ł��B
CREATE AGGREGATE array_accum
(
BASETYPE = anyelement
, SFUNC = array_append
, STYPE = anyarray
, INITCOND = '{}'
);
�e�[�u���͎��̂悤�ɂȂ��Ă��܂��B
db=# select group_name, food_name from foods;
group_name | food_name
------------+-----------
fruits | orange
fruits | apple
fruits | pears
fish | sardines
fish | tuna
fish | bream
(6 rows)
�쐬�����W��� array_accum ���g���Ă݂܂��B
db=# select group_name db-# , array_accum(food_name) as food db-# from foods db-# group by group_name; group_name | food ------------+----------------------- fruits | {orange,apple,pears} fish | {sardines,tuna,bream} (2 rows)
����ł͒l�̕��т������̂� ���⍇���Ńe�[�u�����\�[�g���܂��B
db=# select group_name db-# , array_accum(food_name) as food db-# from (select * from foods order by food_name) as foods db-# group by group_name; group_name | food ------------+----------------------- fruits | {apple,orange,pears} fish | {bream,sardines,tuna} (2 rows)
������Ƃ��ĕԂ����߂� array_to_string ���g���� �z����������܂��B array_to_string(�z��, '��蕶��') �ł��B
db=# select group_name db-# , array_to_string(array_accum(food_name), '/') as food db-# from (select * from foods order by food_name) as foods db-# group by group_name; group_name | food ------------+--------------------- fruits | apple/orange/pears fish | bream/sardines/tuna (2 rows)
�l�����v���O�����ŏ��������邱�Ƃ��ł��܂��� �������s���܂܂Ȃ��l�̏ꍇ�A ���̂悤�ɉ��s�ŋ���ĕԂ��悤�ɂ���ƈ����Ղ��ł��B
select group_name
, array_to_string(array_accum(food_name), chr(10)) as food
from (select * from foods order by food_name) as foods
group by group_name;
���s�� <br> �ɒu��������� ���̂悤�ɂȂ�܂��B
| group_name | food |
|---|---|
| fruits | apple orange pears |
| fish | bream sardines tuna |
�����s�̃f�[�^���P�s�ŕԂ����̕��@�� �F�X�ȉ��p���ł���̂� �o���Ă����Ɩ��ɗ��Ǝv���܂��B
PostgreSQL 8.1 ����g����悤�ɂȂ��� autovacuum �ł��� 8.2 �܂ł� postgresql.conf �Ɏ��̂悤�ɐݒ肵�Ă��܂����B
# vi postgresql.conf
autovacuum = on stats_row_level = on
PostgreSQL 8.3 ����� autovacuum �� �f�t�H���g�� on �ɂȂ��Ă���̂� �ݒ肵�Ȃ��Ă��ǂ��̂ł����A �ݒ肷��ꍇ�́A stats_row_level �ł͂Ȃ� ���̂悤�� track_counts ��ݒ肷��悤�ɂȂ�܂����B �i stats_row_level ��ݒ肷��ƃG���[�ɂȂ�܂��j
autovacuum = on track_counts = on
PostgreSQL �� �V�X�e���J�^���O�� Oracle �� �f�B�N�V���i�� �ɋ߂����̂ł��B
�Ⴆ�e�[�u���̏����擾����ɂ͎��� pg_tables �r���[���g���܂��B
select schemaname, tablename from pg_tables;
���ɂ��F�X�ȃV�X�e���J�^���O������܂��B �ڂ����� PostgreSQL �̃T�C�g�̏����Q�Ƃ��Ă��������B