Thread: Check if table exists
Even if I've been using Postgres for some years, I figure that this
question fits best on this list:
How do I check if a table exists, eg. from a PLPGSQL function?
I've got a section of a function that only should be run if it's called
from a certain context, that is if the temporary table 'tmp_sources' is
found. But if I try with the line
IF EXISTS (SELECT * FROM tmp_sources) THEN
The function throws an error:
ERROR: relation "tmp_sources" does not exist
Which is kind of off the mark in my context.
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/
After some googling, I found a solution here: <http://www.peterbe.com/plog/pg_class> IF EXISTS (SELECT relname FROM pg_class WHERE relname='tmp_sources') THEN It seems to work ok. -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/
Try
IF EXISTS (SELECT * FROM pg_table WHERE tablename=tmp_sources)
THEN
-- table exists
Alternatively, you could try catching the exception in your original
code and handling it.
IF EXISTS (SELECT * FROM tmp_sources) THEN
-- table or view exists
EXCEPTION
Semyon
> How do I check if a table exists, eg. from a PLPGSQL function?
> IF EXISTS (SELECT * FROM tmp_sources) THEN
>
> The function throws an error:
>
> ERROR: relation "tmp_sources" does not exist
>
Hi, I'm a novice also, but I'm sure that one way of accomplishing this is to check the metadata table/views (eg. information_schema.tables). If you want to make it a little cleaner, you could always wrap the check fo the meta into a function that returns a bool. HTH, David --- On Wed, 3/18/09, Leif B. Kristensen <leif@solumslekt.org> wrote: > From: Leif B. Kristensen <leif@solumslekt.org> > Subject: [NOVICE] Check if table exists > To: pgsql-novice@postgresql.org > Date: Wednesday, March 18, 2009, 11:55 AM > Even if I've been using Postgres for some years, I > figure that this > question fits best on this list: > > How do I check if a table exists, eg. from a PLPGSQL > function? > > I've got a section of a function that only should be > run if it's called > from a certain context, that is if the temporary table > 'tmp_sources' is > found. But if I try with the line > > IF EXISTS (SELECT * FROM tmp_sources) THEN > > The function throws an error: > > ERROR: relation "tmp_sources" does not exist > > Which is kind of off the mark in my context. > -- > Leif Biberg Kristensen | Registered Linux User #338009 > Me And My Database: http://solumslekt.org/blog/ > > - > Sent via pgsql-novice mailing list > (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice
All, I am doing something similar putting all the field (column) names into data objects in a class::method. Here is the select for Postgresql I am using: SELECT a.attname as \"column\", pg_catalog.format_type(a.atttypid, a.atttypmod) as \"datatype\" FROM pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = ( SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(TABLE_NAME_HERE)$' AND pg_catalog.pg_table_is_visible(c.oid) ) Hope this helps. Kulmacet > > Hi, > > I'm a novice also, but I'm sure that one way of accomplishing this is to > check the metadata table/views (eg. information_schema.tables). > > If you want to make it a little cleaner, you could always wrap the check > fo the meta into a function that returns a bool. > > HTH, > > David > > > --- On Wed, 3/18/09, Leif B. Kristensen <leif@solumslekt.org> wrote: > >> From: Leif B. Kristensen <leif@solumslekt.org> >> Subject: [NOVICE] Check if table exists >> To: pgsql-novice@postgresql.org >> Date: Wednesday, March 18, 2009, 11:55 AM >> Even if I've been using Postgres for some years, I >> figure that this >> question fits best on this list: >> >> How do I check if a table exists, eg. from a PLPGSQL >> function? >> >> I've got a section of a function that only should be >> run if it's called >> from a certain context, that is if the temporary table >> 'tmp_sources' is >> found. But if I try with the line >> >> IF EXISTS (SELECT * FROM tmp_sources) THEN >> >> The function throws an error: >> >> ERROR: relation "tmp_sources" does not exist >> >> Which is kind of off the mark in my context. >> -- >> Leif Biberg Kristensen | Registered Linux User #338009 >> Me And My Database: http://solumslekt.org/blog/ >> >> - >> Sent via pgsql-novice mailing list >> (pgsql-novice@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-novice > > - > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice >