Add column if not exists (CINE)
in [PgSql]
|
Prev: [HACKERS] road.thepath no longer in pg_stats?
Next: providing tokenized version of parsed SQL script (was: nodeToString format and exporting the SQL parser)
From: Andrew Dunstan on 28 Apr 2010 12:14 Robert Haas wrote: > On Wed, Apr 28, 2010 at 11:20 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > >> Robert Haas <robertmhaas(a)gmail.com> writes: >> >>> I don't believe you are fairly stating the consensus from previous >>> discussion and I believe that you are actually in the minority on this >>> one. I agree that we probably don't need to support this for object >>> types for which CREATE OR REPLACE is available or can be made >>> available, but that isn't feasible for all object types - tables and >>> columns being the obvious examples. >>> >> What's obvious about it? In particular, I should think that ADD OR >> REPLACE COLUMN would usefully be defined as "ADD if no such column, >> else ALTER COLUMN as necessary to match this spec". Dropping the >> ALTER part of that has no benefit except to lazy implementors; it >> certainly is not more useful to users if they can't be sure of the >> column properties after issuing the command. >> > > Actually, that's a good idea. But how will you handle tables? > > > I think I Iike Heikki's suggestion better, to error out if the object exists but the properties differ. At least I'd like an option for that. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 28 Apr 2010 12:31 Robert Haas <robertmhaas(a)gmail.com> writes: > Actually, that's a good idea. But how will you handle tables? Well, tables are a special case, mainly because it's not clear how to avoid accidentally throwing away data. (In particular if some column in the existing table isn't there in the new definition. It's a bit scary to just drop the column, IMO.) I don't see that that argument applies to doing an automatic ALTER COLUMN, though, especially since the only column type alterations that will go through without a USING clause are reasonably straightforward. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 28 Apr 2010 12:38 On Wed, Apr 28, 2010 at 12:07 PM, Heikki Linnakangas <heikki.linnakangas(a)enterprisedb.com> wrote: > Robert Haas wrote: >> On Wed, Apr 28, 2010 at 11:20 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >>> Robert Haas <robertmhaas(a)gmail.com> writes: >>>> I don't believe you are fairly stating the consensus from previous >>>> discussion and I believe that you are actually in the minority on this >>>> one. I agree that we probably don't need to support this for object >>>> types for which CREATE OR REPLACE is available or can be made >>>> available, but that isn't feasible for all object types - tables and >>>> columns being the obvious examples. >>> What's obvious about it? In particular, I should think that ADD OR >>> REPLACE COLUMN would usefully be defined as "ADD if no such column, >>> else ALTER COLUMN as necessary to match this spec". Dropping the >>> ALTER part of that has no benefit except to lazy implementors; it >>> certainly is not more useful to users if they can't be sure of the >>> column properties after issuing the command. >> >> Actually, that's a good idea. But how will you handle tables? > > What do you mean? Well, how would you define CREATE OR REPLACE TABLE? I think that doesn't make much sense, which is why I think CREATE IF NOT EXISTS is a reasonable approach. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 28 Apr 2010 14:21 On Wed, Apr 28, 2010 at 1:40 PM, Dimitri Fontaine <dfontaine(a)hi-media.com> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> Well, how would you define CREATE OR REPLACE TABLE? I think that >> doesn't make much sense, which is why I think CREATE IF NOT EXISTS is >> a reasonable approach. > > <hand waving time> > > The behavior I'd like to have would be to allow me to give a SELECT > query to run for replacing what is there if there's something. If the > query can not be run on the existing data set, error out of course. > > So you know the state for sure after the command, but it depends on your > query being correct. And you can (de)normalize existing data using joins. > > The REPLACE keyword would here mean that there's a CTAS going under the > hood, then we add the constraints and indexes and triggers etc. That > would mean being able to express those entities changes too, but it > seems important. > > Well, that may be not precise enough as a spec, but at least that's food > for though I hope. This type of hand-waving convinces me more than ever that we should just implement CINE, and it should just C if it doesn't already E. This is what has been requested multiple times, by multiple people, including various people who don't normally poke their head into -hackers. I think the resistance to a straightforward implementation with easy-to-understand behavior is completely unjustifiable. It's completely unobvious to me that all of the above will work at all and, if it did, whether it would actually solve the problems that I care about, like being able to write schema-upgrade scripts that would work in a simple and predictable fashion. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Dimitri Fontaine on 28 Apr 2010 13:40
Robert Haas <robertmhaas(a)gmail.com> writes: > Well, how would you define CREATE OR REPLACE TABLE? I think that > doesn't make much sense, which is why I think CREATE IF NOT EXISTS is > a reasonable approach. <hand waving time> The behavior I'd like to have would be to allow me to give a SELECT query to run for replacing what is there if there's something. If the query can not be run on the existing data set, error out of course. So you know the state for sure after the command, but it depends on your query being correct. And you can (de)normalize existing data using joins. The REPLACE keyword would here mean that there's a CTAS going under the hood, then we add the constraints and indexes and triggers etc. That would mean being able to express those entities changes too, but it seems important. Well, that may be not precise enough as a spec, but at least that's food for though I hope. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |