On 17/11/2015 15:10, Killian Driscoll wrote: > I have a view with 15 columns and want to create another view based on a > join with another table with 15 columns that includes three columns that > reference one lookup table. > > If I use the the below sql I get the error "column "macro_lookup_id" > specified more than once". I have read that I can rename the columns (I > renamed the tables as ml1, ml2, ml3) but can't figure out how to do this > but also use the select * to avoid writing out all rest of the column names. > > CREATE OR REPLACE VIEW sample_macro AS > SELECT * > FROM sample > LEFT JOIN macroscopic ON query_srpnt_sample.samp_id = macroscopic.sample_id > LEFT JOIN macro_lookup AS ml1 ON ml1.macro_lookup_id = macroscopic.grain_id > LEFT JOIN macro_lookup AS ml2 ON ml2.macro_lookup_id = macroscopic.lustre_id > LEFT JOIN macro_lookup AS ml3 ON ml3.macro_lookup_id = > macroscopic.translucency_id > WHERE samp_id is not null; > > What is the most efficient way (in terms of typing out column names) to > create this type of view? Dunno about efficient, but you're going to need to type out your column names - I'm only guessing without seeing the DDL, but it sounds as if a column called macro_lookup_id exists in more than one table. You'll need to do something like this: select sample.macro_lookup_id as col_name_1, macroscopic.macro_lookup_id as col_name_2, [....] This is how you specify column aliases. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@xxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
