agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: 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 10:58:49 +0200
Message-ID: <bd227041-d01b-e1c3-3103-cadf16bd670c@4js.com> (raw)
In-Reply-To: <cc5742f1-7b3f-5051-e033-e99ff69245e7@4js.com>
References: <cc5742f1-7b3f-5051-e033-e99ff69245e7@4js.com>
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
view thread (7+ messages) latest in thread
Message-ID: <bd227041-d01b-e1c3-3103-cadf16bd670c@4js.com>
Permalink: ../bd227041-d01b-e1c3-3103-cadf16bd670c@4js.com/
Also on: postgresql.org/message-id/bd227041-d01b-e1c3-3103-cadf16bd670c@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: <bd227041-d01b-e1c3-3103-cadf16bd670c@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