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?
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;