Generate column values in Postgres with Functions, Triggers, default values and RLS

Can you Generate columns in Postgres? YES!
This is how to generate column values in Postgres with Functions, Triggers, default values and RLS.
Automatically generating column values can be a powerful way to track metadata about your PostgreSQL data. In this video, Jon Meyers demonstrates two different strategies for tracking which user created or updated a row, and when:
1. Postgres Functions and Triggers
2. Default values enforced with Row Level Security (RLS) policies
Additionally, he discusses some database theory, uses Supabase AI to generate SQL and impersonates a specific user to test the RLS policies are working correctly - all without leaving Supabase Studio.
Jon got tagged in this tweet asking about generating values for a collection of columns to keep a track of who created or modified a table and when. So we thought we’d dive into some Postgres and talk through some different options to solve this query!
If you want to generate columns in Postgres, watch this…
What you'll learn:
✅ Supabase Project Setup: Start with creating a new Supabase project to explore SQL solutions using the Supabase AI assistant so you don’thave top create it from scratch.
✅ Postgres Functions with Supabase: Utilize Supabase to create and manage Postgres functions and triggers for dynamically updating 'updated at' and 'updated by' columns.
✅ RLS Policies in Supabase: Implement and test RLS policies in Supabase to enforce default values for 'created at' and 'created by' columns, ensuring data integrity.
After this video, you'll be able to:
✅ Automate Column Values in Supabase: Apply Postgres functions and RLS policies in your Supabase project to auto-generate essential metadata for your tables.
✅ Secure Data Updates with Supabase: Use Supabase's RLS policies to protect your data and ensure that updates are accurately recorded, maintaining a high level of data security.
✅ Leverage Supabase AI and Studio: Use Supabase AI and Studio for efficient SQL generation and database management, which makes development workflow WAY easier!
After this tutorial, we’re confident that your database management skills will skyrocket, while you enjoy automatic data tracking and security within your PostgreSQL tables. This video is an invaluable resource for developers looking to streamline their database operations using Supabase's handy features including AI functionality within the Supabase dashboard.
💻 Videos to watch next:
▶ Fastest way to host a Postgres database: • FASTEST way to host a ...
▶ Fastest way to get started with Next.js and Supabase: • FASTEST way to get sta...
▶ 10 new Supabase features you need to start using: • 10 new Supabase featur...
👇 Learn more about Supabase 👇
🕸 Website: supabase.com/
🏁 Get started: app.supabase.com/
📄 Docs: supabase.com/docs
🔔 Subscribe for more tutorials and feature updates from Supabase: / @supabase
📱 Connect with Us:
🐙 Github: www.github.com/supabase
💬 Discord: www.discord.supabase.com/
🐦 Twitter: / supabase
▶ Instagram (follow for memes): / supabasecom
ABOUT SUPABASE:
Supabase is the open source Firebase alternative. Supabase provides a full Postgres database for every project with pgvector, backups, realtime, and more. Add and manage email and password, passwordless, OAuth, and mobile logins to your project through a suite of identity providers and APIs.
Build in a weekend, scale to millions.
#Supabase #AppDevelopment #RealtimeApps #DeveloperTools

[@dshukertjr]

I genuinely did not know that you can highlight a statement in the SQL editor to just run certain statements 😂

[@JonMeyers]

So handy! 🎉

[@brunocrosier]

really clear explanation of a tricky concept👏👏

[@JonMeyers]

Thanks! Glad you enjoyed it! 🙌

[@remi-avec-un-i]

Hey Jon! First of all, thanks a lot, your explanation makes me realise why you need both "USING" & "WITH CHECK" expressions in the UPDATE. That being said, I realised that you went very fast on the UPDATE: 1) A user can update a post that is not his 2) A user can update the created_at & created_by fields

[@JonMeyers]

Good catch! 🙌

[@badwolf9090]

Why are you changing the capitalisation?

[@JonMeyers]

I just prefer lowercase. Upper feels too shouty! 😂

[@rembautimes8808]

Thanks for the video and in-depth explanation. Now if only supabase can develop is_dodgy_user() 😂. Next release cycle , perhaps?

[@excellencemichel4875]

Yo have to implemente transaction feature to Supabase

[@spartan_j117]

0:56 I read somewhere on the internet, that "bigint primary key generated always as identity" is the right way to set up a primary key. Could you please throw some light on this, why did you change it to "serial", what's the difference? Thank you!

[@JonMeyers]

Good call! Bigserial may have been a better option - will keep this in mind for future videos 👍 Serial translates to a 4 byte integer behind the scenes, so can still store 2,147,483,647 rows! That's a lot of blog posts! 😆 Bigserial would take you up to 9,223,372,036,854,775,807 🤯

[@spartan_j117]

@@JonMeyersTy! BTW, you have an article about this in you corporate blog called "Choosing a Postgres Primary Key". And there the author references a nice post on IDENTITY column from the EDB team.

[@tamsssss6765]

Create a CRUD video with emphasis on UPDATE(*UPSERT*)? 🥹😭🙏🏻

[@JonMeyers]

On it! 🚀