SQL插入语句中表名的别名

Is it possible to specify an alias name for the table I am inserting values into?

是否可以为我要插入值的表指定别名？

I want to specify a condition inside a nested query and the table is too verbose...

我想在嵌套查询中指定一个条件，表格太冗长了......

Something like turning this:

像转这样：

INSERT INTO my_table_with_a_very_long_name (col_a, col_b, col_c)
SELECT foo, bar, baz
FROM other_table
WHERE
  other_table.some_value > 
  (SELECT max(other_value) FROM my_table_with_a_very_long_name);

into this:

进入这个：

INSERT INTO my_table_with_a_very_long_name AS t (col_a, col_b, col_c)
SELECT foo, bar, baz
FROM other_table
WHERE
  other_table.some_value > (SELECT max(other_value) FROM t);

(obviously my case is longer and involves a few references more)

（显然我的案子更长，涉及更多参考）

3 个解决方案

#1

You don't alias a table, you alias an instance of a table reference.

您没有别名表，您为表引用的实例添加别名。

This allows self joins, etc as you have mutliple instances of references to the same physical table. It's not a case where each AS gives that table a new name elsewhere, it's just an alias to refer to That particular reference.

这允许自连接等，因为您具有对同一物理表的引用的多个实例。这不是每个AS在其他地方为该表提供新名称的情况，它只是引用该特定引用的别名。

In your case, there are two show stoppers...

在你的情况下，有两个显示停止...

The table being inserted into isn't itself part of the select query, it's not a referenced set in the same way as foo, bar or baz for example. So, you can't alias it at all (because there's no need, it can never be referenced).

插入的表本身不是select查询的一部分，例如，它不是与foo，bar或baz相同的引用集。所以，你根本不能别名它（因为没有必要，它永远不会被引用）。

Also, even if it was, you can't reference the whole table through an alias. You reference a field, as part the query itterating through the set. For example, this doesn't work either...

此外，即使它是，您也不能通过别名引用整个表。您引用一个字段，作为通过集合迭代的查询的一部分。例如，这不起作用......

SELECT * FROM myTable AS xxx WHERE id = (SELECT MAX(id) FROM xxx)

You can get around the latter example using...

你可以使用...来解决后一个例子

WITH xxx AS (SELECT * FROM myTable) 
SELECT * FROM xx WHERE id = (SELECT MAX(id) FROM xxx)

But that still brings us back to the first point, the table being inserted into never gets referenced in the query part of your statement.

但是这仍然让我们回到第一点，插入的表永远不会在语句的查询部分中被引用。

The only way I can think of getting close is to create a view...

我能想到的唯一方法是创建一个视图......

#2

I think the answer is NO. There is no AS after the tableName

我认为答案是肯定的。 tableName之后没有AS

INSERT INTO table [ ( column [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Reference

参考

Update

The AS clause became part of PostgreSQL as of version 9.5, though as @MatBailie notes above, the nesting means you'll need to alias the INSERT query and the SELECT sub-query separately or things will break. e.g.:

从版本9.5开始，AS子句成为PostgreSQL的一部分，尽管如上所述@MatBailie，嵌套意味着您需要单独对INSERT查询和SELECT子查询进行别名，否则事情就会中断。例如。：

> CREATE TABLE foo (id int, name text);
CREATE TABLE
> INSERT INTO foo VALUES (1, 'alice'), (2, 'bob'), (3, 'claire');
INSERT 0 3
> INSERT INTO foo AS f (SELECT f.* from f);
ERROR:  relation "f" does not exist
LINE 1: INSERT INTO foo AS f (SELECT f.* from f);
                                              ^

-- Next line works, but is confusing. Pick distinct aliases in real life.
-- I chose the same 'f' to illustrate that the sub-select 
-- really is separate.
> INSERT INTO foo AS f (SELECT f.* from foo f); 
INSERT 0 3
> > SELECT * FROM foo;
 id |  name
----+--------
  1 | alice
  2 | bob
  3 | claire
  1 | alice
  2 | bob
  3 | claire
(6 rows)

#3

As others have said, you cannot alias the name as part of the INSERT INTO statement. You would need to put it in the subquery in the WHERE statement.

正如其他人所说，你不能将名称别名作为INSERT INTO语句的一部分。您需要将它放在WHERE语句的子查询中。

INSERT INTO my_table_with_a_very_long_name (col_a, col_b, col_c)
SELECT foo, bar, baz
FROM other_table
WHERE
  other_table.some_value > (SELECT max(other_value) FROM 
      my_table_with_a_very_long_name AS t);