First values in an auto increment trigger

Question

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;

Use `COALESCE`, but really you should be using a sequence, as this is not safe for concurrent transactions. — 404, Jul 24 '18 at 10:44
Please remember to always disclose your version of Postgres. — Erwin Brandstetter, Jul 24 '18 at 11:27
unfortunately I can't alter the tables because I'm not the owner so I have no other choice than to increment a varchar column. And I want the column to be updated on an insert in my app. — user3365621, Jul 24 '18 at 11:39
I tried using the sequences (New.id:=nextval('MySequence')) ( but I have an error "the relation does not exist" for my sequence. I'm using Sql Manager for PostgreSQL 5.9.3 — user3365621, Jul 24 '18 at 11:41
The version of your client (Sql Manager) is hardly relevant for this. Your version of Postgres matters: `SELECT version();` — Erwin Brandstetter, Jul 24 '18 at 15:18

Erwin Brandstetter · Accepted Answer · 2018-07-25T15:06:41.317

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:

Auto increment table column

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():

How to auto increment id with a character

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?

Gap-less sequence where multiple transactions with multiple tables are involved

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:

Remove blank-padding from to_char() output

Thank you for your explanation.But like I said in my previous comment I'm not the owner and I can't alter the table (which would have been more safe) so I have not other choice thant to cast a varchar to an int and vice versa to insert it in my table. I'm ust looking for a way to force the insert to have a certain value for my id column. — user3365621, Jul 24 '18 at 11:44
@user3365621: I added a "working" function - for while you are stuck with your unfortunate table design. — Erwin Brandstetter, Jul 24 '18 at 15:16
@user3365621: And I added `COALESCE` to catch the case with an empty table - your initial question. :) — Erwin Brandstetter, Jul 25 '18 at 15:07

First values in an auto increment trigger

1 Answers1