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

Question

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?

I don't know how to write a trigger. Will you please be able to write it and answer my question @AkhileshMishra? — Naveen Reddy Marthala
– Naveen Reddy Marthala, Commented May 20, 2020 at 17:04

Akhilesh Mishra · Accepted Answer · 2020-05-20 17:15:12Z

1

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

answered May 20, 2020 at 17:15

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Naveen Reddy Marthala Over a year ago

I now have one doubt. After this trigger, can i not specify value for primary_key_three in INSERT statement and will it be executed without issues?

Akhilesh Mishra Over a year ago

if you specify the value for the column primary_key_three, trigger will overwrite it as per its logic. if you will not provide the value for column it will add the value for primary_key_three

Naveen Reddy Marthala Over a year ago

I mean to ask, after this not specify the primary_key_three column name and also not prove any value for it, like fire example:

INSERT INTO food_index_directory (primary_key_one, primary_key_two, varchar_column_one, numeric_column_one, numeric_column_two, numeric_column_three VALUES (value1, value2,,,,,,,);

Naveen Reddy Marthala Over a year ago

Thanks for the fast response.

Akhilesh Mishra Over a year ago

No, "fn_trig_pk"() can be replaced with "schema_name"."trigger_name". RETURNS "pg_catalog"."trigger" you have to keep as it is.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related