Thread

1. Postgresql function which compares values from both tables

   Rehan Saleem <pk_rehan@yahoo.com> — 2012-03-19T19:40:06Z

   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
   

2. Re: Postgresql function which compares values from both tables

   David Johnston <polobo@yahoo.com> — 2012-03-19T20:04:04Z

   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
   
   

3. Re: Postgresql function which compares values from both tables

   Richard Huxton <dev@archonet.com> — 2012-03-19T20:59:23Z

   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
   
   

4. Re: Postgresql function which compares values from both tables

   David Johnston <polobo@yahoo.com> — 2012-03-19T22:14:53Z

   > -----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.
   
   
   
   

5. Re: Postgresql function which compares values from both tables

   Jayadevan M <jayadevan.maymala@ibsplc.com> — 2012-03-20T03:21:10Z

   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."