Suport for native types range on Postgres

[erawk]

This is a follow-up request to prisma/prisma-engines#71

Thanks for adding capability for native types given the database engine. I have a request for an additional type. I'm not sure where the best place for these requests are, do let me know if there's another venue for this.

As per prisma/prisma-engines#1004 (comment), a blocker for us to adopt Prisma is the lack of support for Range types in postgres. Specifically, the "timestamp with timezone range" tstzrange: https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-BUILTIN

Currently, Prisma does not know how to handle this datatype and treats it as a String. When attempting to use the migration system, it ALTERs the table, drops the column and creates a new column with the datatype of String. This results in dataloss and incorrect behaviour in our system.

[albertoperdomo]

Hello @erawk,

• In 2.11.0 we introduced native types as a preview feature (initially supporting introspection only).
• In 2.15.0 we added support for native types in Prisma Migrate
• In 2.16.0 we added a new type Unsupported which should improve some of the behavior you are describing

General documentation on the native types preview feature can be found here: https://www.prisma.io/docs/concepts/components/preview-features/native-types

Ranges are still not supported. The list of currently supported types is here: https://www.prisma.io/docs/concepts/components/preview-features/native-types/native-types-mappings

With the introduction of Unsupported(String) and dbgenerated(String) unsupported types can now be represented in the schema, and will be treated properly by Prisma Migrate and when introspecting. At least Prisma Migrate won't be altering these columns and you can even use Migrate to create these columns and potentially query them using $.queryRaw.

You can find the documentation for Unsupported and dbgenerated here: https://www.prisma.io/docs/concepts/components/preview-features/native-types/native-types-mappings#unsupported

[tlantli]

Is there any update or plans on supporting RANGE types?

[msreekm]

++

[gmwill934]

This would be so beneficial. We are building an appointment system and having this would simplify the work that needs to be done by allowing postgres to do the "hard work".

[shadoath]

What does it take to implement a new type? Can anyone link a PR where this was done?

[shadoath]

@janpio Has this request made it into the backlogs of work at Prisma?

Also, can you give us a lead on my previous question? Looking to know how this task might be done if someone has the ability to contribute.

[janpio]

It is on our radar, but no concrete plans with an ETA.

Generally types need to be implemented in our Prisma Schema Language, code can be found here as part of our Engines: https://github.com/prisma/prisma-engines/ We then need to be able to migrate, introspect, write and read fields of that type. Not something that is very easily to contribute without digging deep into how our Engines work.

[okanji]

This seems pretty beneficial. I definitely want to make use of tstzrange. How can I do this without prisma if it is not supported?

[ryu1maniwa]

+++