current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
    
do you have an error message? what does it says exactly? –  pleasedontbelong Aug 22 '10 at 11:51
    
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 Aug 22 '10 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 Aug 22 '10 at 13:08

4 Answers 4

up vote 11 down vote accepted

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.

share|improve this answer
up vote 2 down vote

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

share|improve this answer
up vote 0 down vote

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

share|improve this answer
up vote 0 down vote

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 
);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.