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