Alias for table name in SQL insert statement

Question

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)

Post your original query. There are probably ways to simplify it. Abstraction is not useful for this case. An alias on the INSERT table itself would be pointless as it cannot be referenced. But the same table can referenced in the SELECT statement and have an alias there. — Erwin Brandstetter, Feb 01 '12 at 17:27
In fact it was a dynamic query, so I wanted to replace the table name just once in the heading and reference the rest by the alias. — fortran, Feb 02 '12 at 09:47
Aaron Bertrand used this analogy once... and I thought it was such a perfect analogy that I stole it. When someone asks you how you can ride your cow to work... the first question should not be "how?"... but "why?" And also, NO is a valid answer. ;) My answer wasn't meant to be a critical.. I was just asking the OP to think about why he wants to do this in the first place. My apologies if that sounded rude. — sam yi, Apr 08 '14 at 13:58

score 12 · Accepted Answer · edited Apr 08 '14 at 10:56

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.

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

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

score 8 · Answer 2 · edited Oct 18 '16 at 03:45

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

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.:

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

score 0 · Answer 3 · answered Mar 21 '16 at 19:37

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

Alias for table name in SQL insert statement

3 Answers3

Update