Skip to content
Advertisement

How to fix NEXTVAL returning null in insert query but returns correct value when executed alone in PostgreSQL?

When I execute the following insert query:

I get the following output:

The third column which is supposed to be filled by the NEXTVAL function result is being sent null, which is violating a certain non-nullity constraint.

However when I execute NEXTVAL independently like this:

The function returns a correct value as shown below in the screenshot:

nextval

Advertisement

Answer

Your insert provides a value for the column stagingid, but not for the column tradestagingid (to which the error message refers)

You INSERT statement doesn’t even list a column named tradestagingid so the default value for that column will be used.

As you seem to expect the tradestagingid to be populated with the sequence value, you need to change the column list of your INSERT and use tradestagingid instead of stagingid. Or better: define it as an identity column, so it gets populated automatically.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement