PSQLException: ERROR: null value in column violate

Question

I am using PostgreSQL 8.4.13 on x86_64-pc-linux-gnu on Debian 4.4.5-8, 64-bit.

I have created the following table:

CREATE TABLE users (
user_id             serial PRIMARY KEY NOT NULL,
name                varchar(200),
username            varchar(150),
password            varchar(150),
);

Then, using a Java application, I execute the following code:

String insertTableSQL = "INSERT INTO USERS"
                + "(name, username, password) VALUES"
                + "(?,?,?)";
PreparedStatement preparedStatement = DBCon.prepareStatement(insertTableSQL);
preparedStatement.setString(1, userInfo.get("name"));           
preparedStatement.setString(2, userInfo.get("username"));
preparedStatement.setString(3, userInfo.get("password")));

preparedStatement.executeUpdate();

The issue here is that the executeUpdate() generates the following exception:

org.postgresql.util.PSQLException: ERROR: null value in column "user_id" violates not-null constraint

The weird thing is that if I execute the same insert statement using psql, it executes successfully.

Any ideas would be much appreciated.

Thank you.

Answer 1

As @mu commented, the error message contradicts the rest of your question.

The only reasonable explanation left is that you are, in fact, writing to a different table

Try:

INSERT INTO users (user_id, name, username, password) VALUES
(1234,'foo', 'foo', 'foo')";

And check your table. Did the INSERT arrive at the table you expected? If not, check your settings:

• IP, port, db name?
• Same schema in the DB? Check your search_path setting.
• You did not by accident double quote the table name "USERS"? Double-quoted identifiers are not cast to lower case. Read the chapter Identifiers and Key Words for details..

Find the other instance of table users and fix potential damage you may have done. :)

Answer 2

String insertTableSQL = "INSERT INTO USERS"
            + "(name, username, password) VALUES"
            + "(?,?,?)";
PreparedStatement preparedStatement = DBCon.prepareStatement(insertTableSQL);
preparedStatement.setString(1, userInfo.get("name"));           
preparedStatement.setString(2, userInfo.get("username"));
preparedStatement.setString(3, userInfo.get("password")));

preparedStatement.executeUpdate();

Note only three question marks and now the fields line up correctly.

Also don't worry about the NOT NULL constraint. It will handle itself (how could it ever be NULL, it's SERIAL) and could be causing your error. Just remove it.