小编典典

如何检查给定模式中是否存在表

all

Postgres 8.4 和更高版本的数据库包含模式中的公共表和public模式中的公司特定表company
company架构名称始终以公司编号开头'company'和结尾。
所以可能有这样的模式:

public
company1
company2
company3
...
companynn

应用程序始终适用于一家公司。相应地在 odbc 或 npgsql 连接字符串中指定,例如
search_path

search_path='company3,public'

你将如何检查给定的表是否存在于指定的companyn模式中?

例如:

select isSpecific('company3','tablenotincompany3schema')

应该返回false,并且

select isSpecific('company3','tableincompany3schema')

应该返回true

在任何情况下,该函数都应该只检查companyn传递的模式,而不是其他模式。

如果给定的表同时存在于public传递的模式中,则该函数应返回true.
它应该适用于 Postgres 8.4 或更高版本。


阅读 157

收藏
2022-06-28

共1个答案

小编典典

这取决于您要 _ 准确_ 测试的内容。

信息架构?

要查找“表是否存在”( 无论是谁询问 ) ,严格来说,查询信息架构 ( information_schema.tables) 是
不正确的,因为( 根据文档):

仅显示当前用户有权访问的那些表和视图(通过成为所有者或具有某些特权)。

提供的查询可以返回FALSE,但表仍然可以存在。它回答了这个问题:

如何检查表(或视图)是否存在,当前用户是否可以访问它?

SELECT EXISTS (
   SELECT FROM information_schema.tables 
   WHERE  table_schema = 'schema_name'
   AND    table_name   = 'table_name'
   );

信息模式主要用于保持跨主要版本和跨不同 RDBMS 的可移植性。但是实现起来很慢,因为 Postgres
必须使用复杂的视图来遵守标准(information_schema.tables这是一个相当简单的例子)。并且一些信息(如
OID)在系统目录的翻译中丢失了——这些目录 实际上 携带了所有信息。

系统目录

你的问题是:

如何检查表是否存在?

SELECT EXISTS (
   SELECT FROM pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'schema_name'
   AND    c.relname = 'table_name'
   AND    c.relkind = 'r'    -- only tables
   );

直接使用系统目录pg_classpg_namespace这也快得多。但是,根据以下文档pg_class

目录对pg_class表和大多数其他具有列或与表类似的内容进行编目。这包括 索引 (但另见pg_index)、 序列
视图物化视图复合类型TOAST 表

对于这个特定的问题,您还可以使用系统视图
pg_tables

。跨主要 Postgres 版本更简单、更便携(这个基本查询几乎不关心):

SELECT EXISTS (
   SELECT FROM pg_tables
   WHERE  schemaname = 'schema_name'
   AND    tablename  = 'table_name'
   );

标识符在上述 所有 对象中必须是唯一的。如果你想问:

如何检查给定模式中的表或类似对象的名称是否被采用?

SELECT EXISTS (
   SELECT FROM pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'schema_name'
   AND    c.relname = 'table_name'
   );

替代方案:强制转换为[

regclass](https://www.postgresql.org/docs/current/datatype-oid.html)

SELECT 'schema_name.table_name'::regclass

如果(可选的模式限定)表(或占用该名称的其他对象)不存在,则会 引发异常。

如果您不对表名进行模式限定,则强制转换regclass默认为
search_path
并返回找到的第一个表的 OID
-
如果表不在列出的模式中,则返回异常。请注意,系统架构pg_catalogpg_temp(当前会话的临时对象的架构)自动成为search_path.

像上面这样的查询避免了可能的异常,因此速度稍快。

to_regclass(rel_name)在 Postgres 9.4+

现在简单多了:

SELECT to_regclass('schema_name.table_name');

与演员相同, 它返回......

… null 而不是在找不到名称时抛出错误

2022-06-28