How to avoid implicit type casts in PostgreSQL?

Question

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?

Answer 1

• 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

Answer 2

What @Jack said.
Plus, to be precise, the cast in your INSERT is "implicit" in a common sense:

insert into d values ( 42 );

But it's an assignment cast, not an implicit cast in Postgres terminology. That's an important distinction to make, implicit casts have far more applications. See:

• Generate series of dates - using date type as input

Assignment or implicit casts only happen based on ...

1. An entry in the system catalog pg_cast

with castcontext = 'a' or castcontext = 'i' respectively.

The complete list for your current installation:

SELECT casttarget::regtype AS target_type
     , castsource::regtype AS source_type
     , castcontext
FROM   pg_cast
WHERE  castcontext IN ('i', 'a')
ORDER  BY 1, 2;

2. Additional generic rules

The manual:

It should be noted that pg_cast does not represent every type conversion that the system knows how to perform; only those that cannot be deduced from some generic rule. For example, casting between a domain and its base type is not explicitly represented in pg_cast. Another important exception is that “automatic I/O conversion casts”, those performed using a data type's own I/O functions to convert to or from text or other string types, are not explicitly represented in pg_cast.

Bold emphasis mine.

You could theoretically mess with entries in pg_cast by changing 'i' or 'a' to 'e' ("explicit") - which can have far reaching consequences and is a bad idea unless you know exactly what you are doing - but not with automatic I/O conversion casts.

Related:

• changing float->numeric casts from assignment to implicit, dangerous?
• PostgreSQL parses string literal as record before calling cast

Casts from unknown

Quoting the manual about "Constants" once more:

There are three kinds of implicitly-typed constants in PostgreSQL: strings, bit strings, and numbers. Constants can also be specified with explicit types, which can enable more accurate representation and more efficient handling by the system.

More details in that chapter.

unknown-type literals are strings and can be cast using the above mentioned “automatic I/O conversion casts”. But Postgres needs to determine the target type before figuring out an appropriate cast. In plain assignment, the target type is obvious. In other cases, determining the target type can be tricky. There are extensive bodies of rules in:

• Operator Type Resolution
• Function Type Resolution