DEFAULT gen_random_uuid() in CREATE TABLE fails #9

New issue

Open

opened 2026-08-07 14:22:15 +00:00 by hum3 · 0 comments

hum3 commented

2026-08-07 14:22:15 +00:00

Owner

Problem

DEFAULT gen_random_uuid() in a column definition causes a SQLite syntax error:

CREATE TABLE accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);
-- sqlite3: SQL logic error: near "(": syntax error

Workaround

Generate the UUID in the INSERT statement instead:

CREATE TABLE accounts (id VARCHAR(36) PRIMARY KEY);
INSERT INTO accounts (id) VALUES (gen_random_uuid()) RETURNING id;

gen_random_uuid() works fine in queries and INSERT expressions — the issue is only in DDL DEFAULT clauses where the SQL rewriter does not translate the function call into SQLite-compatible syntax (DEFAULT (gen_random_uuid()) or equivalent).

Expected

The DDL translator should rewrite DEFAULT gen_random_uuid() so it works against SQLite, matching PostgreSQL behaviour.

Migrated from Codeberg: originally #9, opened 2026-04-13.

## Problem `DEFAULT gen_random_uuid()` in a column definition causes a SQLite syntax error: ```sql CREATE TABLE accounts ( id UUID PRIMARY KEY DEFAULT gen_random_uuid() ); -- sqlite3: SQL logic error: near "(": syntax error ``` ## Workaround Generate the UUID in the INSERT statement instead: ```sql CREATE TABLE accounts (id VARCHAR(36) PRIMARY KEY); INSERT INTO accounts (id) VALUES (gen_random_uuid()) RETURNING id; ``` `gen_random_uuid()` works fine in queries and INSERT expressions — the issue is only in DDL DEFAULT clauses where the SQL rewriter does not translate the function call into SQLite-compatible syntax (`DEFAULT (gen_random_uuid())` or equivalent). ## Expected The DDL translator should rewrite `DEFAULT gen_random_uuid()` so it works against SQLite, matching PostgreSQL behaviour. --- *Migrated from Codeberg: originally [#9](https://codeberg.org/hum3/go-postgres/issues/9), opened 2026-04-13.*

No labels

OnDemand

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference

hum3/go-postgres#9

No description provided.

Rows
Columns