Re: Best way to "add" columns

Jesse Scott writes:

It would appear that ADD COLUMN was implemented by 6.5.x but when I tried
"ALTER TABLE members ALTER COLUMN server SET DEFAULT 0;" it told me that
that feature hadn't be implemented yet.

It is now.

--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

Martijn van Oosterhout <kleptog@cupid.suninternet.com> writes:

Marten Feldtmann wrote:

The varable lengths columns should be at the end of the row, therefore
it does not seem to be good to add an integer column after a varchar
column.

1. Is this true? Should variable length column really be at the end
of a row?

There is some microscopic performance advantage if you put fixed-width
columns before variable-width columns: columns that are at a fixed
offset in the table records don't require scanning through earlier
columns to access. But I'd be surprised if you could actually measure
any difference, unless perhaps on tables with hundreds of columns.

2. If so, surly postgres can reorder tham internally so that on disk
they are in the optimal format.

There are notes in the source code indicating that the original
Berkeley Postgres crew thought about this and decided it wasn't
worth the trouble.

regards, tom lane

Martijn van Oosterhout <kleptog@cupid.suninternet.com> writes:

Marten Feldtmann wrote:

The varable lengths columns should be at the end of the row, therefore
it does not seem to be good to add an integer column after a varchar
column.

1. Is this true? Should variable length column really be at the end
of a row?

There is some microscopic performance advantage if you put fixed-width
columns before variable-width columns: columns that are at a fixed
offset in the table records don't require scanning through earlier
columns to access. But I'd be surprised if you could actually measure
any difference, unless perhaps on tables with hundreds of columns.

2. If so, surly postgres can reorder tham internally so that on disk
they are in the optimal format.

There are notes in the source code indicating that the original
Berkeley Postgres crew thought about this and decided it wasn't
worth the trouble.

Yes, agreed. Can anyone measure the difference. It would show up
accessing columns past the variable length column.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026