public inbox for [email protected]  

help / color / mirror / Atom feed

From: yanliang lei <[email protected]>
To: [email protected] <[email protected]>
Subject: =?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:_?=
Date: Wed, 1 Oct 2025 18:04:09 +0800 (CST)
Message-ID: <[email protected]> (raw)

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 !

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: =?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:_?=
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox