Hash Functions

ora_hash(expression,[seed])

Description: Calculates the hash value of a given expression. expression: The value can be a character string, time, or number. The hash value is calculated based on the expression. seed: an int8 value that can return different results for the same input value. This parameter is optional and is used to calculate the hash value with a random number.

Return type: hash value of the int8 type.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

gaussdb=#SELECT ora_hash(123);
 ora_hash 
------------
 4089882933
(1 row)
gaussdb=#SELECT ora_hash('123');
 ora_hash 
------------
 2034089965
(1 row)
gaussdb=#SELECT ora_hash('sample');
 ora_hash 
------------
 1573005290
(1 row)
gaussdb=#SELECT ora_hash(to_date('2012-1-2','yyyy-mm-dd'));
 ora_hash 
------------
 1171473495
(1 row)
gaussdb=#SELECT ora_hash(123,234);
 ora_hash 
------------
 -9089505052966355682
(1 row)
gaussdb=#SELECT ora_hash('123',234);
 ora_hash 
------------
 5742589019960764616
(1 row)
gaussdb=#SELECT ora_hash('sample',234);
 ora_hash 
------------
 -1747984408055821656
(1 row)
gaussdb=#SELECT ora_hash(to_date('2012-1-2','yyyy-mm-dd'),234);
 ora_hash 
------------
 -3306025179710572679
(1 row)

This function is valid only when a_format_version is set to 10c and a_format_dev_version is set to s2.

hash_array(anyarray)

Description: Hashes an array, obtains the result of an array element using the hash function, and returns the combination result.

Parameter: data of the anyarray type.

Return type: integer

Example:

1
2
3
4
5

gaussdb=#SELECT hash_array(ARRAY[[1,2,3],[1,2,3]]);
 hash_array 
------------
 -382888479
(1 row)

hash_numeric(numeric)

Description: Calculates the hash value of numeric data.

Parameter: data of the numeric type.

Return type: integer

Example:

1
2
3
4
5

gaussdb=#SELECT hash_numeric(30);
 hash_numeric 
--------------
   -282860963
(1 row)

hash_range(anyrange)

Description: Calculates the hash value of a range.

Parameter: data of the anyrange type.

Return type: integer

Example:

1
2
3
4
5

gaussdb=#SELECT hash_range(numrange(1.1,2.2));
 hash_range 
------------
  683508754
(1 row)

hashbpchar(character)

Description: Calculates the hash value of bpchar.

Parameter: data of the character type.

Return type: integer

Example:

1
2
3
4
5

gaussdb=#SELECT hashbpchar('hello');
 hashbpchar  
-------------
 -1870292951
(1 row)

If the GUC parameter behavior_compat_options is set to a_hash_bpchar, the hash value is calculated with spaces at the end of the character string retained.

hashchar(char)

Description: Converts char and Boolean data into hash values.

Parameter: data of the char or bool type.

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

gaussdb=#SELECT hashbpchar('hello');
 hashbpchar  
-------------
 -1870292951
(1 row)

gaussdb=#SELECT hashchar('true');
  hashchar  
------------
 1686226652
(1 row)

If the GUC parameter behavior_compat_options is set to a_hash_bpchar, the hash value is calculated with spaces at the end of the character string retained.

hashenum(anyenum)

Description: Converts enumerated values to hash values.

Parameter: data of the anyenum type.

Return type: integer

Example:

1
2
3
4
5
6
7

gaussdb=#CREATE TYPE b1 AS ENUM('good', 'bad', 'ugly');
CREATE TYPE
gaussdb=#call hashenum('good'::b1);
  hashenum  
------------
 1821213359
(1 row)

hashfloat4(real)

Description: Converts float4 values to hash values.

Parameter: data of the real type.

Return type: integer

Example:

1
2
3
4
5

gaussdb=#SELECT hashfloat4(12.1234);
 hashfloat4 
------------
 1398514061
(1 row)

hashfloat8(double precision)

Description: Converts float8 values to hash values.

Parameter: data of the double precision type.

Return type: integer

Example:

1
2
3
4
5

gaussdb=#SELECT hashfloat8(123456.1234);
 hashfloat8 
------------
 1673665593
(1 row)

hashinet(inet)

Description: Converts inet or cidr values to hash values.

Parameter: data of the inet type.

Return type: integer

Example:

1
2
3
4
5

gaussdb=#SELECT hashinet('127.0.0.1'::inet);
  hashinet   
-------------
 -1435793109
(1 row)

hashint1(tinyint)

Description: Converts INT1 values to hash values.

Parameter: data of the tinyint type.

Return type: uint32

Example:

1
2
3
4
5

gaussdb=#SELECT hashint1(20);
  hashint1   
-------------
 -2014641093
(1 row)

hashint2(smallint)

Description: Converts INT2 values to hash values.

Parameter: data of the smallint type.

Return type: uint32

Example:

gaussdb=#SELECT hashint2(20000);
  hashint2  
------------
 -863179081
(1 row)