0

This is my PostgreSQL code:

CREATE TABLE "user" (
    id serial UNIQUE,
    username varchar,
    password varchar,
    email varchar,
);

I want to create Java method, which adds new user to my table user:

public static void addUser(Connection con) throws SQLException {
        String sql = "INSERT INTO user VALUES (?, ?, ?)";

        try(PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, "test");
            ps.setString(2, "test");
            ps.setString(3, "test@email.com");

            ps.executeUpdate();
            }
        }
    }

Because column id is serial, I don't create prepared statement for this column (even without java, I would make insert only with remaining values, since id would be generated automatically). Hovewer, when I run this code, I get the following error:

ERROR: column "id" is of type integer but expression is of type character varying

What am I doing wrong?

enneenne
  • 209
  • 1
  • 8

1 Answers1

1

Always specify the target columns in an INSERT statement. And as user is a reserved keyword, you have to quote it (but it would be better if you found a different name)

String sql = "INSERT INTO \"user\" (username, password, email) VALUES (?, ?, ?)";
  • Ok I fixed my insert statement, but it still looks like it does not respect serial data type in id column. I have already several users in my table, but my code still adds user with id 1 (what violates unique constraint). Is there anything else I should fix? – enneenne Mar 24 '20 at 12:55
  • @enneenne: you apparently inserted rows providing a value for the ID column, bypassing the sequence. That's why `identity` columns (with `generated always`) are [preferred](https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_serial) over `serial` nowadays. To fix this, see e.g. here: https://stackoverflow.com/questions/244243/ or here https://stackoverflow.com/questions/9314382 –  Mar 24 '20 at 13:00
  • Yes, that seems to be the problem. – enneenne Mar 24 '20 at 13:03