[FEATURE]: Add IF NOT EXISTS when adding new values to existing POSTGRES custom types

[mumincelal]

Describe what you want

When we add new values to existing POSTGRES custom type DrizzleORM generates SQL like below:

ALTER TYPE "custom_type" ADD VALUE "type3"

But it will be safer if generates SQL like below:

ALTER TYPE "custom_type" ADD VALUE IF NOT EXISTS "type3"

[AndriiSherman]

What are the cases, when this value will exists in this type?

[mumincelal]

It can happen any time. In development, we added a type and its values, later we reverted migration that the type values exist, after days, we decided to add new values to existing type and we try to add an existing value of type in database without knowing and because of that our migration failed.

If IF NOT EXISTS exists in generated SQL, migration won't fail. Because of that this is a need.

[net]

Also needed for prototyping with drizzle-kit push.

Related: #1865

[ighoshsubho]

Hi I can work on fixing this. Can you assign to me?

[waptik]

What are the cases, when this value will exists in this type?

In addition to https://github.com/drizzle-team/drizzle-orm/issues/1854#issuecomment-1926530161, i happen to manually delete some tables in sqlite because migrate:sqlite and push:sqlite failed after i made some changes to some freshly created tables(empty). So i had to manually add IF NOT EXISTS in every CREATE statements inside my migration files

[ben-hearn-sb]

Chiming in on this one as I was digging around trying to figure this out for some hours and also had to manually edit the SQL.

I also noticed that you are adding the UNIQUE constraint to any keys after the table is generated. This is also incredibly error prone. Even if you edit the CREATE statement to includeIF NOT EXISTS the SQL driver will run CREATE UNIQUE INDEX table_key_unique ON table (key); after this and error out due to the fact the key exists already and or is already unique.

The create column should ideally have the constraints listed next to each key e.g. key text UNIQUE NOT NULL COLLATE NO CASE etc.

Would be great to get a fix, its a great library but this feels like quite an oversight.