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
?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With