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(dot)klaver(at)aklaver(dot)com