SQL 如果不存在如何创建序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13752885/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create sequence if not exists
提问by Andrus
I tried to use code from Check if sequence exists in Postgres (plpgsql).
我尝试使用Check if sequence exists in 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;
回答by Erwin Brandstetter
Postgres 9.5+
Postgres 9.5+
IF NOT EXISTSwas added to CREATE SEQUENCEin Postgres 9.5. That's the simple solution now:
IF NOT EXISTSCREATE SEQUENCE在 Postgres 9.5 中添加。这是现在的简单解决方案：
CREATE SEQUENCE IF NOT EXISTS myschema.myseq;
But consider details of the outdated answer anyway ...
And you know about serialcolumns, right?
但无论如何都要考虑过时答案的细节......
你知道serial列，对吧？
Postgres 9.4 or older
Postgres 9.4 或更高版本
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 tablein the same schema.
序列名称必须与同一架构中的任何其他 序列、表、索引、视图或外部表的名称不同。
Bold emphasis mine.
So you have three cases:
大胆强调我的。
所以你有三种情况：
- Name does not exist. -> Create sequence.
- Sequence with the same name exists. -> Do nothing? Any output? Any logging?
- Other conflicting object with the same name exists. -> Do something? Any output? Any logging?
- 名称不存在。-> 创建序列。
- 存在同名序列。-> 什么都不做？有输出吗？有记录吗？
- 存在其他具有相同名称的冲突对象。 -> 做点什么？有输出吗？有记录吗？
You need to specify what you want to do in either of these cases. The DOstatement 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_classaccording to the manual:
根据手册中的对象类型 ( relkind)pg_class：
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:
有关的：
回答by Joe Shaw
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.
这样做的一个好处是您无需担心当前的架构是什么。
回答by Evan Siroky
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;
回答by Osify
Postgres doesn't have CREATE SEQUENCE IF NOT EXISTSand if the table has default value using the sequence if you just drop the sequence, you might get error:
Postgres 没有CREATE SEQUENCE IF 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>;
回答by sierrasdetandil
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
...
回答by Loek Bergman
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
洛克
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。可能会影响信息的存储位置。
