I would like to do
select col1, col2 from foo union values (null, null)
but null is given the default type of TEXT, so I get the error "UNION types [e.g.] integer and text cannot be matched". In specific cases I can provide the types of the columns of foo, but I am constructing SQL statements programatically and it would be preferable if I didn't have to carry around the column type information with me.
Is there a workaround for this?
You can query INFORMATION_SCHEMA table COLUMNS using query like this:
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'mytable'
or you can use PostgreSQL specific form:
SELECT attname, atttypid::regtype
FROM pg_attribute
WHERE attrelid = 'public.mytable'::regclass
AND attnum > 0
This will give you data types for columns of interest in your table. Having this, in your automated framework you can generate UNION string to add empty row by casting NULLs to required data type, like this:
SELECT col1, col2 FROM foo
UNION ALL VALUES (NULL::VARCHAR, NULL::INTEGER)
Probably more important question is why do you want empty row? Perhaps you can get around without having this synthetic empty row in first place?
Just abuse an outer join like so:
select col1, col2 from foo
full join (select) as dummy on false
If col1 is of type bar and col2 is of type baz then
select col1, col2 from foo union values (null::bar, null::baz)
Will work
Actually, you can cast NULL to int, you just can't cast an empty string to int. Assuming you want NULL in the new column if data1 contains an empty string or NULL, you can do something like this:
UPDATE table SET data2 = cast(nullif(data1, '') AS int);
or
UPDATE table SET data2 = nullif(data1, '')::int;
