Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Alias for table name in SQL insert statement

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)

like image 392
fortran Avatar asked Feb 01 '12 16:02

fortran


2 Answers

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

like image 164
MatBailie Avatar answered Sep 20 '22 10:09

MatBailie


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)
like image 25
John Woo Avatar answered Sep 20 '22 10:09

John Woo