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?

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