1

I am trying to convert the following SQL Server T-SQL block to a PostgreSQL compatible one using, preferably CTE or a function. The issue with converting is I am not able to figure out how I return the last inserted id (scope_identity() in SQL Server returns the last inserted id in the identity column).

 IF (
        NOT EXISTS (
            SELECT p_id
            FROM qr_request
            WHERE q_ota = 'Done'
                            )
        )
BEGIN
    INSERT INTO qr_request (
        u_id
        ,c_id
        ,r_id
        ,q_ota
        )
    VALUES (
        1
        ,2
        ,3
        ,'P'
        )

    COMMIT

    SELECT Scope_Identity() AS id
        ,p.email AS n
    FROM person p
    WHERE p.uid = 33
        AND deleted = 0
        
END
ELSE
    SELECT NULL AS id
        ,p.email AS n
    FROM person p
    WHERE p.uid = 33
        AND deleted = 0

Any help would be appreciated.

1

Since PostgreSQL doesn't use the same traditional "ID" concept on Tables like SQL Server, rather it uses Sequences, getting the last value is a little different but there are a multitude of ways to do so as noticed in this well versed StackOverflow answer.

Specifically the functions you can leverage are CURRVAL and LASTVAL.

Relevant info from the aforementioned and linked StackOverflow answer:

CURRVAL():

INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval('persons_id_seq');

The name of the sequence must be known. Caveat: currval() only works after an INSERT (which has executed nextval() ), in the same session.

LASTVAL():

This is similar to the previous, only that you don't need to specify the sequence name: it looks for the most recent modified sequence (always inside your session, same caveat as above.)

9
  • Does that mean I'll have to create a sequence first? – Rahul Dec 14 '20 at 12:57
  • 1
    @Rahul: no. If you define the column as generated always as identity, Postgres will automatically manage the sequence for you – a_horse_with_no_name Dec 14 '20 at 12:58
  • As a_horse_with_no_name indicates, it's a similar concept to ID in SQL Server, mostly just semantically different. But the concept of IDENTITY carries across database systems. – J.D. Dec 14 '20 at 13:00
  • 1
    @Rahul I'll add, if it's not already an identity column then you won't be able to get the last ID that was generated. The same is true in SQL Server. The SCOPE_IDENTITY() function only works on Tables where the column is defined as an IDENTITY. Otherwise this too would not be doable in SQL Server. – J.D. Dec 14 '20 at 13:08
  • 1
    Thanks everyone. This dba.stackexchange.com/questions/281511/… would be helpful. – Rahul Dec 14 '20 at 13:24
0

As there is no IF in SQL (or Postgres), I would rewrite this to use a data modifying common table expression:

with new_request as (
  INSERT INTO qr_request (u_id,c_id,r_id,q_ota)
  select * 
  from ( VALUES (1,2,3,'P') ) as x(u_id,c_id,r_id,q_ota)
  where not exists (SELECT p_id
                    FROM qr_request
                    WHERE q_ota = 'Done')
  returning id --<< returns the generated ID if an INSERT happened
) 
select nr.id, 
       p.email AS n
FROM person p
  left join new_request nr on true
WHERE p.uid = 33
  AND deleted = 0
;

Obviously qr_request.id must be defined such that it automatically generates a new value during insert. The recommended way to do this, is using generated always as identity. So if you have this in SQL Server:

create table qr_request
(
  id   integer identity primary key
  u_id integer, 
  c_id integer, 
  r_id integer, 
  q_ota varchar(5)
);

you would use the following in Postgres:

create table qr_request
(
  id   integer primary key generated always as identity
  u_id integer, 
  c_id integer, 
  r_id integer, 
  q_ota varchar(5)
);
5
  • doesn't returning id here looks like it is being returned from new_request ? The original query has scope_identity() being returned from person – Rahul Dec 14 '20 at 13:07
  • @Rahul: but the insert was done into qr_request so scope_identity() can only return the generated "ID" from that table, not from the person table. – a_horse_with_no_name Dec 14 '20 at 13:11
  • are you saying that the query I shared will return the "id" from qr_request and not person table ? – Rahul Dec 14 '20 at 13:19
  • Yes, that's what happens – a_horse_with_no_name Dec 14 '20 at 13:21
  • Ok. Thanks a_horse_with_no_name – Rahul Dec 14 '20 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.