postgresql 在 Postgres 9.1 中确定表的 OID？

Determining the OID of a table in Postgres 9.1?

提问by Tony Vitabile

Does anyone know how to find the OID of a table in Postgres 9.1? I am writing an update script that needs to test for the existence of a column in a table before it tries to create the column. This is to prevent run of the script after the first from erroring out.

有谁知道如何在 Postgres 9.1 中找到表的 OID？我正在编写一个更新脚本，它需要在尝试创建列之前测试表中列的存在。这是为了防止脚本在第一个错误后运行。

采纳答案by jmelesky

The postgres catalog table pg_classis what you should look at. There should be one row per table, with the table name in the column relname, and the oid in the hidden column oid.

postgres 目录表pg_class是您应该查看的内容。每个表应该有一行，列中为表名，relname隐藏列中为 oid oid。

The catalog tables are in the postgresdatabase, so make sure to connect to that, rather than your application database.

目录表位于postgres数据库中，因此请确保连接到该数据库，而不是您的应用程序数据库。

You may also be interested in the pg_attributecatalog table, which includes one row per table column.

您可能还对pg_attribute目录表感兴趣，其中每个表列包含一行。

See: http://www.postgresql.org/docs/current/static/catalog-pg-class.htmland http://www.postgresql.org/docs/current/static/catalog-pg-attribute.html

请参阅：http: //www.postgresql.org/docs/current/static/catalog-pg-class.html和http://www.postgresql.org/docs/current/static/catalog-pg-attribute.html

回答by Erwin Brandstetter

To get a table OID, cast to the object identifier typeregclass(while connected to the same DB):

要获取表 OID，请转换为对象标识符类型regclass（连接到同一数据库时）：

SELECT 'mytbl'::regclass::oid;

This finds the first table (or view, etc.) with the given name along the search_pathor raises an exception if not found.

这将找到具有给定名称的第一个表（或视图等），search_path如果未找到则引发异常。

Schema-qualify the table name to remove the dependency on the search path:

模式限定表名以删除对搜索路径的依赖：

SELECT 'myschema.mytbl'::regclass::oid;

In Postgres 9.4or later you can also use to_regclass('myschema.mytbl'), which doesn't raise an exception if the table is not found:

在 Postgres 9.4或更高版本中，您还可以使用to_regclass('myschema.mytbl')，如果找不到表，则不会引发异常：

• How to check if a table exists in a given schema

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

Then you only need to query the catalog table pg_attributefor the existence of the column:

那么你只需要查询catalog表pg_attribute中是否存在该列：

SELECT TRUE AS col_exists
FROM   pg_attribute 
WHERE  attrelid = 'myschema.mytbl'::regclass
AND    attname  = 'mycol'
AND    NOT attisdropped  -- no dropped (dead) columns
-- AND attnum > 0        -- no system columns (you may or may not want this)

回答by user3132194

SELECT oid FROM pg_class WHERE relname = 'tbl_name' AND relkind = 'r';

回答by Stefan

Just to complete the possibilities I'd like to add that there exists a syntax for dropping columns in order to no error out:

只是为了完成我想补充的可能性，存在一种用于删除列的语法，以便不会出错：

ALTER TABLE mytbl DROP COLUMN IF EXISTS mycol

ALTER TABLE mytbl DROP COLUMN 如果存在 mycol

See http://www.postgresql.org/docs/9.0/static/sql-altertable.html

见http://www.postgresql.org/docs/9.0/static/sql-altertable.html

Then you can safely add your column.

然后您可以安全地添加您的列。