Обсуждение: Querying same lookup table with multiple columns based on another view

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@iol.ie

On 17/11/2015 15:33, Killian Driscoll wrote:
>
> In terms of database structure and typing efficiency, it might be better
> to restructure the lookup tables and create a new lookup table for each
> of the three columns instead of one combined one?

[Please keep you replies on-list - thanks!]

You'd better show us your table structures - we're only guessing
otherwise. However, I think typing efficiency isn't important, but
rather what your application needs to do with the data - you only type
the query once. :-)

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 17/11/2015 16:07, Killian Driscoll wrote:
>
>     >
>     > In terms of database structure and typing efficiency, it might be better
>     > to restructure the lookup tables and create a new lookup table for each
>     > of the three columns instead of one combined one?
>
>     [Please keep you replies on-list - thanks!]
>
>
> Sorry! Didn't mean that....
>
>
>     You'd better show us your table structures - we're only guessing
>     otherwise. However, I think typing efficiency isn't important, but
>     rather what your application needs to do with the data - you only type
>     the query once. :-)
>
>
> In terms of structure I'm going to go with separate lookup tables as
> they are and should be distinct really.
>
> I'm using pstgresql 9.3. I have set up the new lookup tables and now
> want to alter the fk constraints but keep the data already there (the
> new lookup tables with maintain the old pks). Below is one of the
> constraints:
>
> CONSTRAINT macro_lookupg_macroscopic FOREIGN KEY (grain_id)
>       REFERENCES irll.macro_lookup (macro_lookup_id) MATCH SIMPLE
>       ON UPDATE CASCADE ON DELETE RESTRICT.
>
> In 9.3 is there an alter constraint option or do I have to drop the
> constraint and add the new one?

I just had a quick look at the docs for 9.3:

http://www.postgresql.org/docs/9.3/static/sql-altertable.html

I don't see ALTER CONSTRAINT in there, so it looks as if you need to
drop and re-create it.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 11/17/15 9:21 AM, Melvin Davidson wrote:
> Your problem is the use of SELECT *

I've often wished for a way to do *, but make changes to the results
that a normal * would give you. The two operations I've wished for are
to either remove some fields from the list, or to rename some fields.

I suspect you could create a function that would do this. You wouldn't
want to just blindly operate on a CREATE VIEW command because of things like

SELECT a.*, b.blah FROM (SELECT 1,2,3) a, b

but I think it might be possible to get the raw data out of the parser
(the grammar certainly provides it). It would probably involve C code
though.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com