Get last order number with PostgreSQL trigger

postgresqlIn multi user applications it is not easy to serve a unique order or invoice numbers for each save process. You should check carefully several times on every database insert. But with database triggers you can get last order or invoice number easily as described below.

I have used PostgreSQL 9.3.5 version on Ubuntu for this trigger.

Create order table:

-- order table sequence
DROP SEQUENCE IF EXISTS order_order_id_seq CASCADE;
CREATE SEQUENCE order_order_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE public.order_order_id_seq OWNER TO postgres;

-- order table
DROP TABLE IF EXISTS order CASCADE;
CREATE TABLE order (
  order_id bigint DEFAULT nextval('order_order_id_seq'::regclass) NOT NULL,
  order_number bigint,
  order_date timestamp without time zone NOT NULL
  -- add other fields
);

ALTER TABLE public.order OWNER TO postgres;

Create params table. The parameters table has last order number;

-- params table sequence
DROP SEQUENCE IF EXISTS params_params_id_seq CASCADE;
CREATE SEQUENCE params_params_id_seq
  INCREMENT BY 1
  NO MAXVALUE
  NO MINVALUE
  CACHE 1;

ALTER TABLE public.params_params_id_seq OWNER TO postgres;

-- params table
DROP TABLE IF EXISTS params CASCADE;
CREATE TABLE params (
  params_id bigint DEFAULT nextval('params_params_id_seq'::regclass) NOT NULL,
  last_order_number bigint
  -- add other fields
);

ALTER TABLE public.params OWNER TO postgres;

Create trigger:

-- get last order number function
CREATE OR REPLACE FUNCTION get_last_order_number()
    RETURNS TRIGGER AS
$BODY$
DECLARE
    v_last_order_number bigint;
BEGIN
  SELECT last_order_number INTO v_last_order_number FROM params WHERE params_id = 1;
  UPDATE params set last_order_number = v_last_order_number + 1 WHERE params_id = 1;
  NEW.order_number = v_last_order_number + 1;
  RETURN NEW;
END $BODY$
    LANGUAGE plpgsql;

ALTER FUNCTION public.get_last_order_number() OWNER TO postgres;

-- last order number trigger
CREATE TRIGGER last_order_number_trigger BEFORE INSERT ON order
  FOR EACH ROW EXECUTE PROCEDURE get_last_order_number();

PostgreSQL installation and settings on Ubuntu 12.04

postgresql

Updated for Ubuntu GNU/Linux 13.04 (Raring Ringtail).

  • Install latest PostgreSQL 9.1 in console:
sudo apt-get install postgresql libpq-dev
  • PostgreSQL has a super user is called postgres. Change user to the PostgreSQL user:
sudo su - postgres
  • Change password of postgres user:
psql -d postgres -U postgres
psql (9.1.3) Type "help" for help
postgres=# alter user postgres with password 'YOUR_NEW_PASSWORD';
ALTER ROLE
postgres=# \q
#logout postgres user
logout
  • Restart the PostgreSQL server:
sudo /etc/init.d/postgresql restart

pgAdmin III: PostgreSQL administration and management tools

  • If pgAdminIII is not installed, the installation is easy:
sudo apt-get install pgadmin3
  • Open pgAdminIII and add new localhost server. Go to menu File > Add Server as the following screenshot:

pgAdmin

Set up pgAdmin III server instrumentation:
When connecting to a PostgreSQL database using pgAdmin you may receive an error letting you know that the server instrumentation is not installed.

  • Install postgresql-contrib package:
sudo apt-get install postgresql-contrib
  • Install adminpack extension:
sudo -u postgres psql
postgres=# CREATE EXTENSION "adminpack";
postgres=# \q