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 !
