Why No WHERE clause for INSERT statements
Hi,
the postgresql 8.4 documentation defines the syntax of the DELETE statement as:
DELETE FROM [ ONLY ] table [ [ AS ] alias ]
[ USING usinglist ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
The syntax includes a where clause allowing qualified declarative
control over what is deleted.
Why would the same not hold for the insert statement?
More specifically; the INSERT statement is currently defined as:
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [,
...] | query }
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
Does not symmetry dictate that the definition of the INSERT statement
be similar to:
INSERT INTO table [ ( column [, ...] ) ]
[ USING usinglist ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [,
...] | query }
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
This syntax would allow tuples to be inserted only when some condition is true.
What I am missing?
Cheers,
Maurice
On Aug 17, 2010, at 4:32 , Maurice Gittens wrote:
More specifically; the INSERT statement is currently defined as:
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [,
...] | query }
^^^^^
INSERT INTO table [ ( column [, ...] ) ]
SELECT ...
WHERE condition
This syntax would allow tuples to be inserted only when some condition is true.
What I am missing?
See above.
Michael Glaesemann
grzm seespotcode net
Maurice Gittens <mainmanmauricio@gmail.com> writes:
The syntax includes a where clause allowing qualified declarative
control over what is deleted.
Why would the same not hold for the insert statement?
All that stuff is buried in the "query" option. You can do something
like
INSERT INTO table SELECT ... WHERE ...
so the full power of SELECT is available already.
regards, tom lane
All that stuff is buried in the "query" option. You can do something
likeINSERT INTO table SELECT ... WHERE ...
so the full power of SELECT is available already.
regards, tom lane
Thank you Michael, Tom; I must have missed the nonterminal symbol
'query' in the syntax rule;
Kind regards.
Maurice