Postgresql: invalid input syntax for integer: “false”

postgresql

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.

Best Answer

The UNION result set data types will be determined by logic that favours values that are not string literals -- it is described in detail in the manual, also note that, without 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.

...

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 (in your case fourth) column in your UNIONised query, Postgres will attempt to coerce the values in the same column in other SELECTs into an 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.