Enhancement: support popular Postgres extension dialects

[ftsartek]

Summary

SQLGlot's support for certain popular Postgres extensions is effectively nonexistent if the tokeniser comes across a partial match for a valid 'base' comparator within an extension comparator.

PGVector has partial support currently by the nature of some of its comparators such as <-> are already defined (albeit for separate usage) in the base keywords.

Some of the extended PGVector comparators like <#> however, as well as ParadeDB's comparators (||| and &&&) are not supported by SQLGlot because the tokeniser will partially match them against other keywords (e.g. && or #>).

There's already an example of extended dialects for Spanner/Spangres which give a good example of how this would need to plug in. One note here is that the PGVector/ParadeDB would apply to multiple dialects (asyncpg, psycopg, psqlpy and probably others) so these might need to be centralised externally.

[ftsartek]

Based off the spanner/spangres example I'll have an initial shot at getting this behaving for asyncpg specifically and then if that behaves a decision can be made on how to abstract that into the other drivers

[ftsartek]

While I've got this working, I've had to cheat and use unused TokenType names to make this work. While this is functionally fine; postgres doesn't use them; it's not a 'nice' approach.

I've opened an issue on SQLGlot to ask about expected TokenType support: tobymao/sqlglot#6949

[ftsartek]

While I've got this working, I've had to cheat and use unused TokenType names to make this work. While this is functionally fine; postgres doesn't use them; it's not a 'nice' approach.

I've opened an issue on SQLGlot to ask about expected TokenType support: tobymao/sqlglot#6949

Adding the syntax was denied but they provided an idea of a workaround. I've somewhat gone with it but borrowed unused postgres tokens to simplify it a bit.