如果不存在，创建序列 [英] 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:

序列...已存在。

sequence ... already exists.

仅当不存在时才能创建序列？

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 +

如果不存在已添加到Postgres 9.5中的 CREATE SEQUENCE 。这是现在的简单解决方案：

Postgres 9.5+

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 列，对吧？

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

• 自动增量SQL功能

• Auto increment SQL function

序列的名称与几种类型的对象名称冲突，而不仅仅是序列。 手册：

The name of a sequence conflicts with names of objects of several types, not just sequences. The manual:

序列名称必须不同于任何其他
序列，表，索引，视图或外部表的名称模式。

粗体强调我的。

所以你有三种情况：

Bold emphasis mine.
So you have three cases:

1. 名称不存在。 - >创建序列。


2. 同名的序列存在。 - >什么都不做？任何输出？任何日志记录？


3. 存在其他具有相同名称的冲突对象。做某事？任何输出？任何日志记录？

1. Name does not exist. -> Create sequence.
2. Sequence with the same name exists. -> Do nothing? Any output? Any logging?
3. Other conflicting object with the same name exists. -> Do something? Any output? Any logging?

您需要在这些情况中指定要执行的操作。 DO 语句可能如下所示：

You need to specify what you want to do in either of these cases. The 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$;

pg_class 中的目录pg-class.htmlrel =nofollow noreferrer>对象类型（ relkind ）手册：

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

r =普通表

i = index

S =序列

v =视图

m =物化视图

c =复合类型

t = TOAST表

f = foreign表

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

相关：

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

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

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