Inserting a serial type field data in postgresql using java

Question

This is my code which is not working

    String sql="insert into user_master(User_Id,'First_Name','Last_Name','Address','City','Country','Email_Id',Cell_Number) values(DEFAULT,'"+fname+"','"+lname+"','"+address+"','"+city+"','"+country+"','"+email+"',"+cellno+")";

This one is working fine

    String sql="insert into user_master values(1,'"+fname+"','"+lname+"','"+address+"','"+city+"','"+country+"','"+email+"',"+cellno+")";

But I don't want to increment id every time, I have already used serial type as i specified. But with DEFAULT it's not working.

Any idea?

No, I just meant that without it your code is susceptible to SQL injection attacks. — David Conrad
– David Conrad, Commented Mar 4, 2013 at 19:30
Ya. Thanks @David Conrad. Any idea where am lacking in the code? — zennith
– zennith, Commented Mar 4, 2013 at 19:33
Sorry, I'm not that familiar with postgresql. What happens if you simply omit all reference to the User_Id column? I'm guessing it's the primary key; is it set up to autoincrement? — David Conrad
– David Conrad, Commented Mar 4, 2013 at 19:36

zennith · Accepted Answer · 2013-03-04 20:19:03Z

2

Use named columns, and omit the one you want to have defaulted:

String sql="insert into user_master(first_name,last_name,address,city,country,email_id,cell_number) values('"+fname+"','"+lname+"','"+address+"','"+city+"','"+country+"','"+email+"',"+cellno+")";

This will solve your problem, but you should still use a prepared statement as mentioned above for other reasons (SQL injection, quoting issues, etc).

edited Mar 4, 2013 at 20:19

zennith

4102 gold badges5 silver badges17 bronze badges

answered Mar 4, 2013 at 19:53

Andres Olarte

4,3803 gold badges27 silver badges46 bronze badges

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

5 Comments

zennith Over a year ago

Thanks for reply. Still its not working. With error ERROR: syntax error at or near "'First_Name'"

zennith Over a year ago

Now following error appear ERROR: column "first_name" of relation "user_master" does not exist. Even though Column First_Name is there in table user_master.

Andres Olarte Over a year ago

This might be due to the way you created your table if you quoted your columns when you created the table, the case might be different. Verify how it looks in the database, and try using double quotes around the column names: "First_Name","Last_Name". Also make sure to escape the double quotes in Java.

zennith Over a year ago

No they are the same. But i fixed the problem by converting all the fields to lower case. May be that means postgresql support lower case fields names.

Andres Olarte Over a year ago

Did you convert the fields in your java code or in the database table? By default postgresql field names are not case sensitive, but if you declare them inside double quotes, they will be case sensitive.

Collectives™ on Stack Overflow

Inserting a serial type field data in postgresql using java

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related