Connecting pg_attribute with pg_namespace

Started by Igor Korot22 days ago6 messagesgeneral
Jump to latest
#1Igor Korot
ikorot01@gmail.com

Hi,
How do I add schema name to

[code]
SELECT attnum FROM pg_attribute WHERE attrelid = 'my_table_name';
[/code]

Thank you.

#2Ron
ronljohnsonjr@gmail.com
In reply to: Igor Korot (#1)
Re: Connecting pg_attribute with pg_namespace

Join pg_attribute.attrelid to pg_class.oid and thus you have the table's
relnamespace value.

On Sat, Oct 31, 2026 at 1:34 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi,
How do I add schema name to

[code]
SELECT attnum FROM pg_attribute WHERE attrelid = 'my_table_name';
[/code]

Thank you.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

#3Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Ron (#2)
Re: Connecting pg_attribute with pg_namespace

On 10/31/26 3:04 PM, Igor Korot wrote:

Hi, Ron,

On Sat, Oct 31, 2026 at 1:24 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

Join pg_attribute.attrelid to pg_class.oid and thus you have the table's relnamespace value.

From the docs:

[quote]
relnamespace oid (references pg_namespace.oid)

The OID of the namespace that contains this relation
[/quote]

I have a schema name as a string.

Should I cast that value to something when I write
"pg_class.relnamespace = 'public'"?

Take a look at this link below:

https://www.postgresql.org/docs/current/datatype-oid.html

It will make your like easier.

So:

select relname from pg_catalog.pg_class where relname = 'cell_per' and
relnamespace = 'public'::regnamespace;

relname
----------
cell_per

Thank you.

On Sat, Oct 31, 2026 at 1:34 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi,
How do I add schema name to

[code]
SELECT attnum FROM pg_attribute WHERE attrelid = 'my_table_name';
[/code]

Thank you.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--
Adrian Klaver
adrian.klaver@aklaver.com

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Adrian Klaver (#3)
Re: Connecting pg_attribute with pg_namespace

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 10/31/26 3:04 PM, Igor Korot wrote:

I have a schema name as a string.
Should I cast that value to something when I write
"pg_class.relnamespace = 'public'"?

select relname from pg_catalog.pg_class where relname = 'cell_per' and
relnamespace = 'public'::regnamespace;

Given that the original requirement was

SELECT attnum FROM pg_attribute WHERE attrelid = 'my_table_name';

maybe just

SELECT attnum FROM pg_attribute
WHERE attrelid = 'my_schema_name.my_table_name'::regclass;

? But doing the joins explicitly is probably more adaptable
to more-complex cases.

regards, tom lane

#5Igor Korot
ikorot01@gmail.com
In reply to: Ron (#2)
Re: Connecting pg_attribute with pg_namespace

Hi, Ron,

On Sat, Oct 31, 2026 at 1:24 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

Join pg_attribute.attrelid to pg_class.oid and thus you have the table's relnamespace value.

From the docs:

[quote]
relnamespace oid (references pg_namespace.oid)

The OID of the namespace that contains this relation
[/quote]

I have a schema name as a string.

Should I cast that value to something when I write
"pg_class.relnamespace = 'public'"?

Thank you.

Show quoted text

On Sat, Oct 31, 2026 at 1:34 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi,
How do I add schema name to

[code]
SELECT attnum FROM pg_attribute WHERE attrelid = 'my_table_name';
[/code]

Thank you.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

#6Igor Korot
ikorot01@gmail.com
In reply to: Adrian Klaver (#3)
Re: Connecting pg_attribute with pg_namespace

On Sat, Oct 31, 2026 at 4:43 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 10/31/26 3:04 PM, Igor Korot wrote:

Hi, Ron,

On Sat, Oct 31, 2026 at 1:24 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

Join pg_attribute.attrelid to pg_class.oid and thus you have the table's relnamespace value.

From the docs:

[quote]
relnamespace oid (references pg_namespace.oid)

The OID of the namespace that contains this relation
[/quote]

I have a schema name as a string.

Should I cast that value to something when I write
"pg_class.relnamespace = 'public'"?

Take a look at this link below:

https://www.postgresql.org/docs/current/datatype-oid.html

It will make your like easier.

So:

select relname from pg_catalog.pg_class where relname = 'cell_per' and
relnamespace = 'public'::regnamespace;

relname
----------
cell_per

Thx.

Show quoted text

Thank you.

On Sat, Oct 31, 2026 at 1:34 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi,
How do I add schema name to

[code]
SELECT attnum FROM pg_attribute WHERE attrelid = 'my_table_name';
[/code]

Thank you.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--
Adrian Klaver
adrian.klaver@aklaver.com