在PostgreSQL中生成顺序代码的最佳实践是什么？

Question

Let's say I have to use formatted code for my transaction

for example: 20151208-1-20-xxx : Where xxx is in sequence number (eg: 001,002).

If I have to check the previous transaction code, it would be a problem if the transaction happens in the same time. What is the best practice to generate this kind of code?

Thank you~

Answer 1

You can use a sequence. Create a function for convenience:

create table test (code text);
create sequence test_sequence;

create function next_code()
returns text language sql as $$
select format('20151208-1-20-%s', to_char(nextval('test_sequence'), 'FM000'));
$$;

insert into test values (next_code());
insert into test values (next_code());

select * from test;

       code        
-------------------
 20151208-1-20-001
 20151208-1-20-002
(2 rows)

Note that though it is probably the simplest effective and safe method, it does not provide gapless sequences.

Read more: CREATE SEQUENCE and Sequence Manipulation Functions.

Check a_horse_with_no_name's answer in ROLLBACK event triggers in postgresql for an alternative (gapless) solution.