public inbox for [email protected]
help / color / mirror / Atom feed=?GBK?Q?_in_the_different_schema_=A3=ACthe_sequence_name_?= =?GBK?Q?is_same=A3=AC_and_a_table's__column_definition_us?= =?GBK?Q?e__this_sequence,so=A3=AChow_can_I__identify__seq?= =?GBK?Q?uence's_schema_name___by_system_view/table:_?=
4+ messages / 3 participants
[nested] [flat]
* =?GBK?Q?_in_the_different_schema_=A3=ACthe_sequence_name_?= =?GBK?Q?is_same=A3=AC_and_a_table's__column_definition_us?= =?GBK?Q?e__this_sequence,so=A3=AChow_can_I__identify__seq?= =?GBK?Q?uence's_schema_name___by_system_view/table:_?=
@ 2025-10-01 10:04 yanliang lei <[email protected]>
2025-10-01 11:07 ` Re: in the different schema ，the sequence name is same， and a table's column definition use this sequence,so，how can I identify sequence's schema name by system view/table: Greg Sabino Mullane <[email protected]>
2025-10-01 12:20 ` Re: in the different schema ，the sequence name is same， and a table's column definition use this sequence,so，how can I identify sequence's schema name by system view/table: David G. Johnston <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: yanliang lei @ 2025-10-01 10:04 UTC (permalink / raw)
To: [email protected] <[email protected]>
hi ，everyone，
my postgresql version is 18.0,
in the different schema ，the sequence name is same， and a table‘s column definition use this sequence，
so，how can I identify sequence's schema name by system view/table?
the following is example:
[pg180@kunpeng3 ~]$ psql -d postgres -U pg180 -p 5418
psql (18.0)
输入 "help" 来获取帮助信息.
postgres=# create database dbversion180
postgres-# ;
CREATE DATABASE
postgres=# \c dbversion180 ;
您现在已经连接到数据库 "dbversion180",用户 "pg180".
dbversion180=# create schema schema_1;
CREATE SCHEMA
dbversion180=# create schema schema_2;
CREATE SCHEMA
dbversion180=# create sequence public.seq_xx_yy;
CREATE SEQUENCE
dbversion180=# create table schema_1.test_tab_100(c1 int default nextval('seq_xx_yy'));
CREATE TABLE
dbversion180=# SELECT table_schema,table_name,column_name,column_default FROM information_schema.columns WHERE column_default LIKE 'nextval%' and table_name='test_tab_100';
table_schema | table_name | column_name | column_default
--------------+--------------+-------------+--------------------------------
schema_1 | test_tab_100 | c1 | nextval('seq_xx_yy'::regclass) ---->> We know: this "seq_xx_yy" sequence's schema is public.
(1 行记录)
dbversion180=# select * from pg_sequences;
schemaname | sequencename | sequenceowner | data_type | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value
------------+--------------+---------------+-----------+-------------+-----------+---------------------+--------------+-------+------------+------------
public | seq_xx_yy | pg180 | bigint | 1 | 1 | 9223372036854775807 | 1 | f | 1 |
(1 行记录)
dbversion180=# create sequence schema_1.seq_xx_yy;
CREATE SEQUENCE
dbversion180=# SELECT table_schema,table_name,column_name,column_default FROM information_schema.columns WHERE column_default LIKE 'nextval%' and table_name='test_tab_100';
table_schema | table_name | column_name | column_default
--------------+--------------+-------------+--------------------------------
schema_1 | test_tab_100 | c1 | nextval('seq_xx_yy'::regclass)
(1 行记录)
dbversion180=#
dbversion180=# select version();
version
-----------------------------------------------------------------------------------
PostgreSQL 18.0 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
(1 行记录)
dbversion180=#
--------------->> how can I identify sequence's schema name by system view/table:
the column c1 in the schema_1.test_tab_100 is associated with which sequence ?? schema_1.seq_xx_yy or public.seq_xx_yy??
thanks !
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: in the different schema ，the sequence name is same， and a table's column definition use this sequence,so，how can I identify sequence's schema name by system view/table:
2025-10-01 10:04 =?GBK?Q?_in_the_different_schema_=A3=ACthe_sequence_name_?= =?GBK?Q?is_same=A3=AC_and_a_table's__column_definition_us?= =?GBK?Q?e__this_sequence,so=A3=AChow_can_I__identify__seq?= =?GBK?Q?uence's_schema_name___by_system_view/table:_?= yanliang lei <[email protected]>
@ 2025-10-01 11:07 ` Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Greg Sabino Mullane @ 2025-10-01 11:07 UTC (permalink / raw)
To: yanliang lei <[email protected]>; +Cc: [email protected] <[email protected]>
On Wed, Oct 1, 2025 at 6:04 AM yanliang lei <[email protected]> wrote:
> the column c1 in the schema_1.test_tab_100 is associated with which
> sequence ?? *schema_1*.seq_xx_yy or *public*.seq_xx_yy??
>
This is better asked on the pgsql-general mailing list, but the short
answer is that you have to look at your search_path as well. Since you just
created schema_1, and public is in your search_path, a plain seq_xx_yy is
the one from the public schema. To see the fully-qualified name, run:
SET search_path = pg_catalog;
before issuing your select from information_schema.columns
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: in the different schema ，the sequence name is same， and a table's column definition use this sequence,so，how can I identify sequence's schema name by system view/table:
2025-10-01 10:04 =?GBK?Q?_in_the_different_schema_=A3=ACthe_sequence_name_?= =?GBK?Q?is_same=A3=AC_and_a_table's__column_definition_us?= =?GBK?Q?e__this_sequence,so=A3=AChow_can_I__identify__seq?= =?GBK?Q?uence's_schema_name___by_system_view/table:_?= yanliang lei <[email protected]>
@ 2025-10-01 12:20 ` David G. Johnston <[email protected]>
2025-10-01 12:38 ` Re: in the different schema ，the sequence name is same， and a table's column definition use this sequence,so，how can I identify sequence's schema name by system view/table: David G. Johnston <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2025-10-01 12:20 UTC (permalink / raw)
To: yanliang lei <[email protected]>; +Cc: [email protected] <[email protected]>
On Wednesday, October 1, 2025, yanliang lei <[email protected]> wrote:
>
> dbversion180=# create table schema_1.test_tab_100(c1 int default
> nextval('seq_xx_yy'));
>
Since you didn’t schema qualify the sequence name every single time a
default value is created the sequence will be looked up anew. The stored
expression is not associated with any specific object.
This is also why there is a separate step to mark a sequence as being owned
by a table. That establishes a dependency that this textual form is unable
to do.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: in the different schema ，the sequence name is same， and a table's column definition use this sequence,so，how can I identify sequence's schema name by system view/table:
2025-10-01 10:04 =?GBK?Q?_in_the_different_schema_=A3=ACthe_sequence_name_?= =?GBK?Q?is_same=A3=AC_and_a_table's__column_definition_us?= =?GBK?Q?e__this_sequence,so=A3=AChow_can_I__identify__seq?= =?GBK?Q?uence's_schema_name___by_system_view/table:_?= yanliang lei <[email protected]>
2025-10-01 12:20 ` Re: in the different schema ，the sequence name is same， and a table's column definition use this sequence,so，how can I identify sequence's schema name by system view/table: David G. Johnston <[email protected]>
@ 2025-10-01 12:38 ` David G. Johnston <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2025-10-01 12:38 UTC (permalink / raw)
To: yanliang lei <[email protected]>; +Cc: [email protected] <[email protected]>
On Wednesday, October 1, 2025, David G. Johnston <[email protected]>
wrote:
> On Wednesday, October 1, 2025, yanliang lei <[email protected]> wrote:
>>
>> dbversion180=# create table schema_1.test_tab_100(c1 int default
>> nextval('seq_xx_yy'));
>>
>
> Since you didn’t schema qualify the sequence name every single time a
> default value is created the sequence will be looked up anew. The stored
> expression is not associated with any specific object.
>
> This is also why there is a separate step to mark a sequence as being
> owned by a table. That establishes a dependency that this textual form is
> unable to do.
>
>
Ignore that…we do stored the parsed representation which nominally has the
schema recorded, it’s just that the text serialization it too “helpful” by
inspecting the search_path and only produces the schema prefix if it would
be necessary to resolve the reference.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-10-01 12:38 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-01 10:04 =?GBK?Q?_in_the_different_schema_=A3=ACthe_sequence_name_?= =?GBK?Q?is_same=A3=AC_and_a_table's__column_definition_us?= =?GBK?Q?e__this_sequence,so=A3=AChow_can_I__identify__seq?= =?GBK?Q?uence's_schema_name___by_system_view/table:_?= yanliang lei <[email protected]>
2025-10-01 11:07 ` Re: in the different schema ，the sequence name is same， and a table's column definition use this sequence,so，how can I identify sequence's schema name by system view/table: Greg Sabino Mullane <[email protected]>
2025-10-01 12:20 ` Re: in the different schema ，the sequence name is same， and a table's column definition use this sequence,so，how can I identify sequence's schema name by system view/table: David G. Johnston <[email protected]>
2025-10-01 12:38 ` Re: in the different schema ，the sequence name is same， and a table's column definition use this sequence,so，how can I identify sequence's schema name by system view/table: David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox