From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | David Johnston <polobo(at)yahoo(dot)com> |
Cc: | 'Rehan Saleem' <pk_rehan(at)yahoo(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Postgresql function which compares values from both tables |
Date: | 2012-03-19 20:59:23 |
Message-ID: | 4F679E2B.2010207@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 19/03/12 20:04, David Johnston wrote:
> You would need to install the “HSTORE” extension to convert the record
> into a key->value pair then perform the comparison on that.
No, you can do this just fine with a simple join.
BEGIN;
CREATE TABLE ta (id int PRIMARY KEY, d date, t text);
CREATE TABLE tb (id int PRIMARY KEY, d date, t text);
INSERT INTO ta
SELECT 100+n, '2001-01-01'::date + n, 'entry ' || n
FROM generate_series(1,19) n;
INSERT INTO tb
SELECT 200+n, '2001-01-01'::date + n, 'entry ' || n
FROM generate_series(1,19) n;
SELECT ta.id AS id_a, tb.id AS id_b, ta.d, ta.t
FROM ta JOIN tb USING (d,t);
ROLLBACK;
If the fields were named differently you'd need something like:
FROM ta JOIN tb ON (ta.d1, ta.t1) = (tb.d2, tb.t2)
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2012-03-19 22:14:53 | Re: Postgresql function which compares values from both tables |
Previous Message | David Johnston | 2012-03-19 20:04:04 | Re: Postgresql function which compares values from both tables |