Error when enabling postgis in migra migration in local development

[ff6347]

I'm writing this for others who might run into the same problem when enabling postgis locally.

TL;DR

While writing this bug report and creating a minimal proof I found out that migra creates the following statements:

CREATE TYPE "public"."geometry_dump" AS (
	"path" integer[],
	"geom" geometry
);

CREATE TYPE "public"."valid_detail" AS (
	"valid" boolean,
	"reason" character varying,
	"location" geometry
);

After removing them the supabase db reset command works.

---

When I start a new project for local development using some postgis features I'm getting the following error after creating a migration for my schema and running supabase db reset for testing if they work.

The command I used for the migration is:

supabase db diff --file setup --schema public --use-migra

And this is the error.

Error: Error starting database: ERROR:  type "geometry_dump" already exists
ERROR:  current transaction is aborted, commands ignored until end of transaction block

I tried the following things to add postgis to my local database.

• Added extensions.sql with CREATE EXTENSION postgis; in it. No luck
• Added CREATE EXTENSION postgis; to the first migration. No luck
• Added a second migration and moved all other statements except the CREATE EXTENSION postgis; to the second migration. No luck
• Removed all images and containers using docker rmi --force $(docker images --format '{{.Repository}}:{{.Tag}}' | grep 'public.ecr.aws/t3w2s2c9'). No luck
• Updated supabase/cli to its latest version. No luck

See the gist of the migration https://gist.github.com/ff6347/5c9155ff7978a5397b301eba94493797

[danishmughal]

Thanks! Ran into this exact same issue 2 years later, and removing those two statements above as well as the below statement solved it:

create type "tiger"."norm_addy" as (
  "address" integer,
  "predirabbrev" character varying,
  "streetname" character varying,
  "streettypeabbrev" character varying,
  "postdirabbrev" character varying,
  "internal" character varying,
  "location" character varying,
  "stateabbrev" character varying,
  "zip" character varying,
  "parsed" boolean,
  "zip4" character varying,
  "address_alphanumeric" character varying
);