PostgreSQL, SQLiteで共通のSQLを用いてboolean型カラムに値を投入したい時の方法。
例えば、テキストファイルにSQLを直接記述して、両方で実行できるようにしたい、という際に使える。
検証環境
- Windows 10
- PostgreSQL 9.3.2
- sqlite 3.8.11.1
PostgreSQL、SQLiteでのboolean型の扱い
PostgreSQLにはboolean型が明示的に存在する。
これには
true
, 'true'
, '1'
のようなリテラルを投入すると 真値が入る。
一方、SQLiteにはboolean型は無い。
booleanは整数値のように扱われ、整数値の0が偽値, 1が真値として扱われる。
数値型カラム(INTERGER affinity、またはNUMERIC affinityカラム)に文字列 '1' を渡すと整数値 1 が入る。
よって、以下のように '1'
をインサートすると、全く同じSQL文でどちらにも真値が投入できる。
INSERT INTO boolean_table (boolean_column) VALUES ('1');
実行結果
PostgreSQL 9.3.2:
test_db=# CREATE TABLE boolean_table (boolean_column boolean);
CREATE TABLE
test_db=# INSERT INTO boolean_table (boolean_column) VALUES ('0');
INSERT 0 1
test_db=# INSERT INTO boolean_table (boolean_column) VALUES ('1');
INSERT 0 1
test_db=#
test_db=# SELECT * FROM boolean_table;
boolean_column
----------------
f
t
(2 行)
SQLite (spatialite 4.3.0a / sqlite 3.8.11.1):
spatialite> CREATE TABLE boolean_table (boolean_column boolean); -- カラム名の後ろにbooleanと書くとNUMERIC affinityのカラムになる
spatialite> INSERT INTO boolean_table (boolean_column) VALUES ('0');
spatialite> INSERT INTO boolean_table (boolean_column) VALUES ('1');
spatialite> SELECT * FROM boolean_table;
boolean_column
0
1
spatialite>