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
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 |
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 |