Skip site navigation (1) Skip section navigation (2)

Re: Whole-row comparison ?

From: <christian(dot)roche(dot)ext(at)nsn(dot)com>
To: <rabroersma(at)yahoo(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Whole-row comparison ?
Date: 2007-05-17 18:00:47
Message-ID: CCC9DCA122011F4CA593F6A548BFFBD354D260@esebe111.NOE.Nokia.com (view raw or flat)
Thread:
Lists: pgsql-sql
Ok I understand now that this row(t.*) syntax is new to postgres 8.2

As explained in the documentation, ยง4.2.11. Row Constructors:

A row constructor can include the syntax rowvalue.*, which will be expanded to a list of the elements of the row value, just as occurs when the .* syntax is used at the top level of a SELECT list. For example, if table t has columns f1 and f2, these are the same:
SELECT ROW(t.*, 42) FROM t;
SELECT ROW(t.f1, t.f2, 42) FROM t;

This would obviously simplify the syntax of my queries a lot since I have so many columns.  I'm going to try and install the newest version and check it.  I'll keep you informed.

Thanks to all!
Christian

--- 
> select * from temp."BSC_Table" t, public.bsc_view p where t.id = p.id 
> and row(t) <> row(p);
> 
> ERROR: operator does not exist: "temp"."BSC_Table" <> bsc_view SQL 
> state: 42883
> Hint: No operator matches the given name and argument type(s). You may 
> need to add explicit type casts.
> 

Don't forget to reply all so that everyone on the list can participate.  Also, do this is good since it doesn't limit you to my limited knowledge. ;)

row() doesn't do what you think it does.

you have to specify every column that you want to compare, so:

row(t.col1, t.col2, t.col3, t.col4) <> row(p.col1, p.col2, p.col3, p.col4)

where the datatypes of each respective column match. i.e. t.col1 maps to p.col1, ...

Regards,
Richard Broersma Jr.

In response to

Responses

pgsql-sql by date

Next:From: LewDate: 2007-05-18 12:43:41
Subject: Re: hi
Previous:From: Richard Broersma JrDate: 2007-05-17 17:20:35
Subject: Re: Whole-row comparison ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group