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
parameterpotentially 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