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
