Re: Serial sequence name when table/column name in uppercase

On 3/31/21 3:55 PM, Tom Lane wrote:
> Sebastien FLAESCH <sf@4js.com> writes:
>> Ok thanks a lot I got it now, must specify double-quotes around table name.
> 
> You could use quote_ident() if you don't want to be bothered with the
> rules for when to double-quote.  Also, if you have the table's OID at
> hand in the query, oid::regclass::text will produce a properly quoted
> and schema-qualified name.
> 
>             regards, tom lane
> 

Thanks for the tip!

In fact table names are supposed to be all lowercase, since created with
CREATE TABLE tab1 without using double quotes.

This is best practice obviously.

I have tested with double quotes and uppercase letters in table names
because pg_get_serial_sequence() was returning NULL. I realized now
that the column was created as an INTEGER instead of SERIAL...

For the background, imagine you have:

create table tab1 (pkey serial primary key,name varchar(50))

I need to deduce the underlying sequence name just from the table name.

The sequence name will then be used to automatically to retrieve the last
generated serial, and potentially update the sequence if an explicit
value is given:

insert into tab1 (name) VALUES ('bbb')
   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)

I assume that the table is in the current schema or in the temp tab schema
because it's a temp table.

So I use now the following SELECT to get the sequence name:

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 think this is better than my previous query, that was using:

select pg_get_serial_sequence(ns.nspname||'.'||p.relname,c.attname)
    ...


Seb