0

I'm new in PostgreSQL and a little bit confused.

I want to automatically generate unique transaction id with predefined code and attach to it inside the table.

like :

TRX201909260001

The pattern should be: 'TRX' + (yyyy/mm/dd) + (random number)

I've found this question that shows me how to do it like I want, but it is about SQL Server.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Hanifan Risyad
  • 3
  • 1
  • 2
  • 2
    Will [UUID](https://www.postgresql.org/docs/current/uuid-ossp.html)s do? – Laurenz Albe Aug 26 '19 at 08:21
  • Thanks! i already tried it but it generate total unique number. i want some semi-unique like TRX - (yyyy/mm/dd) - (random number) . so it will be like : TRX201908271319 – Hanifan Risyad Aug 27 '19 at 06:57

1 Answers1

1

You could use a sequence:

CREATE SEQUENCE unique_id_seq;

Then you can generate the unique identifier with the following SQL expression:

'TRX' || to_char(current_date, 'YYYYMMDD') || lpad(nextval('unique_id_seq'), 18, '0')

That will not be a random value, and the prefix is not necessary to guarantee uniqueness, but it should fullfill the requirement.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • it should be an easy task, but since I'm newbie here, I'm still confused.. What should I write in pgAdmin? this is my current query: CREATE TABLE master_transaction( trx_id uuid DEFAULT uuid_generate_v4 (), invoice , number_plate VARCHAR(15) NOT NULL, ts TIMESTAMP , length real, width real ); INSERT INTO master_transaction (invoice, number_plate, ts, length, width) VALUES . I want that "trx..." in my invoice column – Hanifan Risyad Aug 28 '19 at 03:56
  • Right - use the expression as `DEFAULT` value for the column. – Laurenz Albe Aug 28 '19 at 06:35