Table Aliases: Changes to Name Resolution for Quoted Column Identifiers¶

In the FROM clause in a SELECT statement, you can use the AS clause to define an alias for the table and its columns. For example:

SELECT * FROM my_table AS my_table_alias(my_column_1_alias, my_column_2_alias);

This announcement describes the changes to way in which quoted column identifiers in the table alias are resolved:

Before the change:

If you use double quotes around the column identifier in the table alias, the double quotes become part of the column alias.

For example, the following query defines a table alias (table_alias) and a column alias that includes quotes (“column_alias”):

SELECT * FROM table_1 AS my_table_alias("my_column_alias");

Copy

In the output, the name of the first column includes the quotes ("my_column_alias").

Because the quotes are currently part of the column alias name, if you need to refer to this column alias, you must include the quotes. For example, if you want to refer to the column alias in the list of selected columns, you must include the quotes in the column alias name:

SELECT """my_column_alias""" FROM table_1 AS my_table_alias("my_column_alias");

Copy

Note that in the example above, the column alias is enclosed in quotes, and the quotes within the column alias name are specified with two double-quote characters.

After the change:

If you use double quotes around the column identifier in the table alias, the double quotes are not used as part of the column alias.

SELECT * FROM table_1 AS my_table_alias("my_column_alias");

Copy

In the output, the name of the first column does not include the quotes (my_column_alias).

Queries that use quotes within the column alias fail with an “invalid identifier” error:

SELECT """my_column_alias""" FROM table_1 AS my_table_alias("my_column_alias");

Copy

If you use double quoted identifiers for columns in a table alias, use one of the following approaches to fix your SQL statements:

• If you need to keep the column names as is (preserving quotes and case sensitivity), rewrite the existing queries to use common table expressions to define column aliases.

  For example, change:

  SELECT """My_Column_Alias"""
    FROM table_1 AS my_table_alias("My_Column_Alias")
  

  Copy

  to:

  WITH my_table_alias("""My_Column_Alias""")
      AS (SELECT * FROM table_1)
    SELECT """My_Column_Alias""" FROM my_table_alias
  

  Copy

• If you can change output column names of the existing queries, consider removing quotes from column alias definitions.

  For example, change:

  SELECT """my_column_alias"""
    FROM table_1 AS my_table_alias("my_column_alias");
  

  Copy

  to:

  SELECT my_column_alias
    FROM table_1 AS my_table_alias(my_column_alias);
  

  Copy

  Note that this statement creates and resolves the my_column_alias identifier as uppercase characters without any quotes. If you use this approach, you might need to adjust code or statements that refer to this column alias.

  For example, instead of accessing the above column using "my_column_alias", client applications might need to refer to it as MY_COLUMN_ALIAS.

Ref: 881