How to change primary key of a postgres database table from bill no to bill no and year part of purchase date column(it is oif type date)?

Question

I have a table called product_details in my PostgreSQL database, two columns of this table are bill_no INT and purchase_date DATE.

Currently bill_no is the primary key, but my client wants it changed to bill_no and year of purchase, as bill numbers start with 1 at the start of every year.

Is there a way to change the primary key of the table to bill_no and only the year part of purchase_date?

Answer 1

You can drop a primary key like:

alter table bill drop constraint bill_pkey;

If the key's name is not "bill_pkey", use \d to find its name.

You can add a new primary key like:

alter table bill add constraint bill_pkey primary key (bill_no, purchase_date);

But you only want a key on the year part. Postgres does not support that, but you can use a unique index:

create unique index ux_bill on bill (bill_no, extract(year from purchase_date));

A unique index will do everything a primary key does, but it won't have the primary key label.