21–30 of 76 posts
Earlier quoted context omitted.
Apologies for my ignorance, but what does "FDW" stand for in your comment? :)
Foreign Data Wrapper
Native enums in Postgres just seem to have many more drawbacks than advantages. I never really used them, it didn't seem worth it. I usually use C# Enums translated by EF Core now, which works perfectly fine on the C# side. The only missing part would be to give the DB the information about the enum names, so it could show those to me instead of just the raw numbers. But I assume there is no way to do that. Adding en…
> I usually use C# Enums translated by EF Core now (...) Does this approach ensure data consistency? Or could you accidentally insert a number value into your table which is not represented by any of the enum values?
I “manually” add a check constraint (via EF, so it’s not so much manual as it is “remember to copy and paste this in your db.cs) to assert the value is greater than or equal to zero and less than the number of inhabitants in the enum, but this fails if you manually code the enum values (eg for flags, legacy interop, etc).
Earlier quoted context omitted.
Foreign Data Wrapper
specifically, they are one of the coolest features in postgres. Have you ever wanted to pretend that a CSV (or your filesystem or google) or basically anything else was a SQL database? With a couple hundred lines of code, you can do this. The performance won't be great, but it's ridiculously flexible.
smallint looks like a good alternative, with dictionary in the app or separate table. So far i've only seen storing dictionary in app source code approach
Just to make sure I understand your suggestion fully, you're saying: 1. Use `smallint` instead of `text` for the column type. 2. Otherwise follow our "`CHECK` constraint" approach (without it we're jeopardizing data consistency because we can store anything between -32768 and 32767 in that column). 3. Translate the int to the enum in your application. Right?
Little known bug, but enums also choke dump/restore if you use them as a hash partition key. That said, they can be really nice for all the same reasons static types are nice. Good article!
a. Add CHECK() constraints on columns of
the relevant ENUM type checking that
the value is one of the "live" values.
b. RENAME "dead" ENUM values to indicate
that they're dead.
c. UPDATE ... SET column_of_that_enum_type = ...
WHERE column_of_that_enum_type IN (dead_values_here) ...
Native enums in Postgres just seem to have many more drawbacks than advantages. I never really used them, it didn't seem worth it. I usually use C# Enums translated by EF Core now, which works perfectly fine on the C# side. The only missing part would be to give the DB the information about the enum names, so it could show those to me instead of just the raw numbers. But I assume there is no way to do that. Adding en…
If you peek at a particular table often enough and it has a crazy enough enum to maybe justify making your life a little easier, what you can do is add a table just for debug purposes and use a view that maps the int to it, but otherwise only use the direct int:enum cast in your app/orm/etc. thereby not paying any runtime cost for foreign keys or integrity checking.
After having suffered through the consequences of "type" enums on MySQL, and see some things go through a long life that used "enums" in the database (in multiple different databases, include Postgres), I'm not convinced that either of these are the right choice for representing enumerations. The string with check constraint seems dumb if for no other reason than if the table that uses it winds up having many rows, y…
CREATE TABLE my_enum ( name TEXT PRIMARY KEY );
CREATE TABLE foo ( my_enum TEXT REFERENCES my_enum (name) );
Earlier quoted context omitted.
PostgreSQL enums are there for you to use on your code API. They can go into tables because sometimes it's important to put code data into tables. What they are absolutely not for is to replace the one standard way to define data enums that everybody use since relational algebra was created. It's such an outrageously naive idea that I'm sure most people here were attracted to the title thinking it's about algebraic t…
Can you elaborate on what it means to use PostgreSQL enums "on your code API"? > It's such an outrageously naive idea that I'm sure most people here were attracted to the title thinking it's about algebraic types or some other similar misunderstanding. Just to share how we at Close got into this discussion (which I personally don't find as "outrageous" as you), SQLAlchemy – which we use in our Python code – uses `nat…
> When False, uses VARCHAR + check constraint for all backends
Is this behaviour new? Or is it that alembic still doesn't pick up changes to the enum to recreate the constraint? We implemented our own alembic hooks to automate the migrations for us. We've found the check constraint model to be fairly effective.