Product Documentation

Appearance

DECRYPT (deprecated) ​

Given an encrypted character string, return the decrypted value. See also ENCRYPT (deprecated).

DECRYPT(encrypted_string, key [, algorithm [, ivec]])

This function returns a VARCHAR value that is slightly smaller than the input expression. The formula depends on the algorithm specified:

  • If algorithm 1, 2, or 3 is explicitly specified as a constant in the function:
VARCHAR(FLOOR((input * 6) / 8))
  • Otherwise (algorithm = constant > 3, not specified, or not a constant, such as the result of an expression):
VARCHAR(CEIL(input / 4) * 3)

Parameters ​

encrypted_string

An encrypted character string (Base64-encoded), as produced by the ENCRYPT function. This string represents the data that you want to decrypt on output.

key

A hexadecimal string or an expression that evaluates to a hexadecimal string. For example, you can specify a character string inside an MD5 function. This key is like a password, providing a level of security for your use of the function and a guarantee of consistent encryption and decryption. To produce consistent results when encrypting and decrypting the same input expression, users must provide the same key value. See the description of the algorithm parameter for information about the required length of the key. See also Encrypting Sensitive Data.

algorithm

The specific encryption algorithm that you want to use. Valid entries are 1, 2, and 3. The default is 1. All of these algorithms use Output Feedback Mode (OFB).

Function parameterAlgorithm
1Initialize AES with a 128-bit key
2Initialize AES with a 192-bit key
3Initialize AES with a 256-bit key

The algorithm that you select determines the required size of the key value: 128, 192, or 256 bits. The size of the initialization vector (ivec) for all three algorithms is 128 bits.

When the input key or ivec is too short, the input data is expanded to produce the required length by appending the input provided. When the input key or the ivec is too long, the extra input is folded into the required input, starting from the beginning, using XOR logic.

ivec

An initialization vector for the algorithm (optional). You must specify a hexadecimal string or an expression that evaluates to a hexadecimal string. If you specify a vector, you must also specify an algorithm. See Encrypting Sensitive Data.

Examples ​

Decrypt the nickname column, which is stored encrypted in a table named encrypted_team. The first query shows the encrypted nickname values:

premdb=# select * from encrypted_team order by 1 limit 5;
 teamid | htid | atid |       name       |  nickname   |    city    |     stadium      | capacity 
--------+------+------+------------------+-------------+------------+------------------+----------
     1 |    2 |   51 | Arsenal          | A8jstaVuQ0  | London     | Emirates Stadium |    60260
     2 |    3 |   52 | Aston Villa      | RuCsrql{7K2 | Birmingham | Villa Park       |    42785
     3 |    4 |   53 | Barnsley         | PuztyyE     | Barnsley   | Oakwell Stadium  |    23009
     4 |    5 |   54 | Birmingham City  | FiSmyyE     | Birmingham | St. Andrew's     |    30016
     5 |    6 |   55 | Blackburn Rovers | VWimyuEu    | Blackburn  | Ewood Park       |    31367
(5 rows)

The second query shows the decrypted nickname values. Note that the key provided here must be exactly the same as the key that was used when the column was encrypted. If you do not know this key, you will not be able to decrypt the values correctly.

premdb=# select name, decrypt(nickname, md5('We are such stuff as dreams are made on')) 
from encrypted_team order by teamid limit 5;
      name       | decrypt  
------------------+----------
 Arsenal          | Gunners
 Aston Villa      | Villains
 Barnsley         | Tykes
 Birmingham City  | Blues
 Blackburn Rovers | Rovers
(5 rows)

Parent topic:String Functions