BOOLEAN datatype with PHP-compatible output
If you use pure PHP (no database abstraction layer) with PostgreSQL, you may run into the problem that your BOOLEAN columns are not recognized by PHP but the value is instead returned as a string. So any code like:
if (!$boolean)
always returns true because the 't' and 'f' are just strings in PHP.
Other programming languages like Perl, Python or Java and even the newer PHP PDO don't have this problem so it's clearly a PHP issue ... but i don't expect this one to be fixed, because this may break a lot existing applications. The column type information is available in the query result information so normally this should not be a big problem.
How to resolve this problem? There are some possibilities, one simple way would be to just use a SMALLINT instead a BOOLEAN but with the disadvantage that you loose the boolean input values. Another way is to create a new BOOLEAN type and change the output to something PHP-compatible. This new type is binary compatible with the existing BOOLEAN type so casts in either way are not a problem.
Note: i asked around, but it seems, there is no easy solution which does not require updates in the PHP or SQL code. Any workaround like explicit casts to integer are error-prone and you cannot easily find out, because your application will work, but in a wrong way.
--
-- create PHP compatible BOOLEAN type
--
-- drop the data type, if exist
-- this will drop all functions and drop all columns which use this type
DROP TYPE IF EXISTS boolean2 CASCADE;
-- input and output functions
-- we can use already existing internal functions
CREATE FUNCTION boolean2_in(cstring)
RETURNS boolean2
AS 'boolin'
LANGUAGE internal STRICT;
CREATE FUNCTION boolean2_out(boolean2)
RETURNS cstring
AS 'int2out'
LANGUAGE internal STRICT;
CREATE FUNCTION boolean2_recv(internal)
RETURNS boolean2
AS 'boolrecv'
LANGUAGE internal STRICT;
CREATE FUNCTION boolean2_send(boolean2)
RETURNS bytea
AS 'boolsend'
LANGUAGE internal STRICT;
-- create the data type
CREATE TYPE boolean2 (
input = boolean2_in,
output = boolean2_out,
receive = boolean2_recv,
send = boolean2_send,
internallength = 1,
alignment = char,
storage = plain,
passedbyvalue
);
COMMENT ON TYPE boolean2 IS 'boolean, ''1''/''0''';
-- since boolean2 is binary compatible with boolean, we can cast
-- in both ways without need for a supporting function
CREATE CAST (boolean2 AS boolean)
WITHOUT FUNCTION
AS ASSIGNMENT;
CREATE CAST (boolean AS boolean2)
WITHOUT FUNCTION
AS ASSIGNMENT;
-- create casting functions for integer versus boolean2
CREATE FUNCTION int4(boolean2)
RETURNS int4
AS 'bool_int4'
LANGUAGE internal STRICT;
CREATE FUNCTION boolean2(int4)
RETURNS boolean2
AS 'int4_bool'
LANGUAGE internal STRICT;
-- create the casts
CREATE CAST (boolean2 AS int4)
WITH FUNCTION int4(boolean2)
AS ASSIGNMENT;
CREATE CAST (int4 AS boolean2)
WITH FUNCTION boolean2(int4)
AS ASSIGNMENT;
-- we need some operators and supporting functions
CREATE FUNCTION boollt(boolean2, boolean)
RETURNS boolean
AS 'boollt'
LANGUAGE internal STRICT;
CREATE FUNCTION boollt(boolean, boolean2)
RETURNS boolean
AS 'boollt'
LANGUAGE internal STRICT;
CREATE FUNCTION boollt(boolean2, boolean2)
RETURNS boolean
AS 'boollt'
LANGUAGE internal STRICT;
CREATE OPERATOR < (
PROCEDURE = boollt,
LEFTARG = boolean2,
RIGHTARG = boolean,
COMMUTATOR = >,
NEGATOR = >=,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);
CREATE OPERATOR < (
PROCEDURE = boollt,
LEFTARG = boolean,
RIGHTARG = boolean2,
COMMUTATOR = >,
NEGATOR = >=,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);
CREATE OPERATOR < (
PROCEDURE = boollt,
LEFTARG = boolean2,
RIGHTARG = boolean2,
COMMUTATOR = >,
NEGATOR = >=,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);
CREATE FUNCTION boolle(boolean2, boolean)
RETURNS boolean
AS 'boolle'
LANGUAGE internal STRICT;
CREATE FUNCTION boolle(boolean, boolean2)
RETURNS boolean
AS 'boolle'
LANGUAGE internal STRICT;
CREATE FUNCTION boolle(boolean2, boolean2)
RETURNS boolean
AS 'boolle'
LANGUAGE internal STRICT;
CREATE OPERATOR <= (
PROCEDURE = boolle,
LEFTARG = boolean2,
RIGHTARG = boolean,
COMMUTATOR = >=,
NEGATOR = >,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);
CREATE OPERATOR <= (
PROCEDURE = boolle,
LEFTARG = boolean,
RIGHTARG = boolean2,
COMMUTATOR = >=,
NEGATOR = >,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);
CREATE OPERATOR <= (
PROCEDURE = boolle,
LEFTARG = boolean2,
RIGHTARG = boolean2,
COMMUTATOR = >=,
NEGATOR = >,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);
CREATE FUNCTION boolne(boolean2, boolean)
RETURNS boolean
AS 'boolne'
LANGUAGE internal STRICT;
CREATE FUNCTION boolne(boolean, boolean2)
RETURNS boolean
AS 'boolne'
LANGUAGE internal STRICT;
CREATE FUNCTION boolne(boolean2, boolean2)
RETURNS boolean
AS 'boolne'
LANGUAGE internal STRICT;
CREATE OPERATOR <> (
PROCEDURE = boolne,
LEFTARG = boolean2,
RIGHTARG = boolean,
COMMUTATOR = <>,
NEGATOR = =,
RESTRICT = neqsel,
JOIN = neqjoinsel
);
CREATE OPERATOR <> (
PROCEDURE = boolne,
LEFTARG = boolean,
RIGHTARG = boolean2,
COMMUTATOR = <>,
NEGATOR = =,
RESTRICT = neqsel,
JOIN = neqjoinsel
);
CREATE OPERATOR <> (
PROCEDURE = boolne,
LEFTARG = boolean2,
RIGHTARG = boolean2,
COMMUTATOR = <>,
NEGATOR = =,
RESTRICT = neqsel,
JOIN = neqjoinsel
);
CREATE FUNCTION booleq(boolean2, boolean)
RETURNS boolean
AS 'booleq'
LANGUAGE internal STRICT;
CREATE FUNCTION booleq(boolean, boolean2)
RETURNS boolean
AS 'booleq'
LANGUAGE internal STRICT;
CREATE FUNCTION booleq(boolean2, boolean2)
RETURNS boolean
AS 'booleq'
LANGUAGE internal STRICT;
CREATE OPERATOR = (
PROCEDURE = booleq,
LEFTARG = boolean2,
RIGHTARG = boolean,
COMMUTATOR = =,
NEGATOR = <>,
HASHES,
RESTRICT = eqsel,
JOIN = eqjoinsel,
SORT1 = <,
SORT2 = <,
LTCMP = <,
GTCMP = >
);
CREATE OPERATOR = (
PROCEDURE = booleq,
LEFTARG = boolean,
RIGHTARG = boolean2,
COMMUTATOR = =,
NEGATOR = <>,
HASHES,
RESTRICT = eqsel,
JOIN = eqjoinsel,
SORT1 = <,
SORT2 = <,
LTCMP = <,
GTCMP = >
);
CREATE OPERATOR = (
PROCEDURE = booleq,
LEFTARG = boolean2,
RIGHTARG = boolean2,
COMMUTATOR = =,
NEGATOR = <>,
HASHES,
RESTRICT = eqsel,
JOIN = eqjoinsel,
SORT1 = <,
SORT2 = <,
LTCMP = <,
GTCMP = >
);
CREATE FUNCTION boolgt(boolean2, boolean)
RETURNS boolean
AS 'boolgt'
LANGUAGE internal STRICT;
CREATE FUNCTION boolgt(boolean, boolean2)
RETURNS boolean
AS 'boolgt'
LANGUAGE internal STRICT;
CREATE FUNCTION boolgt(boolean2, boolean2)
RETURNS boolean
AS 'boolgt'
LANGUAGE internal STRICT;
CREATE OPERATOR > (
PROCEDURE = boolgt,
LEFTARG = boolean2,
RIGHTARG = boolean,
COMMUTATOR = <,
NEGATOR = <=,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);
CREATE OPERATOR > (
PROCEDURE = boolgt,
LEFTARG = boolean,
RIGHTARG = boolean2,
COMMUTATOR = <,
NEGATOR = <=,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);
CREATE OPERATOR > (
PROCEDURE = boolgt,
LEFTARG = boolean2,
RIGHTARG = boolean2,
COMMUTATOR = <,
NEGATOR = <=,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);
CREATE FUNCTION boolge(boolean2, boolean)
RETURNS boolean
AS 'boolge'
LANGUAGE internal STRICT;
CREATE FUNCTION boolge(boolean, boolean2)
RETURNS boolean
AS 'boolge'
LANGUAGE internal STRICT;
CREATE FUNCTION boolge(boolean2, boolean2)
RETURNS boolean
AS 'boolge'
LANGUAGE internal STRICT;
CREATE OPERATOR >= (
PROCEDURE = boolge,
LEFTARG = boolean2,
RIGHTARG = boolean,
COMMUTATOR = <=,
NEGATOR = <,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);
CREATE OPERATOR >= (
PROCEDURE = boolge,
LEFTARG = boolean,
RIGHTARG = boolean2,
COMMUTATOR = <=,
NEGATOR = <,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);
CREATE OPERATOR >= (
PROCEDURE = boolge,
LEFTARG = boolean2,
RIGHTARG = boolean2,
COMMUTATOR = <=,
NEGATOR = <,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);
-- end defining operators
-- create functions supporting operator classes
CREATE FUNCTION btboolcmp(boolean2, boolean)
RETURNS int4
AS 'btboolcmp'
LANGUAGE internal STRICT;
CREATE FUNCTION btboolcmp(boolean, boolean2)
RETURNS int4
AS 'btboolcmp'
LANGUAGE internal STRICT;
CREATE FUNCTION btboolcmp(boolean2, boolean2)
RETURNS int4
AS 'btboolcmp'
LANGUAGE internal STRICT;
-- create operator classes, needed for index support
CREATE OPERATOR CLASS _bool2_ops
DEFAULT FOR TYPE boolean2[] USING gin AS
STORAGE boolean2 ,
OPERATOR 1 &&(anyarray,anyarray) ,
OPERATOR 2 @>(anyarray,anyarray) ,
OPERATOR 3 <@(anyarray,anyarray) RECHECK ,
OPERATOR 4 =(anyarray,anyarray) RECHECK ,
FUNCTION 1 btboolcmp(boolean2,boolean2) ,
FUNCTION 2 ginarrayextract(anyarray,internal) ,
FUNCTION 3 ginarrayextract(anyarray,internal) ,
FUNCTION 4 ginarrayconsistent(internal,smallint,internal);
CREATE OPERATOR CLASS bool2_ops
DEFAULT FOR TYPE boolean2 USING btree AS
OPERATOR 1 <(boolean2,boolean2) ,
OPERATOR 2 <=(boolean2,boolean2) ,
OPERATOR 3 =(boolean2,boolean2) ,
OPERATOR 4 >=(boolean2,boolean2) ,
OPERATOR 5 >(boolean2,boolean2) ,
FUNCTION 1 btboolcmp(boolean2,boolean2);
CREATE OPERATOR CLASS bool2_ops
DEFAULT FOR TYPE boolean2 USING hash AS
OPERATOR 1 =(boolean2,boolean2) ,
FUNCTION 1 hashchar("char");
-- create a test case
DROP TABLE IF EXISTS boolean2_test;
CREATE TABLE boolean2_test (
id SERIAL NOT NULL PRIMARY KEY,
test boolean2
);
-- true, false, true, false, true, false
INSERT INTO boolean2_test (test) VALUES ('true');
INSERT INTO boolean2_test (test) VALUES ('false');
INSERT INTO boolean2_test (test) VALUES ('1');
INSERT INTO boolean2_test (test) VALUES ('0');
INSERT INTO boolean2_test (test) VALUES ('yes');
INSERT INTO boolean2_test (test) VALUES ('no');
INSERT INTO boolean2_test (test) VALUES ('y');
INSERT INTO boolean2_test (test) VALUES ('n');
-- this should fail
INSERT INTO boolean2_test (test) VALUES ('blub');
-- the output should be 0/1
SELECT * FROM boolean2_test ORDER BY id;
DROP TABLE IF EXISTS boolean2_test2;
CREATE TABLE boolean2_test2 (
id SERIAL NOT NULL
UNIQUE,
t1 INTEGER NOT NULL,
t2 BOOLEAN2 NOT NULL
DEFAULT FALSE
);
CREATE INDEX t1_t1 ON boolean2_test2(t1);
CREATE INDEX t1_t2 ON boolean2_test2(t2);
INSERT INTO boolean2_test2 (t1, t2) SELECT *, FALSE FROM generate_series(1,10000);
INSERT INTO boolean2_test2 (t1, t2) SELECT *, TRUE FROM generate_series(1,5000);
-- make sure, PG will try to use the index:
SET enable_seqscan=0;
VACUUM FULL ANALYZE boolean2_test2;
EXPLAIN SELECT COUNT(*) FROM boolean2_test2 WHERE t2='true';
EXPLAIN SELECT COUNT(*) FROM boolean2_test2 WHERE t2='false';
EXPLAIN SELECT COUNT(*) FROM boolean2_test2 WHERE t2='0';
EXPLAIN SELECT COUNT(*) FROM boolean2_test2 WHERE t2='1';
Comments
Display comments as Linear | Threaded
Richard Broersma Jr. on :