Subject: 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:

From: "yanliang lei" <[email-masked]>
Date: Wed, 1 Oct 2025 18:04:09 +0800 (CST)

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 !

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 1 Oct 2025 07:07:57 -0400

On Wed, Oct 1, 2025 at 6:04 AM yanliang lei <[email-masked]> 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

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 1 Oct 2025 07:20:34 -0500

> dbversion180=# create table schema_1.test_tab_100(c1 int default

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.

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 1 Oct 2025 07:38:12 -0500

On Wednesday, October 1, 2025, David G. Johnston <[email-masked]>
wrote:

> 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.