[SQL] Database with "override" tables
Hello, I am in a situation where I have various tables (including data such as a product list) that are read-only to me. I wish to provide the functionality of changing this table: Removing items, modifying items, creating new ones. My original idea is to use a second table that is formatted similarly to the first, read-only table. However, I can't just duplicate all the data and work in a separate table, as the original table may be modified by an outside source at any time. Generally, I prefer to use the updated data from the read-only table, unless there has been reason to modify it in the past -- in which case the update may be safely ignored, and I would continue to use the second table. The most effective set up I have come up with thus far is as follows: CREATE TABLE initial_table (initial_id SERIAL PRIMARY KEY, desc TEXT); CREATE TABLE override_table (override_id SERIAL PRIMARY KEY, initial_id INT, desc TEXT); An entry in override_table with an initial_id matching an initial_id in initial_table would take precedence over the entry in initial_table. A fairly simple SELECT statement can return the data I require: SELECT COALESCE(ot.desc, it.desc) FROM initial_table it FULL JOIN override_table ot ON it.initial_id = ot.initial_id; So far so good, but then when I need to reference data in those two tables from somewhere else, I need to reference both initial_id and override_id on the result set from that table. I also end up using COALESCE an uncomfortable amount (though, if this is required I will do so). I would prefer to treat the initial + override tables as a single SELECT set, but cannot come up with a way to do this cleanly, especially with regards to the IDs. I am willing to abandon this format of "overriding" the initial table if it is way off-course, provided that the initial data be considered read-only and update-able in the absence of "override" data. All suggestions are greatly appreciated! Thanks in advance, Mike. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Database with "override" tables
Lane Van Ingen wrote: I think I have a similar situation involving the naming of assets, where the usual asset description is used, but users can enter a description in a separate table which 'overrides' the original name with a name that is more familiar to the individual. IF THIS IS WHAT YOU WANT, it was accomplished by doing a UNION between two select statements, like this: select from foo1 union select from foo2 where ; Hope this helps. That almost works, and it is a much cleaner query than I had before. However, there's a possibility that some columns in the overridden table are NULL (indicating that the original value should be used). So, a particular asset may contain a description and price; the price may be NULL, meaning the read-only value should be used, but the user may have attached a special description as we previously outlined. What I'm looking for is the ability to, perhaps, "overlay" foo2 onto foo1, joined on foo1_id. Then, NULL values in foo2 become "transparent" and we see the foo1 values behind them. Presently I am using COALESCE() for every pair individually, ie. COALESCE(foo2.price, foo1.price), COALESCE(foo2.descr, foo1.descr), ... and then doing a FULL JOIN. This works. I'm starting to wonder if it's worth the extra hassle, I may just use your suggested UNION method instead. Thanks again, Mike. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Database with "override" tables
Lane Van Ingen wrote: Not quite sure how to answer this, but one thought does occur to me: I was perhaps assuming that an override table would override an entire record in the 'original' table(that is what we are doing), and we require that critical fields in the override field be NOT NULL (and in some cases, provide DEFAULT values). Will that help? I fought with the UNION method for a while before backing off to my extremely-long-query system with a FULL JOIN: SELECT COALESCE(ot.desc, it.desc) FROM initial_table it FULL JOIN override_table ot ON it.initial_id = ot.initial_id; It's not so bad until you get to cross-reference other tables that have override tables, but then it just becomes pretty ugly. So I'll stick with that for now. If the override table did indeed override entire records in the original table, the UNION would have worked beautifully and probably made things cleaner. Thanks for the advice. Mike. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Querying date_time for date only ?
On December 20, 2005 08:59 am, Aarni Ruuhimäki wrote: > Hello List, > > I have a time stamp without time zone field, -MM-DD hh:mm:ss, in my > table. I want to also find something just for a particular day regardless > of the time. > > (Pg)SQL way to do this ? You can try, SELECT field::date FROM mytable; to select only the date part. Likewise, you can use field::time if you want to disregard the date. > TIA, > > Aarni HTH. Mike. -- Michael Burke [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Sub-query as function argument
Is it possible to execute a SELECT query as an argument to a function?
Example:
SELECT my_func('Sample', NULL, SELECT MIN(year) FROM audio);
In my particular case, my_func inserts columns into another table; I wish to
use values from another table as the arguments. The interior SELECT will
return only a single value; perhaps it needs to be wrapped in another
function?
TIA.
Mike.
--
Michael Burke
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Sub-query as function argument
On December 20, 2005 10:52 am, Jaime Casanova wrote:
> have you tried?
Yes:
=> SELECT my_func('Sample', NULL, SELECT MIN(year) FROM audio);
ERROR: syntax error at or near "SELECT" at character 32
PostgreSQL 7.4.9, myfunc is pl/pgsql.
Just found a working method, though:
=> SELECT my_func('Sample', NULL, MIN(year)) FROM audio [ WHERE ... ];
--
Michael Burke
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] Using calculated columns as arguments in same SELECT
Hello, I am looking to simplify this query (uses PostGIS, but I have encountered this scenario with other chains of functions): gtest=# SELECT X( SetSRID( Transform( GeomFromText( 'POINT(142512 1020225)', 26910 ), 4326 ), -1) ) as xcoord, Y( SetSRID( Transform( GeomFromText( 'POINT(142512 1020225)', 26910 ), 4326 ), -1) ) AS ycoord; This SELECT works, but the rather long arguments to X(geom) and Y(geom) are the same. Is it possible and feasible to pre-calculate the argument, such as: gtest=# SELECT SetSRID( Transform( GeomFromText( 'POINT(142512 1020225)', 26910 ), 4326 ), -1) AS transformed_geom, X(transformed_geom) AS xcoord, Y(transformed_geom) AS ycoord Where I don't really care about transformed_geom being returned, but it saves double-calling the inside functions This doesn't work -- it complains that transformed_geom is not a column. SELECT version(); gives: PostgreSQL 8.0.6 on i386-portbld-freebsd5.4, compiled by GCC cc (GCC) 3.4.2 [FreeBSD] 20040728 Thanks in advance! Mike. -- Michael Burke Engineering Technologies Canada Ltd. [EMAIL PROTECTED] 1 (902) 628-1705 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Using calculated columns as arguments in same SELECT
On February 16, 2006 11:07 am, Michael Burke wrote: > Hello, > > I am looking to simplify this query (uses PostGIS, but I have encountered > this scenario with other chains of functions): > -- snip -- Immediately after sending this one, I realized I could do it with a sub-select: gtest=# SELECT X(SubSel.transformed_geom), Y(SubSel.transformed_geom) FROM ( SELECT SetSRID( Transform( GeomFromText( 'POINT(142512 1020225)', 26910 ), 4326 ), -1) AS transformed_geom) SubSel; This works fine. Mike. -- Michael Burke Engineering Technologies Canada Ltd. [EMAIL PROTECTED] 1 (902) 628-1705 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] SELECT composite type
Hello, I have a function get_xy that returns a composite type with columns "x" and "y". I would like to SELECT these as well as some data from a table, like so: SELECT (get_xy(SetSRID(sightings.location, 26910), 4326)).x, (get_xy(SetSRID(sightings.location, 26910), 4326)).y, sightings.title FROM sightings WHERE sighting_id = 25; This statement works, but I don't want to duplicate the function call as this should be unnecessary. Selecting simply get_xy returns both fields in a single column, which is undesirable. I tried: SELECT foo.x, foo.y, sightings.title FROM sightings, get_xy(SetSRID(sightings.location, 26910), 4326) foo WHERE sighting_id = 25; But, because the function refers to sightings, I get this error: ERROR: function expression in FROM may not refer to other relations of same query level ...which is reasonable. So I basically want to call get_xy for every row in sightings, and use its output for two columns; or perhaps there is another way to think of this. I am using Postgres 8.1.2 (same with client) on FreeBSD, with PostGIS 1.1.1: $ postmaster --version postmaster (PostgreSQL) 8.1.2 Thanks in advance! Mike. -- Michael Burke Engineering Technologies Canada Ltd. - http://www.engtech.ca/ [EMAIL PROTECTED] 1 (902) 628-1705 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SELECT composite type
On Wednesday 05 April 2006 19:29, Tom Lane wrote: > Niklas Johansson <[EMAIL PROTECTED]> writes: > > You could try > > > > SELECT foo.x, foo.y, title FROM > > (SELECT > >get_xy(SetSRID(sightings.location, 26910), 4326) AS foo, > >sightings.title > > FROM sightings > > WHERE sighting_id = 25) bar; > > Note however that the above is only a cosmetic answer: you avoid typing > the function call twice, but the planner will "flatten" the subquery > into the outer query and thereby end up with two evaluations anyway. > If you're really intent on avoiding the extra evaluation then you need > to do something to prevent the flattening from happening. One > handy trick is to use a LIMIT or OFFSET clause in the subquery as an > optimization fence: Thanks for the suggestions. This case is mostly for aesthetic reasons, or perhaps to prevent typing errors in the future (in case I have to modify the function call). Forming the sub-query that way will effectively solve my problem, even if it doesn't specifically aid in efficiency -- however my get_xy function is STABLE, so it shouldn't harm it too much (unsure about SetSRID); additionally, adding the OFFSET 0 is an interesting trick that I will also try. Thus, my final query: SELECT (xy).x, (xy).y, title FROM (SELECT get_xy(SetSRID(sightings.location, 26910), 4326) AS xy, sightings.title FROM sightings WHERE sighting_id = 25 OFFSET 0) bar; Thanks again. Mike. -- Michael Burke Engineering Technologies Canada Ltd. - http://www.engtech.ca/ [EMAIL PROTECTED] 1 (902) 628-1705 ---(end of broadcast)--- TIP 6: explain analyze is your friend
