Обсуждение: 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:

Поиск
Список
Период
Сортировка

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:

От
"yanliang lei"
Дата:
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 !

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
Дата:
On Wed, Oct 1, 2025 at 6:04 AM yanliang lei <msdnchina@163.com> 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

--
Enterprise Postgres Software Products & Tech Support

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"
Дата:
On Wednesday, October 1, 2025, yanliang lei <msdnchina@163.com> 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.

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"
Дата:
On Wednesday, October 1, 2025, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, October 1, 2025, yanliang lei <msdnchina@163.com> 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.