Thread: Multiple insert

Multiple insert

From
"FarjadFarid\(ChkNet\)"
Date:

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.

 

Kind Regards

 

 

Farjad

 

 

Re: Multiple insert

From
James Keener
Date:
You are inserting ((),()). It should be (),(). Skip the outer set of parentheses.

On September 19, 2015 2:31:53 PM EDT, "FarjadFarid(ChkNet)" <farjad.farid@checknetworks.com> 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.

 

Kind Regards

 

 

Farjad

 

 


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

Re: Multiple insert

From
Adrian Klaver
Date:
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


Re: Multiple insert

From
Adrian Klaver
Date:
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