Обсуждение: pg_attribute.attnum - wrong column ordinal?

create table ms_tst

(

col1 varchar(50),

col2 varchar(50)

);

alter table ms_tst add column col3 varchar(50);

alter table ms_tst drop column col2;

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"

On Wed, Nov 25, 2009 at 1:03 AM, Konstantin Izmailov <pgfizm@gmail.com> wrote:

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

You could use something like:

row_number() over (partition by T.schemaname,T.viewname order by
attnum) as "ORDINAL_POSITION"

If you just stick this in there in place of attnum it'll cause an
extra sort. It should be possible with enough clever rearranging of
the query to do the whole query with a single sort since that's the
same sort order that the results are ordered in.

Incidentally you probably want UNION ALL rather than UNION in the
original query.

--
greg

On Wed, Nov 25, 2009 at 2:00 AM, Konstantin Izmailov <pgfizm@gmail.com> wrote:
> Greg,
> this is brilliant - thank you very much!
>
> Is "partition by" compatible to PostgreSQL 8.0/8.2? I could not find
> compatibility information. It works fine with PG 8.3/8.4 and Greenplum 3.3
> thou.

It's 8.4 only.

You could also just generate them on the client since the ordering the
query requests is the right order for it.

--
greg

Greg Stark escribió:
> On Wed, Nov 25, 2009 at 1:03 AM, Konstantin Izmailov <pgfizm@gmail.com> wrote:
>
> > 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?
>
> You could use something like:
>
> row_number() over (partition by T.schemaname,T.viewname order by
> attnum) as "ORDINAL_POSITION"

Should we recast the attributes and columns views in information_schema?
I notice they still use attnum.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Should we recast the attributes and columns views in information_schema?
> I notice they still use attnum.

I'd vote against it, at least until we have something better than a
row_number solution.  That would create another huge performance penalty
on views that are already ungodly slow.

When and if we get around to separating physical from logical column
position, the issue might go away "for free".

            regards, tom lane

On tor, 2009-12-03 at 10:09 -0500, Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Should we recast the attributes and columns views in information_schema?
> > I notice they still use attnum.
>
> I'd vote against it, at least until we have something better than a
> row_number solution.  That would create another huge performance penalty
> on views that are already ungodly slow.

Should be easy to test the performance impact of this, since the limit
for columns per table is 1600.