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

Question

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.

JDBC uses `?` as the parameter placeholder. See the tutorial for details: http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html — , Apr 06 '16 at 05:44
also causes same issue if `?` is quoted like '?'` `eg, where ad_date>='?'` is incorrect — prayagupa, Jun 27 '19 at 02:26

Tran Ho · Accepted Answer · 2016-04-06T00:57:10.250

24

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

edited Apr 06 '16 at 00:57

answered Apr 06 '16 at 00:29

Tran Ho

1,442
9
15

1

The first field is of type serial (auto increment id), i need to insert only the four following values, it means the first one receive the value automatically – Nesan Mano Apr 06 '16 at 00:37
The answer is good when changing VALUES($1,$2,$3,$4) to VALUES(?,?,?,?) – Nesan Mano Apr 06 '16 at 12:41

score 3 · Answer 2 · answered Sep 14 '21 at 14:06

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

score 0 · Answer 3 · answered Nov 03 '21 at 04:06

0

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

answered Nov 03 '21 at 04:06

Daryn

1,551
1
15
21

score 0 · Answer 4 · answered Dec 11 '21 at 01:59

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

score 0 · Answer 5 · answered Apr 13 '22 at 13:30

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.

score -1 · Answer 6 · answered Oct 07 '20 at 05:16

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

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

6 Answers6