How do I add a null row in Postgres in a generic manner

Question

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?

Answer 1

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?

Answer 2

Just abuse an outer join like so:

select col1, col2 from foo 
full join (select) as dummy on false