nextval and curval confusion in PostgreSQL

What will happen if multiple user run the following query at the same time?

INSERT INTO "Retail"."Attributes"("AttributeId","AttributeCode","AttributeName")
VALUES(nextval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass),'COL','Color');

INSERT INTO "Retail"."AttributeDetails"  
  ("AttributeId","AttributeDetailCode","AttributeDetailName")
VALUES
(
  currval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass), 'COL-Red', 'Color Red'
),
(
  currval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass), 'COL-Blu', 'Color Blue'
), 
(
  currval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass), 'COL-Gre', 'Color Green'
);

Is this as method as reliable as SCOPE_IDE开发者_开发百科NTITY() of SQL SERVER? Any suggestion will be highly appreciated.

currval() is session specific. If a separate session increments your sequence, currval() will continue to return your expected value.

That said you may want to use insert returning:

insert into foo values (...) returning id;

insert into bar (foo_id, ...) values (:id, ...),  (:id, ...), ...;

Based on the documentation - 'multiple sessions are guaranteed to allocate distinct sequence values'

So you will get distinct values but depending on the 'cache' setting specified while creating the sequence object, the values obtained across various concurrent sessions might not always be sequential. Read the documentation on sequences, especially the 'cache' parameter.

Your usage of currval is perfectly OK

I don't know SCOPE_IDENTITY() so I cannot compare the two concepts.

Btw: you don't need the cast in your statement:

currval('"Retail"."CompoundUnits_CompoundUnitId_seq"')

is enough