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

Re: Check if table exists

From: kulmacet101(at)kulmacet(dot)com
To: dsaracini(at)yahoo(dot)com
Cc: pgsql-novice(at)postgresql(dot)org, "Leif B(dot) Kristensen" <leif(at)solumslekt(dot)org>
Subject: Re: Check if table exists
Date: 2009-03-19 14:29:11
Message-ID: (view raw or whole thread)
Lists: pgsql-novice

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

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.

> 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(at)solumslekt(dot)org> wrote:
>> From: Leif B. Kristensen <leif(at)solumslekt(dot)org>
>> Subject: [NOVICE] Check if table exists
>> To: pgsql-novice(at)postgresql(dot)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:
>> -
>> Sent via pgsql-novice mailing list
>> (pgsql-novice(at)postgresql(dot)org)
>> To make changes to your subscription:
> -
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:

In response to

pgsql-novice by date

Next:From: Leif B. KristensenDate: 2009-03-19 14:40:12
Subject: Re: How do I drop something that might not exist?
Previous:From: Rob RichardsonDate: 2009-03-19 14:26:34
Subject: How do I drop something that might not exist?

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