CODEWITHSUNDEEP
javapostgresqlinsert
Nesan Mano
Nesan Mano

Reputation: 2166

error: The column index is out of range: 1, number of columns: 0

I'm trying to solve the problem of doing an insert into a Postgresql table

I looked at this similar question but it did not solve my problem

ERROR : The column index is out of range: 1, number of columns: 0

here is the part of code getting the error:

String query = "INSERT INTO reviews (nbstar, body, author, product_id) VALUES($1,$2,$3,$4)";

PreparedStatement prepareStatement = connection.prepareStatement(query);
prepareStatement.setInt(1, nbStar);
prepareStatement.setString(2, body);
prepareStatement.setString(3, author);
prepareStatement.setInt(4, productId);

boolean executed = prepareStatement.execute();

i tried several times to change the index number but still the same error

and here is the schema of the table:

table schema

can anyone give me an advice ?

thanks.

Upvotes: 20

Views: 115739

Answers (7)

Johan
Johan

Reputation: 425

In my case, remove any sql comments inside string query.

Upvotes: 1

Collins C.
Collins C.

Reputation: 181

Incase you get this error, for my own issue, I was passing a field ending with a character next to a variable e.g.:

select * from my_schema."my_table" mt
where
        mt.field_2 = variable_2
        and mt.field_1 = 'variable_1' 
        [[and cast(mt.date as DATE) = {{date_picked}}]]

-> This failed with an error of: The column index is out of range: 1, number of columns: 0

Changed to:

select * from my_schema."my_table" mt
where
        mt.field_1 = 'variable_1' 
        and mt.field_2 = variable_2
        [[and cast(mt.date as DATE) = {{date_picked}}]]
  • Please note the variable ending with a quote character has been moved.

-> This worked for me.

Upvotes: 0

Madis Männi
Madis Männi

Reputation: 388

I got that same error because I had the last \n missing in following query, I hope this helps somebody.

                    "                order by mp.id desc\n" +
                "                limit 1\n" +
                "            ) as mge_mp\n" +
                "      )\n" +
                "\n" +
                "-- #pageable\n",

My last line was

                    "-- #pageable",

Upvotes: 0

Daryn
Daryn

Reputation: 1609

For me, I had added a comment which included a question mark. Silly me!

Upvotes: 1

aar0n
aar0n

Reputation: 131

My problem was that the question mark had single quotes around it and I copy pasted the query from straight sql so I just replaced the string with a ?. For example Select * from datatable where id = '?' and I had to change it to Select * from datatable where id = ?

Upvotes: 6

asherbret
asherbret

Reputation: 6018

For me the issue was having a semicolon at the end of the query string. Something like:

String query = "INSERT INTO reviews (nbstar, body, author, product_id) VALUES(?,?,?,?);";

Notice the ; appended to the end of the query string. The fix is, well, to remove it:

String query = "INSERT INTO reviews (nbstar, body, author, product_id) VALUES(?,?,?,?)";

Upvotes: -1

Tran Ho
Tran Ho

Reputation: 1490

In the sql query, you want to insert the values for 5 fields (id, nbstar, body, author, product_id) but there are only 4 values VALUES($1,$2,$3,$4).

Update following your edited question, just modify your query as follows:

VALUES($1,$2,$3,$4)

to 

VALUES(?,?,?,?)

Upvotes: 25

Related Questions