CODEWITHSUNDEEP
databasepostgresql
Bartosz Ociepka
Bartosz Ociepka

Reputation: 57

ID with Year formatting in PostgreSQL

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.

Upvotes: 0

Views: 166

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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"

Upvotes: 1

Related Questions