Re: [PATCHES] ANSI Compliant Inserts - Mailing list pgsql-hackers
From | Rod Taylor |
---|---|
Subject | Re: [PATCHES] ANSI Compliant Inserts |
Date | |
Msg-id | 07d901c1e432$237e44e0$8001a8c0@jester Whole thread Raw |
Responses |
Re: [PATCHES] ANSI Compliant Inserts
Re: [PATCHES] ANSI Compliant Inserts Re: [PATCHES] ANSI Compliant Inserts |
List | pgsql-hackers |
I submitted a patch which would make Postgresql ANSI compliant in regards to INSERT with a provided column list. As Tom states below, this is not full compliance. CREATE TABLE tab(col1 text, col2 text); INSERT INTO tab (col1, col2) VALUES ('val1'); -- bad by spec (enforced by patch) INSERT INTO tab (col1, col2) VALUES ('val1', 'val2'); -- good INSERT INTO tab VALUES ('val1'); -- bad by spec (not enforced) INSERT INTO tab VALUES ('val1', 'val2'); -- good Currently in postgres all of the above are valid. I'd like to rule out the first case (as enforced by the patch) as it's obvious the user had intended to have two values. Especially useful when the user misses a value and inserts bad data into the table as a result. For the latter one, it could be argued that the user understands the table in question and has inserted the values they require. New columns are added at the end, and probably don't affect the operation in question so why should it be changed to suit new columns? But, automated code should always be written with the columns explicitly listed, so this may be a user who has simply forgotten to add the value -- easy to do on wide tables. Thoughts? -- Rod Taylor ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Bruce Momjian" <pgman@candle.pha.pa.us> Cc: "Rod Taylor" <rbt@zort.ca>; <pgsql-patches@postgresql.org> Sent: Sunday, April 14, 2002 11:49 PM Subject: Re: [PATCHES] ANSI Compliant Inserts > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Do you want to argue we should continue allowing it? > > No; I'm objecting that there hasn't been adequate discussion about > this change of behavior. > > BTW, if the rationale for the change is "ANSI compliance" then the patch > is still wrong. SQL92 says: > > 3) No <column name> of T shall be identified more than once. If the > <insert column list> is omitted, then an <insert column list> > that identifies all columns of T in the ascending sequence of > their ordinal positions within T is implicit. > > 5) Let QT be the table specified by the <query expression>. The > degree of QT shall be equal to the number of <column name>s in > the <insert column list>. > > The patch enforces equality only for the case of an explicit <insert > column list> --- which is the behavior I suggested in the original > comment, but the spec clearly requires an exact match for an implicit > list too. How tight do we want to get? > > In any case this discussion should be taking place someplace more public > than -patches. > > regards, tom lane >
pgsql-hackers by date: