Thread: SQL INSERT Statement -- Multi-Row Insert

  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 )

   Yes, I have looked through the PostgreSQL documentation, and it DOES
NOT appear as though it can.
   I was hoping that I did not read it right.

/Alan

On Sat, Jun 14, 2003 at 09:52:26 -0400,
  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 )
>
>    Yes, I have looked through the PostgreSQL documentation, and it DOES
> NOT appear as though it can.
>    I was hoping that I did not read it right.

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;

I made a mistake in my advice. I use just "union" for small examples done
by hand, but when doing it for programattically generated SQL you want
to use "union all". "union all" doesn't remove duplicates and doesn't
waste effort checking for duplicates. So you really want something like:

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

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