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.

  • 2
    Unrelated, but: Why the complicated select with a union? Why don't you simply use values (1,'key1','D1',25), (2,'key2','D2',365), (3,'key3','D3','2017-01-01')? – a_horse_with_no_name Nov 24 '17 at 15:22
  • You will get the same result even without insert, just with union of selects . – Abelisto Nov 24 '17 at 21:23
1

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.

  • You are contradicting yourself by first saying that the data types are determined by the first SELECT and then suggesting to resolve the issue by using string literals for the fourth columns in all SELECTs. The latter is correct, which means the former isn't. You will indeed get the same error if you try to run e.g. this: SELECT '1' UNION ALL SELECT 2 UNION ALL SELECT 'A'. So, it's not really the first SELECT that determines the data type for each UNIONed column. Rather, every SELECT is taken into account and the final type is probably determined based on some rules of type precedence. – Andriy M Nov 24 '17 at 22:33
  • @AndriyM thanks, you prompted me to learn more today :) – mustaccio Nov 24 '17 at 23:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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