Re: Getting the table ID

On 19 Jul 2022, at 5:10, Igor Korot wrote:

> Hi, guys,
>
> In the database theory each table is identified as
> "schema_name"."table_name".
>
> When I tried to look at how to get the table id inside the PostgreSQL,
> I saw that I needed to look at the pg_class table.
>
> SELECT oid FROM pg_class WHERE relname = "table_name";
>
> However that query will give a non-unique table id (see the first
> sentence).
>
> So how do I get the table id based on the "schema_name.table_name"?
>
> There is a pg_namespace table - is this where the schema should come
> from?
> If yes - how?
> Looking at that table I don't see any unique fields...
> Or is this something that is hidden?
>
> In fact I'm trying to run following query:
>
> SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
> c.relnamespace AND c.relname = ? AND nc.nspname = ?;
>
> from my ODBC based program, but it returns 0 rows on SQLFetch.
>
> I know PostgreSQL does not use '?' for query parameters
> but I thought that since its an ODBC everything should work.
>
> Nevertheless, all bindings were successful, but now rows are returned.
>
> Is this query correct?
>
> Thank you.

That's more or less the same query that I am using:

select
r.oid as oid,
n.nspname || '.' || r.relname as name
from
pg_catalog.pg_namespace n
join
pg_catalog.pg_class r on n.oid = r.relnamespace
where
(r.relkind = 'r') and
(n.nspname not like 'pg_%') and
(n.nspname != 'information_schema') and
(n.nspname = 'email') and
(r.relname = 'emailhistory')

Maybe your problem has to to with uppercase/lowercase schema and/or
table names?

Servus,
Walter