Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pr8DX-0006BX-N7 for pgsql-general@arkaria.postgresql.org; Tue, 25 Apr 2023 02:15:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pr8DW-000471-JA for pgsql-general@arkaria.postgresql.org; Tue, 25 Apr 2023 02:15:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pr8DW-00046m-80 for pgsql-general@lists.postgresql.org; Tue, 25 Apr 2023 02:15:42 +0000 Received: from mout-u-204.mailbox.org ([80.241.59.204]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pr8DT-004u4J-OH for pgsql-general@lists.postgresql.org; Tue, 25 Apr 2023 02:15:41 +0000 Received: from smtp202.mailbox.org (smtp202.mailbox.org [IPv6:2001:67c:2050:b231:465::202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-204.mailbox.org (Postfix) with ESMTPS id 4Q55FM6w13z9sSx; Tue, 25 Apr 2023 04:15:35 +0200 (CEST) Date: Tue, 25 Apr 2023 04:15:35 +0200 (CEST) From: Erik Wienhold To: Jagmohan Kaintura , "pgsql-generallists.postgresql.org" Message-ID: <1167580627.575710.1682388935428@office.mailbox.org> In-Reply-To: References: Subject: Re: murmur3 hash binary data migration from Oracle to PostgreSQL MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4Q55FM6w13z9sSx List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 25/04/2023 03:21 CEST Jagmohan Kaintura wrot= e: > > We are doing Migration from Oracle to PostgreSQL. In SOurce database we h= ave > Binary data stored using murmur3 hashing=C2=A0function. In Oracle this da= ta is > being generated from the Java code and inserted into the Oracle database. Do you store the hash and the binary data? The hash is a key to the binary data? > As part of Migration processes the reference data on which this murmur3 i= s > generated is also getting changed while migrating to PostgreSQL. Why is the data changing during migration? Shouldn't a migration preserve the data and only adapt it if the database model needs to change? > In PostgreSQL do we have any mechanism for fetching this murmur3 hash > function for any UUID. I don't understand what you mean by that. What does it have to do with UUI= D? Do you want to generate the MurmurHash in Postgres? Postgres has no builti= n support for that hash function and I can't find any extension in a quick online search. Or do you want to just look up rows by the MurmurHash? That's a trivial SELECT statement. Store the hash in an indexed column of type bytea to hav= e performant lookups. -- Erik