Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #273 > unrolled thread

Column Order

Back to article view | Back to comp.databases.postgresql

Contents

  Column Order Michael Hufschmidt <Michael.Hufschmidt@omnis.net> - 2011-11-04 12:18 +0100
    Re: Column Order Robert Klemme <shortcutter@googlemail.com> - 2011-11-04 17:39 +0100
      Re: Column Order Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2011-11-04 18:29 +0000
    Re: Column Order Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2011-11-04 18:23 +0000

#273 — Column Order

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

[toc] | [next] | [standalone]

#274

On 11/04/2011 12:18 PM, Michael Hufschmidt wrote:
> 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

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

[toc] | [prev] | [next] | [standalone]

#276

Robert Klemme  <shortcutter@googlemail.com> 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.

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-

[toc] | [prev] | [next] | [standalone]

#275

Michael Hufschmidt  <Michael.Hufschmidt@omnis.net> wrote:
> 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?

That's right. It's on the 'to do' list, but it's been there for a while
now.


> Any ideas are welcome - Michael

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

-M-

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web