postgresql nextval throws error relation does not exist

Question

I have the following sql:

 INSERT INTO in_api_settings ( 
   ENTITY_ID, 
   ACTIVE_START_DATE, 
   ACTIVE_END_DATE) 
 VALUES ( 
   nextval('in_api_settings_seq'), 
   current_date, 
   null)

And when i execute it i get error

ERROR: relation "in_api_settings_seq" does not exist

On Oracle i use IN_API_SETTINGS_SEQ.NEXTVAL and this works fine. I am merging from Oracle to PostgreSQL and not sure what I am doing wrong.

Answer 1

specify schema in

nextval('in_api_settings_seq')

so it would be

nextval('schema_name.in_api_settings_seq')

also from the sequence naming I believe it was auto generated, thus it would be assigned as default value to, and thus you could try

INSERT INTO in_api_settings ( 
   ENTITY_ID, 
   ACTIVE_START_DATE, 
   ACTIVE_END_DATE) 
 VALUES ( 
   DEFAULT, 
   current_date, 
   null)

Answer 2

Use separate quotes for schema and sequence

SELECT NEXTVAL('"schema_name"."sequence_tbl"')

Eg. SELECT NEXTVAL('"Master_SHM"."ATTRIBUTE_ID_SEQ"');

 nextval
---------
       5
(1 row)