Default value using sequence

Question

I am trying to populate a column's value based on sequence.

I create the sequence:

CREATE SEQUENCE sch1.incrementbyone_seq; ALTER SEQUENCE sch1.incrementbyone_seq
OWNED BY sch1.featurepoint1.seqnumber_;

This does not work, the "seqnumber_" coloumn never gets populated. I have made sure that the owner and the schema name of the sequence are the same and located on the same schema as the Featurepoint1 Feature Class

The sequence itself is correct when its is called by a query  "SELECT nextval('sch1.incrementbyone_seq');" it gives out the correct number.

How do I enable the sequence to be called when editing happens ?

Then I try using a trigger and function:

Function: new.seqnumber:= nextval('sch1.incrementbyone_seq');

Trigger: before INSERT OR UPDATE ON sch1.featurepoint1 FOR EACH ROW EXECUTE PROCEDURE function_name();

It works perfectly.

Whats the difference between using and not using trigger ? Why the trigger works ?

yockee · Answer

I already put the "nextval" as the DEFAULT. I add a point on the feature class. All coloumns are fine, except the "seqnumber_" coloumn. No value there, the coloumn showed "NULL"

But, when I tested it on a non-spatial table (using INSERT INTO), it works perfectly, no problem.

I am still wondering why it is not working on feature class.

CodyPatterson · Answer

Hey @yockee

By default, the sequence would not automatically populate without a trigger or some type of default declaration. There's no method that the database employs to do this on its own, you would have to use some type of trigger in order to activate this.

You would also be able to use a default maybe, you may attempt to try something like this, just be aware that this will work for the items moving forward that are created, rather than updating anything already in place:

ALTER TABLE sch1.featurepoint1 ALTER COLUMN seqnumber_ SET DEFAULT nextval('sch1.incrementbyone_seq');

Be sure to backup your database before attempting any actions such as this, better to be safe!

Cody