pg_attribute.attnum - wrong column ordinal?

From: Konstantin Izmailov <pgfizm(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pg_attribute.attnum - wrong column ordinal?
Date: 2009-11-25 01:03:03
Message-ID: 72746b5e0911241703q1c5b9323gcd70bff83b2e7be0@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Today I was contacted by a Microsoft (!) developer Kamil who was working on
issues in Linked Servers to PostgreSQL. He brought the following scenario:
if a column is dropped then ordinal positions of remaining columns are
reported incorrectly.

Here is test scenario:
1) create a table in PGAdmin:

create table ms_tst
(
col1 varchar(50),
col2 varchar(50)
);

2) Add col3 and drop col2:

alter table ms_tst add column col3 varchar(50);
alter table ms_tst drop column col2;

3) Use the following query to retrieve columns information (this query is
generated by the OLEDB provider):

select * from
(select T.schemaname as "TABLE_SCHEMA", T.tablename as "TABLE_NAME",
A.attname as "COLUMN_NAME", A.attnum as "ORDINAL_POSITION",
A.atthasdef as "COLUMN_HASDEFAULT", A.atttypid as "DATA_TYPE",
TY.typname as "TYPNAME", A.attnotnull as "NOT_NULL",
A.attlen as "FIELD_LENGTH", A.atttypmod as "FIELD_MOD", D.adsrc as
"COLUMN_DEFAULT"
from pg_attribute A
inner join pg_class C on (A.attrelid=C.oid)
inner join pg_tables T on (C.relname=T.tablename)
inner join pg_namespace NS on (NS.oid=C.relnamespace and
NS.nspname=T.schemaname)
inner join pg_type TY on (TY.oid=A.atttypid)
left outer join pg_attrdef D on (D.adrelid=C.oid and D.adnum=A.attnum)
where A.attnum>0 and A.attisdropped='f'
union select T.schemaname as "TABLE_SCHEMA", T.viewname as "TABLE_NAME",
A.attname as "COLUMN_NAME", A.attnum as "ORDINAL_POSITION",
A.atthasdef as "COLUMN_HASDEFAULT", A.atttypid as "DATA_TYPE",
TY.typname as "TYPNAME", A.attnotnull as "NOT_NULL",
A.attlen as "FIELD_LENGTH", A.atttypmod as "FIELD_MOD", D.adsrc as
"COLUMN_DEFAULT"
from pg_attribute A
inner join pg_class C on (A.attrelid=C.oid)
inner join pg_views T on (C.relname=T.viewname)
inner join pg_namespace NS on (NS.oid=C.relnamespace and
NS.nspname=T.schemaname)
inner join pg_type TY on (TY.oid=A.atttypid)
left outer join pg_attrdef D on (D.adrelid=C.oid and D.adnum=A.attnum)
where A.attnum>0 and A.attisdropped='f') s
where "TABLE_SCHEMA"='public' and "TABLE_NAME"='ms_tst'
order by "TABLE_SCHEMA", "TABLE_NAME", "ORDINAL_POSITION"

4) Note that ORDINAL_POSITIONs of col1 and col3 are 1 and 3 (expected 1 and
2).

My question: can pg_attribute.attnum be used to determine the sequential
ordinal positions of columns in a table? What is a right way to get the
ordinal numbers?

Please help!

Konstantin

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christophe Pettus 2009-11-25 01:05:29 Re: Processing Delay
Previous Message Michael Lawson (mshindo) 2009-11-25 00:38:44 Processing Delay

Browse pgsql-hackers by date

  From Date Subject
Next Message Hitoshi Harada 2009-11-25 01:03:11 Re: Syntax conflicts in frame clause
Previous Message Roger Leigh 2009-11-25 00:14:32 Re: garbage in psql -l