0

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.

0

1 Answer 1

2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.