Create Aliases In Bulk?

Question

I'm trying to join three tables, products, taxes & categories. All these tables have conflicting column names. I'm aware that we get around this conflict by creating aliases.

products:
    id
    name
    ...

taxes
    id
    name
    ...

categories
    id
    name
    ...

My question is, is there a convenient way to create aliases in bulk? What I mean is something like

SELECT products.* as product.*, taxes.* as tax.*, categories.* as category.*
...

I would expect the result set to have columns like:

product.id, product.name, tax.id, tax.name, ...

Or, do I have to stick with something tedious as:

SELECT products.id as product_id, products.name as product_name,
       taxes.id as tax_id, taxes.name as tax_name, ...

Lukasz Szozda · Accepted Answer · 2016-01-21T20:09:23.083

You search for * on steroid. Unfortunaltely there is no such functionality in SQL.

Workaround 1:

Use power of block selection in your favourite text editor(vim,atom,...). Place each column in new row. Block selection to write AS and table prefix. Then block selection and copy column names.

Workaround 2:

Generate select list using INFORMATION_SCHEMA.COLUMNS:

SELECT 
 string_agg(FORMAT('%s.%s AS %s_%s', "table_name",
                   column_name,"table_name", column_name), ', ')
FROM information_schema.columns
WHERE "table_name" IN ('products', 'taxes', 'categories');

SqlFiddleDemo

You could use E',\n' to place each column in new line.

Output:

╔════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                                                                   string_agg                                                                                   ║
╠════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║ products.id AS products_id, products.name AS products_name, taxes.id AS taxes_id, taxes.name AS taxes_name, categories.id AS categories_id, categories.name AS categories_name ║
╚════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

Create Aliases In Bulk?

1 Answers1

Linked