Changing Column Order (Was Re: MySQL vs PostgreSQL.)
On 12 Oct 2002, Hannu Krosing wrote:
Alvaro Herrera kirjutas L, 12.10.2002 kell 04:16:
On Fri, Oct 11, 2002 at 07:08:18PM -0700, Jeff Davis wrote:
And it really is a minor matter of convenience. I end up dropping and
recreating all my tables a lot in the early stages of development, which is
mildly annoying. Certainly not as bad, I suppose, as if you're led to believe
that a feature does something safely, and it kills all your data.Now that ALTER TABLE DROP COLUMN is implemented, there probably isn't
any more the need to do such frequent drop/create of tables.Did attlognum's (for changing column order) get implemented for 7.2 ?
I cannot think of any reason why changing column order should be
implemented in Postgres. Seems like a waste of time/more code bloat for
something which is strictly asthetic.
Regardless, I do have collegues/clients who ask when such a feature will
be implemented. Why is this useful?
Gavin
Import Notes
Reply to msg id not found: 1034413052.26027.7.camel@taru.tm.ee
I cannot think of any reason why changing column order should be
implemented in Postgres. Seems like a waste of time/more code bloat for
something which is strictly asthetic.
What about copy? AFAIK, copy doesn't allow column names being specified,
so it's not purely aesthetic...
Did attlognum's (for changing column order) get implemented for 7.2 ?
I cannot think of any reason why changing column order should be
implemented in Postgres. Seems like a waste of time/more code bloat for
something which is strictly asthetic.Regardless, I do have collegues/clients who ask when such a feature will
be implemented. Why is this useful?
I think even "asthetic" might go too far. It seems mostly irrelevent except
for people who are obsessive compulsive and operate in interactive psql a
lot. It's marginally simpler to get the columns ordered the way you want so
that you can just do "SELECT * ..." rather than "SELECT att0,att1,... ..." at
the interactive psql prompt, and still get the columns in your favorite
order.
As far as I can tell, the order the attributes are returned makes no
difference in a client application, unless you're referencing attributes by
number. All applications that I've made or seen all use the name instead, and
I've never heard otherwise, or heard any advantage to using numbers to
reference columns.
When someone asks, ask them "why?". I'd be interested to know if they have
some other reason. I would think that if they absolutely wanted to fine-tune
the order of columns they'd use a view (seems a little easier than
continually changing order around by individual SQL statements).
Regards,
Jeff
On 12 Oct 2002 at 2:54, Jeff Davis wrote:
As far as I can tell, the order the attributes are returned makes no
difference in a client application, unless you're referencing attributes by
number. All applications that I've made or seen all use the name instead, and
I've never heard otherwise, or heard any advantage to using numbers to
reference columns.
Even in that case you can obtain field number for a given name and vise versa..
When someone asks, ask them "why?". I'd be interested to know if they have
some other reason. I would think that if they absolutely wanted to fine-tune
the order of columns they'd use a view (seems a little easier than
continually changing order around by individual SQL statements).
Sounds fine but what is about that "continually changing"? A view needs a
change only if it alters fields selected/tables to select from/selection
criteria. Field order does not figure in there..
Bye
Shridhar
--
QOTD: "A child of 5 could understand this! Fetch me a child of 5."
On Sat, Oct 12, 2002 at 12:43:37 +0300,
Antti Haapala <antti.haapala@iki.fi> wrote:
I cannot think of any reason why changing column order should be
implemented in Postgres. Seems like a waste of time/more code bloat for
something which is strictly asthetic.What about copy? AFAIK, copy doesn't allow column names being specified,
so it's not purely aesthetic...
The SQL COPY command does (at least in 7.3). The \copy psql command
doesn't seem to allow this though.
Bruno Wolff III <bruno@wolff.to> writes:
On Sat, Oct 12, 2002 at 12:43:37 +0300,
Antti Haapala <antti.haapala@iki.fi> wrote:What about copy? AFAIK, copy doesn't allow column names being specified,
so it's not purely aesthetic...
The SQL COPY command does (at least in 7.3). The \copy psql command
doesn't seem to allow this though.
That's an oversight; \copy should have been fixed for 7.3.
Do we want to look at this as a bug (okay to fix for 7.3) or a new
feature (wait for 7.4)?
I see something that I think is a must-fix omission in the same code:
it should allow a schema-qualified table name. So I'm inclined to fix
both problems now.
regards, tom lane
Tom Lane wrote:
Bruno Wolff III <bruno@wolff.to> writes:
On Sat, Oct 12, 2002 at 12:43:37 +0300,
Antti Haapala <antti.haapala@iki.fi> wrote:What about copy? AFAIK, copy doesn't allow column names being specified,
so it's not purely aesthetic...The SQL COPY command does (at least in 7.3). The \copy psql command
doesn't seem to allow this though.That's an oversight; \copy should have been fixed for 7.3.
Do we want to look at this as a bug (okay to fix for 7.3) or a new
feature (wait for 7.4)?I see something that I think is a must-fix omission in the same code:
it should allow a schema-qualified table name. So I'm inclined to fix
both problems now.
I don't think we can say \copy missing columns is a bug; we never had
it in previous release. Seems like a missing feature. The COPY schema
names seems valid.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
Tom Lane wrote:
Do we want to look at this as a bug (okay to fix for 7.3) or a new
feature (wait for 7.4)?
I don't think we can say \copy missing columns is a bug; we never had
it in previous release. Seems like a missing feature. The COPY schema
names seems valid.
Well, we never had schema names in previous releases either. So I'm not
sure that I see a bright line between these items. The real issue is
that psql's \copy has failed to track the capabilities of backend COPY.
I think we should just fix it.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Tom Lane wrote:
Do we want to look at this as a bug (okay to fix for 7.3) or a new
feature (wait for 7.4)?I don't think we can say \copy missing columns is a bug; we never had
it in previous release. Seems like a missing feature. The COPY schema
names seems valid.Well, we never had schema names in previous releases either. So I'm not
sure that I see a bright line between these items. The real issue is
that psql's \copy has failed to track the capabilities of backend COPY.
I think we should just fix it.
OK, I added it to the open items list.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Sat, 2002-10-12 at 11:37, Gavin Sherry wrote:
I cannot think of any reason why changing column order should be
implemented in Postgres. Seems like a waste of time/more code bloat for
something which is strictly asthetic.Regardless, I do have collegues/clients who ask when such a feature will
be implemented. Why is this useful?
Has column ordering any effect on the physical tuple disposition? I've
heard discussions about keeping fixed-size fields at the beginning of
the tuple and similar.
Sorry for the lame question. :-)
--
Alessio F. Bragadini alessio@albourne.com
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-22-755750
"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925
Alessio Bragadini wrote:
On Sat, 2002-10-12 at 11:37, Gavin Sherry wrote:
I cannot think of any reason why changing column order should be
implemented in Postgres. Seems like a waste of time/more code bloat for
something which is strictly asthetic.Regardless, I do have collegues/clients who ask when such a feature will
be implemented. Why is this useful?Has column ordering any effect on the physical tuple disposition? I've
heard discussions about keeping fixed-size fields at the beginning of
the tuple and similar.Sorry for the lame question. :-)
Yes, column ordering matches physical column ordering in the file, and
yes, there is a small penalty for accessing any columns after the first
variable-length column (pg_type.typlen < 0). CHAR() used to be a fixed
length column, but with TOAST (large offline storage) it became variable
length too. I don't think there is much of a performance hit, though.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Mon, Oct 14, 2002 at 11:04:07AM -0400, Bruce Momjian wrote:
Alessio Bragadini wrote:
On Sat, 2002-10-12 at 11:37, Gavin Sherry wrote:
I cannot think of any reason why changing column order should be
implemented in Postgres. Seems like a waste of time/more code bloat for
something which is strictly asthetic.Has column ordering any effect on the physical tuple disposition? I've
heard discussions about keeping fixed-size fields at the beginning of
the tuple and similar.Yes, column ordering matches physical column ordering in the file, and
yes, there is a small penalty for accessing any columns after the first
variable-length column (pg_type.typlen < 0).
And note that if column ordering was to be implemented through the use
of attlognum or something similar, the physical ordering would not be
affected. The only way to physically reoder the columns would be to
completely rebuild the table.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Aprende a avergonzarte mas ante ti que ante los demas" (Democrito)
Bruce Momjian wrote:
Alessio Bragadini wrote:
On Sat, 2002-10-12 at 11:37, Gavin Sherry wrote:
I cannot think of any reason why changing column order should be
implemented in Postgres. Seems like a waste of time/more code bloat for
something which is strictly asthetic.Regardless, I do have collegues/clients who ask when such a feature will
be implemented. Why is this useful?Has column ordering any effect on the physical tuple disposition? I've
heard discussions about keeping fixed-size fields at the beginning of
the tuple and similar.Sorry for the lame question. :-)
Yes, column ordering matches physical column ordering in the file, and
yes, there is a small penalty for accessing any columns after the first
variable-length column (pg_type.typlen < 0). CHAR() used to be a fixed
length column, but with TOAST (large offline storage) it became variable
length too. I don't think there is much of a performance hit, though.
When was char() fixed size? We had fixed size things like char, char2,
char4 ... char16. But char() is internally bpchar() and has allways been
variable-length.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Jan Wieck wrote:
Bruce Momjian wrote:
Alessio Bragadini wrote:
On Sat, 2002-10-12 at 11:37, Gavin Sherry wrote:
I cannot think of any reason why changing column order should be
implemented in Postgres. Seems like a waste of time/more code bloat for
something which is strictly asthetic.Regardless, I do have collegues/clients who ask when such a feature will
be implemented. Why is this useful?Has column ordering any effect on the physical tuple disposition? I've
heard discussions about keeping fixed-size fields at the beginning of
the tuple and similar.Sorry for the lame question. :-)
Yes, column ordering matches physical column ordering in the file, and
yes, there is a small penalty for accessing any columns after the first
variable-length column (pg_type.typlen < 0). CHAR() used to be a fixed
length column, but with TOAST (large offline storage) it became variable
length too. I don't think there is much of a performance hit, though.When was char() fixed size? We had fixed size things like char, char2,
char4 ... char16. But char() is internally bpchar() and has allways been
variable-length.
char() was fixed size only in that you could cache the column offsets
for char() becuase it was always the same width on disk before TOAST.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
Jan Wieck wrote:
When was char() fixed size?
char() was fixed size only in that you could cache the column offsets
for char() becuase it was always the same width on disk before TOAST.
But that was already broken by MULTIBYTE.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Jan Wieck wrote:
When was char() fixed size?
char() was fixed size only in that you could cache the column offsets
for char() becuase it was always the same width on disk before TOAST.But that was already broken by MULTIBYTE.
Yes, I think there was conditional code that had the optimization only
for non-multibyte servers. Of course, now multibyte is default.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073