The Boolean value is inserted into the variable field of the character in PostgreSQL

advertisements

PostgreSQL 9.1 will happily insert a boolean value into a varchar field. My expectation is that this would fail because I'm inserting data of the wrong type, and I'd like that to call out an error in my application logic. Is there any way to make this the default behavior, apart from making a trigger or constraint for every single varchar field?

CREATE TABLE
foo
(
    text_field CHARACTER VARYING(32)
);

Both these insert 'false' into text_field:

INSERT INTO foo (text_field) VALUES (FALSE);
INSERT INTO foo (text_field) VALUES (CAST('f' AS BOOLEAN));


This happens, because PostgreSQL will accept any expression (in an INSERT statement) with a type that can automatically converted to that column's type (more precisely, where an ASSIGNMENT or IMPLICIT cast exists between those two types: this is why these casts exists for the first place). F.ex:

-- lets see what casts exists to boolean & text
-- "castcontext" is 'a' for ASSIGNMENT, 'i' for IMPLICIT and 'e' for EXPLICIT casts
select castsource::regtype::text,
       casttarget::regtype::text,
       castcontext
from   pg_cast
where  casttarget in ('boolean'::regtype, 'text'::regtype);

create table foo (
  varchar_field varchar,
  text_field    text,
  bool_field    boolean
);

-- these are equivalents, because of some casts
insert into foo values ('false', 'false', 'false');
insert into foo values (false, false, false);
-- this will throw an error
insert into foo values (0, 0, 0);

Okay, I cheated a little. There is a special case. When you write a string literal (like 'false'), it has an unknown type. Unknown values always passed to the actual column type's input function. True text typed literals looks a little different: text 'false'. If you look again the default casts PostgreSQL provides, you can see that there is no cast from text to boolean. That's why this last statement will fail:

insert into foo values (text 'false', text 'false', text 'false');

SQLFiddle

Edit: So, there is an ASSIGNMENT cast from boolean to text by default (& there is one to character varying also). That's why your table's text_field accepts boolean input.