PostgreSQL 错误：关系已经存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8792912/
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
PostgreSQL Error: Relation already exists
提问by nsbm
I am trying to create a table that was dropped previously.
我正在尝试创建一个以前删除的表。
But when I do the CREATE TABLE A ... I am getting below error:
但是当我执行CREATE TABLE A ... 我收到以下错误：
Relation 'A' already exists.
关系“A”已存在。
I verified doing SELECT * FROM A, but then I got another error:
我验证了做SELECT * FROM A，但后来又出现了一个错误：
Relation 'A' does not exists.
关系“A”不存在。
I already tried to find it in \dS+listing all relations, and it is not there.
To complicate this, I have tested this by creating this table in another database and I got the same error. I am thinking that could be an error when this table was dropped. Any ideas?
我已经尝试在\dS+列出所有关系中找到它，但它不存在。
更复杂的是，我通过在另一个数据库中创建这个表来测试这个，我得到了同样的错误。我认为删除此表时可能会出错。有任何想法吗？
Here is the code: I'm using a generated code from Power SQL. I have the same error without using the sequence. It just works when I change the name and in this case I can not do that.
这是代码：我使用的是从 Power SQL 生成的代码。我在不使用序列的情况下也有同样的错误。它只在我更改名称时起作用，在这种情况下我不能这样做。
CREATE SEQUENCE csd_relationship_csd_relationship_id_seq;
CREATE TABLE csd_relationship (
csd_relationship_id INTEGER NOT NULL DEFAULT nextval('csd_relationship_csd_relationship_id_seq'::regclass),
type_id INTEGER NOT NULL,
object_id INTEGER NOT NULL,
CONSTRAINT csd_relationship PRIMARY KEY (csd_relationship_id)
);
采纳答案by nsbm
I finally discover the error. The problem is that the primary key constraint name is equal the table name. I don know how postgres represents constraints, but I think the error "Relation already exists" was being triggered during the creation of the primary key constraint because the table was already declared. But because of this error, the table wasnt created at the end.
我终于发现了错误。问题是主键约束名称等于表名称。我不知道 postgres 如何表示约束，但我认为在创建主键约束期间触发了错误“关系已经存在”，因为表已经声明。但是由于这个错误，最后没有创建表。
回答by Erwin Brandstetter
There should be no single quotes here 'A''some value'.
Either use double quotes to preserve the upper case spelling of "A":
这里不应该有单引号'A''some value'.
要么使用双引号来保留“A”的大写拼写：
CREATE TABLE "A" ...
Or don't use quotes at all:
或者根本不使用引号：
CREATE TABLE A ...
which is identical to
这与
CREATE TABLE a ...
because all unquoted identifiersare folded to lower case automatically in PostgreSQL.
因为所有的未引用的标识符被折叠以在PostgreSQL自动小写。
You could avoid problems with the index name completely by using simpler syntax:
您可以使用更简单的语法完全避免索引名称的问题：
CREATE TABLE csd_relationship (
csd_relationship_id serial PRIMARY KEY,
type_id integer NOT NULL,
object_id integer NOT NULL
);
Does the same as your original query, only it avoids naming conflicts automatically. It picks the next free identifier automatically. More about the serial type in the manual.
与您的原始查询相同，只是它会自动避免命名冲突。它会自动选择下一个空闲标识符。有关手册中串行类型的更多信息。
回答by SingleNegationElimination
You cannot create a table with a name that is identical to an existing table or view in the cluster. To modify an existing table, use ALTER TABLE(link), or to drop all data currently in the table and create an empty table with the desired schema, issue DROP TABLEbefore CREATE TABLE.
您不能创建名称与集群中现有表或视图相同的表。要修改现有表，请使用ALTER TABLE(link)，或删除当前表中的所有数据并创建一个具有所需架构的空表，在DROP TABLE之前发出CREATE TABLE。
It could be that the sequence you are creating is the culprit. In PostgreSQL, sequences are implemented as a table with a particular set of columns. If you already have the sequence defined, you should probably skip creating it. Unfortunately, there's no equivalent in CREATE SEQUENCEto the IF NOT EXISTSconstruct available in CREATE TABLE. By the looks of it, you might be creating your schema unconditionally, anyways, so it's reasonable to use
可能是您创建的序列是罪魁祸首。在 PostgreSQL 中，序列被实现为具有一组特定列的表。如果您已经定义了序列，您可能应该跳过创建它。遗憾的是，在没有相应CREATE SEQUENCE的IF NOT EXISTS可用结构CREATE TABLE。从它的外观来看，无论如何，您可能会无条件地创建架构，因此使用它是合理的
DROP TABLE IF EXISTS csd_relationship;
DROP SEQUENCE IF EXISTS csd_relationship_csd_relationship_id_seq;
before the rest of your schema update; In case it isn't obvious, This will delete all of the data in the csd_relationshiptable, if there is any
在您的架构更新的其余部分之前；如果不明显，这将删除csd_relationship表中的所有数据，如果有的话
回答by Dave Van den Eynde
In my case, I had a sequence with the same name.
就我而言，我有一个同名的序列。
回答by isedwards
Another reason why you might get errors like "relation already exists" is if the DROPcommand did not execute correctly.
您可能会收到诸如“关系已经存在”之类的错误的另一个原因是DROP命令没有正确执行。
One reason this can happen is if there are other sessions connected to the database which you need to close first.
发生这种情况的一个原因是，如果有其他会话连接到您需要先关闭的数据库。
回答by user5775085
In my case, it wasn't until I PAUSEd the batch file and scrolled up a bit, that wasn't the only error I had gotten. My DROPcommand had become ???DROPand so the table wasn't dropping in the first place (thus the relation did indeed still exist). The ???I've learned is called a Byte Order Mark (BOM). Opening this in Notepad++, re-save the SQL file with Encoding set to UTM-8 without BOM and it runs fine.
就我而言，直到我暂停批处理文件并向上滚动一点，这不是我遇到的唯一错误。我的DROP命令已经变成了???DROP，所以桌子一开始就没有下降（因此这种关系确实仍然存在）。在???我学会被称为字节顺序标记（BOM）。在 Notepad++ 中打开它，重新保存编码设置为 UTM-8 的 SQL 文件，没有 BOM，它运行良好。
回答by Nicolas Boisteault
In my case I was migrating from 9.5 to 9.6. So to restore a database, I was doing :
就我而言，我是从 9.5 迁移到 9.6。所以要恢复数据库，我正在做：
sudo -u postgres psql -d databse -f dump.sql
Of course it was executing on the old postgreSQL database where there are datas! If your new instance is on port 5433, the correct way is :
当然，它是在有数据的旧 postgreSQL 数据库上执行的！如果您的新实例在端口 5433 上，则正确的方法是：
sudo -u postgres psql -d databse -f dump.sql -p 5433
