1

I need to create some customer number on record insert, format is 'A' + 4 digits, based on the ID. So record ID 23 -> A0023 and so on. My solution is currently this:

-- Table
create table t (
  id bigserial unique primary key,
  x text,
  y text
);

-- Insert
insert into t (x, y) select concat('A',lpad((currval(pg_get_serial_sequence('t','id')) + 1)::text, 4, '0')), 'test';

This works perfectly. Now my question is ... is that 'safe', in the sense that currval(seq)+1 is guaranteed the same as the id column will receive? I think it should be locked during statement execution. Is this the correct way to do it or is there any shortcut to access the to-be-created ID directly?

Flo
  • 421
  • 4
  • 6

1 Answers1

1

Instead of storing this data, you could just query it each time you needed it, making the whole thing a lot less error-prone:

SELECT id, 'A' + LPAD(id::varchar, 4, '0')
FROM   t
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Hmm ... yes. But actually, the example is a bit simplified for illustration, in practice, the prefix of the customer ID it not constant 'A', so i'd like to really have it stored. – Flo Nov 14 '18 at 19:10
  • @Flo this sounds a bit like an XY problem. Could you unsimplify the question to include some more "meat"? – Mureinik Nov 14 '18 at 21:13
  • Sure. The prefix actually comes from external input (so the actual insert SQL is dynamically created). In fact, it does not really play a role here, as the actual question is whether that sequence numer + 1 is realibly delivering exactly the same as what the ID will be (=will a parallel insert cause any trouble; I don't think it can but not sure). Of course, I could just store the prefix in a column and then dynamically add prefix and LPAD(), but I'd like to avoid that, simply because it will be used in several queries. Maybe use a procedure? Mmmh, works, but really would prefer a plain field. – Flo Nov 14 '18 at 23:01