Skip site navigation (1) Skip section navigation (2)

Supplying a default on read, if a column does not exist in DB

From: Bryce Nesbitt <bryce2(at)obviously(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Supplying a default on read, if a column does not exist in DB
Date: 2008-05-19 21:48:34
Message-ID: 4831F5B2.7010807@obviously.com (view raw or flat)
Thread:
Lists: pgsql-sql
Is there a clean way in Postgres to specify a default return value, if a 
column does not exist in a database?  In pseudocode:

select p_email,
CASE WHEN EXISTS("p_email_alt") THEN p_email_alt ELSE 'none' END
from eg_application;

I can kind of almost get there with:

select p_email,
CASE WHEN EXISTS(SELECT * FROM information_schema.columns
WHERE table_schema='public' and table_catalog='stage' and 
table_name='eg_application' and column_name='p_email_alt')
THEN p_email_alt ELSE 'none' END
from eg_application;

Except that Postgres evaluates the non-existent column name in the 
"THEN", and errors out, even though the test will be false.

Note that use of stored procedures won't work for my particular use case.

             -Bryce

Google Keywords: "if column exists", "if column defined", test for 
existence of column, default column value, defaults, information schema, 
existence, definition, missing column.

pgsql-sql by date

Next:From: Karl DenningerDate: 2008-05-20 18:14:47
Subject: SQL question....
Previous:From: Dave PageDate: 2008-05-19 08:22:16
Subject: Re: dblinks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group