Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

Is there a benefit to using a trigger over a generated stored column?


Generated columns are not supported by older PG versions. For example if you used AWS AuroraDB with Postgres compatibility, they didn’t support generated columns till a few years ago.

In my experience using PG FTS, there are often some preprocessing tasks you might like to do before storing the index. One common thing is the generated index has to fit inside the column length limit. So if your user provides a long text field with a lot of unique words, then the index will be very long and might lead to a database error. So here you can check for that on the fly, and truncate the result.


Sure, that makes sense.


Not sure it would apply all that often for search indices, but joins are the usual reason I can't use them when I want to; triggered functions (obviously) allow any query.


You can technically do way more robust things in a trigger. If you don’t need it then :shrug: but I often create fairly robust updates


TRIGGER can run on UPDATE.


Not sure I follow, generated columns are updated on UPDATE, so that's just the same?




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: