agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedSerial sequence name when table/column name in uppercase
7+ messages / 3 participants
[nested] [flat]
* Serial sequence name when table/column name in uppercase
@ 2021-03-31 08:35 Sebastien FLAESCH <sf@4js.com>
2021-03-31 08:51 ` Re: Serial sequence name when table/column name in uppercase Ian Lawrence Barwick <barwick@gmail.com>
2021-03-31 08:58 ` Re: Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
0 siblings, 2 replies; 7+ messages in thread
From: Sebastien FLAESCH @ 2021-03-31 08:35 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Serial sequence name when table/column name in uppercase
2021-03-31 08:35 Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
@ 2021-03-31 08:51 ` Ian Lawrence Barwick <barwick@gmail.com>
2021-03-31 10:13 ` Re: Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
1 sibling, 1 reply; 7+ messages in thread
From: Ian Lawrence Barwick @ 2021-03-31 08:51 UTC (permalink / raw)
To: Sebastien FLAESCH <sf@4js.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Serial sequence name when table/column name in uppercase
2021-03-31 08:35 Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
2021-03-31 08:51 ` Re: Serial sequence name when table/column name in uppercase Ian Lawrence Barwick <barwick@gmail.com>
@ 2021-03-31 10:13 ` Sebastien FLAESCH <sf@4js.com>
2021-03-31 13:55 ` Re: Serial sequence name when table/column name in uppercase Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Sebastien FLAESCH @ 2021-03-31 10:13 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Serial sequence name when table/column name in uppercase
2021-03-31 08:35 Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
2021-03-31 08:51 ` Re: Serial sequence name when table/column name in uppercase Ian Lawrence Barwick <barwick@gmail.com>
2021-03-31 10:13 ` Re: Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
@ 2021-03-31 13:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-03-31 14:57 ` Re: Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
2021-03-31 15:13 ` Re: Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2021-03-31 13:55 UTC (permalink / raw)
To: Sebastien FLAESCH <sf@4js.com>; +Cc: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Serial sequence name when table/column name in uppercase
2021-03-31 08:35 Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
2021-03-31 08:51 ` Re: Serial sequence name when table/column name in uppercase Ian Lawrence Barwick <barwick@gmail.com>
2021-03-31 10:13 ` Re: Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
2021-03-31 13:55 ` Re: Serial sequence name when table/column name in uppercase Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-03-31 14:57 ` Sebastien FLAESCH <sf@4js.com>
1 sibling, 0 replies; 7+ messages in thread
From: Sebastien FLAESCH @ 2021-03-31 14:57 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Serial sequence name when table/column name in uppercase
2021-03-31 08:35 Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
2021-03-31 08:51 ` Re: Serial sequence name when table/column name in uppercase Ian Lawrence Barwick <barwick@gmail.com>
2021-03-31 10:13 ` Re: Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
2021-03-31 13:55 ` Re: Serial sequence name when table/column name in uppercase Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-03-31 15:13 ` Sebastien FLAESCH <sf@4js.com>
1 sibling, 0 replies; 7+ messages in thread
From: Sebastien FLAESCH @ 2021-03-31 15:13 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Serial sequence name when table/column name in uppercase
2021-03-31 08:35 Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
@ 2021-03-31 08:58 ` Sebastien FLAESCH <sf@4js.com>
1 sibling, 0 replies; 7+ messages in thread
From: Sebastien FLAESCH @ 2021-03-31 08:58 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-03-31 15:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-31 08:35 Serial sequence name when table/column name in uppercase Sebastien FLAESCH <sf@4js.com>
2021-03-31 08:51 ` Ian Lawrence Barwick <barwick@gmail.com>
2021-03-31 10:13 ` Sebastien FLAESCH <sf@4js.com>
2021-03-31 13:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-03-31 14:57 ` Sebastien FLAESCH <sf@4js.com>
2021-03-31 15:13 ` Sebastien FLAESCH <sf@4js.com>
2021-03-31 08:58 ` Sebastien FLAESCH <sf@4js.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox