1

something strange is happening to my database. my understanding is that the SERIAL datatype is the Postgres equivalent of MySQL's auto-increment and that omitting it from an INSERT clause will automatically fill it with the next value. I have a simple table of the following structure:

CREATE TABLE data.categories
(
    "categoryId" serial NOT NULL,
    "categoryName" character varying(256) NOT NULL,
    PRIMARY KEY ("categoryId")
)

I'm expecting to be able to add a record without having to specify the id, like this:

INSERT INTO data.categories("categoryName") VALUES('Undefined');

yet, it throws the following error and I don't understand why, it's not meant to happen:

ERROR:  null value in column "categoryId" violates not-null constraint
DETAIL:  Failing row contains (1, Undefined, null).
SQL state: 23502

any thoughts on what I'm doing wrong and why this is happening? is this a bug? every answer on stackoverflow "confirms" that my thinking is right and that using serial as the data type should solve the problem of manually managing ids.

5
  • 1
    I tried your setup at: dbfiddle.uk/… and it appears to be working (I did not have permission to create the schema though). Are you sure that the table is defined that way in your database? Commented Jul 23, 2020 at 18:40
  • Please tag your PostgreSQL version.
    – McNets
    Commented Jul 23, 2020 at 19:28
  • The error message doesn't match your table structure. It shows you are inserting a row with three columns, however your table in the question only contains two columns. Please edit your question and show us the real table definition and the real INSERT you are using.
    – user1822
    Commented Jul 23, 2020 at 19:34
  • Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/…
    – user1822
    Commented Jul 23, 2020 at 19:34
  • Note that with modern Postgres versions identity columns are preferred over serial
    – user1822
    Commented Jul 23, 2020 at 19:35

1 Answer 1

2

thanks for all your help, it seems it was my mistake after all. there's another NOT NULL column in the table I'm trying to insert data into, obviously that won't work. having said that, I did changed serial to INT GENERATED ALWAYS AS IDENTITY.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.