undefined | Better HN

0 pointsthom2y ago0 comments

You can create an implicit cast to and from text with CREATE CAST and it becomes transparent.

0 comments

Can you explain a little more what you mean here? I'm not sure this helps my use case. E.g. if I run a BQ external query like the following

    SELECT * FROM EXTERNAL_QUERY("my-pg-connection-string", """
        SELECT enum_column FROM foo;
    """);

that always fails for me, even if I define "CREATE CAST (my_enum_type AS text) WITH INOUT AS IMPLICIT;"

The only way I can get it to work is if I use "SELECT enum_column::text FROM foo;" and I don't need a custom cast definition to do that.

thomOP2y ago

Apologies, I’m hallucinating features here. In our codebase we also have lots of JDBC extensions to map PG objects to language types and back, and this is the easiest way to handle queries over enums. But obviously some type info has to trigger the coercion, so SELECT * FROM might work in the context of an INSERT INTO but elsewhere you’d have to be explicit.

e12e2y ago

I think you would need a way to trigger the cast either way? Eg concatenate with a string?

Otherwise, maybe a function and a generated column (Ed: in a VIEW i guess, if it chokes on the raw enum)?

https://dba.stackexchange.com/questions/276477/immutably-con...

aidos2y ago

By the time you've done a generated column you might as well go with the articles solution of using text with a check constraint since you're now using the space anyway.

cryptonector2y ago

The CREATE CAST will help on DMLs, but not SELECTs.

aidos2y ago

Oh nice. I didn’t know about that feature.

j / k navigate · click thread line to collapse

0 comments

hn_throwaway_992y ago

Can you explain a little more what you mean here? I'm not sure this helps my use case. E.g. if I run a BQ external query like the following

    SELECT * FROM EXTERNAL_QUERY("my-pg-connection-string", """
        SELECT enum_column FROM foo;
    """);

that always fails for me, even if I define "CREATE CAST (my_enum_type AS text) WITH INOUT AS IMPLICIT;"

The only way I can get it to work is if I use "SELECT enum_column::text FROM foo;" and I don't need a custom cast definition to do that.

thomOP2y ago

e12e2y ago

I think you would need a way to trigger the cast either way? Eg concatenate with a string?

Otherwise, maybe a function and a generated column (Ed: in a VIEW i guess, if it chokes on the raw enum)?

https://dba.stackexchange.com/questions/276477/immutably-con...

aidos2y ago

By the time you've done a generated column you might as well go with the articles solution of using text with a check constraint since you're now using the space anyway.

cryptonector2y ago

The CREATE CAST will help on DMLs, but not SELECTs.

aidos2y ago

Oh nice. I didn’t know about that feature.

j / k navigate · click thread line to collapse