CODEWITHSUNDEEP
postgresqltriggersauto-increment
user3365621
user3365621

Reputation: 47

First values in an auto increment trigger

I am working with the following table in PostgreSQL 10.3:

CREATE TABLE s_etpta.tab1 (
  Number VARCHAR(40) NOT NULL,
  id VARCHAR(8),
  CONSTRAINT i_tab1 PRIMARY KEY(Number)
)

I need to increment the column id by 1 with every insert. I can't alter the table because I'm not the owner so I have no other choice than to increment a varchar column.

The column is type varchar prefixed with zeros. How can I specify that I want to start with '00000001' if the table is empty? Because when I already have values in my table the trigger gets the last value and increment it for the next insert which is correct, but when my table is empty the id column stays empty since the trigger has no value to increment.

CREATE OR REPLACE FUNCTION schema."Num" (
)
RETURNS trigger AS
$body$
DECLARE    
BEGIN

NEW.id := lpad(CAST(CAST(max (id) AS INTEGER)+1 as varchar),8, '0') from 
schema.tab1;

return NEW;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100;

Upvotes: 1

Views: 1574

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657922

A trigger design is unsafe and expensive trickery that can easily fail under concurrent write load. Don't use a trigger. Use a serial or IDENTITY column instead:

Don't use text (or varchar) for a numeric value.

Don't pad leading zeroes. You can format your numbers any way you like for display with to_char():

In Postgres 10 or later your table could look like this:

CREATE TABLE s_etpta.tab1 (
  number numeric NOT NULL PRIMARY KEY,         -- not VARCHAR(40)
  id     bigint GENERATED ALWAYS AS IDENTITY   -- or just int?
);

No trigger.

Seems odd that number is the PK. Would seem like id should be. Maybe you do not need the id column in the table at all?

If you need to get the underlying sequence in sync:

If you cannot fix your table, this trigger function works with the existing one (unreliably under concurrent write load):

CREATE OR REPLACE FUNCTION schema.tab1_number_inc()
  RETURNS trigger AS
$func$
DECLARE    
BEGIN
   SELECT to_char(COALESCE(max(id)::int + 1, 0), 'FM00000000')
   FROM   schema.tab1
   INTO   NEW.id;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER tab1_before_insert
BEFORE INSERT ON schema.tab1
FOR EACH ROW EXECUTE PROCEDURE schema.tab1_number_inc();

The FM modifier removes leading blanks from to_char() output:

Upvotes: 1

Related Questions