有人可以帮我理解并将此MySql语句转换为Postgresql吗？

so the code I am working on has this statement executed by PHP (note:This is taken from the PostgreSQL log file so it doesn't include any PHP stuff):

所以我正在处理的代码有这个语句由PHP执行（注意：这是从PostgreSQL日志文件中获取的，因此它不包含任何PHP内容）：

CREATE temporary table IF NOT EXIST temp tablename(id int primary key,
shared int default 0) replace select 1, userid as id 
from tablefoo where sharedid = 1337

I don't quite understand what's going on here exactly, I know what a temporary table is, and I can quite accurately guestimate what IF NOT EXIST does, but what is replace doing here? I know replace is like insert but it replaces stuff as well, but in this case, nothing is specified for it to replace with, so does it just replace something with nothing and why the Select 1, I know that pretty much just tells you if your table has rows or something, but what is the point of using it here?

我不太明白这里到底发生了什么，我知道临时表是什么，我可以非常准确地猜测IF NOT EXIST的作用，但在这里做什么更换呢？我知道替换就像插入，但它也替换了东西，但在这种情况下，没有指定替换它，所以它只是替换什么没有什么和为什么选择1，我知道几乎只是告诉你是否你的桌子有行或什么，但在这里使用它有什么意义？

After some research, I found that IF NOT EXIST and replace do not exist in PostgreSQL. Most online sources suggest that SQL functions be used to replace them.

经过一番研究，我发现PostgreSQL中不存在IF NOT EXIST和replace。大多数在线资源都建议使用SQL函数来替换它们。

Should I use an SQL function to emulate IF NOT EXIST? If so, what would I write (sorry, I am pretty new to SQL) or should I just use a PHP function. What about replace?

我应该使用SQL函数来模拟IF NOT EXIST吗？如果是这样，我会写什么（对不起，我是SQL的新手）或者我应该只使用PHP函数。更换怎么样？

Sorry for the trouble, thanks for your time, oh and if you guys aren't busy or anything, you could also tell me about how to emulate "ignore", my current solution involves arbitrarily removing it.

对不起，感谢您的时间，哦，如果你们不忙或什么，你也可以告诉我如何模仿“忽略”，我目前的解决方案涉及任意删除它。

2 个解决方案

#1

Many uses in MySQL for temporary tables can be replaced in PostgreSQL with common table expressions or ordinary subselects.

MySQL中用于临时表的许多用法可以在PostgreSQL中用公共表表达式或普通子选择替换。

WITH someCTE AS (
    SELECT 
     ...
) SELECT/UPDATE/DELETE ... WHERE sometable.column = someCTE.another_column;

#2

Look into CREATE TABLE documentation. Temporary tables are just as name suggests not permanent:

查看CREATE TABLE文档。临时表就像名称所暗示的那样永久：

If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.

如果指定，则将表创建为临时表。临时表在会话结束时自动删除，或者可选地在当前事务结束时删除（请参阅下面的ON COMMIT）。当临时表存在时，具有相同名称的现有永久表对当前会话不可见，除非它们使用模式限定名称引用。在临时表上创建的任何索引也是自动临时的。

In particular temp tables are stored in diffrent (non-public) schema, e.g.:

特别是临时表存储在不同（非公共）模式中，例如：

=> Create Temporary Table someTempTable (value integer);
CREATE TABLE
=> \dt someTempTable
              List of relations
  Schema   |     Name      | Type  |  Owner
-----------+---------------+-------+----------
 pg_temp_2 | sometemptable | table | postgres
(1 row)

PostgreSQL doesn't have IF NOT EXISTS like in MySQL's CREATE TABLE, so you can't use it. If you want to create some table you need to firstly drop existing one (if it exists). Fortunately you could use SQL command DROP TABLE IF EXISTS to handle this:

PostgreSQL没有像MySQL的CREATE TABLE那样的IF NOT EXISTS，所以你不能使用它。如果要创建某个表，首先需要删除现有表（如果存在）。幸运的是，您可以使用SQL命令DROP TABLE IF EXISTS来处理：

=> Drop Table If Exists someTempTable;
DROP TABLE
=> Drop Table If Exists someTempTable;
NOTICE:  table "sometemptable" does not exist, skipping
DROP TABLE

#1