title: First values in an auto increment trigger
body: <p>I am working with the following table in PostgreSQL 10.3:</p>
  
  <pre><code>CREATE TABLE s_etpta.tab1 (
    Number VARCHAR(40) NOT NULL,
    id VARCHAR(8),
    CONSTRAINT i_tab1 PRIMARY KEY(Number)
  ) 
  </code></pre>
  
  <p>I need to increment the column <code>id</code> 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 <code>varchar</code> column.</p>
  
  <p>The column is type <code>varchar</code> 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.</p>
  
  <pre><code>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;
  </code></pre>