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:
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 !
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
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
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.
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.