how to search for relation by name?

when i try to copy database (into another database), i get "relation
does not exist" errors for 'super objects' like sequences. (that is
fine since i am using pg_dump, not pg_dumpall) but there is one
relation i can't find to recreate in the new database.

how can i search database for relation by name (some catalog table)?

Here is the sql

SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname LIKE ('%dt%')
AND pg_catalog.pg_table_is_visible(c.oid)

replace dt with your sequence name

pg_catalog has the information.

Thanks
Deepak

On Wed, Apr 22, 2009 at 2:36 PM, zach cruise <zachc1980(at)gmail(dot)com> wrote:

> when i try to copy database (into another database), i get "relation
> does not exist" errors for 'super objects' like sequences. (that is
> fine since i am using pg_dump, not pg_dumpall) but there is one
> relation i can't find to recreate in the new database.
>
> how can i search database for relation by name (some catalog table)?
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Thanks! I just created new database using old database as template,
and everything got copied over without errors. is that recommended
over pg_dump though when just recreating database for different
encoding?

http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html

On Wed, Apr 22, 2009 at 6:14 PM, DM <dm(dot)aeqa(at)gmail(dot)com> wrote:
> Here is the sql
> SELECT c.oid,
>   n.nspname,
>   c.relname
> FROM pg_catalog.pg_class c
>      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relname LIKE ('%dt%')
>   AND pg_catalog.pg_table_is_visible(c.oid)
> replace dt with your sequence name
> pg_catalog has the information.
> Thanks
> Deepak
>
> On Wed, Apr 22, 2009 at 2:36 PM, zach cruise <zachc1980(at)gmail(dot)com> wrote:
>>
>> when i try to copy database (into another database), i get "relation
>> does not exist" errors for 'super objects' like sequences. (that is
>> fine since i am using pg_dump, not pg_dumpall) but there is one
>> relation i can't find to recreate in the new database.
>>
>> how can i search database for relation by name (some catalog table)?
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general

zach cruise <zachc1980(at)gmail(dot)com> writes:
> Thanks! I just created new database using old database as template,
> and everything got copied over without errors. is that recommended
> over pg_dump though when just recreating database for different
> encoding?

It's pretty much guaranteed not to work, since the data will just be
copied without any encoding conversion. You could possibly get away
with it if the database is empty of data.

regards, tom lane