checksum

[Federico Balbi]

Hi,
I was wondering if PGSQL has a function similar to binary_checksum () of
MS SQL Server 2000. It is pretty handy when it comes to compare rows of
data instead of having to write long boolean expressions.
binary_checksum () takes a list of fields and it returns an integer value
which sumarize the row content.

Thanks,
Fed
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[David Helgason]

> I was wondering if PGSQL has a function similar to binary_checksum ()

of
MS SQL Server 2000. It is pretty handy when it comes to compare rows of
data instead of having to write long boolean expressions.
binary_checksum () takes a list of fields and it returns an integer
value
which sumarize the row content.

On a similar note, I've found myself wanting an extended '=' operator
meaning
(a = b or (a is null and b is null))

same goal of course, for more general comparisons...

d.
--
David Helgason,
Business Development et al.,
Over the Edge I/S (http://otee.dk)
Direct line [phone]
Main line [phone]
On 26. sep 2004, at 19:58, Federico Balbi wrote:
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Joshua D. Drake]

David Helgason wrote:

I was wondering if PGSQL has a function similar to
binary_checksum () of
MS SQL Server 2000. It is pretty handy when it comes to compare rows of
data instead of having to write long boolean expressions.
binary_checksum () takes a list of fields and it returns an integer value
which sumarize the row content.

You could use the md5 function.... such as :

select md5(foo) from bar where baz = 2;

J

On a similar note, I've found myself wanting an extended '=' operator
meaning
(a = b or (a is null and b is null))

same goal of course, for more general comparisons...

d.

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - jd@commandpromp t.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Federico Balbi]

> David Helgason wrote:

I was wondering if PGSQL has a function similar to
binary_checksum () of
MS SQL Server 2000. It is pretty handy when it comes to compare rows of
data instead of having to write long boolean expressions.
binary_checksum () takes a list of fields and it returns an integer value
which sumarize the row content.

You could use the md5 function.... such as :

select md5(foo) from bar where baz = 2;

Looks like md5() takes only a string. I need to pass alist of fields
instead. I was looking at the documentattion and I think I can write
soemthing like:

field1, field2, ..., fieldn = expr1, expr2, ..., exprn

This way one operator will check all the fields for equality.

Fed
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Joshua D. Drake]

You could use the md5 function.... such as :

select md5(foo) from bar where baz = 2;
Looks like md5() takes only a string. I need to pass alist of fields
instead. I was looking at the documentattion and I think I can write
soemthing like:

field1, field2, ..., fieldn = expr1, expr2, ..., exprn

This way one operator will check all the fields for equality.

Maybe I am missing what you are saying, but you can md5() the data
column... So you could do:

select one,two from foo where md5(one) = 'e4da3b7fbbce23 45d7772b0674a31 8d5';

or

select one,two from foo where md5(one) = $1; or whatever.

for example....

Sincerely,

Joshua D. Drake
Fed
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - jd@commandpromp t.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

[Mike Rylander]

On Sun, 26 Sep 2004 20:16:52 +0200, David Helgason <da***@uti.is > wrote:

I was wondering if PGSQL has a function similar to binary_checksum ()
of
MS SQL Server 2000. It is pretty handy when it comes to compare rows of
data instead of having to write long boolean expressions.
binary_checksum () takes a list of fields and it returns an integer
value
which sumarize the row content.

As noted, you can use the md5(text) function with the || (concat) operator
On a similar note, I've found myself wanting an extended '=' operator
meaning
(a = b or (a is null and b is null))

Setting 'transform_null _equals' to true in postgresql.conf should do
what you want.

--miker
same goal of course, for more general comparisons...

d.
--
David Helgason,
Business Development et al.,
Over the Edge I/S (http://otee.dk)
Direct line [phone]
Main line [phone]
On 26. sep 2004, at 19:58, Federico Balbi wrote:

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

On Mon, 27 Sep 2004, Mike Rylander wrote:

On Sun, 26 Sep 2004 20:16:52 +0200, David Helgason <da***@uti.is > wrote:

On a similar note, I've found myself wanting an extended '=' operator
meaning
(a = b or (a is null and b is null))

Setting 'transform_null _equals' to true in postgresql.conf should do
what you want.

Unfortunately, it probably won't. That only changes the explicit token
sequence = NULL into an IS NULL, it won't help if you're doing a=b where a
or b may be NULL.

The original does appear to be equivalent to "not(a is distinct from b)",
although I'm not sure that's necessarily easier to use than the above.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Greg Stark]

Stephan Szabo <ss****@megazon e.bigpanda.com> writes:

On Sun, 26 Sep 2004 20:16:52 +0200, David Helgason <da***@uti.is > wrote:

On a similar note, I've found myself wanting an extended '=' operator
meaning
(a = b or (a is null and b is null))

The original does appear to be equivalent to "not(a is distinct from b)",
although I'm not sure that's necessarily easier to use than the above.

I often do things like "coalesce(a ,0) = coalesce(b,0)".
(Or whatever value you know won't appear)

Though for pretty small values of "often". It always makes me think twice
about my data model when I find myself doing this. But there are definitely
still cases where it's useful and as clean as anything else I could think of.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Dean Gibson]

Even simpler: COALESCE( a = b, a IS NULL AND b IS NULL )

-- Dean

Greg Stark wrote on 2004-09-27 08:17:

Stephan Szabo <ss****@megazon e.bigpanda.com> writes:

On Sun, 26 Sep 2004 20:16:52 +0200, David Helgason <da***@uti.is > wrote:

> On a similar note, I've found myself wanting an extended '=' operator
> meaning
> (a = b or (a is null and b is null))

The original does appear to be equivalent to "not(a is distinct from b)",
although I'm not sure that's necessarily easier to use than the above.

I often do things like "coalesce(a ,0) = coalesce(b,0)".
(Or whatever value you know won't appear)

Though for pretty small values of "often". It always makes me think twice
about my data model when I find myself doing this. But there are definitely
still cases where it's useful and as clean as anything else I could think of.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[David Helgason]

On 27. sep 2004, at 22:08, Dean Gibson (DB Administrator) wrote:

Greg Stark wrote on 2004-09-27 08:17:

Stephan Szabo <ss****@megazon e.bigpanda.com> writes:

>> On Sun, 26 Sep 2004 20:16:52 +0200, David Helgason <da***@uti.is >

wrote:

>>> On a similar note, I've found myself wanting an extended '='

operator

>>> meaning
>>> (a = b or (a is null and b is null))
>
> The original does appear to be equivalent to "not(a is distinct

from b)",

> although I'm not sure that's necessarily easier to use than the

above.

I often do things like "coalesce(a ,0) = coalesce(b,0)".
(Or whatever value you know won't appear)

Even simpler: COALESCE( a = b, a IS NULL AND b IS NULL )

I'm not quite sure what is being accomplished here... My original
expression wasn't that bad, just clunky. I'd prefer a === b or (a
samevalue b), but the above just complicates matters. Also, a 'set'
command outside the expression goes completely against the idea, that
certain fields have 'null' as a legal, comparable value, while others
do not.

Anyway, idle speculation :)

d.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match