PostgreSQL serial pattern

Question

Is it possible to declare a serial field in Postgres (9.0) which will increment based on a pattern?

For example:

 Pattern: YYYY-XXXXX
 where YYYY is a year, and XXXXX increments from 00000 - 99999.

Or should I just use a trigger?

EDIT: I prefer the year to be auto-determined based, maybe, on server date. The XXXXX part does start with 00000 for each year and "resets" to 00000 then increments again to 99999 when the year part is modified.

I don't see anything in the AutoIncrement documentation that suggests that you can supply a pattern. Looks like a Trigger is probably your best bet. — Robert Harvey, Jan 10 '13 at 06:34
Oh, how sad. I cannot also find something like this in Google. I'll accept your answer if you can repost it below. — shippou, Jan 10 '13 at 06:51
I agree with Robert Harvey，may be you shoud format the value in your program before insert into the table. — francs, Jan 10 '13 at 07:10
Thanks for the reaffirmation. :) I was hoping there was an "automatic" way of doing this, but sadly there is currently no way of automatically doing it (except via triggers). — shippou, Jan 10 '13 at 07:24
Is it implied by the pattern that the rightmost counter should be reset when the year changes? Is YYYY always the year of the insert, or any year? The question is too vague. — Daniel Vérité, Jan 10 '13 at 10:39
@shippou: This can be done without triggers. Clarify your question. How is the year determined? Does the XXX part start with 0 for each year? What range of years is possible? — Erwin Brandstetter, Jan 10 '13 at 14:19
@ErwinBrandstetter: I do prefer the year to be auto-determined based, maybe, on server date. the XXXXX part does start with 0 for each year. Edited the question. — shippou, Jan 11 '13 at 02:17

score 2 · Accepted Answer · edited May 23 '17 at 10:24

2

I would create a separate SEQUENCE for each year, so that each sequence keeps track of one year - even after that year is over, should you need more unique IDs for that year later.

This function does it all:
_{Improved with input from @Igor and @Clodoaldo in the comments.}

CREATE OR REPLACE FUNCTION f_year_id(y text = to_char(now(), 'YYYY'))
  RETURNS text AS
$func$
BEGIN

LOOP
   BEGIN
      RETURN y ||'-'|| to_char(nextval('year_'|| y ||'_seq'), 'FM00000');

   EXCEPTION WHEN undefined_table THEN   -- error code 42P01
      EXECUTE 'CREATE SEQUENCE year_' || y || '_seq MINVALUE 0 START 0';
   END;
END LOOP;

END
$func$ LANGUAGE plpgsql VOLATILE;

Call:

SELECT f_year_id();

Returns:

2013-00000

Basically this returns a text of your requested pattern. Automatically tailored for the current year. If a sequence of the name year_<year>_seq does not exist yet, it is created automatically and nextval() is retried.

Note that you cannot have an overloaded function without parameter at the same time (like my previous example), or Postgres will not know which to pick and throw an exception in despair.

Use this function as DEFAULT value in your table definition:

CREATE TABLE tbl (id text DEFAULT f_year_id(), ...)

Or you can get the next value for a year of your choice:

SELECT f_year_id('2012');

Tested in Postgres 9.1. Should work in v9.0 or v9.2 just as well.

To understand what's going on here, read these chapters in the manual:

CREATE FUNCTION
CREATE SEQUENCE
39.6.3. Simple Loops
39.5.4. Executing Dynamic Commands
39.6.6. Trapping Errors
Appendix A. PostgreSQL Error Codes
Table 9-22. Template Pattern Modifiers for Date/Time Formatting

edited May 23 '17 at 10:24

Community

1
1

answered Jan 11 '13 at 03:30

Erwin Brandstetter

605,456
145
1,078
1,228

Thank you very much for the ideas and code! I've never thought of this. :) – shippou Jan 11 '13 at 04:23
1

Why dynamic SQL for the `SELECT` ? The whole `EXECUTE .. RETURN;` can be replaced with something like `RETURN y ||'-'|| to_char(nextval('year_'|| y ||'_seq'), 'FM00000');` – Ihor Romanchenko Jan 11 '13 at 10:08
@IgorRomanchenko: Good point. Dynamic SQL is only needed for the sequence creation. I improved the answer with your hint. Adapted the `RETURN` type to agree with it. – Erwin Brandstetter Jan 11 '13 at 15:08
To make the _even after that year is over_ part true you should pass the year as a parameter. – Clodoaldo Neto Jan 11 '13 at 16:03
@Clodoaldo: Well, the sequence exists and can be used independently of the function once created. I inlined the date because the OP asks for the `year to be auto-determined`. – Erwin Brandstetter Jan 11 '13 at 16:06
1

It would be auto-determined if `(id text DEFAULT f_year_id(extract(year from now())), ...)`. Then later he would not need to discover the sequence name nor how to operate it. – Clodoaldo Neto Jan 11 '13 at 16:12
@Clodoaldo: That's a good idea! I incorporated it into the answer. Added a warning to go along with it. – Erwin Brandstetter Jan 11 '13 at 16:24
@Erwin And in the process I learned how to create an optional parameter! :) – Clodoaldo Neto Jan 11 '13 at 16:30

score 1 · Answer 2 · answered Jan 10 '13 at 12:31

1

You can create a function that will form this value (YYYY-XXXXX) and set this function as a default for a column.

Details here.

answered Jan 10 '13 at 12:31

Ihor Romanchenko

26,995
8
48
44

Oh. Nice idea! :) This is a cleaner approach, IMHO, than defining an explicit trigger. – shippou Jan 11 '13 at 02:14

PostgreSQL serial pattern

2 Answers2