All Products
Search

This Product

    PolarDB:Converting plaintext and ciphertext

    Document Center

    PolarDB:Converting plaintext and ciphertext

    Last Updated:Mar 30, 2026

    In an always-confidential database cluster, you can change an existing column's encryption state using ALTER TABLE ... ALTER COLUMN. The operation works in both directions: encrypt a plaintext column or decrypt a ciphertext column back to plaintext.

    Prerequisites

    Before you begin, ensure that you have:

    How it works

    The ALTER TABLE ... ALTER COLUMN ... USING statement changes the column type and transforms all existing data in a single operation. The result depends on the current encryption state and the target type you specify:

    Current state Target type Result
    Plaintext Ciphertext (e.g., enc_int4) Encrypts the column
    Ciphertext Original plaintext (e.g., int4) Decrypts the column

    The following table lists the supported type pairs and their corresponding conversion functions.

    Plaintext type Ciphertext type Encrypt function Decrypt function
    int4 enc_int4 encdb.enc_int4_encrypt encdb.decrypt
    int8 enc_int8 encdb.enc_int8_encrypt encdb.decrypt
    float4 enc_float4 encdb.enc_float4_encrypt encdb.decrypt
    float8 enc_float8 encdb.enc_float8_encrypt encdb.decrypt
    numeric enc_decimal encdb.enc_decimal_encrypt encdb.decrypt
    text enc_text encdb.enc_text_encrypt encdb.decrypt
    timestamp enc_timestamp encdb.enc_timestamp_encrypt encdb.decrypt

    Syntax

    ALTER TABLE <table_name>
    ALTER COLUMN <column_name> [SET DATA] TYPE <target_type>
    USING <conversion_function>(<column_name>[, <keyname>]);

    Parameter notes:

    • To decrypt (ciphertext to plaintext): pass only column_name to the conversion function

    • To encrypt (plaintext to ciphertext): pass column_name alone to use the default database key, or pass both column_name and keyname to use a specific key

    Examples

    Encrypt a column using a custom key

    Use a custom key to control the encryption algorithm (for example, deterministic vs. random) and the master encryption key (MEK).

    Step 1: Generate a keyname.

    SELECT encdb.keyname_generate(<user_name>, <database_name>, <schema_name>, <table_name>, <column_name>);

    Replace the placeholders with the actual values for your column. For example:

    SELECT encdb.keyname_generate('alice', 'patients_db', 'public', 'patients', 'ssn');

    Step 2: Generate a data encryption key (DEK) for the keyname.

    SELECT encdb.dek_generate(<keyname>[, <json_params>]);

    The json_params argument is optional. Pass a JSON object to customize the key:

    Key Description Default Valid values
    algorithm Encryption algorithm AES_128_GCM AES_128_GCM, AES_128_ECB, AES_128_CTR, AES_128_CBC, SM4_128_CBC, SM4_128_ECB, SM4_128_CTR, CLWW_ORE
    policy Encryption policy (reserved) DEFAULT DEFAULT
    flags Encryption method RND RND (random encryption), DET (deterministic encryption)
    mekid ID of the MEK used to generate DEKs MEK ID of the current user Any valid MEK ID
    Note

    CLWW_ORE implements order-revealing encryption (ORE), which preserves sort order on encrypted data. For details, see Practical Order-Revealing Encryption with Limited Leakage.

    Step 3: Encrypt the column.

    The following example converts the ssn column in the patients table from int4 to enc_int4 using the keyname generated in step 1.

    ALTER TABLE patients
    ALTER COLUMN ssn SET DATA TYPE enc_int4
    USING encdb.enc_int4_encrypt(ssn, <keyname>);

    Encrypt a column using the default database key

    If you do not need a custom key, use the default key for the current database. The default keyname follows the format |<User>|<Database>|.

    ALTER TABLE patients
    ALTER COLUMN ssn SET DATA TYPE enc_int4
    USING encdb.enc_int4_encrypt(ssn);

    Decrypt a column

    To convert a column back to plaintext, specify the original plaintext type and use encdb.decrypt. Pass only the column name — no keyname is required.

    ALTER TABLE patients
    ALTER COLUMN ssn SET DATA TYPE int4
    USING encdb.decrypt(ssn);

    What's next