PSQLException: ERROR: null value in column violates not-null constraint

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 is that executeUpdate() generates the following exception:

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. Why?

Solution

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. :)

Comments

[Maestros]

Thank you for your responses! The extra question marks and the out of order issue was a copy-paste error due to a modification of the code to present a simple version of it. I corrected the code. The problem seems to be related to the auto increment column of the database.

[Maestros]

Thank you very much Erwin! I was writing to the correct table. The problem was that I added the serial sequence to a different table (different schema). So, the table that the java application was writing to, had no serial sequence at the user_id column. Thank you for your help!