Search code examples
sqlcockroachdb

SQL error when trying to use GENERATED BY DEFAULT AS IDENTITY in CockroachDb

I'm trying to create a simple table auto auto-generated identity key, but Cockroachdb is throwing a syntax error for the keyword GENERATED. Is there something I'm missing?

CREATE TABLE IF NOT EXISTS "users"
(
    "id" bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    "age" bigint NOT NULL, 
    "name" varchar NOT NULL DEFAULT '', 
    "email" varchar NOT NULL, 
    PRIMARY KEY("id")
);
Solution

  • CockroachDB appears to use SERIAL for this purpose:

    "id" serial8 primary key,

    Note that because Cockroach is a distributed databases, this introduces contention and SERIAL is not recommended for assigning unique ids to rows.