Use generated columns to perform queries with formulas in a scalable way

[@dshukertjr]

We are working on series of Postgres videos on this channel right now! What other Postgres concepts would you like us to cover? Let us know in the comments blow 👇

[@DinoRossYT]

More computing actions scenarios data oriented so we can take advantage of backend all-in-one 🙏

[@loucasi]

Amazing presenters of Supabase very informative videos! Would you PLEASE make a video explaining best practices for handling time + timezones in & out of Supabase ? If you could add some specifics regarding Flutterflow and how it handles the issue - and how the two could come together - apart from helping my particular non coder situation could be the ONE video on the internet that shows best practices and rules to handle the "time" issue once and for all. PLEASE! 🤞 😅 🤕

[@davcoding]

Awesome, thank you for the quality content and great service you provide.❤

[@Hagemony]

I think people should explore more on postgres powers not just storing data, thanks for this

[@Yusuf-ok5rk]

hey these new videos of yours are pretty good. keep up the good work. Oh also imo you should add your name + any other person that worked in the video in description.

[@dshukertjr]

Great suggestion! Let me add it in!

[@brotherly9058]

Thanks very much for this!!!!❤❤

[@dshukertjr]

Glad you liked it!

[@shmuel-k]

Geberated columns are only able to access data from the current row. So you can't use them for rollup fields or to reference another table. If you need data from another table, you're back to triggers, functions, and views

[@dshukertjr]

Yup, views are awesome!

[@champechilufya1458]

Is this new or has it always been there? I think computed columns might have always been a postgres thing, but this allows me to generate unique compound ids, which i can use in conflict upsert type queries. Glad i clicked

[@dshukertjr]

It's relatively new as it was only introduced in Posgres 12, which was released in 2019.

[@thibault7190]

Great. Does it work with jsonb column?

[@dshukertjr]

Yup, that is another awesome use case. You could extract certain values from jsonb columns as a generated column, or you could construct jsonb columns using other columns.

[@flanderstruck3751]

This was very useful. Thank you very much

[@jaguarconsultoriadigital8147]

Thanks very much.

[@dshukertjr]

Glad to hear you liked it!

[@luisandrade1291]

I wish you had shown the execution time of the before and after.

[@dshukertjr]

I only had 1000 rows in this particular example, but yes, I should have prepared enough sample data so that I could demonstrate the execution time difference. Trust me though, querying millions of rows with this formula in the query will take a very long time without using a generated column!

[@brotherly9058]

@@dshukertjr Using formula took me very long time to run query and very hard to maintain. I wish i knew this 3 years ago

[@fawazjoseph878]

Awesome feature 👏🏿🙌🏿 Can you create a generated column using the supabase ui column editor

[@dshukertjr]

It's not quite yet supported, but hopefully in the future!

[@matthewbeardsley7004]

This was very useful, thanks!!

[@mrrolandlawrence]

persistant computed columns are awesome.

[@akshykhade]

i wasted so much time with triggers and functions for small tasks like this

[@dshukertjr]

I have been there 😂

[@champechilufya1458]

Same here !!!!

[@DinoRossYT]

Same

[@gargantooga]

is there a difference, performance-wise, between this method and making functions with triggers?

[@dshukertjr]

Performance-wise probably not. At least nothing significant.

[@DinoRossYT]

So not trigger or functions anymore? 😅

[@dshukertjr]

If your use case can be done using generated column, you probably should use generated columns, but triggers have more capabilities than generated column such as reaching out to other tables. So the answer is "it depends"!

[@DinoRossYT]

@@dshukertjr always on point, great example u made here 🙏

[@gargantooga]

do one on different type of indexes and approximate string / natural language search

[@dshukertjr]

Awesome suggestion! Will definitely cover the topic in the future!

[@brotherly9058]

How can we alter that column formula?

[@Supabase]

You can drop the existing column and add a new one with the same name in a transaction!