Skip site navigation (1) Skip section navigation (2)

Re: ANSI Compliant Inserts

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Loftis <mloftis(at)wgops(dot)com>
Cc: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Rod Taylor <rbt(at)zort(dot)ca>,pgsql-patches(at)postgresql(dot)org
Subject: Re: ANSI Compliant Inserts
Date: 2002-04-15 14:08:55
Message-ID: 22667.1018879735@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Michael Loftis <mloftis(at)wgops(dot)com> writes:
> I'm on the fence in that situation.  Though I'd lean towards a patch 
> thats a sort of compromise.  IIF the 'remaining' columns (IE columns 
> unspecified) have some sort of default or auto-generated value (forgive 
> me I'm just getting back into workign with postgresql) like a SERIAL or 
> TIMESTAMP allow it, IFF any of them do not have a default value then 
> fail.  This will make it 'do the right thing'

I think the apparent security is illusory.  Given the presence of ALTER
TABLE ADD/DROP DEFAULT, the parser might easily accept a statement for
which an end-column default has been dropped by the time the statement
comes to be executed.  (Think about an INSERT in a rule.)

Another reason for wanting it to work as proposed is ADD COLUMN.
Consider

CREATE TABLE foo (a, b, c);

create rule including INSERT INTO foo(a,b,c) VALUES(..., ..., ...);

ALTER TABLE foo ADD COLUMN d;

The rule still works, and will be interpreted as inserting the default
value (NULL if unspecified) into column d.

Now consider same scenario except I write the rule's INSERT without
an explicit column list.  If we follow the letter of the spec, the
rule will now fail.  How is this sensible or consistent behavior?
The case that should be laxer/easier is being treated *more* rigidly.

In any case, the above comparison shows that it's not very consistent
to require explicit defaults to be available for the omitted column(s).
INSERT with an explicit column list does not have any such requirement.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2002-04-15 14:15:06
Subject: Re: Importing Large Amounts of Data
Previous:From: Tom LaneDate: 2002-04-15 13:55:46
Subject: Re: more on large oids

pgsql-patches by date

Next:From: Michael LoftisDate: 2002-04-15 14:33:20
Subject: Re: ANSI Compliant Inserts
Previous:From: Zhenbang WeiDate: 2002-04-15 13:33:46
Subject: [PATCH]errors_zh_TW.properties

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group