Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

From: Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com>
To: Thomas Kellerer <shammat(at)gmx(dot)net>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column
Date: 2023-02-06 18:03:53
Message-ID: DBAP191MB1289E9989C9F934FC4E54979B0DA9@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I am writing generic code and don't know in advance the name of identity column.

Only the table name is known (parsing of INSERT INTO tabname statement is easy)

Column names may use uppercase or lowercase letters.

I guess I could figure out what column is defined as identity, from pg_attrribute.attidentity = 'd' / 'a', since I know the table name...

For serial/bigserial/smallserial types, I am using:

select ns.nspname||'.'||substring(pg_get_expr(a.adbin,0) from 'nextval.''([^'']*)') seqname, c.attname
from pg_class p
join pg_attrdef a on (p.oid=a.adrelid)
join pg_attribute c on (p.oid=c.attrelid and a.adnum=c.attnum)
join pg_namespace ns on (p.relnamespace=ns.oid)
where upper(p.relname)=upper('tab1')
and pg_get_expr(a.adbin,0) like 'nextval%'
and (ns.nspname=current_schema() or ns.oid=pg_my_temp_schema());

I hope it's possible to do something similar for identity columns.

Seb

________________________________
From: Thomas Kellerer <shammat(at)gmx(dot)net>
Sent: Monday, February 6, 2023 6:43 PM
To: pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

EXTERNAL: Do not click links or open attachments if you do not recognize the sender.

Sebastien Flaesch schrieb am 06.02.2023 um 18:17:
> Assuming that a sequence is used to implement |GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY|
>
> Is there any built-in function that returns the underlying sequence name used for such column?
>
> Otherwise, an SQL query to return the sequence name?
>
> I need the sequence name, in order to reset it (setval) or to get the last generated value (currval) ...
>
> The query must work with all PostgreSQL versions 10 to 15 ...

Despite its name pg_get_serial_sequence() also works for identity columns

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sebastien Flaesch 2023-02-06 18:11:25 Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column
Previous Message David G. Johnston 2023-02-06 18:02:21 Re: plpgsql: ambiguous column reference in ON CONFLICT clause