Create unique numeric token on PostgreSQL I have a database where I want to generate unique tokens of six numeric characters. These tokens should be generated each time an invoice number is inserted in the invoice_no column. This would mean that every time I do an insert and the column invoice_no is not null, the token is generated. 

I want the token to be to generate as many tokens as possible without a token being repeated.

create table ledgers( invoice_no varchar(10), 
                      debit numeric, 
                      credit numeric, 
                      details varchar(20), 
                      token char(6) );
If you don't mind the unique token being sequential, you can use a sequence, like so:

CREATE TABLE invoices
(
    invoice_no integer,
    token char(6)
);

CREATE SEQUENCE invoices_token_seq START WITH 100000;

ALTER TABLE invoices 
ALTER COLUMN token
SET DEFAULT CAST(NEXTVAL('invoices_token_seq') AS CHAR(6));


To test:

postgres=# INSERT INTO invoices ( invoice_no ) VALUES ( 1 );
INSERT 0 1
postgres=#
postgres=# SELECT * FROM invoices;
 invoice_no | token
------------+--------
          1 | 100000
(1 row)

postgres=#


Note that I have used CAST as you specified that the token should be "numeric characters".

The above is the same as using an AUTO_INCREMENT column in MySQL.

However, to accommodate for the fact that you only want to generate a token when the invoice_no IS NOT NULL, you need to use a trigger as follows:

create table ledgers
(
    invoice_no varchar(10), 
    debit numeric(6,2), 
    credit numeric(6,2), 
    details varchar(20), 
    token char(6) 
);

CREATE SEQUENCE ledgers_token_seq START WITH 100000;

CREATE OR REPLACE FUNCTION token_insert() RETURNS trigger AS '
BEGIN
  IF NEW.invoice_no IS NOT NULL
  THEN
    NEW.token := CAST(NEXTVAL(''ledgers_token_seq'') AS CHAR(6));
    END IF;
  RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER ledgers_insert BEFORE INSERT ON ledgers FOR EACH ROW EXECUTE PROCEDURE token_insert();


Test:

postgres=# INSERT INTO ledgers ( invoice_no, debit, credit, details ) VALUES ( 1, 2, 3, 'NOT NULL invoice_no' );
INSERT 0 1
postgres=#
postgres=# INSERT INTO ledgers ( debit, credit, details ) VALUES ( 2, 3, 'NULL invoice_no' );
INSERT 0 1
postgres=#
postgres=# select * from ledgers;
 invoice_no | debit | credit |       details       | token
------------+-------+--------+---------------------+--------
 1          |  2.00 |   3.00 | NOT NULL invoice_no | 100000
            |  2.00 |   3.00 | NULL invoice_no     |
(2 rows)

postgres=#