如果不存在，如何创建序列 [英] How to create sequence if not exists

问题描述

我尝试使用 检查 Postgres (plpgsql) 中是否存在序列.

如果序列不存在，则创建序列.运行此代码两次导致异常:

To create sequence if it does not exists. Running this code two times causes an exception:

序列...已经存在.

如果序列不存在，如何创建序列?

How to create sequence only if it does not exist?

如果序列不存在，则不应写入任何消息，也不应发生错误，因此我无法在此问题的另一个答案中使用存储过程，因为如果序列存在，它每次都会将消息写入日志文件.

If the sequence does not exist, no message should be written and no error should occur so I cannot use the stored procedure in the other answer to this question since it writes message to log file every time if sequence exists.

do $$
begin

SET search_path = '';
IF not EXISTS (SELECT * FROM pg_class
             WHERE relkind = 'S'
               AND oid::regclass::text = 'firma1.' || quote_ident('myseq'))
  THEN

SET search_path = firma1,public;

create sequence myseq;

END IF;

SET search_path = firma1,public;

end$$;

select nextval('myseq')::int as nr;

推荐答案

Postgres 9.5 或更高版本

IF NOT EXISTS 已添加到 在 Postgres 9.5 中创建序列.这就是现在的简单解决方案:

Postgres 9.5 or later

IF NOT EXISTS was added to CREATE SEQUENCE in Postgres 9.5. That's the simple solution now:

CREATE SEQUENCE IF NOT EXISTS myschema.myseq;

但无论如何都要考虑过时答案的细节......
您知道 serial 或 IDENTITY 列，对吗?

But consider details of the outdated answer anyway ...
And you know about serial or IDENTITY columns, right?

• 自增表列

序列与其他几个类似表的对象共享命名空间.手册:

Sequences share the namespace with several other table-like objects. The manual:

序列名称必须与任何其他名称不同同一架构中的序列、表、索引、视图或外部表.

The sequence name must be distinct from the name of any other sequence, table, index, view, or foreign table in the same schema.

粗体强调我的.所以分为三种情况:

Bold emphasis mine. So there are three cases:

1. 名称不存在.-> 创建序列.
2. 存在同名序列.-> 什么都不做?有输出吗?任何日志记录?
3. 存在其他具有相同名称的冲突对象.-> 做点什么?有输出吗?任何日志记录?

指定在任何一种情况下要做什么.DO 语句可能如下所示:

Specify what to do in either case. A DO statement could look like this:

DO
$do$
DECLARE
   _kind "char";
BEGIN
   SELECT relkind
   FROM   pg_class
   WHERE  oid = 'myschema.myseq'::regclass  -- sequence name, optionally schema-qualified
   INTO  _kind;

   IF NOT FOUND THEN       -- name is free
      CREATE SEQUENCE myschema.myseq;
   ELSIF _kind = 'S' THEN  -- sequence exists
      -- do nothing?
   ELSE                    -- object name exists for different kind
      -- do something!
   END IF;
END
$do$;

relkind 中的对象类型 (relkind)code>pg_class 根据手册:

Object types (relkind) in pg_class according to the manual:

r = 普通表
我 = 索引
S = 序列
v = 视图
m = 物化视图
c = 复合类型
t = TOAST 表
f = 外部表

r = ordinary table
i = index
S = sequence
v = view
m = materialized view
c = composite type
t = TOAST table
f = foreign table

相关:

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

这篇关于如果不存在，如何创建序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！