Postgresql: invalid input syntax for integer: "false"

Question

Below is my table structure

CREATE TABLE configurations
(
  id serial NOT NULL,
  key text,
  description text,
  value text,
  CONSTRAINT configurations_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

I am inserting data with below query

insert into configurations
(id, key, description, value)
select 1,'key1','D1',25
UNION
select 2,'key2','D2',365
UNION
select 3,'key3','D3','2017-01-01'

But i am getting below error

ERROR:  invalid input syntax for integer: "2017-01-01"
LINE 7: select 3,'key3','D3','2017-01-01'

I don't know why i am getting this error. value "2017-01-01" is a text value and column data type is also text then why i am getting integer specific error.

Answer 1

The UNION result set data types will be determined by the logic that favours values that are not string literals -- it is described in detail in the manual, also note that, without an explicit type specification, string literals are treated as being of type unknown.

Specifically, these rules apply in your case:

5. Choose the first non-unknown input type which is a preferred type in that category, if there is one.

and

7. Convert all inputs to the selected type. Fail if there is not a conversion from a given input to the selected type.

This basically means that if you have at least one numeric literal value in a particular column (in your case fourth) in your UNIONised query, Postgres will attempt to coerce the values in the same column in other SELECTs into a number, which obviously fails for the character value '2017-01-01'.

Subsequently, if you have at least one character value in that column that cannot be cast into a number, you will have to use explicit character values for the fourth column in all SELECTs:

insert into configurations
(id, key, description, value)
select 1,'key1','D1', '25'
UNION
select 2,'key2','D2', '365'
UNION
select 3,'key3','D3','2017-01-01'

H/T to Andriy M for his comment that prompted me to read the manual more thoroughly.