0

I want to create auto-increment column in the form 'YYYYMMDD-xxxxx'.

  • 'YYYYMMDD' is the date the data was inserted.
  • 'xxxxx' is a value that auto increases by 1.

Is this possible? could you tell me how?

Example:

20220101-00001,
20220102-00002,
20220102-00003,
...
20220102-99999,

20220701-00001,
20220701-00002,
...
eloiz
  • 81
  • 9
  • Do you want to actually store it like that, or do you just need this when you run a SELECT? –  Jul 01 '22 at 05:39
  • @a_horse_with_no_name I want to save it like that. – eloiz Jul 01 '22 at 05:40
  • Let me guess: no gaps are allowed, right? And what happens if there are more than 99999 entries per day? Do you get duplicates? – Laurenz Albe Jul 01 '22 at 05:47
  • @LaurenzAlbe Yes. no gap are allowd. It's used in small production lines, so we don't put in more than 100,000 per day. However, duplicate is not allowed. – eloiz Jul 01 '22 at 05:49
  • 1
    You could use the technique mentioned towards the end of [this article](https://www.cybertec-postgresql.com/en/gaps-in-sequences-postgresql/). – Laurenz Albe Jul 01 '22 at 05:59
  • @LaurenzAlbe Thank you for the answer. but I want is a way to put data consisting of today's date and automatic increment. No gap is required, but it's not important. gap is allowed it if necessary. – eloiz Jul 01 '22 at 06:11
  • If gaps are allowed then simply use a sequence. If no gaps are allowed - then simply use 2 columns (one for the date and another for the incremental number) and use `INSERT .... SELECT ....,1+COALESCE(0,MAX(counter)) AS counter FROM tbl WHERE dateColumn = xxx` – IVO GELOV Jul 01 '22 at 07:07
  • @IVOGELOV Thank you for your answer. I think that's the best way. – eloiz Jul 01 '22 at 07:40
  • @IVOGELOV Oh....I was asked that the number should start from number 1 every month. Unfortunately, I don't think I can use this method. – eloiz Jul 01 '22 at 08:58
  • 1
    Why not? You can reset the sequence every 1st of the month at 00:00:01 by some cron. – Nebojsa Susic Jul 01 '22 at 09:16

0 Answers0