CREATE VIEW as join between tables with identically named columns

Question

As the title says, I'd like to create a view from a join between tables which have columns with identical names (surprisingly "id").

Postgres doesn't prepend the table name to the result, but for some reason the query works fine, returning multiple columns with the same name and different data.

CREATE TABLE is less forgiving, though, and returns:

ERROR: column "id" specified more than once

Others have suggested SELECTing the columns under a different name (e.g. here), but there are many columns to both tables.

Do I need to somehow automatically rename / remove the id columns (such as this way), or is there a better way?

Answer 1

There was a bug in my answer to the question you referenced. While being at it, I improved some other details:

• Prepend table name to each column in a result set in SQL? (Postgres specifically)

If you have many columns and / or for repeated use, I would use dynamic SQL as outlined over there.

If you'd been using a proper naming convention, you could avoid most of these cases. Never use id as column name, it's too ambiguous as you are just finding out the hard way. I would use tbl_id instead, "tbl" being the table name.
Unfortunately, some ORMs work with this anti-pattern.

• How to implement a many-to-many relationship in PostgreSQL?

And you do not get any exception for a plain SELECT because Postgres does not require distinct column names in a plain SELECT (although the usefulness of this is limited beyond ad-hoc queries). Distinct names are required for columns of a table or view, though - even derived tables in subqueries. The manual:

Just as in a table, every output column of a SELECT has a name. In a simple SELECT this name is just used to label the column for display, but when the SELECT is a sub-query of a larger query, the name is seen by the larger query as the column name of the virtual table produced by the sub-query.

Postgres has no need for distinct labels. But actual column names must be distinct to avoid ambiguities.