Обсуждение: Multiple insert

You are inserting ((),()). It should be (),(). Skip the outer set of parentheses.


--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

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.

http://www.postgresql.org/docs/9.4/interactive/sql-insert.html

To insert multiple rows using the multirow VALUES syntax:

INSERT INTO films (code, title, did, date_prod, kind) VALUES
     ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
     ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

so:

INSERT INTO "Lookup"." CNEnum1"(" Id","Description")
VALUES (1::integer,'Desc1'::varchar) ,(2::ineger,''Desc2'::varchar);

>
> Kind Regards
>
> Farjad
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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