pg_attribute.attnum - wrong column ordinal?

[Konstantin Izmailov <pgfizm@xxxxxxxxx>]

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