Hi @ all,

When I do a "SELECT * FROM myTable WHERE ..." using psql I want to have
the columns displayed in a meaningful order by default. I want to avoid
a "SELECT firstname, lastname, <many other cols to follow> ...". Is it
possible to modify the order of columns with an
ALTER TABLE myTable ALTER COLUMN lastname <put behind firstname>

Same holds for adding a new column, how can I
ALTER TABLE myTable ADD COLUMN title VARCHAR(255) <put before firstname>

This is possible in mySQL, not in PostgreSQL?

Any ideas are welcome - Michael

On 11/04/2011 12:18 PM, Michael Hufschmidt wrote:
IMHO it is generally a bad idea to rely on column order because that may
change etc. It's usually best to explicitly select columns in the order
that you desire. You could define a view with the proper order on the
table but you'll have to update that view definition every time your
table has a new column.

Bottom line: do not use "SELECT *" other than for briefly checking
column contents. In any sort of application I would always name only
those columns explicit which I need for the query. That may also save
you a lot of network bandwidth between client and database server.

Kind regards

robert

Michael Hufschmidt <Michael.Hufschmidt (AT) omnis (DOT) net> wrote:
That's right. It's on the 'to do' list, but it's been there for a while
now.


The following page from the postgresql wiki lists some workarounds:
http://wiki.postgresql.org/wiki/Alter_column_position

-M-

Robert Klemme <shortcutter (AT) googlemail (DOT) com> wrote:
That's true, but there are plenty of situations other then SELECT
queries where you end up looking at a table's columns in their
intrinsic order (particularly for people who use GUI database tools).
So it would be nice if when you add a new column you could put it in a
natural place rather than at the end.

-M-