PostgreSQL: Generate Random String

I’ve built systems that need to generate random tokens for user verification, API keys for integrations, or test data for development. You can’t hardcode random values—you need the database to generate them on demand. PostgreSQL makes this straightforward by combining its random number generator with hash functions.

The key is understanding that you’re converting random numbers into readable string formats. You might want 32-character hex tokens (perfect for authentication), shorter 8-character codes (for user-facing references), or even alphanumeric mixes. PostgreSQL gives you the building blocks to create any of these.

Quick Answer: Use MD5 with Random

To generate random strings:

SELECT
    SUBSTRING(MD5(RANDOM()::TEXT), 1, 10) as random_10_char,
    SUBSTRING(MD5(RANDOM()::TEXT), 1, 20) as random_20_char,
    MD5(RANDOM()::TEXT) as random_32_char
FROM generate_series(1, 5);

Returns:

random_10_char | random_20_char       | random_32_char
---------------+---------------------+---
5c9fd7a1b2    | 7d3e5f8a1c4b9d2e6f5 | 9d5e8c2f1a4b7e3d6c...

This generates random strings quickly.

How It Works

RANDOM() generates a random number. MD5() converts it to a 32-character hex string. SUBSTRING() extracts as many characters as you need.

Different Approaches & When to Use Them

Approach 1: MD5 + RANDOM (Simple)

SELECT MD5(RANDOM()::TEXT) as random_string;

32 characters, hex only (0-9, a-f).

Best for: Tokens, hashes.

Approach 2: Custom Length

SELECT
    SUBSTRING(MD5(RANDOM()::TEXT), 1, 8) as token_8,
    SUBSTRING(MD5(RANDOM()::TEXT), 1, 16) as token_16
FROM generate_series(1, 100);

Extract any length.

Approach 3: Alphanumeric Mix

SELECT
    TRANSLATE(MD5(RANDOM()::TEXT), '0123456789abcdef', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') as mixed_case
FROM generate_series(1, 10);

Mix of numbers and letters.

Approach 4: Alphabet Only

SELECT
    CHR(65 + (RANDOM() * 25)::INT) as single_letter,
    STRING_AGG(CHR(65 + (RANDOM() * 25)::INT), '') OVER (ORDER BY 1) as word
FROM generate_series(1, 8);

Just letters A-Z.

Approach 5: Custom Charset

SELECT
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%',
    (RANDOM() * 41)::INT + 1, 1) as random_char
FROM generate_series(1, 20);

Any character set you define.

Real-World Example: Generate Invitation Codes

Create unique invite codes for users:

SELECT
    customer_id,
    customer_name,
    SUBSTRING(MD5(customer_id::TEXT || RANDOM()::TEXT), 1, 8) as invite_code,
    CURRENT_TIMESTAMP as generated_at
FROM customers
LIMIT 10;

Update Columns with Random Values

Generate and store random tokens:

UPDATE users
SET reset_token = SUBSTRING(MD5(RANDOM()::TEXT), 1, 32),
    token_created_at = CURRENT_TIMESTAMP
WHERE reset_token IS NULL;

SELECT user_id, reset_token FROM users LIMIT 5;

Performance Notes

All methods are fast. No performance concerns even for generating millions of strings.

Common Patterns

-- 10-char token
SUBSTRING(MD5(RANDOM()::TEXT), 1, 10)

-- Full MD5 (32 chars)
MD5(RANDOM()::TEXT)

-- With timestamp for uniqueness
MD5(RANDOM()::TEXT || CURRENT_TIMESTAMP::TEXT)

-- With row identifier
MD5(table_id::TEXT || RANDOM()::TEXT)

-- Repeated generation
SUBSTRING(MD5(RANDOM()::TEXT), 1, length)
FROM generate_series(1, count)

FAQ

Q: Is this cryptographically secure? A: No. For security-critical tokens, use dedicated functions or external libraries.

Q: Why MD5 and not SHA? A: MD5 is faster and good enough for non-security purposes. Use SHA256() for better security.

Q: How do I ensure uniqueness? A: Include row identifiers: MD5(id::TEXT || RANDOM()::TEXT).

Q: Can I generate lowercase only? A: Yes: LOWER(SUBSTRING(MD5(RANDOM()::TEXT), 1, length)).

Q: What about UUID generation? A: Use gen_random_uuid() for built-in UUID generation.

Wrapping Up

Generate random strings using SUBSTRING(MD5(RANDOM()::TEXT), 1, length). It’s simple, fast, and good enough for most use cases. For security-critical needs, use proper cryptographic functions.