PostgreSQL – How to Avoid Implicit Type Casts

datatypespostgresqltype conversion

I just discovered that I can insert values of any type into a PostgreSQL (9.6) column of type text:

drop table if exists d cascade;
create table d ( a text );
insert into d values ( 42 );
insert into d values ( true );
select a, pg_typeof( a ) from d;

  a   | pg_typeof
------+-----------
 42   | text
 true | text
(2 rows)

Is this an intentional feature? Have I done something wrong? Is there a setting to avoid this? Doesn't this violate
the assumption that RDBMSs should be typesafe?

I know that text acts like a catch-all in PostgreSQL which is often convenient because you can then write
string representations of arbitrary types. But surely sometimes you want to make sure that only strings get
inserted into a given column, to the exclusion of implicitly cast values.

Is there anything I can do to avoid 'casual' type casts?

Best Answer

  • Is this an intentional feature?

    Yes, implicit type conversion is an intentional feature. There are pros and cons either way but this is how postgres is designed to work:

    In many cases a user does not need to understand the details of the type conversion mechanism. However, implicit conversions done by PostgreSQL can affect the results of a query. When necessary, these results can be tailored by using explicit type conversion

  • Doesn't this violate the assumption that RDBMSs should be typesafe?

    No, everything is still typesafe and "SQL is a strongly typed language". Implicit conversions don't change that.

  • Is there anything I can do to avoid 'casual' type casts?

    Short of messing around with the system catalog, which is usually a very bad idea (and impossible in some cases), there isn't a lot you can do to avoid implicit casting entirely. Even if you include explicit casts everywhere implicit casts might still occur by mistake:

    create table t(foo text);
insert into t(foo) values(11::integer);

    select * from t;

    | foo |
| :-- |
| 11  |

    dbfiddle here

Related Question