Support for range types

[magick93]

Feature request

Is your feature request related to a problem? Please describe.

When generating types columns that use a range are being generated with unknown.

For example:

CREATE TABLE public.sample_table (
	duration tstzrange NULL
);

Will generate:

sample_table: {
        Row: {
          duration: unknown | null
        }
        Insert: {
          duration?: unknown | null
        }
        Update: {
          duration?: unknown | null
        }
      }

As there are already several range types in Postgres these should be supported.

[dallastjames]

While this issue is specifically around range types, I'd love to see a way to be able to handle columns that come from any extension in a generic way. In my case, I have a username column using the citext extension which in turn is reported as unknown in the generated types. I'm aware of this extension and what type it should be generated as, so best case for me is a way to tell the command, "when you encounter x column type, output y as the type".

I've personally been working around this by hand-editing my type definition files, but as I move to a more automated type generation, and specifically type checks as suggested by the environments document, I can't hand-edit the types the CI generates to check for uncommitted changes. This leaves me with choosing between scripting the changes with a tool like sed or manually casting from unknown in the code. Both totally workable, but not ideal. Something that let's me tell the CI tool how to define "unknown" column types would go a very long ways in supporting extensions in the type gen.

[BernieSumption]

I've got a workaround that allows me to type my citext columns correctly without manually editing the generated interfaces.

Say you use citext in the company.name, user.name and user.email columns.

If you currently generate your types into database.ts, instead write the generated code to _generated_database.ts and replace the content of database.ts with this:

import { Database as GeneratedDatabase, Json } from "./_generated_database";

export type { Json };

type Override<
  Table extends keyof GeneratedDatabase["public"]["Tables"],
  Column extends keyof GeneratedDatabase["public"]["Tables"][Table]["Row"],
  RowType,
  InsertType = RowType
> = {
  public: {
    Tables: {
      [T in Table]: {
        Insert: { [C in Column]: InsertType };
        Update: { [C in Column]?: RowType };
        Row: { [C in Column]: RowType };
      };
    };
  };
};

export type Database = GeneratedDatabase &
  Override<"user", "name" | "email", string> &
  Override<"company", "name", string>;

The purpose of the 4th generic parameter (InsertType) is to allow you to specify a different type for inserts. You'll need to do this if your field is non-nullable and has a default value, in which case it needs to be non-optional in the row and optional in the insert.

[magick93]

FYI - for ranges, I found this advice from Postgrest - https://postgrest.org/en/stable/how-tos/working-with-postgresql-data-types.html?highlight=range#casting-a-range-to-a-json-object

[Shyrogan]

FYI - for ranges, I found this advice from Postgrest - https://postgrest.org/en/stable/how-tos/working-with-postgresql-data-types.html?highlight=range#casting-a-range-to-a-json-object

This might also work for GeoJSON objects using postgis. I'm not sure if casting with a function will change the type generation, but combining this with @BernieSumption's solution might help us find a good fix. I'll test this in a few weeks and let you know how it goes.

I also think it's a big problem that the CLI can't change type generation. This forces TypeScript developers to either use unknown types, which isn't great, or come up with tricky workarounds.