'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.

javapostgresqlinsert


Solution 1:[1]

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(?,?,?,?)

Solution 2:[2]

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 = ?

Solution 3:[3]

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

Solution 4:[4]

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",

Solution 5:[5]

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.

Solution 6:[6]

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(?,?,?,?)";

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 aar0n
Solution 3 Daryn
Solution 4 Madis Männi
Solution 5 Collins C.
Solution 6 asherbret

Related Questions