full featured alter table?

The process you describe really is not that difficult. My general method
is:

Rename existing column
Create new column
Repopulate from existing column
Drop existing column
Re-Add constraints/indexes (honestly I don't use constraints that much).

Jon

On Thu, 12 Jun 2003, Sven Koehler wrote:

The process you describe really is not that difficult. My general method
is:

yes, but it takes time and is error-prone.
it a feature that most DBMS have, but postgresql does not, and what you
describe is a nice workaround, but not an answer to my question.

when will postgresql will finally have a full-featured alter table command?

On Thu, 12 Jun 2003, Sven Koehler wrote:

one of the biggest disease of PostGreSQL is, that i can't change the
definition of a column.

In order to do that, i'd have to drop any keys, drop the column and
create a new one with all indexes etc.

Are there any plans to overcome that problem?

Pretty much when someone who cares about it enough comes along with
a sufficient plan (and preferrably code) to implement it without breaking
things would be my guess (especially given that AFAICS it's not part of
either SQL92 or SQL99). Note that a sufficient plan would possibly
involve a lot of things not directly involved with changing the type
such as being able to deal with cached query plans for functions and
such.

Even simple changes like varchar(20) to varchar(200) are not allowed.

True, but at least these are simple manipulations of the system catalog.

So my DB is planned well, but i have to change it every now and than
because i must implement the changes that my client demands me to do,
and have some extra work that nobody will pay me for, if there's no way
to change a column.

Why? If your client asks for a change, they should pay for what's
involved. You have to make it known that changes are more costly for
this setup up front, but if it has other advantages, they can choose
which way to go.

Pretty much when someone who cares about it enough comes along with
a sufficient plan (and preferrably code) to implement it without breaking
things would be my guess (especially given that AFAICS it's not part of
either SQL92 or SQL99). Note that a sufficient plan would possibly
involve a lot of things not directly involved with changing the type
such as being able to deal with cached query plans for functions and
such.

In other word, nobody cares about that at the moment, and so this will
not be implemented.

something i hate most about opensource movement are these comments like
"submit a patch". your staments are not that rough, but i'd need more
time to get into the code than to implement the feature.
so if this feature was never foreseen, than it might get a heavy task,
and it might be even havier in a few years to implement that.

even a tool could help to improve that. perhaps pgAdmin could be teached
to change a column's type by performing the steps it would need.

i'll submit a feature-request to the pgadmin team.

On Thu, 12 Jun 2003, Sven Koehler wrote:

Pretty much when someone who cares about it enough comes along with
a sufficient plan (and preferrably code) to implement it without breaking
things would be my guess (especially given that AFAICS it's not part of
either SQL92 or SQL99). Note that a sufficient plan would possibly
involve a lot of things not directly involved with changing the type
such as being able to deal with cached query plans for functions and
such.

In other word, nobody cares about that at the moment, and so this will
not be implemented.

Actually, it's more that it's a big job (bigger than actually just
changing the column) and it has some side issues about things like storage
(can we use 2x space to do it, do we need to do something else, what
happens if it fails part way due to something like a failure to convert
data), stored plans (do plpgsql functions and foreign keys continue
functioning after it, what about set returning functions that return that
type), and probably other things that I can't think of. With limited
resources, you have to make choices about what you work on. I'm sure Tom
(for example) could have done it, but he possibly wouldn't have been able
to do some or all of the error code stuff, protocol changes, expression
indexes, various optimizer enhancements, bug fixes, etc.

something i hate most about opensource movement are these comments like
"submit a patch". your staments are not that rough, but i'd need more
time to get into the code than to implement the feature.
so if this feature was never foreseen, than it might get a heavy task,
and it might be even havier in a few years to implement that.

It's actually probably going to move towards being easier over time
hopefully, since some of the side issues are things that are probably
going to be addressed anyway.

On Thu, 12 Jun 2003, Sven Koehler wrote:

Hi,

one of the biggest disease of PostGreSQL is, that i can't change the
definition of a column.

In order to do that, i'd have to drop any keys, drop the column and
create a new one with all indexes etc.

Are there any plans to overcome that problem?
Even simple changes like varchar(20) to varchar(200) are not allowed.

I asked this question about 2 years ago, and there were only some guys,
that told me that i wouldn't need to change my DB if i'd plan it well.

So my DB is planned well, but i have to change it every now and than
because i must implement the changes that my client demands me to do,
and have some extra work that nobody will pay me for, if there's no way
to change a column.

This is the only missing feature, that prevent me to use this DBMS - i'd
love to, because it's an ORDBMS and that's what i'd have needed sometimes.

OK, so how do we handle things like converting a varchar to a timestamp
with time zone? What if one of the rows has invalid date syntax? Do we
convert the column anyway, or throw the whole change out with an error?

What about converting int8 to int4 where we have integers that are LARGER
than what an int4 can hold. Do we just set the int4 to max/min when the
int8 is out of range?

How about converting varchar(64) to varchar(32). Do you want it to error
out if there are any fields over 32 chars, or just chop them off?

MySQL has a "feature" like the one you want. It's not well thought out,
and easily causes as much misery and pain as it prevents. If you convert
a column to date type it silently converts any field it can't suss out to
the very wonderful value of 0000-00-00. Since their DDL is not
transactable, you've just lost all your dates. Hope you had a backup
laying around.

Converting column types is like walking around with a gun pointed at your
foot. If the possible error cases aren't well thought out, then bam! the
gun goes off and your data is so much mush.

Other vendors may not care as much about data integrity as the postgresql
team, and if you prefer their product feel free to use it. The Postgresql
team has a tendency of not incorporating such changes until they can work
for virtually all cases without error.

Here's how I do it:

# pg_dump -a -t tabletochange >tabledata.sql
# pg_dump -s -t tabletochange >tableschema.sql
vi tableschema.sql (make changes)
psql testdatabase <tableschema.sql
psql testdatabase <tabledata.sql
psql testdatabase

look and see if your data is still there.

Changing column types is playing fast and loose with your data and is not
recommended.

It's also, as near as I can tell, not in the SQL3 spec.

It may bug you that open source projects say "submit a patch and we'll
see" but that's way better than "oh yeah, we have that feature. what?
Your data got mucked up? Wow, that's never happened before."

"scott.marlowe" <scott.marlowe@ihs.com> writes:

OK, so how do we handle things like converting a varchar to a timestamp
with time zone? What if one of the rows has invalid date syntax? Do we
convert the column anyway, or throw the whole change out with an error?

I think this particular point is a red herring. We have data conversion
functions. My thought would be to apply the same cast function we
would if you were doing an implicit-cast assignment. That is, the
behavior would be exactly like

ALTER TABLE tab ADD COLUMN newcol newtype;
UPDATE tab SET newcol = oldcol;

If there is no cast function, or the cast function burps at any row,
then the command fails and rolls back ... then it's up to you to fix
the data and try again, or use a manual process with some more-complex
conversion function.

However, there are plenty of much-more-subtle semantic issues to worry
about. Here are a couple:

* Indexes. How do you translate an index definition involving one
datatype into an index involving another? There may be no index opclass
at all for the given index type and the new datatype, or there might be
multiple opclasses. Consider for example the recent discussions about
providing reverse-sort opclasses standardly. I'm not sure how ALTER
TABLE could pick the right opclass if there's more than one choice.

* Constraints. If the old column has, say, CHECK (foo(x) > 0), how do
we translate this to a new datatype? Should we assume that if we can
find a function named foo() on the new datatype, it's the right thing
to use? Seems like a mighty dangerous assumption to me.

Functional indexes present *both* of these sets of problems, and I think
there are probably other issues lurking in the advanced features.

Now a human working through the conversion process by hand probably
wouldn't have a lot of problems deciding what to do, but I'm unconvinced
that an automatic ALTER command would get these things right.

What would make more sense to me than a tightly-wrapped-up ALTER command
at the SQL level is some sort of interactive conversion wizard, perhaps
as part of phppgadmin or Red Hat's graphical tool suite. It could walk
you through all these considerations, get your okay on each nontrivial
semantic change, and then apply all the operations within a single
transaction. AFAIK we have all the ALTER functionality needed to
support such a tool.

regards, tom lane

On Fri, Jun 13, 2003 at 06:49:01PM -0400, Tom Lane wrote:

What would make more sense to me than a tightly-wrapped-up ALTER command
at the SQL level is some sort of interactive conversion wizard, perhaps
as part of phppgadmin or Red Hat's graphical tool suite. It could walk
you through all these considerations, get your okay on each nontrivial
semantic change, and then apply all the operations within a single
transaction. AFAIK we have all the ALTER functionality needed to
support such a tool.

The one thing we don't have that I think would be useful is a way to
re-order the columns in a table. Maybe it's just me, but I tend to want
column to appear in a specific order, and the only way you can
accomplish this today is by re-creating the entire table.
--
Jim C. Nasby (aka Decibel!) jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

The one thing we don't have that I think would be useful is a way to
re-order the columns in a table. Maybe it's just me, but I tend to want
column to appear in a specific order, and the only way you can
accomplish this today is by re-creating the entire table.

i agree. i'd love to "reorder" columns.

On Sat, Jun 14, 2003 at 10:31:02 -0500,
"Jim C. Nasby" <jim@nasby.net> wrote:

The one thing we don't have that I think would be useful is a way to
re-order the columns in a table. Maybe it's just me, but I tend to want
column to appear in a specific order, and the only way you can
accomplish this today is by re-creating the entire table.

You can specify the order that columns are output now. The real underlying
order should really be up to postgres to decide based on what is most
efficient.
If you were thinking about the order you get when you use * as a shorthand
for all of the columns, perhaps there could be some way to change that
without actually changing the order of the columns in the table.
I doubt it would be a good idea to do that though. It is already a bad
idea to use * in applications in case something does change the order
or number of columns. * is mostly going to be used in adhoc queries
where you care about saving some typing. It may not be worth adding
a feature like that for the limited amount of use it would get.

Hello everybody,

The one thing we don't have that I think would be useful is a
way to re-order the columns in a table. Maybe it's just me,
but I tend to want column to appear in a specific order, and
the only way you can accomplish this today is by re-creating
the entire table.

I agree with Jim, this would be a 'cool but not mandatory' feature ! :-)

This feature is not mandatory since I avoid the use SELECT * FROM...
(and I forbid the use of SELECT * to my subordinates).

But, it would be very nice to have the possibility order the different
columns of a table in PostgreSQL. Why ? For example, I use a
reverse-engineering tool that generate graphical database map but that
tool don't give me the possibility to change the display order... So,
this feature would be very nice ! :-)

---------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net

"Jim" == Jim C Nasby <jim@nasby.net> writes:

Jim> The one thing we don't have that I think would be useful is a way to
Jim> re-order the columns in a table. Maybe it's just me, but I tend to want
Jim> column to appear in a specific order, and the only way you can
Jim> accomplish this today is by re-creating the entire table.

Column order shouldn't matter. It matters only when you say "SELECT *",
and the best literature out there warns against the foibles of such.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/&gt;
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

You can specify the order that columns are output now. The real
underlying
order should really be up to postgres to decide based on what is most
efficient.

how can i specify it?
i think we all ment cosmetical changes, not the "real" underlying order
which should be up to postgresql of course!

If you were thinking about the order you get when you use * as a
shorthand
for all of the columns, perhaps there could be some way to change that
without actually changing the order of the columns in the table.

"select *" should refelect the cosmetical order of the columns.
"select *" could be tranformed into something like "select col1, col2,
..." according to the cosmetical order that's defined.
if that's not the case at the moment, i don't care, because i don't use
"select *" by assuming any column-order for the reasons you already
mentioned.

You can specify the order that columns are output now. The real
underlying
order should really be up to postgres to decide based on what is
most
efficient.

how can i specify it?
i think we all ment cosmetical changes, not the "real" underlying
order which should be up to postgresql of course!

let's express it another way: we want to define the order of the columns
when they are shown in tools like phpPgAdmin or PgAdminII.

there should be some statement which is used by the tools to query the
list of columns in the table - the resultset contains the columns in a
defined order which is - in most cases - the order in which the columns
were created. we'd like to be abled influence the order of the columns
within that resultset.

If you were thinking about the order you get when you use * as a
shorthand
for all of the columns, perhaps there could be some way to change
that
without actually changing the order of the columns in the table.

"select *" should refelect the cosmetical order of the columns.
"select *" could be tranformed into something like "select col1,
col2, ..." according to the cosmetical order that's defined.
if that's not the case at the moment, i don't care, because i don't
use "select *" by assuming any column-order for the reasons you
already mentioned.

It sounds what you are looking for is soem way to set the order so
that you can save some typing when doing adhoc queries?

no - i don't do any "select *" - it's just a cosmetical thing, because
after some time - especially when you have to perform the discussed
steps to change a column definition - your columns will mix-up.
Usually the order of the columns in a table follows some intension of
the user who created it.

Jim> The one thing we don't have that I think would be useful is a way to
Jim> re-order the columns in a table. Maybe it's just me, but I tend to want
Jim> column to appear in a specific order, and the only way you can
Jim> accomplish this today is by re-creating the entire table.

Column order shouldn't matter. It matters only when you say "SELECT *",
and the best literature out there warns against the foibles of such.

I think we don't want to reorder our column to make "select *" have a
order that fits our needs.

as i said in my other posts, we only want it for our selves to have a
defined order, that doesn't look that much chaotic.

Le Samedi 14 Juin 2003 15:34, Sven Köhler a écrit :

The one thing we don't have that I think would be useful is a way to
re-order the columns in a table. Maybe it's just me, but I tend to want
column to appear in a specific order, and the only way you can
accomplish this today is by re-creating the entire table.

i agree. i'd love to "reorder" columns.

Same for me. It would be great to be able to reorganize some tables...

--
Guillaume <!-- http://absfr.tuxfamily.org/ -->.

On Sat, Jun 14, 2003 at 19:35:15 +0200,
Sven K�hler <skoehler@upb.de> wrote:

let's express it another way: we want to define the order of the columns
when they are shown in tools like phpPgAdmin or PgAdminII.

That is something that those tools should do then. It isn't really a
postgres issue as it already provides ways for the tools to get
information about columns of a table.

let's express it another way: we want to define the order of the columns
when they are shown in tools like phpPgAdmin or PgAdminII.

That is something that those tools should do then. It isn't really a
postgres issue as it already provides ways for the tools to get
information about columns of a table.

The problem is, that the information should be stored somewhere in the
database.
If each tool does maintain its own column-order storage, that we'll have
a chaos.

Bruno Wolff III <bruno@wolff.to> writes:

Sven K�hler <skoehler@upb.de> wrote:

let's express it another way: we want to define the order of the columns
when they are shown in tools like phpPgAdmin or PgAdminII.

That is something that those tools should do then. It isn't really a
postgres issue as it already provides ways for the tools to get
information about columns of a table.

Perhaps, but pg_attribute is the natural place to keep the column
ordering info.

ISTM we talked months ago about adding an "attlognum" column to
pg_attribute, which would be separate from "attnum" (the physical column
position) and would be used by the backend only to determine the order
of SELECT * expansion. Admin tools could use this to determine column
display order too. It didn't get done, but it still seems like a
reasonable idea to me.

regards, tom lane