Re: SQL INSERT Statement -- Multi-Row Insert

Bruno Wolff III <bruno@wolff.to> writes:
> Alan Searles <alien@attglobal.net> wrote:
>> Is PostgreSQL able to perform multi-row inserts using a single INSERT
>> statement as shown below ?
>>
>> INSERT INTO table ( col1, col2, col3 )
>> VALUES  ( value1, value2, value3 ),
>> ( value4, value5, value6 ),
>> ( value7, value8, value9 )

We should support this syntax --- it is in the SQL spec --- but no one's
gotten around to it yet.

> This was recently covered on one of the postgres lists. For small
> (I am not sure how long query strings can be, but small is at least
> hundreds of values.) numbers of values you can use union. For example:

> INSERT INTO table ( col1, col2, col3 )
>   select value1, value2, value3 union
>   select value4, value5, value6 union
>   select value7, value8, value9;

It'd be better to use UNION ALL to keep the system from spending time
trying to eliminate duplicate rows from the union result (especially
since it might succeed, which you'd likely not want...)

A bigger problem with this approach is that the system won't make use of
the INSERT context in assigning datatypes to the union construct's
columns, so you may have to add explicit casts to get things to work
nicely.

So it's a workaround, but not an especially good one.

            regards, tom lane