Thread

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

   Sebastien Flaesch <sebastien.flaesch@4js.com> — 2023-02-06T17:17:17Z

   Hello!
   
   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 ...
   
   Seb
   

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

   shammat@gmx.net — 2023-02-06T17:43:52Z

   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
   
   
   
   

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

   Sebastien Flaesch <sebastien.flaesch@4js.com> — 2023-02-06T18:03:53Z

   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@gmx.net>
   Sent: Monday, February 6, 2023 6:43 PM
   To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.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
   
   
   

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

   Sebastien Flaesch <sebastien.flaesch@4js.com> — 2023-02-06T18:11:25Z

   ... and I would like to automatically return the last generated identity, and also reset the sequence, if a value was specified explicitly for the identity column and this value is greater than the last generated value.
   
   With serial/bigserial/smallserial I am adding a returning clause to each INSERT:
   
   insert into tab1 (name) VALUES ('aaa')
         returning tab1.pkey,
            (select case when tab1.pkey > (select last_value from public.tab1_pkey_seq)
                   then setval('public.tab1_pkey_seq',tab1.pkey,true) else 0 end)
   
   Seb
   ________________________________
   From: Sebastien Flaesch <sebastien.flaesch@4js.com>
   Sent: Monday, February 6, 2023 7:03 PM
   To: Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.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.
   
   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@gmx.net>
   Sent: Monday, February 6, 2023 6:43 PM
   To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.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
   
   
   

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

   Sebastien Flaesch <sebastien.flaesch@4js.com> — 2023-02-07T10:43:06Z

   Just sharing my conclusion so far, maybe this could do the job?
   
   Check the usage of double quotes, to manage upper/lower case in schema and table name.
   This is not needed for the column name, according to the doc of pg_get_serial_sequence().
   
   
   with table_sequence as (
   select '"'||ns.nspname||'"."'||p.relname||'"' tabname, c.attname colname
     from pg_class p
          join pg_attribute c on p.oid=c.attrelid
          join pg_namespace ns on (p.relnamespace=ns.oid)
    where upper(p.relname)=upper('tab1')
      and c.attidentity = 'd'
      and (ns.nspname=current_schema() or ns.oid=pg_my_temp_schema())
   )
   select tabname, colname, pg_get_serial_sequence(tabname,colname)
     from table_sequence;
   
   
   Any better suggestion?
   
   Seb
   ________________________________
   From: Sebastien Flaesch <sebastien.flaesch@4js.com>
   Sent: Monday, February 6, 2023 7:11 PM
   To: Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.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.
   
   ... and I would like to automatically return the last generated identity, and also reset the sequence, if a value was specified explicitly for the identity column and this value is greater than the last generated value.
   
   With serial/bigserial/smallserial I am adding a returning clause to each INSERT:
   
   insert into tab1 (name) VALUES ('aaa')
         returning tab1.pkey,
            (select case when tab1.pkey > (select last_value from public.tab1_pkey_seq)
                   then setval('public.tab1_pkey_seq',tab1.pkey,true) else 0 end)
   
   Seb
   ________________________________
   From: Sebastien Flaesch <sebastien.flaesch@4js.com>
   Sent: Monday, February 6, 2023 7:03 PM
   To: Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.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.
   
   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@gmx.net>
   Sent: Monday, February 6, 2023 6:43 PM
   To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.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
   
   
   

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

   Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2023-02-08T11:07:34Z

   On 07.02.23 11:43, Sebastien Flaesch wrote:
   > select '"'||ns.nspname||'"."'||p.relname||'"' tabname, c.attname colname
   
   Just a side note: You might find the quote_ident() function useful.
   
   
   
   
   

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

   Sebastien Flaesch <sebastien.flaesch@4js.com> — 2023-02-08T13:00:21Z

   Good point, thanks Peter!
   Seb
   ________________________________
   From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
   Sent: Wednesday, February 8, 2023 12:07 PM
   To: Sebastien Flaesch <sebastien.flaesch@4js.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.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.
   
   On 07.02.23 11:43, Sebastien Flaesch wrote:
   > select '"'||ns.nspname||'"."'||p.relname||'"' tabname, c.attname colname
   
   Just a side note: You might find the quote_ident() function useful.