1

I created this table in Planetscale

CREATE TABLE `community` (
    `id` serial PRIMARY KEY,
    `name` varchar(256),
    `email` varchar(256) NOT NULL,
    `created_at` timestamp(2) NOT NULL DEFAULT (now()),
    `last_updated_at` timestamp(2)
);

But if I try to create the this unique index

CREATE UNIQUE INDEX email_unique_index ON community (`email`);

I get this error:

18:01:21    CREATE UNIQUE INDEX email_unique_index ON community (`email`)   Error Code: 1067. target: gili.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'created_at' (errno 1067) (sqlstate 42000) (CallerID: ewuobpfp487ve5g2p79l): Sql: "alter table community add UNIQUE INDEX email_unique_index (email)", BindVars: {REDACTED}  0.418 sec

I could create the same table and index in my local mysql instance. I was able to create the index in planetscale after i removed DEFAULT (now()) as show below.

CREATE TABLE `community` (
    `id` serial PRIMARY KEY,
    `name` varchar(256),
    `email` varchar(256) NOT NULL,
    `created_at` timestamp(2) NOT NULL,
    `last_updated_at` timestamp(2)
);

CREATE UNIQUE INDEX email_unique_index ON community (`email`);

Why does this happen in Planetscale? Is there a fix for it?

inginia
  • 412
  • 1
  • 6
  • 15
  • If you can afford to lose the precision of the `created_at` column you can change it to: `\`created_at\` timestamp NOT NULL DEFAULT now()` and the index `email_unique_index` should be created without problem. Otherwise you can directly ask the [PlanetScale :: Discussions](https://github.com/planetscale/discussion/discussions). – wchiquito Mar 16 '23 at 22:06

0 Answers0