Skip to content

BOOLEAN datatype with PHP-compatible output

Posted by Andreas 'ads' Scherbaum on

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

Trackbacks

Postgres OnLine Journal on : The Anatomy of PostgreSQL - Part 2 - Database Objects

Show preview
In the first part of this series, we covered PostgreSQL Server object features. In this part, we shall demo the database and dissect the parts. Here we see a snapshot of what a standard PostgreSQL database looks like from a PgAdmin interface. Comments ()

www.pg-forum.de on : PingBack

Show preview
Comments ()

www.pg-forum.de on : PingBack

Show preview
Comments ()

www.pg-forum.de on : PingBack

Show preview
Comments ()

Comments

Display comments as Linear | Threaded

Richard Broersma Jr. on :

I've seen this error message when loading pages from this site: "Done, But with errors on page." Other than the error message, I see that the top portion of the page is completely painted over with this graphic: http://andreas.scherbaum.la/blog/templates/default/img/blank.gif
Comments ()

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options