1

I have a Common Table Expression which joins two tables with some column names in common:

CREATE TABLE first(
    id serial PRIMARY KEY,
    data VARCHAR,
    date DATE
);
CREATE TABLE second(
    id serial PRIMARY KEY,
    key INT REFERENCES first(id),
    data VARCHAR,
    date DATE
);

WITH cte AS
    (SELECT * FROM first JOIN second ON first.id=second.key)
SELECT data FROM cte;

This, of course, results in an error because field data is ambiguous.

I know that it would be possible to extract the ambiguous columns with the CTE and give them unique names.

The question is, is it possible resolve this ambiguity from the main SELECT statement? Alternatively, is there some notation I can use within the CTE to make this impossible without actually creating new names?

Edit

A number of comments suggest using the wild card is not appropriate. I only use it here to simplify the query — never in a serious query. Here is another version:

WITH cte AS
    (SELECT f.id as fid, s.id as sid, f.data, s.data
        FROM first f JOIN second s ON f.id=s.key
    )
SELECT data FROM cte;

I know that I can resolve the issue by aliasing in the CTE:

WITH cte AS
    (SELECT f.id as fid, s.id as sid, f.data as fdata, s.data as sdata
        FROM first f JOIN second s ON f.id=s.key
    )
SELECT fdata FROM cte;
  • If the join columns of both tables got the same name you can use FROM first JOIN second USING (col), which suppresses redundant colums. Otherwise you need to write all columns you actually need. – dnoeth Oct 24 '16 at 6:38
  • @dnoeth As far as I’m aware, that then makes it a sort of NATURAL JOIN, but the joined columns are not duplicated. Even if I used the same column name for the primary key and the foreign key, I would still have to deal with duplicated column names for the other data. Ultimately I would have to rename the columns to unique names which I am trying to avoid. – Manngo Oct 24 '16 at 7:00
  • Correct, this only helps for join columns (NATURAL JOIN should never be used, in your case it would try to join on id). – dnoeth Oct 24 '16 at 7:04
  • @Manngo. join ... using (..) is not the same as a natural join because you have control over which columns are used. natural join picks random columns if both tables have more then one column name in common. The only way to solve this, is to name the output columns explicitely. Btw: this is an excellent example on why you should avoid select * for anything other then simple ad-hoc queries – a_horse_with_no_name Oct 24 '16 at 10:41
2

While there is no real solution that matches your requirements, there are a few things to consider about the requirements.

First, let's see the hint that an error raised about a misnamed column produces:

WITH ts AS (
    SELECT * FROM t1 JOIN t2 USING (t_id)
) SELECT col2 FROM ts;

ERROR:  column "col2" does not exist
LINE 1: ...AS (SELECT * FROM t1 JOIN t2 USING (t_id)) SELECT col2 FROM ...
                                                             ^
HINT:  Perhaps you meant to reference the column "ts.col1" or the column "ts.col1".

Even the database suggests to choose from two indentically named columns - which, as you already guessed, impossible. (Interestingly, you cannot do such a thing in a definition of a view, not to mention tables.)

So, about the requirements:

It is usually wise not to use * in the first place. Apart from being faster to type, it does not bring too much. When writing the main SELECT, we usually have an idea which columns we need - just add those inside the CTE. This might be in some cases even beneficial for the performance of the query (rendering the size of the data set smaller).

When you want to make it simpler to type, you could try something like

SELECT f.*, s.data AS s_data 
FROM first AS f JOIN second AS s ON f.id = s.key

In my practice, it is even better to name columns with a prefix derived from the table name:

CREATE TABLE first(
    f_id serial PRIMARY KEY,
    f_data VARCHAR,
    f_date DATE
);

CREATE TABLE second(
    s_id serial PRIMARY KEY,
    s_first_id INT REFERENCES first(id), -- an exception, for making more clear 
                                         -- what the column refers to
    s_data VARCHAR,
    s_date DATE
);

This way, even in big databases, it is very rare to have columns with the same name. And this completely removes the problem you are fighting with.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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