Auto increment one column of a 3-column composite primary key in postgresql table

user_11814996 0
Viewed 1254

I have three columns as a composite primary key in a table of PostgreSql database. Data for two columns will be provided in insert query. But another column has to be incremented every time insertion happens. Here's an example:

CREATE TABLE "food_index_directory" (
  "primary_key_one"          VARCHAR(6) NOT NULL,
  "primary_key_two"          NUMERIC(4) NOT NULL,
  "primary_key_three"        NUMERIC(5) NOT NULL,
  "varchar_column_one"       VARCHAR(30) NOT NULL,
  "numeric_column_one"       INTEGER NOT NULL,
  "numeric_column_two"       NUMERIC(12,9) DEFAULT .001 NOT NULL,
  "numeric_column_three"     NUMERIC(5,2),
  "last_upd_date"            DATE NOT NULL,
CONSTRAINT "PK_dummy_table" PRIMARY KEY ("primary_key_one","primary_key_two","primary_key_three")
);

As I said above, data for two primary key columns and another(primary_key_three) has to be incremented based on number of occurrences of first and second key columns. Second key column can only take values from 1 to 12, this third column is a count column for second column.
ex:

primary_key_one primary_key_two primary_key_three     ......
91                    1            1                 ......
91                    1            2                 ......
 91                    2            1                 ......
 91                    2            2                 ......
91                    1            3                 ......
 91                    2            3                 ......
  91                    3            1                 ......
 91                    2            4                 ......
  34                    3            1                 ......
91                    1            4                 ......
    91                   4            1                 ......
     91                   5            1                 ......
    34                   4            1                 ......

In the above example, (I have made spaces for better understanding, when data in first two columns are repeated), third column counts number of times first two columns have repeated in this table.

During Insert, I will be providing data for first two columns and the third column has to increment automatically as shown and explained above? How do I do this?

sql-insert postgresql
1 Answers

Find the procedure

CREATE OR REPLACE FUNCTION "fn_trig_pk"()
  RETURNS "pg_catalog"."trigger" AS $BODY$ 
begin
new.primary_key_three = (select count(*)+1 from food_index_directory where primary_key_one=new.primary_key_one and primary_key_two=new.primary_key_two );
return NEW;
end;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

and Trigger for your Table 

CREATE TRIGGER trig_pk
  BEFORE insert 
  ON food_index_directory
  FOR EACH ROW
  EXECUTE PROCEDURE fn_trig_pk();
user_77724830
Related