CODEWITHSUNDEEP
postgresql
amphibient
amphibient

Reputation: 31348

ordinal_position values in information_schema.columns out of sequence

When I run the following SQL to list the columns for base.mytable:

select column_name, ordinal_position, is_nullable, data_type 
from information_schema.columns
where table_catalog = 'mycatalog'
and table_schema = 'base'
and table_name = 'mytable'
order by ordinal_position;

I get the ordinal_position column values out of sequence:

1
2
3
4
5
9
24
25
26
27
28
29
31
32
33
34
36
37
38
39
40
41
42
43
44
45
46
47
48
49

I would expect them to all be in sequence and incremented by 1.

Can anybody explain why this is the case?

Upvotes: 0

Views: 4217

Answers (3)

michael zhang
michael zhang

Reputation: 1

except the method of recreating the table or columns, you can just to change SQL with: row_number() over(order by ordinal_position)

Upvotes: 0

Jorge Campos
Jorge Campos

Reputation: 23381

That field states as the DOCS says Ordinal position of the column within the table (count starts at 1) Which means that it is the creation order but it not say anything about changes you've made on that table. So every change as drop column will not affect (reorder) that increment ordinal_name

Here is just the creation statement

http://sqlfiddle.com/#!15/02bfb/1

Now the same creation with an alter table.

http://sqlfiddle.com/#!15/f6551/1

Upvotes: 3

yieldsfalsehood
yieldsfalsehood

Reputation: 3085

Dropping columns will do it. Also, table inheritance can cause funky things to happen with the ordering.

Upvotes: 1

Related Questions