agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Sebastien FLAESCH <sf@4js.com>
To: pgsql-sql@lists.postgresql.org
Subject: Re: Serial sequence name when table/column name in uppercase
Date: Wed, 31 Mar 2021 16:57:57 +0200
Message-ID: <12c3078e-c980-5446-ff3e-7b6545587cf7@4js.com> (raw)
In-Reply-To: <3167888.1617198904@sss.pgh.pa.us>
References: <cc5742f1-7b3f-5051-e033-e99ff69245e7@4js.com>
	<CAB8KJ=hnuX0aKLj490Q2ryX0aFRg-z-aur=JtiW=7r4-prT+mQ@mail.gmail.com>
	<87d8f930-a87c-2d08-f362-d59655ca6c24@4js.com>
	<3167888.1617198904@sss.pgh.pa.us>

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

view thread (7+ messages)  latest in thread

Message-ID: <12c3078e-c980-5446-ff3e-7b6545587cf7@4js.com>
Permalink:  ../12c3078e-c980-5446-ff3e-7b6545587cf7@4js.com/
Also on:    postgresql.org/message-id/12c3078e-c980-5446-ff3e-7b6545587cf7@4js.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: sf@4js.com, pgsql-sql@lists.postgresql.org
  Subject: Re: Serial sequence name when table/column name in uppercase
  In-Reply-To: <12c3078e-c980-5446-ff3e-7b6545587cf7@4js.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox