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.
Basic Aggregrate Functions
Set of functions to perform calculation on a set of values. The return is a single summary value. (Except for "round()")
CRON job with pg_cron
Schedule PostgreSQL commands directly from the database.
Case Expression
Case Expression.
Change Column Type
Template to modify a column type. Make sure to change the name and type.
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 integer
Template to create a function that return integer like count of rows.
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.
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.
Drop View
Template for dropping / removing a view.
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.
Get columns info of a table
Template to describe a table.
Handle New User - Function & Trigger
Insert new user data into another table when event new user signup using Supabase Auth.
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).
Query specific text from text[]
Query rows that has specific text you need in a text array.
RLS Advanced policies
Row level security with advanced/complicated policies.
RLS Edit Policy
Row level security editing.
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.
Seed unlimited users
SQL seed function for table auth.users.
Sequence operations
Define a sequence generator.
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.
World Countries
Create a Country table for every countries in the world.
Seed unlimited users
SQL seed function for table auth.users.
How it works
This function generates as many users as you want for the table auth.users, given a value in:
FROM generate_series(1, 100)
It will create a user with email {row}@gmail.com and the passsword you pass to:
crypt('password123', gen_salt('bf'))
Usefull for local development and testing
You can directly put the script into your seed.sql file if you're working locally, or run it with the SQL editor in the Supabase dashboard.
BEGIN;
WITH user_values AS (
SELECT
uuid_generate_v4() AS id,
'00000000-0000-0000-0000-000000000000'::uuid AS instance_id,
'authenticated' AS aud,
'authenticated' AS role,
(ROW_NUMBER() OVER ()) || '@gmail.com' AS email,
crypt('password123', gen_salt('bf')) AS encrypted_password,
now() AS email_confirmed_at,
NULL::timestamp AS invited_at,
'' AS confirmation_token,
NULL::timestamp AS confirmation_sent_at,
'' AS recovery_token,
NULL::timestamp AS recovery_sent_at,
'' AS email_change_token_new,
'' AS email_change,
NULL::timestamp AS email_change_sent_at,
now()::timestamp AS last_sign_in_at,
'{"provider":"email","providers":["email"]}'::jsonb AS raw_app_meta_data,
'{}'::jsonb AS raw_user_meta_data,
0::boolean AS is_super_admin,
'2022-10-04 03:41:27.391146+00'::timestamp AS created_at,
'2022-10-04 03:41:27.391146+00'::timestamp AS updated_at,
NULL AS phone,
NULL::timestamp AS phone_confirmed_at,
'' AS phone_change,
'' AS phone_change_token,
NULL::timestamp AS phone_change_sent_at,
'' AS email_change_token_current,
0 AS email_change_confirm_status,
NULL::timestamp AS banned_until,
'' AS reauthentication_token,
NULL::timestamp AS reauthentication_sent_at
FROM generate_series(1, 100)
),
inserted_users AS (
INSERT INTO auth.users (
id,
instance_id,
aud,
role,
email,
encrypted_password,
email_confirmed_at,
invited_at,
confirmation_token,
confirmation_sent_at,
recovery_token,
recovery_sent_at,
email_change_token_new,
email_change,
email_change_sent_at,
last_sign_in_at,
raw_app_meta_data,
raw_user_meta_data,
is_super_admin,
created_at,
updated_at,
phone,
phone_confirmed_at,
phone_change,
phone_change_token,
phone_change_sent_at,
email_change_token_current,
email_change_confirm_status,
banned_until,
reauthentication_token,
reauthentication_sent_at
)
SELECT * FROM user_values RETURNING id, instance_id
)
--(OPTINAL) Here you can inser the created users into another table
-- INSERT INTO public.user_profile (
-- id,
-- created_at,
-- username
-- )
-- SELECT
-- id,
-- now(),
-- 'USUARIO' || ROW_NUMBER() OVER (),
-- FROM inserted_users;
COMMIT;