Supabase SQL
Add Constraint
Template for adding a constraint.
Add column
Template to add a column. Make sure to change the name and type.
Automatically update timestamps
Update a column timestamp on every update.
CRON job with pg_cron
Schedule PostgreSQL commands directly from the database.
Create View
Basic view template. Change according to your preference.
Create basic table
Basic table template. Change "table_name" to the name you prefer.
Create function
Template to create a simple function.
Create function that return table
Template to create a function a function that return set of table.
Create table with constraints
Table with constraints example.
Create table with foreign key
Table with foreign key (fk) template. Change "table_name" to the name you prefer.
Delete Storage Item
Delete items in Storage using SQL
Drop Constraint
Template for dropping / removing constraint.
Drop Function
Template for dropping / removing function.
Drop RLS
Template for dropping / removing row level security.
Drop Trigger
Template for dropping / removing trigger from table.
Full Text Search
How to use full text search in PostgreSQL.
Generate Youtube-like short ID
Generate YouTube-like short IDs as Postgres Primary Keys.
Increment field value
Update a field with incrementing value using stored procedure.
List all constraints
List all constraints and their tables.
List all foreign keys
List all foreign keys (FKs) and their columns.
List all functions
List all the functions in (predefined/user-defined).
List all primary keys
List all primary keys (PKs) and their columns.
List all table size
List all table data size.
List all triggers
List all the triggers in (predefined/user-defined).
RLS Advanced policies
Row level security with advanced/complicated policies.
RLS Policies with joins
Row level security that requires table joins .
RLS Policies with security definer functions
Row level security that make use of security definer functions .
RLS Read Access
Row level security for read access.
RLS Restrict updates
Row level security for restrict updates.
RLS Time to live for rows
Row level security policies to implement TTL.
RLS Verifying email domains
Row level security that verify email domains.
Rename Constraints
Template to rename any constraints you have.
Show Postgres version
Check your database version.
Stripe subscriptions
Starter template for the Next.js Stripe Subscriptions Starter.
Todo List
Build a todo list with Row Level Security.
Update Constraint
Template for updating a constraint.
CRON job with pg_cron
Schedule PostgreSQL commands directly from the database.
0. [OPTIONAL] Drop all scheduled jobs and the pg_cron extension itself
This is important if you already have pg_cron enabled and have jobs set
-- repeat as much to clear all jobs
select cron.unschedule(<job id>);
-- drop the extension
drop extension if exists pg_cron;
1. Enable pg_cron
create extension if not exists pg_cron;
2. [IMPORTANT] Provide the following grants to the user postgres (and in the future any other user that would be using the cron schema). At the baseline, it seems pg_cron uses the postgres user when executing its schedule.
grant usage on schema cron to postgres;
grant all privileges on all tables in schema cron to postgres;
3. Create sample table test_table
CREATE TABLE test_table (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
inserted_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
updated_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
name text
);
4. Run a CRON job via pg_cron to add a row to this sample table
select
cron.schedule(
'whack-every-minute',
'* * * * *',
$$
insert into test_table(name) values ('kek');
$$
);