Re: Multiple insert

On 09/19/2015 11:31 AM, FarjadFarid(ChkNet) wrote:
> Hi,
>
> I am getting errors trying to insert multiple records in single
> statement in table like this
>
> CREATE TABLE "Lookup"."CNEnum1"
>
> (
>
>    "Id" integer NOT NULL,
>
>    "Description" character varying(50),
>
>    CONSTRAINT "PK_Lookup_CNEnum1 " PRIMARY KEY ("Id")
>
> )
>
> WITH (
>
>    OIDS=FALSE
>
> );
>
> INSERT INTO "Lookup"." CNEnum1"(" Id
> ","Description")VALUES((1::integer,'Desc1'::varchar)
> ,(2::ineger,''Desc2'::varchar))
>
> I have tried various versions of sql statement. With field casting and
> without casting.  Both work when a single record is inserted but with
> multiple records the same error is reported.
>
> ERROR:  column "Id" is of type integer but expression is of type record
>
> LINE 1: ... CNEnum1"("Id","Description")VALUES((1::int,'D...
>
>                                                               ^
>
> HINT:  You will need to rewrite or cast the expression.
>
> ********** Error **********
>
> ERROR: column "Id" is of type integer but expression is of type record
>
> SQL state: 42804
>
> Hint: You will need to rewrite or cast the expression.
>
> Character: 73
>
> Any suggestion would be greatly welcomed! And thank you in advance.

Forgot to complete the explanation. VALUES has its own meaning:

http://www.postgresql.org/docs/9.0/static/sql-values.html

So:

test=> VALUES (1, 2), (3,4);
  column1 | column2
---------+---------
        1 |       2
        3 |       4
(2 rows)

test=> VALUES ((1, 2), (3,4));
  column1 | column2
---------+---------
  (1,2)   | (3,4)
(1 row)


>
> Kind Regards
>
> Farjad
>


--
Adrian Klaver
adrian.klaver@aklaver.com