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

I tried to use code from Check if sequence exists in Postgres (plpgsql).

我试图使用Check中的代码来确定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;

6 个解决方案

#1

Postgres 9.5+

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

如果没有EXISTS被添加到Postgres 9.5中的CREATE SEQUENCE中。这是现在的简单解决方案：

CREATE SEQUENCE IF NOT EXISTS myschema.myseq;

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

但是无论如何都要考虑过时答案的细节......你知道串行列，对吗？

• Auto increment SQL function
• 自动增加SQL函数

---

Postgres 9.4 or older

The name of a sequence conflicts with names of objects of several types, not just sequences. 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 you have three cases:

大胆强调我的。所以你有三种情况：

1. Name does not exist. -> Create sequence.
2. 名称不存在。 - >创建序列。
3. Sequence with the same name exists. -> Do nothing? Any output? Any logging?
4. 存在具有相同名称的序列。 - >什么都不做？任何输出？有记录吗？
5. Other conflicting object with the same name exists. -> Do something? Any output? Any logging?
6. 存在具有相同名称的其他冲突对象。 - >做点什么？任何输出？有记录吗？

You need to specify what you want to do in either of these cases. The DO statement could look like this:

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

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$;

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

pg_class中的对象类型（relkind）根据手册：

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

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

Related:

有关：

• How to check if a table exists in a given schema
• 如何检查给定模式中是否存在表

#2

I went a different route: just catch the exception:

我走了一条不同的路线：抓住异常：

DO
$$
BEGIN
        CREATE SEQUENCE myseq;
EXCEPTION WHEN duplicate_table THEN
        -- do nothing, it's already there
END
$$ LANGUAGE plpgsql;

One nice benefit to this is that you don't need to worry about what your current schema is.

这样做的一个很好的好处是您不必担心当前的架构是什么。

#3

If you don't need to preserve the potentially existing sequence, you could just drop it and then recreate it:

如果您不需要保留可能存在的序列，则只需删除它然后重新创建它：

DROP SEQUENCE IF EXISTS id_seq;
CREATE SEQUENCE id_seq;

#4

Postgres doesn't have CREATE SEQUENCE IF NOT EXISTS and if the table has default value using the sequence if you just drop the sequence, you might get error:

Postgres没有CREATE SEQUENCE IF NOT NOT EXISTS如果你只是删除序列，如果表使用序列有默认值，你可能会收到错误：

ERROR: cannot drop sequence (sequence_name) because other objects depend on it SQL state: 2BP01

错误：无法删除序列（sequence_name），因为其他对象依赖于它SQL状态：2BP01

For me, this one can help:

对我来说，这个可以帮助：

ALTER TABLE <tablename> ALTER COLUMN id DROP DEFAULT;
DROP SEQUENCE IF EXISTS <sequence_name>;
CREATE sequence <sequence_name>;

#5

The information about sequences can be retrieved from information_schema.sequences (reference)

可以从information_schema.sequences（参考）中检索有关序列的信息。

Try something like this (untested):

尝试这样的事情（未经测试）：

...
IF not EXISTS (SELECT * FROM information_schema.sequences
    WHERE sequence_schema = 'firma1' AND sequence_name = 'myseq') THEN
...

#6

I have a function to clean all tables in my database application at any time. It is build dynamically, but the essence is that it deletes all data from each table and resets the sequence. This is the code to reset the sequence of one of the tables:

我有一个函数可以随时清理我的数据库应用程序中的所有表。它是动态构建的，但实质是它会删除每个表中的所有数据并重置序列。这是重置其中一个表序列的代码：

perform relname from pg_statio_all_sequences where relname = 'privileges_id_seq';
if found then
  select setval ('privileges_id_seq',1, false) into i_result;
end if;

Hope this helps,

希望这可以帮助，

Loek

Loek

I am using postgres 8.4, I see that you use 9.2. Could make a difference where the information is stored.

我使用postgres 8.4，我看到你使用9.2。可以在存储信息的地方产生影响。