pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhole-row comparison ?
4+ messages / 2 participants
[nested] [flat]
* Whole-row comparison ?
@ 2007-05-17 12:56 christian.roche.ext@nsn.com
0 siblings, 1 reply; 4+ messages in thread
From: christian.roche.ext@nsn.com @ 2007-05-17 12:56 UTC (permalink / raw)
To: pgsql-sql
Hi there,
I'm trying to implement a "versionned" storage for a datawarehouse
system, meaning I have tables which store daily information about
objects and I would like to have a row inserted (i.e. a new version)
only if it differs from the most recent existing row. For instance
instead of storing
version attribute1 attribute2
1 x y
2 x y
3 x y
4 z y
5 z y
6 z t
we would only keep the diffs :
version attribute1 attribute2
1 x y
4 z y
6 z t
This would save lots of space and the full table could be easily
accessed through a view.
However as the tables have a large number of rows (several hundreds) I
would like to avoid having to write conditions like WHERE (old.att1 !=
new.attr1 or old.attr2 != new.attr2 or ... or old.attr245 != new.attr245
)
So my question is: is there a way to do some kind of whole-row
comparison ? For instance a hash of the full row or something similar ?
Is there a standard way of solving this problem ?
Thanks a lot for any hind !
Christian
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Whole-row comparison ?
@ 2007-05-17 13:38 Richard Broersma Jr <rabroersma@yahoo.com>
parent: christian.roche.ext@nsn.com
0 siblings, 0 replies; 4+ messages in thread
From: Richard Broersma Jr @ 2007-05-17 13:38 UTC (permalink / raw)
To: christian.roche.ext@nsn.com; pgsql-sql
> So my question is: is there a way to do some kind of whole-row
> comparison ? For instance a hash of the full row or something similar ?
> Is there a standard way of solving this problem ?
Sure it is called row wise comparison:
http://www.postgresql.org/docs/8.2/interactive/functions-comparisons.html#ROW-WISE-COMPARISON
you can write something like:
SELECT *
FROM Your_table
WHERE (c1,c2,c3,c4) = ('a',2,'xyz',123);
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Whole-row comparison ?
@ 2007-05-17 17:20 Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Richard Broersma Jr @ 2007-05-17 17:20 UTC (permalink / raw)
To: christian.roche.ext@nsn.com; +Cc: pgsql-sql
--- christian.roche.ext@nsn.com wrote:
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Whole-row comparison ?
@ 2007-05-17 18:00 christian.roche.ext@nsn.com
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: christian.roche.ext@nsn.com @ 2007-05-17 18:00 UTC (permalink / raw)
To: rabroersma@yahoo.com; +Cc: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-05-17 18:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-17 12:56 Whole-row comparison ? christian.roche.ext@nsn.com
2007-05-17 13:38 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-05-17 17:20 Re: Whole-row comparison ? Richard Broersma Jr <rabroersma@yahoo.com>
2007-05-17 18:00 ` christian.roche.ext@nsn.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