I'm working on a contract database in PostgreSQL. One column in my database is the contract number/id and should be formatted like ID/Year. I have a column with start of contract date from which I should take the Year from.
Right now I have the ID as a Serial that auto-increments but without the Year part.
I have
ID | DATE_FROM
-----------------------
1 | 2016-10-01
2 | 2016-11-01
3 | 2017-01-01
I need
ID | DATE_FROM
-----------------------
1/2016 | 2016-10-01
2/2016 | 2016-11-01
1/2017 | 2017-01-01
I'm new to PostgreSQL and I don't know how to do it but I'm sure there is a way to do it in PostgreSQL only and I will be glad if anyone could help.
You should change the field name "FROM" other wise you will confuse the keyword FROM
SELECT ID::text || '/' || EXTRACT(YEAR FROM "FROM"),
"FROM"
FROM yourTable;
EDIT: For restart the counter by year you use ROW_NUMBER()
SELECT ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM "FROM")
ORDER BY "ID")::text ||
'/' || EXTRACT(YEAR FROM "FROM"),
"FROM"
FROM yourTable;
I assume the ID are in chronological order, other wise you can change the partition order to ORDER BY "FROM"