Обсуждение: 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 > like > > INSERT 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