Add database type alias information to database type endpoint

[dmos62]

Problem

We have some db types that are equivalent. In such cases, we want to tell the frontend which of those types is the default or canonical one, so as to prevent it from having to hardcode it. E.g. DECIMAL and NUMERIC are different in the SQL standard, but equivalent in Postgres and thus equivalent in Mathesar.

Proposed solution

Off the top of my head I see two approaches:

1. Define a type alias as being the non-canonical equivalent types and append the following attribute in returned JSON to aliases 'alias-of': 'SOME CANONICAL TYPE'.

2. Define all equivalent types, canonical and not, as aliases and append an aliases attribute containing a list of the rest of the equivalent types to all aliases ('aliases': ['SOME OTHER ALIAS', 'SOME YET ANOTHER ALIAS']), then on each alias append whether it's canonical or not: 'is_canonical_alias': true | false.

This is in part about whether to consider all equivalent types as aliases of one another (e.g. NUMERIC and DECIMAL are aliases of one another), or whether to say that the alias is the non-canonical type, while the canonical type is not the alias of its equivalent types (e.g. NUMERIC is an alias of DECIMAL, but not the other way around). The latter option is less verbose, in that you just add the alias-of attribute to non-canonicals.

Additional context

Proposed here: #1035 (comment)

[kgodey]

I like option 1 because the frontend will have a "canonical" type to default to when a user is creating a new column.

[mathemancer]

A couple of quick notes/details:

• There's a "canonical SQL name" for each type available from the pg_catalog.format_type system function. (we'd have to do a bit of mapping from aliases to their OIDs to look this name up).
• Oddly, while it's not too hard to find the OID of, e.g., VARCHAR, I can't figure out how to find the OID of DECIMAL. Hmm.

[dmos62]

@mathemancer Do you think this is worth reflecting? I think at the moment the only types that are equivalent are DECIMAL and NUMERIC, so let's hardcode it backend.

By the way, I'd say that DECIMAL should be canonical. The difference between an integer and a "numeric" is ambiguous, but the difference between an integer and a decimal is obvious.

[kgodey]

@dmos62 Those are not the only types that are equivalent, there's also VARCHAR and TEXT, IIRC. Please do some research and find any others.

We should use whichever type Postgres uses as canonical as canonical in our API.

[mathemancer]

Some examples are:

• CHARACTER and CHAR
• CHARACTER VARYING and VARCHAR
• BOOL and BOOLEAN

Maybe more. I really think we should reflect; I'm not sure how else we can be confident we've caught most cases.

Edit: @kgodey I don't think we should use TEXT and VARCHAR as aliases; they have different OIDs.

[kgodey]

@mathemancer I guess I didn't recall correctly. We still need a way to let the frontend know that the options for TEXT and VARCHAR are the same, though (unless they're different? If so, how are they different?)

[dmos62]

I agree that we'd want to reflect this in the long run. But, I'm still suggesting to hardcode it (for the time being), if that will save time in the short run.

[kgodey]

Agreed on hardcoding if it will save time.

[mathemancer]

VARCHAR takes a length type_option, whereas TEXT does not.

[kgodey]

Ah okay, then they should not be aliases.

[mathemancer]

After looking through the linked PR for this issue, I realized I (we) got distracted by whether we could show aliases, and how to do it, and we forgot to consider whether we should. I think if we're showing two equivalent types to the UI as options for setting the type of a column, that's a bug. We should only show one type string as an option for a given type, and that type string should be the key in the ischema_names dictionary.

[kgodey]

I assume this was resolved by the type refactor, closing.