How to fix NEXTVAL returning null in insert query but returns correct value when executed alone in P-CodePudding

When I execute the following insert query:

insert into FEED_STAGING_GOOD_TRADE (JOBEXECUTIONID, STAGINGID, ROWNUMBER, system, traderef, mtmvaluation, mtmvaluationccy, prcmtmquality, prcdate) 
values (64, NEXTVAL('FEED_STAGING_TRADE_SEQ') ,2,'RMS','TRD3',1.11111111E8,'USD',100.0,'2011-12-09  00'::timestamp)

I get the following output:

ERROR: null value in column "tradestagingid" violates not-null constraint
  Detail: Failing row contains (46, 64, null, 2, null, null, null, RMS, null, null, TRD3, null, null, null, null, null, null, null, null, null, null, null, null, 111111111, USD, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 100, 2011-12-09 00:00:00, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null).

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:

select NEXTVAL('FEED_STAGING_TRADE_SEQ');

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

CodePudding user response：

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.