Alias ​​for table name in SQL insert statement

Question

Can I specify an alias name for the table into which I insert the values?

I want to specify a condition inside a subquery, and the table is too verbose ...

Something like that:

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

in it:

 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 contains some links)

Answer 1

You do not have table aliases; you are also an instance of a table link.

This allows you to join yourself, etc., since you have multiple instances of links to the same physical table. This is not the case when each AS gives this table a new name in another place, it is just an alias related to this particular link.

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

The inserted table itself is not part of the select query, it is not a reference set in the same way as foo , bar or baz , for example. Thus, you cannot use his pseudonym (because there is no need, you can never refer to it).

In addition, even if that were the case, you cannot reference the entire table through an alias. You refer to a field as part of a query repeating through a set. For example, this does not work either ...

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

You can get around the last example using ...

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

But this still brings us back to the first point, the inserted table never refers to the request part of your statement.

The only way I can get closer is to create a view ...

Answer 2

I think the answer is NO . After tableName

no AS

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

Link

Update

The AS clause has become part of PostgreSQL since version 9.5 , although, as @MatBailie notes above, the nesting tools that you will need for the INSERT query alias and SELECT sub-query separately or everything will break. eg:.

 > 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) 

Answer 3

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

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