Обсуждение: Serial sequence name when table/column name in uppercase
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
2021年3月31日(水) 17:35 Sebastien FLAESCH <sf@4js.com>:
>
> 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
Somewhat counterintuitively:
postgres=# SELECT pg_get_serial_sequence('"TAB13"','PKEY');
pg_get_serial_sequence
-------------------------
public."TAB13_PKEY_seq"
(1 row)
Regards
Ian Barwick
--
EnterpriseDB: https://www.enterprisedb.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 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
On 3/31/21 10:51 AM, Ian Lawrence Barwick wrote:
> 2021年3月31日(水) 17:35 Sebastien FLAESCH <sf@4js.com>:
>>
>> 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
>
> Somewhat counterintuitively:
>
> postgres=# SELECT pg_get_serial_sequence('"TAB13"','PKEY');
> pg_get_serial_sequence
> -------------------------
> public."TAB13_PKEY_seq"
> (1 row)
>
> Regards
>
> Ian Barwick
>
> --
> EnterpriseDB: https://www.enterprisedb.com
>
>
Ok thanks a lot I got it now, must specify double-quotes around table name.
test1=> select pg_get_serial_sequence(current_schema||'."TAB13"','PKEY');
pg_get_serial_sequence
-------------------------
public."TAB13_PKEY_seq"
Seb
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
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
Side notes:
1) Seems ALTER SEQUENCE xxx START WITH 100 does not have any
impact on serial/bigserial sequences... expected?
2) I know there is a setval() function. But I was looking for
ALTER SEQUENCE to not produce a result set.
Any way to execute setval() without a preceding SELECT?
test1=> setval('public.tab1_pkey_seq',100,false);
ERROR: syntax error at or near "setval"
LINE 1: setval('public.tab1_pkey_seq',100,false)
^
test1=> select setval('public.tab1_pkey_seq',100,false);
setval
--------
100
(1 row)
I am using the C interface libpq.so ... I assume it's not a
problem to use SELECT setval() but was wondering if there is
a smarter solution.
Seb