Subject: Re: Serial sequence name when table/column name in uppercase
To: pgsql-sql@lists.postgresql.org
References: <cc5742f1-7b3f-5051-e033-e99ff69245e7@4js.com>
From: Sebastien FLAESCH <sf@4js.com>
Organization: Four Js Development Tools
Message-ID: <bd227041-d01b-e1c3-3103-cadf16bd670c@4js.com>
Date: Wed, 31 Mar 2021 10:58:49 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.8.0
MIME-Version: 1.0
In-Reply-To: <cc5742f1-7b3f-5051-e033-e99ff69245e7@4js.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/bd227041-d01b-e1c3-3103-cadf16bd670c%404js.com>
Precedence: bulk

On 3/31/21 10:35 AM, Sebastien FLAESCH wrote:
> Hello,
> 
> How do I get the sequence name for a serial/bigserial column, of a table
> and/or column name is created with uppercase letters?
> 
> test1=> create table "TAB13" ( "PKEY" BIGSERIAL, "NAME" VARCHAR(50) );
> CREATE TABLE
> 
> test1=> select pg_get_serial_sequence(current_schema||'.tab13','pkey');
> ERROR:  relation "public.tab13" does not exist
> 
> test1=> select pg_get_serial_sequence(current_schema||'.TAB13','PKEY');
> ERROR:  relation "public.tab13" does not exist
> 
> test1=> select * from "TAB13";
>   PKEY | NAME
> ------+------
> (0 rows)
> 
> 
> 
> Seb
> 


Looking at the V13 doc:

https://www.postgresql.org/docs/13/functions-info.html

The description for pg_get_serial_sequence() says:

"The first parameter is a table name with optional schema, and the second parameter is a column name. Because the first parameter potentially contains 
both schema and table names, it is parsed per usual SQL rules, meaning it is lower-cased by default. The second parameter, being just a column name, 
is treated literally and so has its case preserved."


To me it means that since this function has no option to preserve the char case
for the table name, it can't be used when table uses uppercase characters.


Is the only alternative then:

     pg_get_expr(pg_attrdef.adbin,0)

on the default value definition for the serial column, to get for ex:

     nextval('"TAB13_PKEY_seq"'::regclass)

and extract the sequence name from that string using regex for ex?

Seb