Database

永井忠一 2024.8.31

インストール

PostgreSQL と SQLite を利用

Linux apt
`$ sudo apt install postgresql sqlite3`

Linux は、Ubuntu 24.04.1 LTS。PostgreSQL のサーバ管理は、systemctl コマンドで行う（「$ systemctl status postgresql」や「$ systemctl restart postgresql」など）

PostgreSQL をインストールすると、PostgreSQL のスーパユーザー postgres が作成されている。パスワードは未設定なので、postgres のパスワードを設定する

$ sudo passwd postgres
New password:

ユーザーの作成

$ su - postgres
Password:
$ whoami
postgres
$ createuser -d ユーザー名
$ exit
logout

（※『ユーザー名』には Linux と同じユーザー名を指定）

ここで、Linux のユーザーを PostgreSQL に追加している。また、-d オプションで、データベースを作成できる権限を付与

データベースの作成

$ createdb testdb

引数でデータベース名を省略した場合には、ユーザー名と同じデータベース名となる

ほかにも、データベースを削除する「dropdb」や、データベースユーザーを削除する「dropuser」などがある（これらは、実際には Perl で書かれたラッパースクリプト）

$ head -n 24 `which createuser`
#!/usr/bin/perl
# Call a PostgreSQL client program with the version, cluster and default
# database specified in ~/.postgresqlrc or
# /etc/postgresql-common/user_clusters.
#
# (C) 2005-2009 Martin Pitt <mpitt@debian.org>
# (C) 2013-2022 Christoph Berg <myon@debian.org>
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.

use warnings;
use strict;
use POSIX;
use PgCommon;

my ($version, $cluster);

（SQL の「CREATE USER」、「DROP USER」などの DDL に対するラッパースクリプトについて。詳細は「man createuser」など）

psql と sqlite3

SQL インタプリタ。PostgreSQL では、「$ psql -l」としてデータベースの一覧を取得できる。SQLite では、データベースはファイル

データベースへの接続、データベースのオープン

PostgreSQL	SQLite
`$ psql testdb psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.2)) Type "help" for help. testdb=> \q`	`$ sqlite3 test.db SQLite version 3.45.1 2024-01-30 16:01:20 Enter ".help" for usage hints. sqlite> .quit`

終了は、メタコマンドを使う。psql は、バックスラッシュコマンドの「\q」、sqlite3 は、ドットコマンドの「.quit」で終了

メタコマンドのヘルプは、「\?」と「.help」

PostgreSQL では、データベース名を省略するとユーザー名と同じデータベースへ接続する。SQLite では、データベース名を省略するとインメモリデータベースとなる

SQLite
`$ sqlite3 SQLite version 3.45.1 2024-01-30 16:01:20 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite>`

バージョンの確認

PostgreSQL SQLite

PostgreSQL	SQLite
`$ psql --version psql (PostgreSQL) 16.4 (Ubuntu 16.4-0ubuntu0.24.04.2) $ psql testdb psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.2)) Type "help" for help. testdb=> SELECT version(); version --------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 16.4 (Ubuntu 16.4-0ubuntu0.24.04.2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit (1 row)`	`$ sqlite3 --version 3.45.1 2024-01-30 16:01:20 e876e51a0ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1 (64-bit) $ sqlite3 test.db SQLite version 3.45.1 2024-01-30 16:01:20 Enter ".help" for usage hints. sqlite> SELECT sqlite_version(); 3.45.1`

$ psql --version
psql (PostgreSQL) 16.4 (Ubuntu 16.4-0ubuntu0.24.04.2)
$ psql testdb
psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.2))
Type "help" for help.

testdb=> SELECT version();
                                                             version
---------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.4 (Ubuntu 16.4-0ubuntu0.24.04.2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit
(1 row)

$ sqlite3 --version
3.45.1 2024-01-30 16:01:20 e876e51a0ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1 (64-bit)
$ sqlite3 test.db
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> SELECT sqlite_version();
3.45.1

出力結果のファイルへの出力

PostgreSQL SQLite

PostgreSQL	SQLite
`$ psql testdb psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.2)) Type "help" for help. testdb=> SELECT 1 + 2; ?column? ---------- 3 (1 row) testdb=> \o test.txt testdb=> SELECT 1 + 2; testdb=>`	`$ sqlite3 test.db SQLite version 3.45.1 2024-01-30 16:01:20 Enter ".help" for usage hints. sqlite> SELECT 1 + 2; 3 sqlite> .output test.txt sqlite> SELECT 1 + 2; sqlite>`
`$ cat test.txt ?column? ---------- 3 (1 row)`	`$ cat test.txt 3`

$ psql testdb
psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.2))
Type "help" for help.

testdb=> SELECT 1 + 2;
 ?column?
----------
        3
(1 row)

testdb=> \o test.txt
testdb=> SELECT 1 + 2;
testdb=>

$ sqlite3 test.db
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> SELECT 1 + 2;
3
sqlite> .output test.txt
sqlite> SELECT 1 + 2;
sqlite>

$ cat test.txt
 ?column?
----------
        3
(1 row)

$ cat test.txt
3

外部ファイルにある SQL 文の実行

PostgreSQL	SQLite
`$ cat test.sql SELECT 1 + 2;`
`testdb=> \i test.sql ?column? ---------- 3 (1 row)`	`sqlite> .read test.sql 3`

非対話的に実行

コマンド引数

PostgreSQL	SQLite
`$ psql testdb -c "SELECT 1 + 2" ?column? ---------- 3 (1 row)`	`$ sqlite3 testdb "SELECT 1 + 2" 3`

もしくは、パイプ

PostgreSQL	SQLite
`$ echo "SELECT 1 + 2" \| psql testdb ?column? ---------- 3 (1 row)`	`$ echo "SELECT 1 + 2" \| sqlite3 test.db 3`

これで、コマンドプログラムに SQL 文を与えることができる

データ型

PostgreSQL		SQLite
CHAR	文字	text	Type Affinity
CHAR(n)	固定長文字列
VARCHAR(n)	可変長文字列
text	制限のない可変長文字列
BOOLEAN	論理値	INTEGER
SMALLINT	符合付き２バイト整数
INTEGER	符合付き４バイト整数
bigint	符合付き８バイト整数
NUMERIC(p, s)	指定の任意の精度
NUMERIC(p, s)	指定の任意の精度	REAL
REAL	単精度浮動小数点数
DOUBLE PRECISION	倍精度浮動小数点数

PostgreSQL では、バックスラッシュコマンドの「\tT」で、型の説明を確認することができる

PostgreSQL

PostgreSQL
testdb=> \dT text List of data types Schema \| Name \| Description ------------+------+-------------------------------------------- pg_catalog \| text \| variable-length string, no limit specified (1 row) testdb=> \dT INTEGER List of data types Schema \| Name \| Description ------------+---------+------------------------------------------------- pg_catalog \| integer \| -2 billion to 2 billion integer, 4-byte storage (1 row) testdb=> \dT 'DOUBLE PRECISION' List of data types Schema \| Name \| Description ------------+------------------+-------------------------------------------------------- pg_catalog \| double precision \| double-precision floating point number, 8-byte storage (1 row)

testdb=> \dT text
                       List of data types
   Schema   | Name |                Description
------------+------+--------------------------------------------
 pg_catalog | text | variable-length string, no limit specified
(1 row)

testdb=> \dT INTEGER
                           List of data types
   Schema   |  Name   |                   Description
------------+---------+-------------------------------------------------
 pg_catalog | integer | -2 billion to 2 billion integer, 4-byte storage
(1 row)

testdb=> \dT 'DOUBLE PRECISION'
                                   List of data types
   Schema   |       Name       |                      Description
------------+------------------+--------------------------------------------------------
 pg_catalog | double precision | double-precision floating point number, 8-byte storage
(1 row)

PostgreSQL では pg_typeof() 関数、SQLite では typeof() 関数で型を確認することができる

PostgreSQL SQLite

PostgreSQL	SQLite
testdb=> SELECT '1', pg_typeof('1'), pg_typeof(CAST('1' AS text)); ?column? \| pg_typeof \| pg_typeof ----------+-----------+----------- 1 \| unknown \| text (1 row) testdb=> SELECT 1, pg_typeof(1); ?column? \| pg_typeof ----------+----------- 1 \| integer (1 row) testdb=> SELECT 1.0, pg_typeof(1.0), pg_typeof(CAST(1.0 AS REAL)); ?column? \| pg_typeof \| pg_typeof ----------+-----------+----------- 1.0 \| numeric \| real (1 row) testdb=> SELECT -9223372036854775808, pg_typeof(-9223372036854775808), 9223372036854775807, pg_typeof(9223372036854775807); ?column? \| pg_typeof \| ?column? \| pg_typeof ----------------------+-----------+---------------------+----------- -9223372036854775808 \| bigint \| 9223372036854775807 \| bigint (1 row)	`sqlite> SELECT '1', typeof('1'); 1\|text sqlite> SELECT 1, typeof(1); 1\|integer sqlite> SELECT 1.0, typeof(1.0); 1.0\|real sqlite> SELECT -9223372036854775808, typeof(-9223372036854775808), 9223372036854775807, typeof(9223372036854775807); -9223372036854775808\|integer\|9223372036854775807\|integer sqlite> SELECT -9223372036854775809, typeof(-9223372036854775809), 9223372036854775808, typeof(9223372036854775808); -9.22337203685478e+18\|real\|9.22337203685478e+18\|real`

testdb=> SELECT '1', pg_typeof('1'), pg_typeof(CAST('1' AS text));
 ?column? | pg_typeof | pg_typeof
----------+-----------+-----------
 1        | unknown   | text
(1 row)

testdb=> SELECT 1, pg_typeof(1);
 ?column? | pg_typeof
----------+-----------
        1 | integer
(1 row)

testdb=> SELECT 1.0, pg_typeof(1.0), pg_typeof(CAST(1.0 AS REAL));
 ?column? | pg_typeof | pg_typeof
----------+-----------+-----------
      1.0 | numeric   | real
(1 row)

testdb=> SELECT -9223372036854775808, pg_typeof(-9223372036854775808), 9223372036854775807, pg_typeof(9223372036854775807);
       ?column?       | pg_typeof |      ?column?       | pg_typeof
----------------------+-----------+---------------------+-----------
 -9223372036854775808 | bigint    | 9223372036854775807 | bigint
(1 row)

sqlite> SELECT '1', typeof('1');
1|text
sqlite> SELECT 1, typeof(1);
1|integer
sqlite> SELECT 1.0, typeof(1.0);
1.0|real
sqlite> SELECT -9223372036854775808, typeof(-9223372036854775808), 9223372036854775807, typeof(9223372036854775807);
-9223372036854775808|integer|9223372036854775807|integer
sqlite> SELECT -9223372036854775809, typeof(-9223372036854775809), 9223372036854775808, typeof(9223372036854775808);
-9.22337203685478e+18|real|9.22337203685478e+18|real

文字エンコーディングの確認

PostgreSQL	SQLite
`testdb=> \encoding UTF8 testdb=> SHOW CLIENT_ENCODING; client_encoding ----------------- UTF8 (1 row)`	`sqlite> PRAGMA encoding; UTF-8`

真偽値のリテラル。TRUE と FALSE

PostgreSQL	SQLite
`testdb=> SELECT TRUE, FALSE; ?column? \| ?column? ----------+---------- t \| f (1 row)`	`sqlite> SELECT TRUE, FALSE; 1\|0`

型変換

CAST() 関数による（明示的な）型変換

PostgreSQL	SQLite
`testdb=> SELECT pi(), CAST(pi() AS INTEGER); pi \| pi -------------------+---- 3.141592653589793 \| 3 (1 row)`	`sqlite> SELECT pi(), CAST(pi() AS INTEGER); 3.14159265358979\|3`

（pi() は、円周率を求める関数）

日付型と時間型

日付時刻型

PostgreSQL には、DATE 型、TIME 型、TIMESTAMP 型、INTERVAL 型がある（ぞれぞれ、日付、時刻、日付と時刻、時間間隔）
SQLite には日付、時刻のデータ型は存在せず、text 型として扱われる

serial 型と autoincrement

（順序数、自動増分整数）

PosttgreSQL には、独自のデータ型の serial 型と serial8 型がある（それぞれ、４バイト、８バイト長)

SQLite では、PRIMARY KEY 制約に「AUTOINCREMENT」属性を付与することができる（INTEGER 型である必要がある）

PostgreSQL SQLite

PostgreSQL	SQLite
`testdb=> \dt Did not find any relations. testdb=> CREATE TABLE test(id serial, long_id serial8, name TEXT); CREATE TABLE testdb=> INSERT INTO test(name) VALUES('a'), ('b'), ('c'); INSERT 0 3 testdb=> SELECT * FROM test; id \| long_id \| name ----+---------+------ 1 \| 1 \| a 2 \| 2 \| b 3 \| 3 \| c (3 rows)`	`sqlite> .tables sqlite> CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, name text); sqlite> INSERT INTO test(name) VALUES('a'), ('b'), ('c'); sqlite> SELECT * FROM test; 1\|a 2\|b 3\|c`

testdb=> \dt
Did not find any relations.
testdb=> CREATE TABLE test(id serial, long_id serial8, name TEXT);
CREATE TABLE
testdb=> INSERT INTO test(name) VALUES('a'), ('b'), ('c');
INSERT 0 3
testdb=> SELECT * FROM test;
 id | long_id | name
----+---------+------
  1 |       1 | a
  2 |       2 | b
  3 |       3 | c
(3 rows)

sqlite> .tables
sqlite> CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, name text);
sqlite> INSERT INTO test(name) VALUES('a'), ('b'), ('c');
sqlite> SELECT * FROM test;
1|a
2|b
3|c

PosttgreSQL の serial 型と serial8 型は、実際には SEQUENCE として実装されている

PostgreSQL

PostgreSQL
testdb=> \dt Did not find any relations. testdb=> \ds Did not find any relations. testdb=> CREATE TABLE test(id serial, long_id serial8, name TEXT); CREATE TABLE testdb=> \d test_id_seq Sequence "public.test_id_seq" Type \| Start \| Minimum \| Maximum \| Increment \| Cycles? \| Cache ---------+-------+---------+------------+-----------+---------+------- integer \| 1 \| 1 \| 2147483647 \| 1 \| no \| 1 Owned by: public.test.id testdb=> \d test_long_id_seq Sequence "public.test_long_id_seq" Type \| Start \| Minimum \| Maximum \| Increment \| Cycles? \| Cache --------+-------+---------+---------------------+-----------+---------+------- bigint \| 1 \| 1 \| 9223372036854775807 \| 1 \| no \| 1 Owned by: public.test.long_id testdb=> SELECT * FROM test_id_seq; last_value \| log_cnt \| is_called ------------+---------+----------- 1 \| 0 \| f (1 row) testdb=> SELECT * FROM test_long_id_seq; last_value \| log_cnt \| is_called ------------+---------+----------- 1 \| 0 \| f (1 row) testdb=> INSERT INTO test(name) VALUES('a'), ('b'), ('c'); INSERT 0 3 testdb=> SELECT * FROM test; id \| long_id \| name ----+---------+------ 1 \| 1 \| a 2 \| 2 \| b 3 \| 3 \| c (3 rows) testdb=> SELECT * FROM test_id_seq; last_value \| log_cnt \| is_called ------------+---------+----------- 3 \| 30 \| t (1 row) testdb=> SELECT * FROM test_long_id_seq; last_value \| log_cnt \| is_called ------------+---------+----------- 3 \| 30 \| t (1 row)

testdb=> \dt
Did not find any relations.
testdb=> \ds
Did not find any relations.
testdb=> CREATE TABLE test(id serial, long_id serial8, name TEXT);
CREATE TABLE
testdb=> \d test_id_seq
                    Sequence "public.test_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.test.id

testdb=> \d test_long_id_seq
                      Sequence "public.test_long_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.test.long_id

testdb=> SELECT * FROM test_id_seq;
 last_value | log_cnt | is_called
------------+---------+-----------
          1 |       0 | f
(1 row)

testdb=> SELECT * FROM test_long_id_seq;
 last_value | log_cnt | is_called
------------+---------+-----------
          1 |       0 | f
(1 row)

testdb=> INSERT INTO test(name) VALUES('a'), ('b'), ('c');
INSERT 0 3
testdb=> SELECT * FROM test;
 id | long_id | name
----+---------+------
  1 |       1 | a
  2 |       2 | b
  3 |       3 | c
(3 rows)

testdb=> SELECT * FROM test_id_seq;
 last_value | log_cnt | is_called
------------+---------+-----------
          3 |      30 | t
(1 row)

testdb=> SELECT * FROM test_long_id_seq;
 last_value | log_cnt | is_called
------------+---------+-----------
          3 |      30 | t
(1 row)

（バックスラッシュコマンドの「\ds」でシーケンスの一覧表示、「\d」コマンドで定義の確認）

SQLite には、sqlite_sequence という名前の、データベースが管理するシステムテーブルがある

PostgreSQL
`sqlite> .headers on sqlite> SELECT * FROM sqlite_sequence; name\|seq test\|3`

そのテーブルの seq カラムに、自動発番された最大の番号が書かれている

現在時刻の取得

PostgreSQL SQLite

PostgreSQL	SQLite
`testdb=> SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP; current_date \| current_time \| current_timestamp --------------+--------------------+------------------------------- 2024-09-08 \| 10:46:51.617097+09 \| 2024-09-08 10:46:51.617097+09 (1 row)`	`sqlite> SELECT date(CURRENT_DATE, 'localtime'), time(CURRENT_TIME, 'localtime'), datetime(CURRENT_TIMESTAMP, 'localtime'); 2024-09-08\|10:46:51\|2024-09-08 10:46:51`

testdb=> SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;
 current_date |    current_time    |       current_timestamp
--------------+--------------------+-------------------------------
 2024-09-08   | 10:46:51.617097+09 | 2024-09-08 10:46:51.617097+09
(1 row)

sqlite> SELECT date(CURRENT_DATE, 'localtime'), time(CURRENT_TIME, 'localtime'), datetime(CURRENT_TIMESTAMP, 'localtime');
2024-09-08|10:46:51|2024-09-08 10:46:51

（SQLite では、modifier に「'localtime'」を指定しないと UTC となる）

（SQLite では CURRENT_TIME が秒の精度なので、サブ秒の分解能のタイムスタンプを得るには「datetime('now', 'subsec')」とする）

date() 関数で、TIMESTAMP（日付と時刻）のデータから日付の部分を取り出すことができる

PostgreSQL	SQLite
`testdb=> SELECT date(CURRENT_TIMESTAMP); date ------------ 2024-09-08 (1 row)`	`sqlite> SELECT date(CURRENT_TIMESTAMP, 'localtime'); 2024-09-08`

PostgreSQL では、CAST() 関数で同様のことが行える

PostgreSQL
`testdb=> SELECT date(CURRENT_TIMESTAMP), CAST(CURRENT_TIMESTAMP AS DATE); date \| current_timestamp ------------+------------------- 2024-09-14 \| 2024-09-14 (1 row)`

時刻を取り出すには、PostgreSQL では CAST() 関数、SQLite では time() 関数を用いる

PostgreSQL	SQLite
`testdb=> SELECT CAST(CURRENT_TIME AS TIME); current_time ----------------- 09:44:35.146744 (1 row)`	`sqlite> SELECT time(CURRENT_TIME, 'localtime'); 09:44:35`

日時の計算

日付の切り捨て

PostgreSQL SQLite

PostgreSQL	SQLite
`testdb=> SELECT date_trunc('year', CURRENT_TIMESTAMP); date_trunc ------------------------ 2024-01-01 00:00:00+09 (1 row) testdb=> SELECT date_trunc('month', CURRENT_TIMESTAMP); date_trunc ------------------------ 2024-09-01 00:00:00+09 (1 row) testdb=> SELECT date_trunc('day', CURRENT_TIMESTAMP); date_trunc ------------------------ 2024-09-14 00:00:00+09 (1 row)`	`sqlite> SELECT datetime(CURRENT_TIMESTAMP, 'localtime', 'start of year'); 2024-01-01 00:00:00 sqlite> SELECT datetime(CURRENT_TIMESTAMP, 'localtime', 'start of month'); 2024-09-01 00:00:00 sqlite> SELECT datetime(CURRENT_TIMESTAMP, 'localtime', 'start of day'); 2024-09-14 00:00:00`

testdb=> SELECT date_trunc('year', CURRENT_TIMESTAMP);
       date_trunc
------------------------
 2024-01-01 00:00:00+09
(1 row)

testdb=> SELECT date_trunc('month', CURRENT_TIMESTAMP);
       date_trunc
------------------------
 2024-09-01 00:00:00+09
(1 row)

testdb=> SELECT date_trunc('day', CURRENT_TIMESTAMP);
       date_trunc
------------------------
 2024-09-14 00:00:00+09
(1 row)

sqlite> SELECT datetime(CURRENT_TIMESTAMP, 'localtime', 'start of year');
2024-01-01 00:00:00
sqlite> SELECT datetime(CURRENT_TIMESTAMP, 'localtime', 'start of month');
2024-09-01 00:00:00
sqlite> SELECT datetime(CURRENT_TIMESTAMP, 'localtime', 'start of day');
2024-09-14 00:00:00

PostgreSQL では、date_trunc() 関数を使う。SQLite では modifier に指定する（modifier 引数は、複数指定することができる）

PostgreSQL では、INTERVAL 型を使った時間間隔の演算を行うことができる。SQLite では、modifier を使う

年月日の足し引き

PostgreSQL SQLite

PostgreSQL	SQLite
testdb=> SELECT CURRENT_DATE + CAST('1 year' AS INTERVAL); ?column? --------------------- 2025-09-14 00:00:00 (1 row) testdb=> SELECT CURRENT_DATE - CAST('1 year' AS INTERVAL), CURRENT_DATE + CAST('-1 year' AS INTERVAL); ?column? \| ?column? ---------------------+--------------------- 2023-09-14 00:00:00 \| 2023-09-14 00:00:00 (1 row) testdb=> SELECT CURRENT_DATE + CAST('1 month' AS INTERVAL); ?column? --------------------- 2024-10-14 00:00:00 (1 row) testdb=> SELECT CURRENT_DATE - CAST('1 month' AS INTERVAL), CURRENT_DATE + CAST('-1 month' AS INTERVAL); ?column? \| ?column? ---------------------+--------------------- 2024-08-14 00:00:00 \| 2024-08-14 00:00:00 (1 row) testdb=> SELECT CURRENT_DATE + CAST('1 day' AS INTERVAL); ?column? --------------------- 2024-09-15 00:00:00 (1 row) testdb=> SELECT CURRENT_DATE - CAST('1 day' AS INTERVAL), CURRENT_DATE + CAST('-1 day' AS INTERVAL); ?column? \| ?column? ---------------------+--------------------- 2024-09-13 00:00:00 \| 2024-09-13 00:00:00 (1 row)	`sqlite> SELECT date(CURRENT_DATE, 'localtime', '1 years'); 2025-09-14 sqlite> SELECT date(CURRENT_DATE, 'localtime', '-1 years'); 2023-09-14 sqlite> SELECT date(CURRENT_DATE, 'localtime', '1 months'); 2024-10-14 sqlite> SELECT date(CURRENT_DATE, 'localtime', '-1 months'); 2024-08-14 sqlite> SELECT date(CURRENT_DATE, 'localtime', '1 days'); 2024-09-15 sqlite> SELECT date(CURRENT_DATE, 'localtime', '-1 days'); 2024-09-13`

testdb=> SELECT CURRENT_DATE + CAST('1 year' AS INTERVAL);
      ?column?
---------------------
 2025-09-14 00:00:00
(1 row)

testdb=> SELECT CURRENT_DATE - CAST('1 year' AS INTERVAL), CURRENT_DATE + CAST('-1 year' AS INTERVAL);
      ?column?       |      ?column?
---------------------+---------------------
 2023-09-14 00:00:00 | 2023-09-14 00:00:00
(1 row)

testdb=> SELECT CURRENT_DATE + CAST('1 month' AS INTERVAL);
      ?column?
---------------------
 2024-10-14 00:00:00
(1 row)

testdb=> SELECT CURRENT_DATE - CAST('1 month' AS INTERVAL), CURRENT_DATE + CAST('-1 month' AS INTERVAL);
      ?column?       |      ?column?
---------------------+---------------------
 2024-08-14 00:00:00 | 2024-08-14 00:00:00
(1 row)

testdb=> SELECT CURRENT_DATE + CAST('1 day' AS INTERVAL);
      ?column?
---------------------
 2024-09-15 00:00:00
(1 row)

testdb=> SELECT CURRENT_DATE - CAST('1 day' AS INTERVAL), CURRENT_DATE + CAST('-1 day' AS INTERVAL);
      ?column?       |      ?column?
---------------------+---------------------
 2024-09-13 00:00:00 | 2024-09-13 00:00:00
(1 row)

sqlite> SELECT date(CURRENT_DATE, 'localtime', '1 years');
2025-09-14
sqlite> SELECT date(CURRENT_DATE, 'localtime', '-1 years');
2023-09-14
sqlite> SELECT date(CURRENT_DATE, 'localtime', '1 months');
2024-10-14
sqlite> SELECT date(CURRENT_DATE, 'localtime', '-1 months');
2024-08-14
sqlite> SELECT date(CURRENT_DATE, 'localtime', '1 days');
2024-09-15
sqlite> SELECT date(CURRENT_DATE, 'localtime', '-1 days');
2024-09-13

時刻の足し引き

PostgreSQL SQLite

PostgreSQL	SQLite
testdb=> SELECT CURRENT_TIME + CAST('1 hour' AS INTERVAL); ?column? -------------------- 11:57:11.059204+09 (1 row) testdb=> SELECT CURRENT_TIME - CAST('1 hour' AS INTERVAL), CURRENT_TIME + CAST('-1 hour' AS INTERVAL); ?column? \| ?column? --------------------+-------------------- 09:57:21.679263+09 \| 09:57:21.679263+09 (1 row) testdb=> SELECT CURRENT_TIME + CAST('1 minute' AS INTERVAL); ?column? -------------------- 10:58:33.395657+09 (1 row) testdb=> SELECT CURRENT_TIME - CAST('1 minute' AS INTERVAL), CURRENT_TIME + CAST('-1 minute' AS INTERVAL); ?column? \| ?column? --------------------+-------------------- 10:56:45.039721+09 \| 10:56:45.039721+09 (1 row) testdb=> SELECT CURRENT_TIME + CAST('1 second' AS INTERVAL); ?column? -------------------- 10:57:57.060373+09 (1 row) testdb=> SELECT CURRENT_TIME - CAST('1 second' AS INTERVAL), CURRENT_TIME + CAST('-1 second' AS INTERVAL); ?column? \| ?column? --------------------+-------------------- 10:58:08.621706+09 \| 10:58:08.621706+09 (1 row)	`sqlite> SELECT time(CURRENT_TIME, 'localtime', '1 hours'); 11:57:16 sqlite> SELECT time(CURRENT_TIME, 'localtime', '-1 hours'); 09:57:27 sqlite> SELECT time(CURRENT_TIME, 'localtime', '1 minutes'); 10:58:38 sqlite> SELECT time(CURRENT_TIME, 'localtime', '-1 minutes'); 10:56:50 sqlite> SELECT time(CURRENT_TIME, 'localtime', '1 seconds'); 10:58:03 sqlite> SELECT time(CURRENT_TIME, 'localtime', '-1 seconds'); 10:58:14`

testdb=> SELECT CURRENT_TIME + CAST('1 hour' AS INTERVAL);
      ?column?
--------------------
 11:57:11.059204+09
(1 row)

testdb=> SELECT CURRENT_TIME - CAST('1 hour' AS INTERVAL), CURRENT_TIME + CAST('-1 hour' AS INTERVAL);
      ?column?      |      ?column?
--------------------+--------------------
 09:57:21.679263+09 | 09:57:21.679263+09
(1 row)

testdb=> SELECT CURRENT_TIME + CAST('1 minute' AS INTERVAL);
      ?column?
--------------------
 10:58:33.395657+09
(1 row)

testdb=> SELECT CURRENT_TIME - CAST('1 minute' AS INTERVAL), CURRENT_TIME + CAST('-1 minute' AS INTERVAL);
      ?column?      |      ?column?
--------------------+--------------------
 10:56:45.039721+09 | 10:56:45.039721+09
(1 row)

testdb=> SELECT CURRENT_TIME + CAST('1 second' AS INTERVAL);
      ?column?
--------------------
 10:57:57.060373+09
(1 row)

testdb=> SELECT CURRENT_TIME - CAST('1 second' AS INTERVAL), CURRENT_TIME + CAST('-1 second' AS INTERVAL);
      ?column?      |      ?column?
--------------------+--------------------
 10:58:08.621706+09 | 10:58:08.621706+09
(1 row)

sqlite> SELECT time(CURRENT_TIME, 'localtime', '1 hours');
11:57:16
sqlite> SELECT time(CURRENT_TIME, 'localtime', '-1 hours');
09:57:27
sqlite> SELECT time(CURRENT_TIME, 'localtime', '1 minutes');
10:58:38
sqlite> SELECT time(CURRENT_TIME, 'localtime', '-1 minutes');
10:56:50
sqlite> SELECT time(CURRENT_TIME, 'localtime', '1 seconds');
10:58:03
sqlite> SELECT time(CURRENT_TIME, 'localtime', '-1 seconds');
10:58:14

（ほかに、Postgres では、日付時刻から要素の値を取得する、標準 SQL の EXTRACT() 関数が用意されている。SQLite には表示形式を指定して取得する strftime() 関数が用意されている）

文字列処理

文字列を連結

PostgreSQL	SQLite
`testdb=> SELECT 'a'\|\|'bc', concat('a', 'bc'); ?column? \| concat ----------+-------- abc \| abc (1 row)`	`sqlite> SELECT 'a'\|\|'bc', concat('a', 'bc'); abc\|abc`

（SQL では、「||」演算子は文字列の連結）

文字列の長さを取得

PostgreSQL SQLite

PostgreSQL	SQLite
`testdb=> SELECT length('123'), CHAR_LENGTH('123'), CHARACTER_LENGTH('123'); length \| char_length \| character_length --------+-------------+------------------ 3 \| 3 \| 3 (1 row)`	`sqlite> SELECT length('123'); 3`

testdb=> SELECT length('123'), CHAR_LENGTH('123'), CHARACTER_LENGTH('123');
 length | char_length | character_length
--------+-------------+------------------
      3 |           3 |                3
(1 row)

sqlite> SELECT length('123');
3

文字列の検索

PostgreSQL	SQLite
`testdb=> SELECT POSITION('cd' IN 'abcdef'); position ---------- 3 (1 row)`	`sqlite> SELECT instr('abcdef', 'cd'); 3`

（PostgreSQL では、標準 SQL の POSITION() 関数が使える。SQLite では、instr() 関数を使う）

大文字、小文字に変換

PostgreSQL	SQLite
`testdb=> SELECT UPPER('abcD'), LOWER('ABCd'); upper \| lower -------+------- ABCD \| abcd (1 row)`	`sqlite> SELECT UPPER('abcD'), LOWER('ABCd'); ABCD\|abcd`

パディング

PostgreSQL

PostgreSQL
`testdb=> SELECT lpad('abc', 5, '_'), rpad('abc', 5, '_'), rpad(lpad('abc', 4, '_'), 5, '_') AS lrpad; lpad \| rpad \| lrpad -------+-------+------- __abc \| abc__ \| _abc_ (1 row) testdb=> SELECT lpad('abc', 4), rpad('abc', 4); lpad \| rpad ------+------ abc \| abc (1 row)`

testdb=> SELECT lpad('abc', 5, '_'), rpad('abc', 5, '_'), rpad(lpad('abc', 4, '_'), 5, '_') AS lrpad;
 lpad  | rpad  | lrpad
-------+-------+-------
 __abc | abc__ | _abc_
(1 row)

testdb=> SELECT lpad('abc', 4), rpad('abc', 4);
 lpad | rpad
------+------
  abc | abc
(1 row)

パディング文字を省略した場合にはスペースとなる

（SQLite には、lpar()、rpad() 関数が無い）

トリミング

PostgreSQL SQLite

PostgreSQL	SQLite
`testdb=> SELECT ltrim(' abc '), rtrim(' abc '), trim(' abc '); ltrim \| rtrim \| btrim -------+-------+------- abc \| abc \| abc (1 row) testdb=> SELECT ltrim('_abc_', '_'), rtrim('_abc_', '_'), trim('_abc_', '_'); ltrim \| rtrim \| btrim -------+-------+------- abc_ \| _abc \| abc (1 row)`	`sqlite> SELECT ltrim(' abc '), rtrim(' abc '), trim(' abc '); abc \| abc\|abc sqlite> SELECT ltrim('_abc_', '_'), rtrim('_abc_', '_'), trim('_abc_', '_'); abc_\|_abc\|abc`

testdb=> SELECT ltrim(' abc '), rtrim(' abc '), trim(' abc ');
 ltrim | rtrim | btrim
-------+-------+-------
 abc   |  abc  | abc
(1 row)

testdb=> SELECT ltrim('_abc_', '_'), rtrim('_abc_', '_'), trim('_abc_', '_');
 ltrim | rtrim | btrim
-------+-------+-------
 abc_  | _abc  | abc
(1 row)

sqlite> SELECT ltrim(' abc '), rtrim(' abc '), trim(' abc ');
abc | abc|abc
sqlite> SELECT ltrim('_abc_', '_'), rtrim('_abc_', '_'), trim('_abc_', '_');
abc_|_abc|abc

トリムする文字を指定することもできる

先頭の文字列、末尾の文字列を取り出す

PostgreSQL
`testdb=> SELECT left('abc123', 2), right('abc123', 2); left \| right ------+------- ab \| 23 (1 row)`

（SQLite には、left() 関数、right() 関数は無い）

文字列を繰り返す

PostgreSQL
`testdb=> SELECT repeat('abc', 3); repeat ----------- abcabcabc (1 row)`

（SQLite には、repeat() 関数は無い）

文字列の置換

PostgreSQL	SQLite
`testdb=> SELECT replace('123123', '123', 'abc'); replace --------- abcabc (1 row)`	`sqlite> SELECT replace('123123', '123', 'abc'); abcabc`

文字列の反転

PostgreSQL
`testdb=> SELECT reverse('123'); reverse --------- 321 (1 row)`

（SQLite には、reverse() 関数は無い）

文字列の切り出し

PostgreSQL	SQLite
`testdb=> SELECT SUBSTRING('12345', 2, 3); substring ----------- 234 (1 row)`	`sqlite> SELECT SUBSTRING('12345', 2, 3); 234`

文字列を、文字単位で置換

PostgreSQL
`testdb=> SELECT translate('112233', '123', 'abc'); translate ----------- aabbcc (1 row)`

（SQLite には、translate() 関数は無い）

テーブルの作成

PostgreSQL SQLite

PostgreSQL	SQLite
`testdb=> \dt Did not find any relations. testdb=> DROP TABLE IF EXISTS test; NOTICE: table "test" does not exist, skipping DROP TABLE testdb=> CREATE TABLE IF NOT EXISTS test(data INTEGER, comment TEXT); CREATE TABLE testdb=> \d test Table "public.test" Column \| Type \| Collation \| Nullable \| Default ---------+---------+-----------+----------+--------- data \| integer \| \| \| comment \| text \| \| \|`	`sqlite> .tables sqlite> DROP TABLE IF EXISTS test; sqlite> CREATE TABLE IF NOT EXISTS test(data INTEGER, comment TEXT); sqlite> .schema test CREATE TABLE test(data INTEGER, comment TEXT);`

testdb=> \dt
Did not find any relations.
testdb=> DROP TABLE IF EXISTS test;
NOTICE:  table "test" does not exist, skipping
DROP TABLE
testdb=> CREATE TABLE IF NOT EXISTS test(data INTEGER, comment TEXT);
CREATE TABLE
testdb=> \d test
                Table "public.test"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 data    | integer |           |          |
 comment | text    |           |          |

sqlite> .tables
sqlite> DROP TABLE IF EXISTS test;
sqlite> CREATE TABLE IF NOT EXISTS test(data INTEGER, comment TEXT);
sqlite> .schema test
CREATE TABLE test(data INTEGER, comment TEXT);

使用しているメタコマンドは、PostgreSQL はバックスラッシュコマンドの「\dt」と「\d」、SQLite では、ドットコマンドの「.tables」と「.schema」。それぞれ、テーブル一覧の表示と、テーブル定義の確認

PostgreSQL と SQLite とも、DDL に、DROP TABLE 文に「IF EXISTS」、CREATE TABLE 文に「IF NOT EXISTS」を付与することができる

レコードの追加

PostgreSQL SQLite

PostgreSQL	SQLite
`testdb=> INSERT INTO test VALUES(1, 'one'), (2, 'two'), (3, 'three'); INSERT 0 3 testdb=> SELECT COUNT() FROM test; count ------- 3 (1 row) testdb=> SELECT FROM test; data \| comment ------+--------- 1 \| one 2 \| two 3 \| three (3 rows)`	`sqlite> INSERT INTO test VALUES(1, 'one'), (2, 'two'), (3, 'three'); sqlite> SELECT COUNT() FROM test; 3 sqlite> SELECT FROM test; 1\|one 2\|two 3\|three`

testdb=> INSERT INTO test VALUES(1, 'one'), (2, 'two'), (3, 'three');
INSERT 0 3
testdb=> SELECT COUNT(*) FROM test;
 count
-------
     3
(1 row)

testdb=> SELECT * FROM test;
 data | comment
------+---------
    1 | one
    2 | two
    3 | three
(3 rows)

sqlite> INSERT INTO test VALUES(1, 'one'), (2, 'two'), (3, 'three');
sqlite> SELECT COUNT(*) FROM test;
3
sqlite> SELECT * FROM test;
1|one
2|two
3|three

PostgreSQL と SQLite とも、DML の INSERT 文の VALUES 句で、カンマで続けて複数レコードを１回の INSERT 文で追加することができる

CSV ファイル

エクスポートとインポート

PostgreSQL SQLite

PostgreSQL	SQLite
`testdb=> SELECT * FROM test; data \| comment ------+--------- 1 \| one 2 \| two 3 \| three (3 rows)` `testdb=> \copy test to ./test.csv with csv COPY 3` `testdb=> DELETE FROM test; DELETE 3 testdb=> SELECT COUNT() FROM test; count ------- 0 (1 row)` `testdb=> \copy test from ./test.csv with csv COPY 3` `testdb=> SELECT FROM test; data \| comment ------+--------- 1 \| one 2 \| two 3 \| three (3 rows)`	`sqlite> SELECT * FROM test; 1\|one 2\|two 3\|three` `sqlite> .mode csv sqlite> .output ./test.csv sqlite> SELECT * FROM test; sqlite> .output sqlite> .mode list` `sqlite> DELETE FROM test; sqlite> SELECT COUNT() FROM test; 0` `sqlite> .import ./test.csv test --csv` `sqlite> SELECT FROM test; 1\|one 2\|two 3\|three`

testdb=> SELECT * FROM test;
 data | comment
------+---------
    1 | one
    2 | two
    3 | three
(3 rows)

testdb=> \copy test to ./test.csv with csv
COPY 3

testdb=> DELETE FROM test;
DELETE 3
testdb=> SELECT COUNT(*) FROM test;
 count
-------
     0
(1 row)

testdb=> \copy test from ./test.csv with csv
COPY 3

testdb=> SELECT * FROM test;
 data | comment
------+---------
    1 | one
    2 | two
    3 | three
(3 rows)

sqlite> SELECT * FROM test;
1|one
2|two
3|three

sqlite> .mode csv
sqlite> .output ./test.csv
sqlite> SELECT * FROM test;
sqlite> .output
sqlite> .mode list

sqlite> DELETE FROM test;
sqlite> SELECT COUNT(*) FROM test;
0

sqlite> .import ./test.csv test --csv

sqlite> SELECT * FROM test;
1|one
2|two
3|three

PostgreSQL では、「\copy」コマンドで、テーブルの CSV ファイルへのエクスポートとインポートができる

SQLite では、「.mode」コマンドと「.output」コマンドとを使って、SELECT 文を発行して CSV ファイルにエクスポートを行う。インポートは「.import」コマンドが用意されている（「--csv」オプションが指定できる）

Perl と Python からの接続

データベースドライバのインストール

Linux apt
`$ sudo apt install libdbi-perl libdbd-pg-perl libdbd-sqlite3-perl python3-psycopg2`

Perl では DBI モジュールが利用できる。Python では DB-API が定義されている（Python には、SQLite が標準ライブラリに含まれている）

インストールした DBD のバージョン確認と、DBI のバージョン（Perl ワンライナー）

Perl DBI
`$ perl -MDBD::Pg -le 'print $DBD::Pg::VERSION' 3.18.0 $ perl -MDBD::SQLite -le 'print $DBD::SQLite::VERSION' 1.74 $ perl -MDBI -le 'print $DBI::VERSION' 1.643`

Python の SQLite と、psycopg2 のバージョンを確認（IPython を利用）

IPython

IPython
`$ ipython3 Python 3.12.3 (main, Jul 31 2024, 17:43:48) [GCC 13.2.0] Type 'copyright', 'credits' or 'license' for more information IPython 8.20.0 -- An enhanced Interactive Python. Type '?' for help. In [1]: import sqlite3 In [2]: sqlite3.sqlite_version Out[2]: '3.45.1' In [3]: import psycopg2 In [4]: psycopg2.__version__ Out[4]: '2.9.9 (dt dec pq3 ext lo64)'`

$ ipython3
Python 3.12.3 (main, Jul 31 2024, 17:43:48) [GCC 13.2.0]
Type 'copyright', 'credits' or 'license' for more information
IPython 8.20.0 -- An enhanced Interactive Python. Type '?' for help.

In [1]: import sqlite3

In [2]: sqlite3.sqlite_version
Out[2]: '3.45.1'

In [3]: import psycopg2

In [4]: psycopg2.__version__
Out[4]: '2.9.9 (dt dec pq3 ext lo64)'

接続確認

connect() 関数でデータベースへ接続

PostgreSQL SQLite

Perl

	PostgreSQL	SQLite
Perl	`use strict; use warnings; use DBI; my $dbh = DBI->connect("dbi:Pg:dbname=testdb") or die; my $sth = $dbh->prepare("SELECT version()") or die; $sth->execute() or die; my @row = $sth->fetchrow_array() or die; print($row[0] . "\n"); $sth->finish(); $dbh->disconnect();`	`use strict; use warnings; use DBI; my $dbh = DBI->connect("dbi:SQLite:dbname=test.db") or die; my $sth = $dbh->prepare("SELECT sqlite_version()") or die; $sth->execute() or die; my @row = $sth->fetchrow_array() or die; print($row[0] . "\n"); $sth->finish(); $dbh->disconnect();`
Python	`import psycopg2 con = psycopg2.connect(database='testdb') cur = con.cursor() cur.execute('SELECT version()') row = cur.fetchone() print(row[0]) cur.close() con.close()`	`import sqlite3 con = sqlite3.connect(database='test.db') cur = con.cursor() cur.execute('SELECT sqlite_version()') row = cur.fetchone() print(row[0]) cur.close() con.close()`

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=testdb") or die;

my $sth = $dbh->prepare("SELECT version()") or die;
$sth->execute() or die;
my @row = $sth->fetchrow_array() or die;
print($row[0] . "\n");

$sth->finish();
$dbh->disconnect();

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=test.db") or die;

my $sth = $dbh->prepare("SELECT sqlite_version()") or die;
$sth->execute() or die;
my @row = $sth->fetchrow_array() or die;
print($row[0] . "\n");

$sth->finish();
$dbh->disconnect();

Python

import psycopg2

con = psycopg2.connect(database='testdb')
cur = con.cursor()

cur.execute('SELECT version()')
row = cur.fetchone()
print(row[0])

cur.close()
con.close()

import sqlite3

con = sqlite3.connect(database='test.db')
cur = con.cursor()

cur.execute('SELECT sqlite_version()')
row = cur.fetchone()
print(row[0])

cur.close()
con.close()

SQL 文の発行には、Perl はステートメントハンドル、Python はカーソルオブジェクトを使う

データベースの準備

PostgreSQL SQLite

PostgreSQL	SQLite
`testdb=> DROP TABLE IF EXISTS test; DROP TABLE testdb=> CREATE TABLE IF NOT EXISTS test(data INTEGER, name text); CREATE TABLE`	`sqlite> DROP TABLE IF EXISTS test; sqlite> CREATE TABLE IF NOT EXISTS test(data INTEGER, name text);`

testdb=> DROP TABLE IF EXISTS test;
DROP TABLE
testdb=> CREATE TABLE IF NOT EXISTS test(data INTEGER, name text);
CREATE TABLE

sqlite> DROP TABLE IF EXISTS test;
sqlite> CREATE TABLE IF NOT EXISTS test(data INTEGER, name text);

テーブルへのレコード追加

PostgreSQL SQLite

Perl

	PostgreSQL	SQLite
Perl	`use strict; use warnings; use DBI; my $dbh = DBI->connect("dbi:Pg:dbname=testdb") or die; $dbh->{AutoCommit} = 0; my $sth = $dbh->prepare("INSERT INTO test(data, name) VALUES(?, ?)") or die; $sth->execute(1, "one") or die; $sth->execute(2, "two") or die; $sth->execute(3, "three") or die; $sth->finish(); $dbh->commit(); $dbh->disconnect();`	`use strict; use warnings; use DBI; my $dbh = DBI->connect("dbi:SQLite:dbname=test.db") or die; $dbh->{AutoCommit} = 0; my $sth = $dbh->prepare("INSERT INTO test(data, name) VALUES(?, ?)") or die; $sth->execute(1, "one") or die; $sth->execute(2, "two") or die; $sth->execute(3, "three") or die; $sth->finish(); $dbh->commit(); $dbh->disconnect();`
Python	`import psycopg2 con = psycopg2.connect(database='testdb') assert con.autocommit == False cur = con.cursor() statement = 'INSERT INTO test(data, name) VALUES(%s, %s)' cur.execute(statement, (1, 'one')) cur.execute(statement, (2, 'two')) cur.execute(statement, (3, 'three')) cur.close() con.commit() con.close()`	`import sqlite3 con = sqlite3.connect(database='test.db', autocommit=False) cur = con.cursor() statement = 'INSERT INTO test(data, name) VALUES(?, ?)' cur.execute(statement, (1, 'one')) cur.execute(statement, (2, 'two')) cur.execute(statement, (3, 'three')) cur.close() con.commit() con.close()`

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=testdb") or die;

$dbh->{AutoCommit} = 0;

my $sth = $dbh->prepare("INSERT INTO test(data, name) VALUES(?, ?)") or die;
$sth->execute(1, "one") or die;
$sth->execute(2, "two") or die;
$sth->execute(3, "three") or die;
$sth->finish();

$dbh->commit();
$dbh->disconnect();

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=test.db") or die;

$dbh->{AutoCommit} = 0;

my $sth = $dbh->prepare("INSERT INTO test(data, name) VALUES(?, ?)") or die;
$sth->execute(1, "one") or die;
$sth->execute(2, "two") or die;
$sth->execute(3, "three") or die;
$sth->finish();

$dbh->commit();
$dbh->disconnect();

Python

import psycopg2

con = psycopg2.connect(database='testdb')
assert con.autocommit == False
cur = con.cursor()
statement = 'INSERT INTO test(data, name) VALUES(%s, %s)'
cur.execute(statement, (1, 'one'))
cur.execute(statement, (2, 'two'))
cur.execute(statement, (3, 'three'))
cur.close()

con.commit()
con.close()

import sqlite3

con = sqlite3.connect(database='test.db', autocommit=False)

cur = con.cursor()
statement = 'INSERT INTO test(data, name) VALUES(?, ?)'
cur.execute(statement, (1, 'one'))
cur.execute(statement, (2, 'two'))
cur.execute(statement, (3, 'three'))
cur.close()

con.commit()
con.close()

この例では、オートコミットモードはオフ。トランザクションは暗黙的に開始され（BEGIN を発行していない）、明示的に commit() を呼び出してトランザクションを完了させている（ロールバックするためには、rollback() メソッドを実行する）

（参照したドキュメント）

Perl: Database Handle Attributes → AutoCommit（日本語訳）
Python: Transaction control via the autocommit attribute（日本語訳）

SQL 文にある「?」と「%s」は、プレースホルダー。Python では、SQL の VALUES 句の値は、タプルで execute() に与える

レコードの読み出し

PostgreSQL SQLite

Perl

	PostgreSQL	SQLite
Perl	`use strict; use warnings; use DBI; my $dbh = DBI->connect("dbi:Pg:dbname=testdb") or die; my $sth = $dbh->prepare("SELECT data, name FROM test") or die; $sth->execute() or die; while (my @row = $sth->fetchrow_array()) { die if not @row == 2; print("$row[0] $row[1]\n"); } $sth->finish(); $dbh->disconnect();`	`use strict; use warnings; use DBI; my $dbh = DBI->connect("dbi:SQLite:dbname=test.db") or die; my $sth = $dbh->prepare("SELECT data, name FROM test") or die; $sth->execute() or die; while (my @row = $sth->fetchrow_array()) { die if not @row == 2; print("$row[0] $row[1]\n"); } $sth->finish(); $dbh->disconnect();`
Python	`import psycopg2 con = psycopg2.connect(database='testdb') cur = con.cursor() cur.execute('SELECT data, name FROM test') for row in cur: assert len(row) == 2 print(row[0], row[1]) cur.close() con.close()`	`import sqlite3 con = sqlite3.connect(database='test.db') cur = con.cursor() cur.execute('SELECT data, name FROM test') for row in cur: assert len(row) == 2 print(row[0], row[1]) cur.close() con.close()`

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=testdb") or die;
my $sth = $dbh->prepare("SELECT data, name FROM test") or die;

$sth->execute() or die;
while (my @row = $sth->fetchrow_array()) {
    die if not @row == 2;
    print("$row[0] $row[1]\n");
}

$sth->finish();
$dbh->disconnect();

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=test.db") or die;
my $sth = $dbh->prepare("SELECT data, name FROM test") or die;

$sth->execute() or die;
while (my @row = $sth->fetchrow_array()) {
    die if not @row == 2;
    print("$row[0] $row[1]\n");
}

$sth->finish();
$dbh->disconnect();

Python

import psycopg2

con = psycopg2.connect(database='testdb')
cur = con.cursor()

cur.execute('SELECT data, name FROM test')
for row in cur:
    assert len(row) == 2
    print(row[0], row[1])

cur.close()
con.close()

import sqlite3

con = sqlite3.connect(database='test.db')
cur = con.cursor()

cur.execute('SELECT data, name FROM test')
for row in cur:
    assert len(row) == 2
    print(row[0], row[1])

cur.close()
con.close()

クエリーを発行して、結果セットをイテレートしてレコードを得る（レコードは、Perl では配列、Python ではタプルにバインドされる）

TIMESTAMP 型とのバインディング

（Perl と Python での、日付時刻の扱いについて）

PostgreSQL では、TIME ZONE がサポートされる

PostgreSQL

PostgreSQL
`testdb=> SHOW TIME ZONE; TimeZone ------------ Asia/Tokyo (1 row) testdb=> SET TIME ZONE 'UTC'; SET testdb=> SHOW TIME ZONE; TimeZone ---------- UTC (1 row) testdb=> SET TIME ZONE 'localtime'; SET testdb=> SHOW TIME ZONE; TimeZone ----------- localtime (1 row) testdb=> SET TIME ZONE 'Asia/Tokyo'; SET testdb=> SHOW TIME ZONE; TimeZone ------------ Asia/Tokyo (1 row)`

testdb=> SHOW TIME ZONE;
  TimeZone
------------
 Asia/Tokyo
(1 row)

testdb=> SET TIME ZONE 'UTC';
SET
testdb=> SHOW TIME ZONE;
 TimeZone
----------
 UTC
(1 row)

testdb=> SET TIME ZONE 'localtime';
SET
testdb=> SHOW TIME ZONE;
 TimeZone
-----------
 localtime
(1 row)

testdb=> SET TIME ZONE 'Asia/Tokyo';
SET
testdb=> SHOW TIME ZONE;
  TimeZone
------------
 Asia/Tokyo
(1 row)

（TIMESTAMP 型も、タイムゾーン付きの「TIMESTAMP WITH TIME ZONE」が利用できる）

SQLite では、TIMESTAMP 型はサポートされない（実際には text 型となる）

PostgreSQL	SQLite
`testdb=> SELECT pg_typeof(CURRENT_TIMESTAMP); pg_typeof -------------------------- timestamp with time zone (1 row)`	`sqlite> SELECT typeof(CURRENT_TIMESTAMP); text`

Perl モジュールのインストール

Linux apt
`$ sudo apt install libdatetime-perl libdatetime-hires-perl libdatetime-format-pg-perl libdatetime-format-sqlite-perl`

テストテーブル作成

PostgreSQL SQLite

testdb=> CREATE TABLE test(dbtime TIMESTAMP WITH TIME ZONE, p_time TIMESTAMP WITH TIME ZONE);
CREATE TABLE
testdb=> \d test
                        Table "public.test"
 Column |           Type           | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
 dbtime | timestamp with time zone |           |          |
 p_time | timestamp with time zone |           |          |

sqlite> CREATE TABLE test(dbtime TIMESTAMP, p_time TIMESTAMP);

データベースには、UTC 時間を格納する

PostgreSQL SQLite

Perl

use strict;
use warnings;
use DBI;
use DateTime;
use DateTime::Format::Pg;

my $dbh = DBI->connect("dbi:Pg:dbname=testdb", "", "", {AutoCommit => 0}) or die;
die if not $dbh->{AutoCommit} == 0;

my $sth = $dbh->prepare("INSERT INTO test(dbtime, p_time) VALUES(CURRENT_TIMESTAMP, ?)") or die;
$sth->execute(DateTime::Format::Pg->format_datetime(DateTime->now()));
$sth->finish();
$dbh->commit();

$sth = $dbh->prepare("SELECT dbtime, p_time FROM test ORDER BY p_time DESC LIMIT 1") or die;
$sth->execute() or die;

my @row = $sth->fetchrow_array() or die;

foreach my $col (@row) {
    $col = DateTime::Format::Pg->parse_datetime($col);
    die if not $col->time_zone =~ /^DateTime::TimeZone::OffsetOnly/; # adhoc
    $col->set_time_zone("local");
    print("$col\n");
}

$sth->finish();
$dbh->disconnect();

use strict;
use warnings;
use DBI;
use DateTime;
use DateTime::Format::SQLite;

my $dbh = DBI->connect("dbi:SQLite:dbname=test.db", "", "", {AutoCommit => 0}) or die;
die if not $dbh->{AutoCommit} == 0;

my $sth = $dbh->prepare("INSERT INTO test(dbtime, p_time) VALUES(CURRENT_TIMESTAMP, ?)") or die;
$sth->execute(DateTime::Format::SQLite->format_datetime(DateTime->now()));
$sth->finish();
$dbh->commit();

$sth = $dbh->prepare("SELECT dbtime, p_time FROM test ORDER BY p_time DESC LIMIT 1") or die;
$sth->execute() or die;

my @row = $sth->fetchrow_array() or die;

foreach my $col (@row) {
    $col = DateTime::Format::SQLite->parse_datetime($col);
    die if not $col->time_zone eq DateTime::TimeZone->new(name => "UTC");
    $col->set_time_zone("local");
    print("$col\n");
}

$sth->finish();
$dbh->disconnect();

Python

from datetime import datetime, timezone
import psycopg2

con = psycopg2.connect(database='testdb')
assert con.autocommit == False

cur = con.cursor()
cur.execute('INSERT INTO test(dbtime, p_time) VALUES(CURRENT_TIMESTAMP, %s)',
            (datetime.now().astimezone(timezone.utc), ))
con.commit()

cur.execute('SELECT dbtime, p_time FROM test ORDER BY p_time DESC LIMIT 1')
row = cur.fetchone()

for col in row:
    assert type(col) == datetime
    from datetime import timedelta
    assert col.tzinfo == timezone(timedelta(hours=9))
    print(col)

cur.close()
con.close()

from datetime import datetime, timezone
import sqlite3

con = sqlite3.connect(database='test.db', autocommit=False)
assert con.autocommit == False

cur = con.cursor()
cur.execute("INSERT INTO test(dbtime, p_time) VALUES(CURRENT_TIMESTAMP, ?)",
            (datetime.now().astimezone(timezone.utc).isoformat(), ))
con.commit()

cur.execute("""
SELECT datetime(dbtime, 'localtime')/* adhoc */, p_time FROM test ORDER BY p_time DESC LIMIT 1
""")
row = cur.fetchone()

for col in row:
    assert type(col) == str
    col = datetime.fromisoformat(col)
    assert (col.tzinfo == None # adhoc
            or col.tzinfo == timezone.utc)
    col = col.astimezone()
    print(col)

cur.close()
con.close()

参照したドキュメント

PostgreSQL
- 日付/時刻データ型
- SET TIME ZONE 構文
SQLite
- Date and Time Datatype
- Date And Time Functions
Perl
- DateTime::Format::Pg
- DateTime::Format::SQLite

トランザクション制御

PostgreSQL と SQLite ともに、BEGIN 文を発行して、明示的にトランザクションを開始する。トランザクションは、COMMIT か ROLLBACK のどちらかで終了する

また、「SAVEPOINT 名前」で名前を付けて、その時点まで「ROLLBACK TO 名前」でトランザクションを巻き戻すことができる

（PostgreSQL 、SQLite ともに、デフォルトは auto commit。COMMIT、ROLLBACK それから SAVEPOINT は、SQL の DCL）

transaction isolation level

（トランザクション分離レベル、隔離レベル）

PostgreSQL のデフォルトの isolation level は read committed

PostgreSQL
`testdb=> SHOW default_transaction_isolation; default_transaction_isolation ------------------------------- read committed (1 row)`

isolation level のデフォルトは「SET default_transaction_isolation = '分離レベル'」で変更することができる

また、トランザクションの中で「SET TRANSACTION ISOLATION LEVEL 分離レベル」を発行するとことで、トランザクションごとに isolation level を指定することができる。現在の isolation level は「SHOW TRANSACTION ISOLATION LEVEL」で確認することができる

PostgreSQL では、isolation level は、read committed と serializable がサポートされる

SQLite では、デフォルトの isolation level は serializable

ほかに、isolation level は read uncommitted がサポートされる。isolation level の read uncommitted への変更は、プラグマ「PRAGMA read_uncommitted = 1」で指定する

プラグマの設定値の確認は「PRAGMA プラグマ名」とする

SQLite
`$ sqlite3 test.db SQLite version 3.45.1 2024-01-30 16:01:20 Enter ".help" for usage hints. sqlite> PRAGMA read_uncommitted; 0`

（デフォルトは read_uncommitted = 0。0 で isolation level は serializable、1 で read uncommitted となる）

isolation level は、read committed で、phantom とnonrepeatable read が発生する可能性がある。read uncommitted では、それら加えて、さらに dirty read が発生しえる（serializable では、FULL ISOLATED となる）

（標準 SQL では、serializable がデフォルト。isolation level は、ほかに repeatable read が定義されている）

明示的なロックの取得

（手動ロック）

PostgreSQL では、SELECT 文に「FOR UPDATE」を付与して、選択したレコードに対してロックを取ることができる（行ロック）。また、トランザクションの中で「LOCK TABLE」文を発行することができる（テーブルロック）

SQLite はそのどちらも使用することができない（SQLite では、ロックの粒度はデータベース）

ロックのタイムアウト

PostgreSQL では、lock_timeout のデフォルトは 0 （タイムアウトしない。発行した SQL 文はブロックされ続ける）

PostgreSQL
`testdb=> SHOW lock_timeout; lock_timeout -------------- 1s (1 row)`

タイムアウト時間を「SET lock_timeout = ミリ秒」で指定することができる

SQLite では、ロックが取れない場合、デフォルトの動作では即座にエラーとなる。変更するためには、ドットコマンドの「.timeout」でミリ秒を指定する

sqlite> .help timeout
.timeout MS              Try opening locked tables for MS milliseconds

デッドロック

PostgreSQL では、デッドロックの可能性をシステムがチェックする。デッドロックが検出された場合、待ち状態のトランザクションをロールバックすることで、デッドロックを解消する

（SQLite では、ロックの粒度はデータベース。細粒度のロックを取ることができない）

管理

不要になったテーブル領域の開放

PostgreSQL SQLite

$ su - postgres
Password:
$ whoami
postgres
$ psql testdb
psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.2))
Type "help" for help.

testdb=# VACUUM;
VACUUM

$ sqlite3 test.db
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> VACUUM;

（PostgreSQL では、権限が必要なため、ユーザー「postgres」で VACUUM コマンドを発行する）

バックアップ

PostgreSQL では、外部コマンドの pg_dump コマンドを使う。SQLite では、ドットコマンドの .dump コマンドを使用する

テーブルを指定してダンプ

PostgreSQL では、「$ pg_dump --inserts データベース名 -t テーブル名」（オプション「--inserts」を指定しないと、INSERT 文ではなく、COPY 文（独自のバルクロード）が生成される）
SQLite では、「sqlite> .dump テーブル名」

テーブル名を指定しなければ、データベース全てが対象になる

pg_dump には「--schema-only」オプションがあり、テーブル定義の出力のみとなる

ダンプ結果をファイルに書き出すには、pg_dump では「-f」オプションを使うか、もしくはリダイレクト。SQLite では、ドットコマンドの「.output」を使用する

デフォルト値

CREATE TABLE 時に、列に DEFAULT 値を指定

PostgreSQL SQLite

testdb=> CREATE TABLE position(x REAL, y REAL, z REAL DEFAULT 0.0);
CREATE TABLE
testdb=> INSERT INTO position(x, y) VALUES (1.1, 1.2);
INSERT 0 1
testdb=> SELECT * FROM position;
  x  |  y  | z
-----+-----+---
 1.1 | 1.2 | 0
(1 row)

sqlite> CREATE TABLE position(x REAL, y REAL, z REAL DEFAULT 0.0);
sqlite> INSERT INTO position(x, y) VALUES (1.1, 1.2);
sqlite> SELECT * FROM position;
1.1|1.2|0.0

PostgreSQL では、ALTER TABLE 文で、テーブル作成後に変更することができる

PostgreSQL

testdb=> \d position
            Table "public.position"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 x      | real |           |          |
 y      | real |           |          |
 z      | real |           |          | 0.0

testdb=> ALTER TABLE position ALTER COLUMN z DROP DEFAULT;
ALTER TABLE
testdb=> \d position
            Table "public.position"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 x      | real |           |          |
 y      | real |           |          |
 z      | real |           |          |

testdb=> ALTER TABLE position ALTER COLUMN z SET DEFAULT 0.0;
ALTER TABLE
testdb=> \d position
            Table "public.position"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 x      | real |           |          |
 y      | real |           |          |
 z      | real |           |          | 0.0

ALTER COLUMN 句で、列を指定して「SET DEFAULT」、「DROP DEFAULT」できる

制約(CONSTRAINT)

PostgreSQL と SQLite とも、制約をサポートする

NOT NULL 制約
UNIQUE 制約
PRIMARY KEY(主キー) 制約
CHECK 制約
FOREING KEY(外部キー) 制約

ただし、SQLite では、FOREING KEY 制約の、参照整合性のチェックが無効になっているので、プラグマで有効にする必要がある

SQLite
`sqlite> PRAGMA foreign_keys; 0 sqlite> PRAGMA foreign_keys = ON;`

NOT NULL 制約と UNIQUE 制約を同時に指定した例

PostgreSQL SQLite

testdb=> CREATE TABLE test(name text NOT NULL UNIQUE);
CREATE TABLE
testdb=> \d test
              Table "public.test"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 name   | text |           | not null |
Indexes:
    "test_name_key" UNIQUE CONSTRAINT, btree (name)

testdb=> INSERT INTO test(name) VALUES(NULL);
ERROR:  null value in column "name" of relation "test" violates not-null constraint
DETAIL:  Failing row contains (null).
testdb=> INSERT INTO test(name) VALUES('aa');
INSERT 0 1
testdb=> INSERT INTO test(name) VALUES('aa');
ERROR:  duplicate key value violates unique constraint "test_name_key"
DETAIL:  Key (name)=(aa) already exists.

sqlite> CREATE TABLE test(name text NOT NULL UNIQUE);
sqlite> .schema test
CREATE TABLE test(name text NOT NULL UNIQUE);
sqlite> INSERT INTO test(name) VALUES(NULL);
Runtime error: NOT NULL constraint failed: test.name (19)
sqlite> INSERT INTO test(name) VALUES('aa');
sqlite> INSERT INTO test(name) VALUES('aa');
Runtime error: UNIQUE constraint failed: test.name (19)

制約に違反したレコードは、システムにチェックされエラーとなり、追加することができない

複合キーの例（複数列に対する PRIMARY KEY）

PostgreSQL SQLite

testdb=> CREATE TABLE test(x REAL, y REAL, z REAL, PRIMARY KEY(x, y, z));
CREATE TABLE
testdb=> \d test
              Table "public.test"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 x      | real |           | not null |
 y      | real |           | not null |
 z      | real |           | not null |
Indexes:
    "test_pkey" PRIMARY KEY, btree (x, y, z)

testdb=> INSERT INTO test(x, y, z) VALUES(1.0, 1.0, 0.0);
INSERT 0 1
testdb=> INSERT INTO test(x, y, z) VALUES(1.1, 1.0, 0.0);
INSERT 0 1
testdb=> INSERT INTO test(x, y, z) VALUES(1.1, 1.0, 0.0);
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (x, y, z)=(1.1, 1, 0) already exists.

sqlite> CREATE TABLE test(x REAL, y REAL, z REAL, PRIMARY KEY(x, y, z));
sqlite> .schema test
CREATE TABLE test(x REAL, y REAL, z REAL, PRIMARY KEY(x, y, z));
sqlite> INSERT INTO test(x, y, z) VALUES(1.0, 1.0, 0.0);
sqlite> INSERT INTO test(x, y, z) VALUES(1.1, 1.0, 0.0);
sqlite> INSERT INTO test(x, y, z) VALUES(1.1, 1.0, 0.0);
Runtime error: UNIQUE constraint failed: test.x, test.y, test.z (19)

（CREATE TABLE 文で、PRIMARY KEY を別に書く必要がある）

CHECK 制約の例

PostgreSQL SQLite

testdb=> CREATE TABLE test(direction text, CHECK(direction IN ('up', 'down', 'left', 'right')));
CREATE TABLE
testdb=> \d test
                Table "public.test"
  Column   | Type | Collation | Nullable | Default
-----------+------+-----------+----------+---------
 direction | text |           |          |
Check constraints:
    "test_direction_check" CHECK (direction = ANY (ARRAY['up'::text, 'down'::text, 'left'::text, 'right'::text]))

testdb=> INSERT INTO test VALUES('center');
ERROR:  new row for relation "test" violates check constraint "test_direction_check"
DETAIL:  Failing row contains (center).

sqlite> CREATE TABLE test(direction text, CHECK(direction IN ('up', 'down', 'left', 'right')));
sqlite> .schema test
CREATE TABLE test(direction text, CHECK(direction IN ('up', 'down', 'left', 'right')));
sqlite> INSERT INTO test VALUES('center');
Runtime error: CHECK constraint failed: direction IN ('up', 'down', 'left', 'right') (19)

FOREING KEY 制約の例

PostgreSQL SQLite

testdb=> CREATE TABLE direction(id INTEGER PRIMARY KEY, name text);
CREATE TABLE
testdb=> INSERT INTO direction(id, name) VALUES(1, 'up'), (2, 'down'), (3, 'left'), (4, 'right');
INSERT 0 4
testdb=> SELECT * FROM direction;
 id | name
----+-------
  1 | up
  2 | down
  3 | left
  4 | right
(4 rows)

testdb=> CREATE TABLE input(direction_id INTEGER, FOREIGN KEY(direction_id) REFERENCES direction(id));
CREATE TABLE
testdb=> INSERT INTO input(direction_id) VALUES(0);
ERROR:  insert or update on table "input" violates foreign key constraint "input_direction_id_fkey"
DETAIL:  Key (direction_id)=(0) is not present in table "direction".
testdb=> INSERT INTO input(direction_id) VALUES(1);
INSERT 0 1
testdb=> DELETE FROM direction WHERE id = 1;
ERROR:  update or delete on table "direction" violates foreign key constraint "input_direction_id_fkey" on table "input"
DETAIL:  Key (id)=(1) is still referenced from table "input".

sqlite> CREATE TABLE direction(id INTEGER PRIMARY KEY, name text);
sqlite> INSERT INTO direction(id, name) VALUES(1, 'up'), (2, 'down'), (3, 'left'), (4, 'right');
sqlite> SELECT * FROM direction;
1|up
2|down
3|left
4|right
sqlite> CREATE TABLE input(direction_id INTEGER, FOREIGN KEY(direction_id) REFERENCES direction(id));
sqlite> PRAGMA foreign_keys = ON;
sqlite> INSERT INTO input(direction_id) VALUES(0);
Runtime error: FOREIGN KEY constraint failed (19)
sqlite> INSERT INTO input(direction_id) VALUES(1);
sqlite> DELETE FROM direction WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed (19)

（※SQLite では、「PRAGMA foreign_keys = ON」を忘れない）

また、参照整合性を保つために、「ON UPDATE CASCADE」と「ON DELETE CASCADE」が指定できる

PostgreSQL SQLite

testdb=> CREATE TABLE direction(id INTEGER PRIMARY KEY, name text);
CREATE TABLE
testdb=> INSERT INTO direction(id, name) VALUES(1, 'up'), (2, 'down'), (3, 'left'), (4, 'right');
INSERT 0 4
testdb=> CREATE TABLE input(direction_id INTEGER,
testdb(> FOREIGN KEY(direction_id) REFERENCES direction(id) ON UPDATE CASCADE ON DELETE CASCADE);
CREATE TABLE
testdb=> INSERT INTO input(direction_id) VALUES(1);
INSERT 0 1
testdb=> SELECT * FROM input;
 direction_id
--------------
            1
(1 row)

testdb=> UPDATE direction SET id = 0 WHERE id = 1;
UPDATE 1
testdb=> SELECT * FROM input;
 direction_id
--------------
            0
(1 row)

testdb=> DELETE FROM direction WHERE id = 0;
DELETE 1
testdb=> SELECT * FROM input;
 direction_id
--------------
(0 rows)

sqlite> PRAGMA foreign_keys;
1
sqlite> CREATE TABLE direction(id INTEGER PRIMARY KEY, name text);
sqlite> INSERT INTO direction(id, name) VALUES(1, 'up'), (2, 'down'), (3, 'left'), (4, 'right');
sqlite> CREATE TABLE input(direction_id INTEGER,
(x1...> FOREIGN KEY(direction_id) REFERENCES direction(id) ON UPDATE CASCADE ON DELETE CASCADE);
sqlite> INSERT INTO input(direction_id) VALUES(1);
sqlite> SELECT * FROM input;
1
sqlite> UPDATE direction SET id = 0 WHERE id = 1;
sqlite> SELECT * FROM input;
0
sqlite> DELETE FROM direction WHERE id = 0;
sqlite> SELECT * FROM input;

参照しているテーブルがテーブルメンテナンスされたとき、参照もとのレコードに対して、それぞれ「UPDATE」、「DELETE」の処理がシステムにより行なわれる

ただし、DROP TABLE によるレコード削除が発生したときの扱いは、PostgreSQL と SQLite とで異なる。PostgreSQL の場合

PostgreSQL

testdb=> CREATE TABLE direction(id INTEGER PRIMARY KEY, name text);
CREATE TABLE
testdb=> INSERT INTO direction(id, name) VALUES(1, 'up'), (2, 'down'), (3, 'left'), (4, 'right');
INSERT 0 4
testdb=> CREATE TABLE input(direction_id INTEGER,
testdb(> FOREIGN KEY(direction_id) REFERENCES direction(id) ON UPDATE CASCADE ON DELETE CASCADE);
CREATE TABLE
testdb=> INSERT INTO input(direction_id) VALUES(1);
INSERT 0 1
testdb=> DROP TABLE direction;
ERROR:  cannot drop table direction because other objects depend on it
DETAIL:  constraint input_direction_id_fkey on table input depends on table direction
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
testdb=> \d input;
                  Table "public.input"
    Column    |  Type   | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
 direction_id | integer |           |          |
Foreign-key constraints:
    "input_direction_id_fkey" FOREIGN KEY (direction_id) REFERENCES direction(id) ON UPDATE CASCADE ON DELETE CASCADE

testdb=> DROP TABLE direction CASCADE;
NOTICE:  drop cascades to constraint input_direction_id_fkey on table input
DROP TABLE
testdb=> \d input;
                  Table "public.input"
    Column    |  Type   | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
 direction_id | integer |           |          |

testdb=> SELECT * FROM input;
 direction_id
--------------
            1
(1 row)

（SQLite は、DROP TABLE によるレコード削除も、DELETE 文によるレコード削除と同じ様に扱う）

（また、PostgreSQL では、ALTER TABLE 文で、テーブル作成後に制約の追加と削除を行うことができる）

インデックス

... ToDo ...

ビュー

... ToDo ...

Database

インストール

データベースの作成

psql と sqlite3

バージョンの確認

出力結果のファイルへの出力

外部ファイルにある SQL 文の実行

非対話的に実行

データ型

型変換

日付型と時間型

serial 型と autoincrement

現在時刻の取得

日時の計算

文字列 処理

テーブルの作成

CSV ファイル

Perl と Python からの接続

接続確認

TIMESTAMP 型とのバインディング

参照したドキュメント

トランザクション制御

transaction isolation level

明示的なロックの取得

ロックのタイムアウト

デッドロック

管理

バックアップ

デフォルト値

制約(CONSTRAINT)

インデックス

ビュー

文字列処理