Postgres returns the default value when the column does not exist

Question

I have a query in which I really want to return a value if a specific column is missing. I was wondering if I could handle this purely in my query (instead of examining and sending a separate query first. In essence, I'm looking for the COALESCE equivalent that handles the case of a missing column.

Imagine the following 2 tables.

 T1 id | title | extra 1 A | value - and - T2 id | title 1 A 

I would like to be able to CHOOSE from any of these tables with the same question.

for example, if t2 really had an “extra” column, I could use

  SELECT id,title, COALESCE(extra, 'default') as extra 

But this only works if the column value is NULL, and not in the complete absence of the column.

I would prefer the SQL version, but I can accept the PLPGSQL function (with behavior similar to COALLESCE).

NOTE for SQL purists: I don’t really like to discuss why I want to do this in SQL and not in the application logic (or why I won’t just add a column to the schema), so please limit your comments / respond to a specific request, not your opinion about the correctness of the database or what else might offend you in this matter.

Answer 1

One way is to find a table of information diagrams and do a little magic with it.

Something like:

 SELECT id, title, CASE WHEN extra_exists THEN extra ELSE 'default' END AS extra FROM mytable CROSS JOIN ( SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='mytable' AND column_name='extra') AS extra_exists) extra 

Edit: where "mytable" needs to be passed to the table you want to query.

Answer 2

Why does Rowan hack work (mostly)?

 SELECT id, title, CASE WHEN extra_exists THEN extra::text ELSE 'default'::text END AS extra FROM mytable CROSS JOIN ( SELECT EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_name = 'mytable' AND column_name = 'extra') AS extra_exists ) AS extra 

Usually this will not work at all. Postgres parses the SQL statement and throws an exception if any of the involved columns does not exist.

The trick is to enter a table name (or an alias) with the same name as the column name in the question. extra in this case. Each table name can be referenced as a whole, as a result of which the entire row is returned as a record type. And since each type can be cast to text , we can cast the whole record to text . Therefore, Postgres accepts the request as valid.

Because column names take precedence over table names, extra::text interpreted as the column mytable.extra if the column exists. Otherwise, by default the entire row of the extra table will be returned - which never happens.

Try choosing a different alias table for extra to see for yourself.

This is an undocumented hack, and it can break if Postgres decides to change the way it parses SQL strings and plans them in future versions — even if this seems unlikely.

unambiguous

If you decide to use it, at least make it unambiguous .

The table name alone is not unique. A table named "mytable" can exist any number of times in several schemas of the same database, which can lead to very confusing and completely false results. You need to specify the schema name in addition:

 SELECT id, title, CASE WHEN col_exists THEN extra::text ELSE 'default'::text END AS extra FROM mytable CROSS JOIN ( SELECT EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'mytable' AND column_name = 'extra' ) AS col_exists ) extra 

Faster

Since this query is hardly portable to other DBMSs, I suggest using the pg_attribute catalog pg_attribute instead of presenting the information_schema.columns information schema. About 10 times faster.

 SELECT id, title, CASE WHEN col_exists THEN extra::text ELSE 'default'::text END AS extra FROM mytable CROSS JOIN ( SELECT EXISTS ( SELECT 1 FROM pg_catalog.pg_attribute WHERE attrelid = 'myschema.mytable'::regclass -- schema-qualified! AND attname = 'extra' AND NOT attisdropped -- no dropped (dead) columns AND attnum > 0 -- no system columns ) AS col_exists ) extra; 

A more convenient and safer cast to regclass is also used - explained in detail here:
What does regclass mean in Postgresql

You can attach the required alias to trick Postgres into any table, including the main table itself. You don’t need to join another relation, which should be the fastest:

 SELECT id, title, CASE WHEN EXISTS ( SELECT 1 FROM pg_catalog.pg_attribute WHERE attrelid = 'mytable'::regclass AND attname = 'extra' AND NOT attisdropped AND attnum > 0 ) THEN extra::text ELSE 'default'::text END AS extra FROM mytable AS extra ; 

convenience

You can enclose the existence test in a simple SQL function (once) by arriving (almost) at the function you requested:

 CREATE OR REPLACE FUNCTION col_exists(_tbl regclass, _col text) RETURNS bool AS $func$ SELECT EXISTS ( SELECT 1 FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attname = $2 AND NOT attisdropped AND attnum > 0 ) $func$ LANGUAGE sql STABLE; COMMENT ON FUNCTION col_exists(regclass, text) IS 'Test for existence of a column. Returns TRUE / FALSE. $1 .. exact table name (case sensitive!), optionally schema-qualified $2 .. exact column name (case sensitive!)'; 

Simplifies the request to:

 SELECT id, title, CASE WHEN col_exists THEN extra::text ELSE 'default'::text END AS extra FROM mytable CROSS JOIN col_exists('mytable', 'extra') AS extra(col_exists); 

Here we use a form with an additional relation, since it turned out to be faster with a function.

However, you only get a textual representation of the column with any of these queries. It is not so simple to get the actual type .

benchmark

I ran a quick test with 100k lines on pages 9.1 and 9.2 to find them the fastest:

 -- fastest SELECT id, title, CASE WHEN EXISTS ( SELECT 1 FROM pg_catalog.pg_attribute WHERE attrelid = 'mytable'::regclass AND attname = 'extra' AND NOT attisdropped AND attnum > 0 ) THEN extra::text ELSE 'default'::text END AS extra FROM mytable AS extra; -- 2nd fastest SELECT id, title, CASE WHEN col_exists THEN extra::text ELSE 'default'::text END AS extra FROM mytable CROSS JOIN col_exists('mytable', 'extra') AS extra(col_exists); 

-> SQLfiddle demo.

Answer 3

I recently tried using the @ErwinBrandstetter solution and it no longer works :(. Someone else has another solution.