Thread: add column if doesn't exist

Is there a way to check for the existence of a column in a table other
than, say, doing a SELECT on that column name and checking the output?

I'm basically looking to do an ALTER TABLE foo ADD COLUMN bar if bar
doesn't exist.

Thanks.

-- 
Brandon

Brandon Metcalf wrote:
> Is there a way to check for the existence of a column in a table
> other than, say, doing a SELECT on that column name and checking the
> output?

SELECT * FROM information_schema.columns;

Customize to taste.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

p == peter_e@gmx.net writes:
p> Brandon Metcalf wrote:p> > Is there a way to check for the existence of a column in a tablep> > other than, say,
doinga SELECT on that column name and checking thep> > output?
 
p> SELECT * FROM information_schema.columns;
p> Customize to taste.


Yes, that's what I'm looking for.  Thanks.

Now, is there a way to mix PostgreSQL commands and SQL and do
something like
 ALTER TABLE foo ADD COLUMN bar WHERE EXISTS(SELECT * FROM   information_schema.columns WHERE ...)

?

-- 
Brandon

bmetcalf@nortel.com ("Brandon Metcalf") writes:

> p == peter_e@gmx.net writes:
>
>  p> Brandon Metcalf wrote:
>  p> > Is there a way to check for the existence of a column in a table
>  p> > other than, say, doing a SELECT on that column name and checking the
>  p> > output?
>
>  p> SELECT * FROM information_schema.columns;
>
>  p> Customize to taste.
>
>
> Yes, that's what I'm looking for.  Thanks.
>
> Now, is there a way to mix PostgreSQL commands and SQL and do
> something like
>
>   ALTER TABLE foo ADD COLUMN bar WHERE EXISTS(SELECT * FROM
>     information_schema.columns WHERE ...)
>
> ?

I set up a stored procedure to do this for Slony-I...  Replace
@NAMESPACE@ with your favorite namespace, and slon_quote_brute can
likely be treated as an identity function unless you use silly
namespace names :-).

create or replace function @NAMESPACE@.add_missing_table_field (text, text, text, text) 
returns bool as '
DECLARE p_namespace alias for $1; p_table     alias for $2; p_field     alias for $3; p_type      alias for $4; v_row
   record; v_query     text;
 
BEGIN select 1 into v_row from pg_namespace n, pg_class c, pg_attribute a    where
@NAMESPACE@.slon_quote_brute(n.nspname)= p_namespace and         c.relnamespace = n.oid and
@NAMESPACE@.slon_quote_brute(c.relname)= p_table and        a.attrelid = c.oid and
@NAMESPACE@.slon_quote_brute(a.attname)= p_field; if not found then   raise notice ''Upgrade table %.% - add field %'',
p_namespace,p_table, p_field;   v_query := ''alter table '' || p_namespace || ''.'' || p_table || '' add column '';
v_query:= v_query || p_field || '' '' || p_type || '';'';   execute v_query;   return ''t''; else   return ''f''; end
if;
END;' language plpgsql;

comment on function @NAMESPACE@.add_missing_table_field (text, text, text, text) 
is 'Add a column of a given type to a table if it is missing';

-- 
output = ("cbbrowne" "@" "ntlug.org")
http://cbbrowne.com/info/sgml.html
"The  surest  sign  that  intelligent  life exists  elsewhere  in  the
universe is that it has never tried to contact us."
-- Calvin and Hobbes

c == cbbrowne@acm.org writes:

 c> I set up a stored procedure to do this for Slony-I...  Replace
 c> @NAMESPACE@ with your favorite namespace, and slon_quote_brute can
 c> likely be treated as an identity function unless you use silly
 c> namespace names :-).

Thanks.

--
Brandon

c == cbbrowne@acm.org writes:
c> I set up a stored procedure to do this for Slony-I...  Replacec> @NAMESPACE@ with your favorite namespace, and
slon_quote_brutecanc> likely be treated as an identity function unless you use sillyc> namespace names :-).
 

Thanks.

-- 
Brandon