3

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?

Improve this question
1
  • 1
    You need to alias the column names to rename them in the view - there's nothing else you can do
    – Philᵀᴹ
    Nov 6, 2016 at 17:51

1 Answer 1

Reset to default
3

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

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.

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.

Improve this answer
7
  • Thanks! I agree about the naming convention. Unfortunately this comes from an ETL from another DB, and I've been trying to avoid setting up a transform to drop/rename those "id" columns.
    – Ohad
    Nov 7, 2016 at 15:40
  • BTW I wonder why distinct columns aren't required in SELECT. I also noticed JOIN keys are duplicated.
    – Ohad
    Nov 7, 2016 at 15:42
  • @Ohad: I added some explanation for that, quoting the manual.
    – Erwin Brandstetter
    Nov 8, 2016 at 1:46
  • Too bad I can't upvote your answer twice :)
    – Ohad
    Nov 8, 2016 at 6:21
  • While you are correct that using a more verbose name for the column would avoid this problem, there are trade-offs to things like this. In this very rare case there is an advantage to the verbose name, but it has a cost, especially if your ORM uses "id" by default. The extra work to specify the verbose name in all cases will be vastly greater than the extra work in this rare case. You have a personal naming practice that you find works for you—that's great, I'm not trying to talk you out of it. But when an entire community has a convention, the cost of breaking with it can be very high.
    – iconoclast
    Aug 16, 2018 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.