CODEWITHSUNDEEP
javapostgresqljdbcnullconstraints
Maestros
Maestros

Reputation: 419

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

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?

Upvotes: 12

Views: 43354

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656716

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

Upvotes: 10

Shahid Hussain Abbasi
Shahid Hussain Abbasi

Reputation: 2692

Resolved.

The problem with me was that I was having 2 column in the database and in entity there was one mapped. it happened due to sql update in properties file

once remove one and get fixed

Upvotes: 0

durron597
durron597

Reputation: 32323

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.

Upvotes: 0

Related Questions