From: David Johnston <polobo@yahoo.com>
To: 'Richard Huxton' <dev@archonet.com>
Cc: 'Rehan Saleem' <pk_rehan@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Postgresql function which compares values from both tables
Date: Mon, 19 Mar 2012 18:14:53 -0400
Message-ID: <00dc01cd061d$b6381780$22a84680$@yahoo.com> (raw)
In-Reply-To: <4F679E2B.2010207@archonet.com>
References: <1332186006.44650.YahooMailNeo@web121605.mail.ne1.yahoo.com>
<00b101cd060b$6f87a560$4e96f020$@yahoo.com>
<4F679E2B.2010207@archonet.com>
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of Richard Huxton
> Sent: Monday, March 19, 2012 4:59 PM
> To: David Johnston
> Cc: 'Rehan Saleem'; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Postgresql function which compares values from both
> tables
>
> 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)
>
I think I read into the question too much. If you want to find matching
rows then ( ROW(alias_1.*) = ROW(alias_2.*) ) will work (and you do not even
have to specify the columns explicitly).
Read this for details. Note that if any of the columns can be NULL you need
to be especially careful.
http://www.postgresql.org/docs/9.0/interactive/functions-comparisons.html
I was thinking that you wanted to know which specific columns matched even
if the row as a whole did not.
Dave J.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, dev@archonet.com, pk_rehan@yahoo.com
Subject: Re: Postgresql function which compares values from both tables
In-Reply-To: <00dc01cd061d$b6381780$22a84680$@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox