LoginSignup
3
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@rubytomato@github(渡邉 真)

SQLサンプルコード (PostgreSQLで動作確認)

Last updated at Posted at 2017-03-13

概要

タイトルの通りSQLのTips的なサンプルコードです。PostgreSQLで動作確認をしています。
内容的には個人的な備忘録になるかもしれませんが、随時更新していきます。

環境

  • Windows10 Professional
  • PostgreSQL 9.6.1

参考

スケジュール表

当日を起点として31日分の日付を返すカレンダー表を作成するサンプルコードです。
GENERATE_SERIES関数で0から30までの連番を生成しその値を当日日付に加算することでカレンダー表を生成しています。

CREATE OR REPLACE VIEW v_schedule (
    index,
    hiduke,
    youbi
) AS
WITH cal AS (SELECT DATE_TRUNC('day', NOW()) AS n),
     idx AS (SELECT GENERATE_SERIES(0, 30) AS i)
SELECT idx.i
     , cal.n + CAST(idx.i || 'days' AS INTERVAL)
     , EXTRACT(DOW FROM cal.n + CAST(idx.i || 'days' AS INTERVAL))
  FROM cal
     , idx
     ;

> SELECT * FROM v_schedule;
 index |         hiduke         | youbi
-------+------------------------+-------
     0 | 2017-03-13 00:00:00+09 |     1
     1 | 2017-03-14 00:00:00+09 |     2
     2 | 2017-03-15 00:00:00+09 |     3
     3 | 2017-03-16 00:00:00+09 |     4
     4 | 2017-03-17 00:00:00+09 |     5
     5 | 2017-03-18 00:00:00+09 |     6
     6 | 2017-03-19 00:00:00+09 |     0
     7 | 2017-03-20 00:00:00+09 |     1
     8 | 2017-03-21 00:00:00+09 |     2
     9 | 2017-03-22 00:00:00+09 |     3
    10 | 2017-03-23 00:00:00+09 |     4
    11 | 2017-03-24 00:00:00+09 |     5
    12 | 2017-03-25 00:00:00+09 |     6
    13 | 2017-03-26 00:00:00+09 |     0
    14 | 2017-03-27 00:00:00+09 |     1
    15 | 2017-03-28 00:00:00+09 |     2
    16 | 2017-03-29 00:00:00+09 |     3
    17 | 2017-03-30 00:00:00+09 |     4
    18 | 2017-03-31 00:00:00+09 |     5
    19 | 2017-04-01 00:00:00+09 |     6
    20 | 2017-04-02 00:00:00+09 |     0
    21 | 2017-04-03 00:00:00+09 |     1
    22 | 2017-04-04 00:00:00+09 |     2
    23 | 2017-04-05 00:00:00+09 |     3
    24 | 2017-04-06 00:00:00+09 |     4
    25 | 2017-04-07 00:00:00+09 |     5
    26 | 2017-04-08 00:00:00+09 |     6
    27 | 2017-04-09 00:00:00+09 |     0
    28 | 2017-04-10 00:00:00+09 |     1
    29 | 2017-04-11 00:00:00+09 |     2
    30 | 2017-04-12 00:00:00+09 |     3
(31 行)

GENERATE_SERIES関数は日付も受け取るので、下記のように日付のFROM、TOを渡すことでもカレンダー表を生成することができます。

SELECT hiduke
     , EXTRACT(DOW FROM hiduke) AS youbi
  FROM (SELECT GENERATE_SERIES(
                 DATE_TRUNC('day', now()),
                 DATE_TRUNC('day', now() + CAST('30 days' AS INTERVAL)),
                 '1 days') AS hiduke
       ) AS schedule
     ;

カンマ区切りの値を扱う

あまり無いと思いますが1つのカラムにカンマ区切りで複数の値を格納しているようなテーブルがあって、そのカラムから任意の位置の値を取得したい場合のサンプルコードです。

下記のビューを使ってサンプルコードを作成しました。
sub_idsというカラムにカンマ区切りで複数のidが格納されているという想定です。

CREATE or REPLACE VIEW dummy (
    id,
    sub_ids,
    name
) AS
SELECT 1 AS id
     , '100,101' AS sub_ids
     , 'AAA' AS name
 UNION ALL
SELECT 2 AS id
     , '200,201,202' AS sub_ids
     , 'BBB' AS name
 UNION ALL
SELECT 3 AS id
     , '300' AS ids
     , 'CCC' AS name
;

> select * from dummy;
 id |   sub_ids   | name
----+-------------+------
  1 | 100,101     | AAA
  2 | 200,201,202 | BBB
  3 | 300         | CCC
(3 行)

Functionを作成すると簡単にカンマ区切りから任意の値を取り出すことが出来ます。

DROP FUNCTION ids_split(_ids VARCHAR, _pos INTEGER);

CREATE OR REPLACE FUNCTION ids_split(_ids VARCHAR, _pos INTEGER)
RETURNS INTEGER AS $$
  DECLARE
    _ret INTEGER;
    _len INTEGER;
  BEGIN

    SELECT CAST(d.ids_array[_pos] AS INTEGER)
         , ARRAY_LENGTH(d.ids_array, 1)
      INTO _ret
         , _len
      FROM (SELECT REGEXP_SPLIT_TO_ARRAY(_ids, ',') AS ids_array) AS d
         ;

    RETURN _ret;

  END;
$$ LANGUAGE 'plpgsql';

> select id, ids_split(sub_ids, 2), name from dummy where id = 2;
 id | ids_split | name
----+-----------+------
  2 |       201 | BBB
(1 行)

パラメータの設定、確認

confファイルで設定するパラメータの設定、確認をpsqlコマンドで行う方法です。
設定を行うにはsetコマンド、確認するにはshowコマンドを使用します。

全設定を表示する

> show all

client_encoding

> show client_encoding;
 client_encoding
-----------------
 SJIS
(1 行)

> set client_encoding = 'UTF8';
SET

deadlock_timeout (integer)

デッドロック状態があるかどうかを調べる前にロックを待つ時間をミリ秒で計算したものです。

> show deadlock_timeout;
 deadlock_timeout
------------------
 10s
(1 行)

log_lock_waits (boolean)

セッションがロックの獲得までの間にdeadlock_timeoutより長く待機する場合にログメッセージを生成するかどうかを制御します。

> show log_lock_waits;
 log_lock_waits
----------------
 on
(1 行)

log_statement (enum)

どのSQL文をログに記録するかを制御します。 有効な値は、none（off）、ddl、mod、およびall（全てのメッセージ）です。

> show log_statement;
 log_statement
---------------
 none
(1 行)

> show transaction isolation level;
 transaction_isolation
-----------------------
 read committed
(1 行)

log_min_duration_statement

トランザクションの分離レベルを確認、設定

> show transaction isolation level;
 transaction_isolation
-----------------------
 read committed
(1 行) 

> begin;
BEGIN
> set transaction isolation level repeatable read;
SET
> commit;
COMMIT

トランザクションが始まっていない状態だと、下記のワーニングが表示されます。

WARNING: SET TRANSACTIONはトランザクションブロック内でのみ使用できます

psql変数の確認、変更

setおよびechoメタコマンドを使用します。

設定されている変数を確認する

> \set
AUTOCOMMIT = 'on'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
VERBOSITY = 'default'
SHOW_CONTEXT = 'errors'
VERSION = 'PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit'
DBNAME = 'test_db'
USER = 'rubytomato'
HOST = 'localhost'
PORT = '5432'
ENCODING = 'SJIS'
LASTOID = '0'

autocommit

> \echo :AUTOCOMMIT;
on;

> \set AUTOCOMMIT off

ロックの確認

ロックの一覧を確認するには[pg_locks] (https://www.postgresql.jp/document/9.6/html/view-pg-locks.html)システムテーブルを参照する。

SELECT a.pid
     , a.datname
     , a.usename
     , a.query
     , a.query_start
     , now() - a.query_start  AS waiting_duration
     , a.state
     , l.pid
     , l.transactionid
     , l.locktype
     , l.mode
     , l.granted
     , l.fastpath
  FROM pg_stat_activity a
     , pg_locks l
 WHERE a.pid = l.pid
   AND l.locktype = 'transactionid'
   AND (a.datname = 'test_db' OR a.datname IS NULL)
   AND l.pid <> pg_backend_pid()
    ;

  pid  | datname |  usename   |                   query                    |          query_start          | waiting_duration |        state        |  pid  | transactionid |   locktype    |     mode      | granted | fastpath
-------+---------+------------+--------------------------------------------+-------------------------------+------------------+---------------------+-------+---------------+---------------+---------------+---------+----------
 12164 | test_db | rubytomato | select * from todo where id = 1 for share; | 2017-04-19 00:04:03.124715+09 | 00:46:46.588213  | idle in transaction | 12164 |           585 | transactionid | ExclusiveLock | t       | f
(1 行)

システム情報関数

pg_backend_pid()

現在のセッションに結びついたサーバプロセスのプロセスID

> select pg_backend_pid();
 pg_backend_pid
----------------
          14404
(1 行)
3
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?