Cannot create tables on cockroachdb

[matreyes]

I'm starting a fresh new application with CockroachDB, and running the first migration it generates the error:

** (Postgrex.Error) ERROR 0A000 (feature_not_supported) at or near ",": syntax error: unimplemented: this syntax

    query: CREATE TABLE IF NOT EXISTS "schema_migrations" ("version" bigint, "inserted_at" timestamp(0), PRIMARY KEY ("version"))

    hint: You have attempted to use a feature that is not yet implemented.
See: https://github.com/cockroachdb/cockroach/issues/32098

at (ecto_sql) lib/ecto/adapters/sql.ex:612: Ecto.Adapters.SQL.raise_sql_call_error/1

It's an error with the timestamp precision which is not supported on CDB but last postgrex version has been tested on cockroach.

I'm using cockroachdb/cockroach:v19.2.5 (latest)

THANKS!

[matreyes]

I've seen that the query is created on https://github.com/elixir-ecto/ecto_sql/blob/master/lib/ecto/adapters/postgres/connection.ex#L1033
not clear for me if it's postgrex or ecto_sql responsability

[josevalim]

If the query works for Postgres but not CockroachDB, then it is rather a CockroachDB issue. We will gladly accept PRs that improve the generated query, but only as long compatibility with PG is still maintained. Thanks!

[matreyes]

They fixed this: cockroachdb/cockroach#42580 but probably will always have differences from postgres that will have to be tunned:(cockroachdb/cockroach#33441).

As they are SQL differences, they should be managed as a different Ecto.Adapter implementation, and maybe keep using postgrex.

Thanks