CODEWITHSUNDEEP
postgresqlcurrval
Flo
Flo

Reputation: 489

PostgreSQL getting new id during insert

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?

Upvotes: 1

Views: 418

Answers (1)

Mureinik
Mureinik

Reputation: 310983

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

Upvotes: 1

Related Questions