How do I ALTER the POSITION of a COLUMN in a Postgresql database? I've tried the following, but I was unsuccessful:
ALTER TABLE person ALTER COLUMN dob POSITION 37;
views:
2108answers:
5
+2
A:
"Alter column position" in the PostgreSQL Wiki says:
PostgreSQL currently defines column order based on the
attnumcolumn of thepg_attributetable. The only way to change column order is either by recreating the table, or by adding columns and rotating data until you reach the desired layout.
That's pretty weak, but in their defense, in standard SQL, there is no solution for repositioning a column either. Database brands that support changing the ordinal position of a column are defining an extension to SQL syntax.
One other idea occurs to me: you can define a VIEW that specifies the order of columns how you like it, without changing the physical position of the column in the base table.
Bill Karwin
2008-11-12 23:08:27
Postgresql = Fail :)
Huuuze
2008-11-12 23:12:41
No, postgres wins. You probably fail for wanting to scrub an entire massive database table for a mere aesthetic reason.
Kent Fredric
2008-11-12 23:14:32
You could always dump the db, re-order the columns and rebuild it..
Dana the Sane
2008-11-12 23:49:53
@Dana: I think that's what's meant by "recreating the table" in the wiki article.
Bill Karwin
2008-11-13 00:04:08
'dump the database' :: great way to damage data. and hence "scrub the massive database" effect.
Kent Fredric
2008-11-13 00:36:47
@Kent: Doubtful, the postgres devs go a long way to ensure data consistency and that would certainly apply to pg_dump. After all, what's the point of doing db backups if the restore is borked?
Dana the Sane
2008-11-13 23:41:31
@Dana, yes, but you're adding 1) a big process to dump, and then you drop, and then you have a big time consuming load. If you have a *seriously* massive database, which is usually the case with 37 columns, you're going to have risks with disk IO choking.
Kent Fredric
2008-11-14 06:22:29
+1
A:
I don't think you can at present: see this article on the Postgresql wiki.
Mike Woodhouse
2008-11-12 23:09:42
+1
A:
Also, the question is why you want to move the column, the other 2 posters have summed up the how, but something about your attempted query worries me:
37 columns is far too many.
If you're trying to move a column on table with that many columns, i suspect you are DoingItWrongTM
Kent Fredric
2008-11-12 23:13:31
Good point! I was also a little concerned about the reason for wanting columns to be in a defined order. If "want" was actually "need" then make that majorly concerned.
Mike Woodhouse
2008-11-13 09:56:41
A:
Yes, Postgres fails. I need to move columns to retain database structure clear. Machine doesn't care are columns ordered properly or not, it simply treats them as a bunch of data, human does. Which do you prefer, row (name, surname, age, height, weight) or row (height, surname, weight, age, name)? Indifferent for machine. Notice, I don't need the columns to be moved physically, all I need is columns to be displayed in a specified order when listing table sctructure and/or table contents.
Ballamuth
2010-03-30 15:37:47
Yes, that is a failing pattern. If you need columns in a specific order, than use a SELECT to put them in that order, or use a VIEW. It is akin to expecting a specific order of records from a query without an ORDER BY clause. When you execute a query against a database, it returns the column names along with the data, use that information.
MkV
2010-03-31 17:14:56