pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql function which compares values from both tables
5+ messages / 4 participants
[nested] [flat]
* Postgresql function which compares values from both tables
@ 2012-03-19 19:40 Rehan Saleem <pk_rehan@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Rehan Saleem @ 2012-03-19 19:40 UTC (permalink / raw)
To: pgsql-sql
hi ,
how to write a function which should read data from 2 tables having same number of columns and should show the common values from those tables.
thanks
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql function which compares values from both tables
@ 2012-03-19 20:04 David Johnston <polobo@yahoo.com>
parent: Rehan Saleem <pk_rehan@yahoo.com>
0 siblings, 2 replies; 5+ messages in thread
From: David Johnston @ 2012-03-19 20:04 UTC (permalink / raw)
To: 'Rehan Saleem' <pk_rehan@yahoo.com>; pgsql-sql
You would need to install the "HSTORE" extension to convert the record into
a key->value pair then perform the comparison on that.
Dave
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Rehan Saleem
Sent: Monday, March 19, 2012 3:40 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Postgresql function which compares values from both tables
hi ,
how to write a function which should read data from 2 tables having same
number of columns and should show the common values from those tables.
thanks
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql function which compares values from both tables
@ 2012-03-19 20:59 Richard Huxton <dev@archonet.com>
parent: David Johnston <polobo@yahoo.com>
1 sibling, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2012-03-19 20:59 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: Rehan Saleem <pk_rehan@yahoo.com>; 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql function which compares values from both tables
@ 2012-03-19 22:14 David Johnston <polobo@yahoo.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 5+ messages in thread
From: David Johnston @ 2012-03-19 22:14 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; +Cc: 'Rehan Saleem' <pk_rehan@yahoo.com>; pgsql-sql
> -----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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql function which compares values from both tables
@ 2012-03-20 03:21 Jayadevan M <Jayadevan.Maymala@ibsplc.com>
parent: David Johnston <polobo@yahoo.com>
1 sibling, 0 replies; 5+ messages in thread
From: Jayadevan M @ 2012-03-20 03:21 UTC (permalink / raw)
To: 'Rehan Saleem' <pk_rehan@yahoo.com>; +Cc: pgsql-sql; pgsql-sql-owner@postgresql.org
Hi,
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Postgresql function which compares values from both
tables
>
> hi ,
> how to write a function which should read data from 2 tables having
> same number of columns and should show the common values from those
tables.
If you want to compare and ensure that the values are same for all
columns, please have a look at INTERSECT
http://www.postgresql.org/docs/9.1/static/sql-select.html
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-03-20 03:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-19 19:40 Postgresql function which compares values from both tables Rehan Saleem <pk_rehan@yahoo.com>
2012-03-19 20:04 ` David Johnston <polobo@yahoo.com>
2012-03-19 20:59 ` Richard Huxton <dev@archonet.com>
2012-03-19 22:14 ` David Johnston <polobo@yahoo.com>
2012-03-20 03:21 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
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