postgresql insert multiple rows - fail

Question

I'm using PostgreSQL 8.1.11.

And I'm losing my mind. Why can I not use a basic SQL statement as INSERT?

I provide:

INSERT INTO the_leads_details ( id, lead_id, question_id, i_value, c_value ) VALUES
( 1, 1, 1, NULL, '4500' ), ( 2, 1, 2,    1, NULL );

                         ^ this comma is a problem

What I am missing? This seems like a basic SQL INSERT statement to insert multiple rows. Is my problem related to my PostgreSQL version?

I am inserting a lot of rows and I am looking to optimize INSERT multiple rows instead of placing several INSERTs.

Show us the error message and we can help you out. The comma isn't the problem. Ps. 8.1.11 is outdated, latest 8.1-version is 8.1.21 and will be out of service this year as well. Start upgrading to a newer version. — Frank Heikens
– Frank Heikens, Commented Aug 22, 2010 at 11:51
SQL error: ERROR: syntax error at or near "," at character 111 In statement: INSERT INTO the_leads_details ( id, lead_id, question_id, i_value, c_value ) VALUES ( 1, 1, 1, NULL, '4500' ), ( 2, 1, 2, 1, NULL ); — bensiu
– bensiu, Commented Aug 22, 2010 at 13:08

Magnus Hagander · Accepted Answer · 2010-08-22 12:07:14Z

14

Multi-row INSERT syntax is not supported in PostgreSQL 8.1, you need to upgrade to 8.2 or newer (and if you upgrade today, you really should upgrade to 8.4, not 8.2!)

Another reason is, as Frank mentioned in a comment, that version 8.1 will go end-of-life in November, so it's really time to start investigating upgrading.

answered Aug 22, 2010 at 12:07

Magnus Hagander

25.5k6 gold badges60 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Edmund · Accepted Answer · 2010-08-22 12:10:08Z

4

I'm not sure Postgresl 8.1 supports multiple rows in VALUES. The syntax is:

INSERT INTO table [ ( column [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

http://www.postgresql.org/docs/8.1/static/sql-insert.html

answered Aug 22, 2010 at 12:10

Edmund

10.8k3 gold badges42 silver badges59 bronze badges

Comments

frogstarr78 · Accepted Answer · 2012-01-07 20:17:32Z

2

I know it's an old thread but, this will work:

INSERT INTO the_leads_details ( id, lead_id, question_id, i_value, c_value ) (
SELECT 1, 1, 1, NULL, '4500' 
UNION SELECT 2, 1, 2,    1, NULL 
);

answered Jan 7, 2012 at 20:17

frogstarr78

8781 gold badge7 silver badges12 bronze badges

Comments

vulkanino · Accepted Answer · 2010-08-22 11:53:48Z

1

The syntax is correct, are you sure that the problem is in the comma?

answered Aug 22, 2010 at 11:53

vulkanino

9,1347 gold badges47 silver badges73 bronze badges

Collectives™ on Stack Overflow

postgresql insert multiple rows - fail

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related