Re: [GENERAL] Querying same lookup table with multiple columns based on another view

[Jim Nasby]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Querying same lookup table with multiple columns based on another view

[Raymond O'Donnell]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Querying same lookup table with multiple columns based on another view

[Raymond O'Donnell]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Querying same lookup table with multiple columns based on another view

[Killian Driscoll]

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

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

Re: [GENERAL] Querying same lookup table with multiple columns based on another view

[Melvin Davidson]

Your problem is the use of SELECT *

You need to be specific about which columns you are selecting from
macro_lookup
and what names you want to give them. Since you have not provided us with
the full structure, I can only suggest something like

CREATE OR REPLACE VIEW sample_macro AS
SELECT sample.col1 as viewcol1
  m1.col1   as viewcol2,
  m1.col2   as viewcol3,
  m2.col4   as viewcol4,
  m3.col7   as viewcol5
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;


On Tue, Nov 17, 2015 at 10:10 AM, Killian Driscoll <
killiandrisc...@gmail.com> 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?
>
>
> Killian DriscoIl
> IRC Postdoctoral Fellow
> UCD School of Archaeology
> University College Dublin
>
> academia.edu/KillianDriscoll 
> www.lithicsireland.ie
> ca.linkedin.com/in/killiandriscoll
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Querying same lookup table with multiple columns based on another view

[Killian Driscoll]

On 17 November 2015 at 16: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;
>

In abbreviating the names for the post I just realised I confused the
names; it should have read.

CREATE OR REPLACE VIEW sample_macro AS
SELECT *
FROM query_srpnt_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?
>
>
> Killian DriscoIl
> IRC Postdoctoral Fellow
> UCD School of Archaeology
> University College Dublin
>
> academia.edu/KillianDriscoll 
> www.lithicsireland.ie
> ca.linkedin.com/in/killiandriscoll
>

Re: [GENERAL] Querying same lookup table with multiple columns based on another view

[Raymond O'Donnell]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general