Re: [SQL] Triggers - need help !!!
"Richard Huxton" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Pradeepkumar, Pyatalo (IE10) wrote: > > IF UPDATE(CreateTime) THEN > > > > > > END IF; > > > > Is this syntax correct. > > No, and I don't recall seeing anything like it in the manuals. ... and it's always an excellent time to read them ... > IF OLD.CreateTime <> NEW.CreateTime THEN > ... ... being mindful of the case where CreateTime might be NULL ... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] olympics ranking query
That 'running aggregate' notion comes up quite regularily, and it has a number of nonintuitive holes, to do with what happens when your ordering allows for ties. ASTRID had it, RedBrick SQL had it, the latest MSSQL has it ... not necessarily a recommendation. Tom Lane wrote: David Garamond <[EMAIL PROTECTED]> writes: This is not quite the same. The ranks are sequential, but they skip, so as to match the number of participating countries. Oh, I missed that bit. What you really want here is a "running sum" function, that is SELECT running_sum(numranker) as rank, * FROM (same subselect as before) ss; There is no such thing in standard SQL, because it's fundamentally dependent on the assumption of the input data coming in a particular order, which is Evil Incarnate according to the relational worldview. But it's not too hard to do in PLs that allow persistent state. I recall Elein having exhibited one in plpython(?) not too long ago --- you might find it on techdocs or varlena.com. You could brute-force it with a subselect (essentially "SUM(numranker) over all rows that should precede this one") but that would involve recomputing the whole subselect for each output row, which doesn't seem very attractive. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Join Table
T E Schmitz wrote: On Mon, Nov 01, 2004 at 04:34:32PM +, T E Schmitz wrote: Question: is it necessary/advisable to create an index for the ITEM_FK column? Or is this redundantbecause this column is already one of the PK columns? However, read the "Multicolumn Indexes" section in the "Indexes" chapter to be sure you understand when the index will be used and when it won't be: http://www.postgresql.org/docs/7.4/static/indexes-multicolumn.html I see. If using a multi-column PK, the order matters. So, if I want to access the table both via the 1st and 2nd PK column, I would have to define an index for the 2nd column to avoid a full table scan. Let's ask the question the other way round: I remember seeing a discussion (re Oracle) whether to use a multi-column PK or a unique constraint in such a situation - I got the impression it is one of these "religious" discussions ;-). What are the pros and cons? Oracle uses a tactic called 'index skip scan' that tries to make use of an index when the first column is not restricted by the query. http://www.oracle-base.com/articles/9i/IndexSkipScanning.php The idea is that scanning the data in the index is fast, and the results sets of rowids can be sorted and batched for (relatively) efficient retrieval from the heap. I've read one review that indicates there were pitfalls with using it in 9i: http://www.praetoriate.com/oracle_tips_skip_scan.htm Having used such schemes for querying (blush) ISAM files I'd say that this isn't surprising. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] CREATE TRIGGER ... FOR EACH STATEMENT
Please ignore this, I just caught up with news in c.d.p.hackers Mischa Sandberg wrote: I notice a dearth of description of the FOR EACH STATEMENT flavour of triggers, even though OLD_TABLE and NEW_TABLE are mentioned. After years of Sybase & MSSQL, being able to deal with the entire INSERTED/DELETED rowsets in a trigger, rather than nibbling away row by row, has been a great efficiency boost. In fact, the only I've resorted to FOR EACH ROW triggers is where joining OLD_TABLE and NEW_TABLE by primary key burned the CPU --- both pseudo-tables being very large in some updates, and perforce having no indexes ... I can see from src/backend/command/trigger.c that ExecASInsertTriggers() would have a hard time getting at the equivalent of OLD_TABLE and NEW_TABLE, ExecBSInsertTriggers even worse. Anyone else out there who feels this would be a significant enhancement? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] CREATE TRIGGER ... FOR EACH STATEMENT
I notice a dearth of description of the FOR EACH STATEMENT flavour of triggers, even though OLD_TABLE and NEW_TABLE are mentioned. After years of Sybase & MSSQL, being able to deal with the entire INSERTED/DELETED rowsets in a trigger, rather than nibbling away row by row, has been a great efficiency boost. In fact, the only I've resorted to FOR EACH ROW triggers is where joining OLD_TABLE and NEW_TABLE by primary key burned the CPU --- both pseudo-tables being very large in some updates, and perforce having no indexes ... I can see from src/backend/command/trigger.c that ExecASInsertTriggers() would have a hard time getting at the equivalent of OLD_TABLE and NEW_TABLE, ExecBSInsertTriggers even worse. Anyone else out there who feels this would be a significant enhancement? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SQL Question
Igor Kryltsov wrote: We have table create table group_facility ( group_id integer not null, facility_id integer not null ) It stores facilities membership in group. For example: "North Region" - facilityA, facilityB I need to extract groups from this table which contain facilityN AND facilityZ and may be others but these two(both) has to be a group member. Query: SELECT DISTINCT group_id FROM facility_group s1 WHERE EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id = s1.group_id AND facility_id = 390) AND EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id = s1.group_id AND facility_id = 999) works but what if I need to find groups where membership is (facilityN1, facilityN100)?? Okay: suppose you have table my_facilities(facility_id integer) --- your facilityN1...facilityN100 SELECT group_id FROMfacility_group s1 JOINmy_facilities s2 USING(facility_id) GROUP BY group_id HAVING COUNT(*) = (SELECT COUNT(*) FROM my_facilities) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] getting count for a specific querry
Quoting Scott Marlowe <[EMAIL PROTECTED]>:
> On Fri, 2005-04-08 at 12:08, Joel Fradkin wrote:
> > I might have to add a button to do the count on command so they don't get
> > the hit.
> > I would want it to return the count of the condition, not the currently
> > displayed number of rows.
>
> Judging postgresql on one single data point (count(*) performance) is
> quite unfair. Unless your system only operates on static data and is
> used to mostly do things like counting, in which case, why are you using
> a database?
For the general discussion of slowness of count(*),
and given no entry on the subject in
http://www.postgresql.org/docs/faqs.FAQ.html
... I guess everyone has to be pointed at:
http://archives.postgresql.org/pgsql-hackers/2005-01/msg00247.php
However, the gist of this person's problem is that an adhoc query,
NOT just a 'select count(*) from table', can take remarkably long.
Again, the problem is that PG can't just scan an index.
--
One workaround for this is to use EXPLAIN.
THIS APPLIES TO CASES WHERE THE USER HAS WIDE LIBERTY IN QUERIES.
It's pointless overhead, otherwise.
default_statistics_target is cranked up to 200 on all such tables,
and pg_autovacuum is running. (If there were anything to improve,
it would be refining the thresholds on this).
If the "(cost...rows=" string returns a number higher than the
QUERY row limit, the user is derailed ("That's not specific enough to answer
immediately; do you want an emailed report?").
Otherwise, it runs EXPLAIN ANALYZE, which is still faster than the query itself.
If the "(actual...rows=...)" is higher than the RESULT row limit (PAGE
limit).
It then runs the query, with the PAGE rows offset and limit --- and happily,
practically everything that query needs is now in shared_buffers.
The count from the EXPLAIN analyze is displayed in the web page.
--
"Dreams come true, not free." -- S.Sondheim, ITW
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] weird SQL statement question
Quoting Frank Bax <[EMAIL PROTECTED]>: > At 01:55 PM 4/23/05, Tadej Kanizar wrote: > > >Ok, so I installed the latest version of Postresql (8.02) on a WinXP > SP2 > >machine.. > > > >My question is why won't the statement SELECT * FROM table work, > whereas > >the statement SELECT * FROM »table« works! > If you create a table using quotes and mixed-case, you will need > quotes > every time you reference the table. Don't use quotes when you create > the > table and you won't need them when you acces the table with other > commands. I don't suppose that the original poster actually named the table "table"? In which case, being a keyword, it will ALWAYS need quotes. "Dreams come true, not free." -- S.Sondheim, ITW ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Trimming the cost of ORDER BY in a simple query
Quoting [EMAIL PROTECTED]:
> I have a simple query with a pretty high cost (EXPLAIN ...), and I'm
> wondering if I can somehow trim it.
>
> Query (shows the last 7 dates):
>
> => SELECT DISTINCT date_part('year', uu.add_date),
> date_part('month',
> uu.add_date), date_part('day', uu.add_date) FROM user_url uu
> WHERE
> uu.user_id=1 ORDER BY date_part('year', uu.add_date) DESC,
> date_part('month', uu.add_date) DESC, date_part('day',
> uu.add_date)
> DESC LIMIT 7;
>
> QUERY PLAN:
>
-
> Limit (cost=4510.14..4522.93 rows=2 width=8) (actual
> time=19.924..20.160 rows=7 loops=1)
>-> Unique (cost=4510.14..4522.93 rows=2 width=8) (actual
> time=19.919..20.139 rows=7 loops=1)
> -> Sort (cost=4510.14..4513.34 rows=1279 width=8) (actual
> time=19.915..20.004 rows=78 loops=1)
>Sort Key: date_part('year'::text, add_date),
> date_part('month'::text, add_date), date_part('day'::text, add_date)
>-> Index Scan using foo on user_url uu
> (cost=0.00...14 rows=1279 width=8) (actual time=0.095..14.761
> rows=1225 loops=1)
> Index Cond: (user_id = 1)
> Total runtime: 20.313 ms
> (7 rows)
>
>
> It looks like the cost is all in ORDER BY, and if I remove ORDER BY
> the
> execution time goes from 20-90 ms to less than 1 ms.
>
> I do need the 7 most recent add_dates. Is there a more efficient
> way
> of grabbing them?
The query analyzer is using the sort to detect and return distinct
values, as well. So there's not much point in trying to remove it.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] HELP: aggregating insert rule for multirow inserts.
I'm having a problem with the use of the NEW rowset,
in a rule intended to aggregate across inserts.
I've never really grokked how NEW and OLD really work,
syntactically, other than that they seem to be implicit
in every top-level FROM clause, and any mention elsewhere
gets an error: '42P01: relation "*NEW*" does not exist'.
I've tried different flavours of the UPDATE command,
in the following rule, and they either produce syntax errors
or the wrong results.
Any suggestions much appreciated ...
== CODE
"How many critters are in the zoo, of the 4,5,6...-legged varieties?"
create table critter(name text, legs int);
create table zoostats(legs int, headcount int default 0,
primary key(legs));
create or replace rule critter_counter as
on INSERT to critter do (
insert into zoostats
select distinct new.legs
where new.legs not in (select legs from zoostats);
update zoostats
setheadcount = headcount + (select count(*)) -- "from new"
where new.legs = zoostats.legs
);
insert into critter values('cat',4);
insert into critter values('starfish',5);
insert into critter values('ant',6);
insert into critter values('dog',4);
insert into critter select * from critter; -- double everything.
select * from zoostats;
drop table zoostats cascade;
drop table critter;
== EXPECTED OUTPUT
legs headcount
-
4 4
5 2
6 2
== ACTUAL OUTPUT
legs headcount
-
4 3 -- !?
5 2
6 2
== OTHER ATTEMPT:
This version of the update looks syntactically right to me,
but makes CREATE RULE fail on a syntax error:
...
update zoostats
set headcount = headcount + tally
from (select new.legs, count(new.legs) as tally -- from new !?
group by new.legs) as poll
where poll.legs = zoostats.legs;
ERROR: 'Subquery in FROM may not refer to other relations
of same query level'.
--
Engineers think equations approximate reality.
Physicists think reality approximates the equations.
Mathematicians never make the connection.
---(end of broadcast)---
TIP 3: 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] Doing range-predicates right (correction)
A few weeks ago I posted a way to do efficient range predicate joins, given only B-tree indexes. I've since gotten back home and looked at the code I last used. My apologies for an offhand hasty posting. The following is the solution I worked out when I used this method on a large data conversion. It has the advantage (over what I posted) that the indexable ranges fit more tightly around the actual ranges --- a pseudo-range will never be more than twice as wide as the original range. For those who didn't see the original, the problem was, how do you get efficient lookups against large tables of the form: TABLE Ranges(rangeid , lower INT, upper INT) ... or any range-bound type that can be mapped to int (e.g. by mapping timestamps to epoch-seconds). ... with queries like select rangeid, sampleid from Samples join Ranges on Samples.val between Ranges.lower and Ranges.upper The problem is that a Btree index on Ranges.lower (or .upper) is ineffective; in most cases, the query planner will rightly ignore it. One (my) solution is to map the given ranges into slightly larger ranges, that have a small number of different widths, and all start on a more limited set of boundaries. One way to do this is to map all ranges to ranges that have a width that is a power of two, and that begin on a whole multiple of that power of two. Unfortunately, if you just map a range (width) to the smallest power of 2 greater than (upper-lower), then lower and upper may be in two different ranges of that width. For example, if your original range is [1003..1030] (i.e. width 28), the power of 2 that covers this range is 32, but 1003 is in the range [992..1023] and 1030 is the one above it. A sloppy fix for this is to take the next higher power of two as the pseudo-width. The original solution created a new version of Ranges that had as many rows as the original Ranges table. The following solution creates a new version with no more than twice as many rows, but with. -- Function to return the lowest power of two greater than -- a given inclusive interval: create or replace function width2(int, int) returns int immutable language 'plpgsql' as ' begin return pow(2, ceil(log(2, $2-$1+1)))::int; end'; -- Construct an augmented Ranges table: select rangeid, lower, upper, width, start-mod(start, width) as start intoPseudoRange from ( select rangeid, lower, upper, start, width2(start, finish) as width from ( select rangeid, lower, upper, lower as start, upper-mod(upper, width2(lower,upper))-1 as finish from Range union all select rangeid, lower, upper, upper-mod(upper, width2(lower,upper)) as start, upper as finish from Range ) as X where start <= finish ) as Y; create unique index PR_start_width on PseudoRange(start,width); The query using PseudoRange also uses a table PseudoWidth. If (lower) and (upper) are ints, this table can at most have 31 rows with values (1,2,4,8,...). This can also be calculated by: select distinct width into PseudoWidth from PseudoRange; ... which will have fewer values, for proportionately faster lookups. The lookup query becomes: select rangeid, sampleid from Samples, PseudoRange as PR, PseudoWidth as PW where PR.width = PW.width and PR.start = Samples.val - (Samples.val % PW.width) and Samples.val between PR.lower and PR.upper The usual query plan will do at most (!) 31 index lookups per Sample row. If this is unacceptable, a power greater than 2 can be used. -- "Dreams come true, not free." -- S.Sondheim ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How do I quit in the middle of a SQL script?
Quoting John DeSoi <[EMAIL PROTECTED]>: > > On May 20, 2005, at 1:22 PM, Wei Weng wrote: > > > Say if I want to add a small snip of code in front of the sql script > > generated by the pg_dump, to check for something then if the condition > > doesn't match, the script terminates right away. (Without actually > > doing the restoring stuff that the following large chunk is supposed > > to do) > > > > Can I do that? > > Put this at the start of the file to make psql stop if there is an > error: > > \set ON_ERROR_STOP 1 > > > And is it a good idea to add arbitrary code to the database dump sql > > script? > > No problem if you know what you are doing and/or have good backups :) You don't have to: pg_restore mydb.dump | psql --set ON_ERROR_STOP=1 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How do write a query...
> |From: Alain Reymond [mailto:[EMAIL PROTECTED] > |Sent: Mittwoch, 01. Juni 2005 18:01 > |Subject: [SQL] How do write a query... > | > |I have a table like > |IdNum Date AValue > |1 10 01/01/2005 50 > |2 10 31/05/2005 60 > |3 25 02/02/2005 55 > |4 25 15/03/2005 43 > |5 25 28/05/2005 62 > |etc.. > | > |Id is unique, Num is an identification number with duplicates > possible, > |date is a ... date and Avalue... a value! > | > |If we have > |IdNum Date AValue > |Id1 Num1Date1 AValue1 > |Id2 Num1Date2 AValue2 > | > |The table is ordered on Num+Date. > |What I would like to calculate is (AValue2-AValue1) for a given Num > |(here num1). > | > |In this case, I would have to calculate > |60-50 for Num 10 > |and > |43-55, 62-43 for Num 25. Not sure if this covers all that you want, given your examples (what if there's only one row for a given Num value?), but ... select Num, AValue-AValue1 from Tafel join (select Num, min(AValue) as AValue1 from Tafel group by Num) as T using(Num) where AValue > AValue1 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] getting back autonumber just inserted
Quoting "jimmy.olsen" <[EMAIL PROTECTED]>:
> I don't know how to create a Global Variable in Postgres, but the
> idea is
> very simple:
> 1. Encapsulate the NextVal function in MyNextVal
> 2. Set to Global variable with NextVal of the desired sequence
> 3. Inspect to value of the global variable (exactally as it's done in
> MsSQL)
Not sure if this thread is about global variables or just how to use
sequences, but ... if you want variables that are global, you can use a
table (global to all processes), a temp table (global to everything
inside one connection), or a row in a global table (can be either). I
tend to keep one global table of (key,value) pairs, with a primary key
constraint so that the query optimizer knows that "SELECT val FROM Env
WHERE key='xxx'" will return at most 1 row. The "keys" are actually
structured paths ("mail.process.thread_limit").
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] sum but not grouped by?
Quoting Henry Ortega <[EMAIL PROTECTED]>: > I have the ff data: > > id | date | hours > AAA07-01-2005 3 > AAA07-02-2005 4 > BBB07-01-2005 6 > BBB07-02-2005 2 > BBB07-03-2005 7 > > Would it be possible to get the ff: > > id | date | hours | id_total > AAA07-01-2005 3 7 > AAA07-02-2005 4 7 > BBB07-01-2005 6 15 > BBB07-02-2005 2 15 > BBB07-03-2005 7 15 > > So it's like SUM OF, but not Grouped By? Is this possible at all? > Thank you for any help. You're really joining two sets: select FFDATA.id, FFDATA.date, FFDATA.hours, FFSUM.id_total fromFFDATA join (select id, sum(hours) as id_total from FFDATA group by id ) as FFSUM using(id) ---(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
Re: [SQL] sum but not grouped by?
Quoting Henry Ortega <[EMAIL PROTECTED]>: > Is it possible at all to do this without any joins > or subselect? Nope. But I'm curious why you make that a condition? ---(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
Re: [SQL] How to alias table columns in result?
Quoting nori <[EMAIL PROTECTED]>: > Hi, > > If I have: > 1.) table car with columns index and name > 2.) table driver with columns index, name and car_index > > and query: > SELECT d.*, c.* FROM driver as d LEFT OUTER JOIN car AS c ON > d.car_index=c.index; > > How can I get results that have distinct names for columns (ex. > d.name, d.index, c.name, c.index,...)? Here's where you have to get explicit; d.* won't work. If you want to have names with (.) in them, they have to be quoted, too. Me, I'd use "d_name, d_index, c_name, ..." SELECT d.name as "d.name", d.index as "d.index", c.name as "c.name", ... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SQL output
Quoting Simon Law <[EMAIL PROTECTED]>:
>
> CREATE TABLE tablename (field INTERVAL);
> INSERT INTO tablename VALUES('3 weeks');
> SELECT field FROM tablename;
> | 21 days |
>
> The output shows up in days or months but not weeks how do i make
Internally, INTERVAL is stored as a 12byte tuple (years, months, days,
hours, minutes, seconds, microseconds). It discards any knowledge of
"weeks" (and "centuries" likewise) when it encodes the interval. So
there's no way to force it to say "weeks" back to you. There is no
datestyle that will do it for you, either.
You can MANUALLY extract the number of days in the interval, and divide
by 7 (round up or down, your choice).
SELECT EXTRACT(DAYS FROM INTERVAL '3 WEEKS')
Note, however, that if you define an interval with units greater than
days (i.e. months or years) you'll get nothing, which is reasonable:
months and years do not have fixed numbers of weeks in them.
---(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
Re: [SQL] Is it This Join Condition Do-Able?
Quoting Dmitri Bichko <[EMAIL PROTECTED]>: > How about: > > SELECT a.keyfld, a.foo1, b.foo2, c.foo3 > FROM a > LEFT JOIN b USING(keyfld) > LEFT JOIN c USING(keyfld) ((( See response at end ))) > > -Original Message- > > [mailto:[EMAIL PROTECTED] On Behalf Of Lane Van > Ingen > > Sent: Wednesday, August 17, 2005 12:55 PM > > Subject: [SQL] Is it This Join Condition Do-Able? > > > > Given three tables: a, b, c ; each consist of a 'keyfld' and > > a field called > > 'foo': > > tbl a tbl b tbl c > >- - - > >a.keyfldb.keyfld c.keyfld > >a.foo1 b.foo2 c.foo3 > > > > I want to always return all of tbl a; and I want to return > > b.foo2 and c.foo3 if they can be joined to based on keyfld.a; > > I know that it will involve a LEFT OUTER JOIN on table a, but > > have not seen any examples of joins like this on 3 or more tables. ... Having a bit of uncertainty of how LEFT JOIN associates, I tried the following test (psql -qe), with (to me) highly surprising results. Anyone care to comment on the third row of output? select version(); version --- PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux) create temp table a(keyf int, val text); create temp table b(keyf int, val text); create temp table c(keyf int, val text); insert into a values(1, 'A1'); insert into a values(2, 'A2'); insert into a values(3, 'A3'); insert into a values(4, 'A4'); insert into b values(1, 'B1'); insert into b values(2, 'B2'); insert into c values(2, 'C2'); insert into b values(3, 'C3'); select keyf, a.val as aval, coalesce(b.val,'Bxx') as bval, coalesce(c.val,'Cxx') as cval from a left join b using(keyf) left join c using (keyf); keyf aval bval cval 1 A1 B1 Cxx 2 A2 B2 C2 3 A3 C3 Cxx 4 A4 Bxx Cxx ---(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
Re: [SQL] Is it This Join Condition Do-Able? Ooh, ouch, blush
The Subject says it all. (author beats a hasty retreat). Quoting Dmitri Bichko <[EMAIL PROTECTED]>: > I don't see what the problem is. > Did you mean to insert (3,'C3') into table c, rather than b? > > create temp table a(keyf int, val text); > > create temp table b(keyf int, val text); > > create temp table c(keyf int, val text); > > insert into a values(1, 'A1'); > > insert into a values(2, 'A2'); > > insert into a values(3, 'A3'); > > insert into a values(4, 'A4'); > > insert into b values(1, 'B1'); > > insert into b values(2, 'B2'); > > insert into c values(2, 'C2'); > > insert into b values(3, 'C3'); > > select keyf, a.val as aval, > > coalesce(b.val,'Bxx') as bval, > > coalesce(c.val,'Cxx') as cval > > from a left join b using(keyf) left join c using (keyf); > > keyf aval bval cval > > > >1 A1 B1 Cxx > >2 A2 B2 C2 > >3 A3 C3 Cxx > >4 A4 Bxx Cxx ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
