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)
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...
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
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With