PostgreSQL中encrypt()函数如何对字符串类型数据进行加密？

Got it, let's break this down for you. The encrypt() function expects your input data to be bytea, but your mobile_no is a string—so the core fix here is converting that string value to a bytea type before passing it to the encryption function.

Step 1: Convert the String to bytea #

Use PostgreSQL's convert_to() function to transform your string into a bytea value. This function lets you specify a character encoding (like UTF-8) to ensure your string converts correctly without garbling data.

For your mobile_no field, the conversion looks like this:

convert_to(mobile_no, 'UTF8')

Step 2: Full Encryption Examples #

Here's how to apply this in common scenarios—whether you're inserting new data, updating existing records, or selecting encrypted values:

Example 1: Insert a new record with encrypted mobile_no

INSERT INTO your_table_name (name, encrypted_mobile)
VALUES (
  'Jane Smith',
  encrypt(convert_to('9876543210', 'UTF8'), convert_to('your_secure_secret_key', 'UTF8'), 'aes')
);

Example 2: Update existing records to encrypt mobile_no

UPDATE your_table_name
SET encrypted_mobile = encrypt(convert_to(mobile_no, 'UTF8'), convert_to('your_secure_secret_key', 'UTF8'), 'aes')
WHERE mobile_no IS NOT NULL;

Step 3: Decrypting the Value (Bonus) #

If you need to retrieve the original string later, use decrypt() to get the bytea value back, then convert it to a string with convert_from():

SELECT 
  name,
  convert_from(decrypt(encrypted_mobile, convert_to('your_secure_secret_key', 'UTF8'), 'aes'), 'UTF8') AS mobile_no
FROM your_table_name;

Key Notes #

• Secure your key: Never hardcode encryption keys in production queries—use a dedicated secrets manager instead.
• Match encodings: Stick to the same encoding (like UTF-8) for both encryption and decryption to avoid data corruption.
• Verify algorithm support: The type parameter (e.g., 'aes') depends on which encryption algorithms your PostgreSQL instance supports—double-check what's available in your environment.

内容的提问来源于stack exchange，提问作者Vivekrao Bhosale