pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Richard Huxton <dev@archonet.com>
To: David Johnston <polobo@yahoo.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 20:59:23 +0000
Message-ID: <4F679E2B.2010207@archonet.com> (raw)
In-Reply-To: <00b101cd060b$6f87a560$4e96f020$@yahoo.com>
References: <1332186006.44650.YahooMailNeo@web121605.mail.ne1.yahoo.com>
	<00b101cd060b$6f87a560$4e96f020$@yahoo.com>

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

view thread (5+ messages)  latest in thread

Message-ID: <4F679E2B.2010207@archonet.com>
Permalink:  ../4F679E2B.2010207@archonet.com/
Also on:    postgresql.org/message-id/4F679E2B.2010207@archonet.com

copy link · copy postgr.es

reply

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: dev@archonet.com, polobo@yahoo.com, pk_rehan@yahoo.com
  Subject: Re: Postgresql function which compares values from both tables
  In-Reply-To: <4F679E2B.2010207@archonet.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