PostgerSQL ARRAY_AGG �� �� Oracle �� LISTAGG

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

PostgreSQL �� �z����e�[�u���̂悤�Ɉ���

�����ł��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}']

PostgreSQL �� ���l�̏����_�ȉ��� 0 �����

�����ł��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

[�Q�l]
8.1. ���l�f�[�^�^ - PostgreSQL 9.3.2����

PostgreSQL �ŁA�ꎞ�I�ɑS�Ẵg���K�[�𖳌��ɂ���

�����ł��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

PostgreSQL �̃p�X���[�h�t�@�C��

�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

Linux �Ńv���Z�X�̊‹��ϐ����m�F����

�ȑ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�[���̐ݒ�

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����

PostgreSQL �ł� watch

�ȑ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

SQL �� LATERAL �L�[���[�h

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 �� �C���f�b�N�X�E�I�����[�E�X�L����

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

���s���p�����[�^�̌��݂̐ݒ��\������ SHOW

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.

PostgreSQL �Őݒ肳��Ă���O���L�[�̈ꗗ���擾����

�����ł��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';

PostgreSQL Study (5)

����̓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

PostgreSQL Study (4)

����̓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/

PostgreSQL Study (3)

����̓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

PostgreSQL Study (2)

����͐ݒ�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 Study (1)

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 �� write failed �G���[

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�t�H���g�̕\�̈�̕ύX

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;

cross-database references are not implemented

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

PostgreSQL �� ROWNUM �݂����Ȃ��Ƃ�������

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 �̎��s�v��� Tid Scan

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

psql �ŃR�}���h���C������ϐ��ɒl��������

�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

psql �ŕϐ�������

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 �̏o�̓R�}���h

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

UNION ���܂܂��r���[�ƃC���f�b�N�X

��Ђŕ����ꂽ �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 �֐�

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 �� index-only

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

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 �̓���

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��ݒ肷��

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 �̎���

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

psql �֗̕��ȃR�}���h \h

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

psql �� SQL �̎��s���Ԃ��v������

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.

SQL �Ŋ֐��쐬

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 �̊‹��ϐ� PGPASSWORD

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

PostgreSQL �� ����̗���܂ރe�[�u���ꗗ���擾���� SQL

�ȉ��� 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

PostgreSQL �� �����i sequence �j�̒l��ݒ肷��

�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 �� PL/pgSQL �֐��̃\�[�X���m�F����

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

PostgreSQL �� PREPARE

�����Ȃ� 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

PostgreSQL �� ANALYZE �̓������m�F����

�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 �� VACUUM �̓������m�F����

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���m�F����

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 �Ŏg����Z�p���Z�q

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 !;

PostgreSQL �Ō������̎擾

db=# SELECT DATE_TRUNC('month', now() + '1 months') + '-1 days';

        ?column?
------------------------
 2010-02-28 00:00:00+09
(1 row)

�����̌��������� 1 �������܂��B

PostgreSQL �� DISTINCT ON

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

psql �� �W�J�\��

�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 �� �l�b�g���[�N�ڑ��ݒ�

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

nonstandard use of escape in a string literal

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�t�@�C�����ɓ��t��t�^����

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 �̃Z�b�V�����̏������o���֐�

PostgreSQL �ɂ� SQL �ŃZ�b�V�����̏����擾����֐��� �F�X�ƒ�`����Ă��܂��B �ȒP�ɏЉ�Ă��������Ǝv���܂��B

version()

�ڑ����� 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)

current_database()

�ڑ����� �f�[�^�x�[�X�̖��O��Ԃ��܂��B

$ psql db1 postgres

db1=# SELECT current_database();
 current_database
------------------
 db1
(1 row)

current_schema()

���݂̃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

current_schemas(boolean)

�����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)

session_user

�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

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

PostgreSQL �� �\�[�g��

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

PostgreSQL �� TRUNCATE ���g�����U�N�V�������ł��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

ORDER BY expressions must appear in select list

���̂悤�� 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

PostgreSQL �� ��L�[���ڂ��擾����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

PostgreSQL �� �W��֐����쐬����

���̂悤�ȃe�[�u���� �f�[�^�x�[�X�ɂ���Ƃ��܂��B

group_namefood_name
fruitsorange
fruitsapple
fruitspears
fishsardines
fishtuna
fishbream

��ʂɕ\������ꍇ�́A ���̂悤�ɃO���[�v�����ĕ\���������Ȃ邱�Ƃ� ����񂶂�Ȃ����Ǝv���܂��B ���͂����ł��B

group_namefood_name
fruitsorange
apple
pears
fishsardines
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_namefood
fruitsapple
orange
pears
fishbream
sardines
tuna

�����s�̃f�[�^���P�s�ŕԂ����̕��@�� �F�X�ȉ��p���ł���̂� �o���Ă����Ɩ��ɗ��‚Ǝv���܂��B

PostgreSQL 8.3 ����� autovacuum �ݒ�

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

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