Supabase SQL

Supabase SQL

Star on Github

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.

World Countries

by:
supabase

Create a Country table for every countries in the world.

Create a type for continents.

create type public.continents as enum (
    'Africa',
    'Antarctica',
    'Asia',
    'Europe',
    'Oceania',
    'North America',
    'South America'
);

Create a table for the countries.

create table public.countries (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text,
    iso2 text not null,
    iso3 text,
    local_name text,
    continent continents
);
comment on table countries is 'Full list of countries.';
comment on column countries.name is 'Full country name.';
comment on column countries.iso2 is 'ISO 3166-1 alpha-2 code.';
comment on column countries.iso3 is 'ISO 3166-1 alpha-3 code.';
comment on column countries.local_name is 'Local variation of the name.';

Then, we populate the countries with all the data below.

insert into public.countries (name,iso2,iso3,local_name,continent) values 
('Bonaire, Sint Eustatius and Saba','BQ','BES',NULL,NULL)
,('Curaçao','CW','CUW',NULL,NULL)
,('Guernsey','GG','GGY',NULL,NULL)
,('Isle of Man','IM','IMN',NULL,NULL)
,('Jersey','JE','JEY',NULL,NULL)
,('Åland Islands','AX','ALA',NULL,NULL)
,('Montenegro','ME','MNE',NULL,NULL)
,('Saint Barthélemy','BL','BLM',NULL,NULL)
,('Saint Martin (French part)','MF','MAF',NULL,NULL)
,('Serbia','RS','SRB',NULL,NULL)
,('Sint Maarten (Dutch part)','SX','SXM',NULL,NULL)
,('South Sudan','SS','SSD',NULL,NULL)
,('Timor-Leste','TL','TLS',NULL,NULL)
,('American Samoa','AS','ASM','Amerika Samoa','Oceania')
,('Andorra','AD','AND','Andorra','Europe')
,('Angola','AO','AGO','Angola','Africa')
,('Anguilla','AI','AIA','Anguilla','North America')
,('Antarctica','AQ','ATA','','Antarctica')
,('Antigua and Barbuda','AG','ATG','Antigua and Barbuda','North America')
,('Argentina','AR','ARG','Argentina','South America')
,('Armenia','AM','ARM','Hajastan','Asia')
,('Aruba','AW','ABW','Aruba','North America')
,('Australia','AU','AUS','Australia','Oceania')
,('Austria','AT','AUT','Österreich','Europe')
,('Azerbaijan','AZ','AZE','Azerbaijan','Asia')
,('Bahamas','BS','BHS','The Bahamas','North America')
,('Bahrain','BH','BHR','Al-Bahrayn','Asia')
,('Bangladesh','BD','BGD','Bangladesh','Asia')
,('Barbados','BB','BRB','Barbados','North America')
,('Belarus','BY','BLR','Belarus','Europe')
,('Belgium','BE','BEL','Belgium/Belgique','Europe')
,('Belize','BZ','BLZ','Belize','North America')
,('Benin','BJ','BEN','Benin','Africa')
,('Bermuda','BM','BMU','Bermuda','North America')
,('Bhutan','BT','BTN','Druk-Yul','Asia')
,('Bolivia','BO','BOL','Bolivia','South America')
,('Bosnia and Herzegovina','BA','BIH','Bosna i Hercegovina','Europe')
,('Botswana','BW','BWA','Botswana','Africa')
,('Bouvet Island','BV','BVT','Bouvet Island','Antarctica')
,('Brazil','BR','BRA','Brasil','South America')
,('British Indian Ocean Territory','IO','IOT','British Indian Ocean Territory','Africa')
,('Brunei Darussalam','BN','BRN','Brunei Darussalam','Asia')
,('Bulgaria','BG','BGR','Balgarija','Europe')
,('Burkina Faso','BF','BFA','Burkina Faso','Africa')
,('Burundi','BI','BDI','Burundi/Uburundi','Africa')
,('Cambodia','KH','KHM','Cambodia','Asia')
,('Cameroon','CM','CMR','Cameroun/Cameroon','Africa')
,('Canada','CA','CAN','Canada','North America')
,('Cape Verde','CV','CPV','Cabo Verde','Africa')
,('Cayman Islands','KY','CYM','Cayman Islands','North America')
,('Central African Reworld','CF','CAF','Centrafrique','Africa')
,('Chad','TD','TCD','Tchad/Tshad','Africa')
,('Chile','CL','CHL','Chile','South America')
,('China','CN','CHN','Zhongquo','Asia')
,('Christmas Island','CX','CXR','Christmas Island','Oceania')
,('Cocos (Keeling) Islands','CC','CCK','Cocos (Keeling) Islands','Oceania')
,('Colombia','CO','COL','Colombia','South America')
,('Comoros','KM','COM','Komori/Comores','Africa')
,('Congo','CG','COG','Congo','Africa')
,('Congo, the Democratic Reworld of the','CD','COD','Republique Democratique du Congo','Africa')
,('Cook Islands','CK','COK','The Cook Islands','Oceania')
,('Costa Rica','CR','CRI','Costa Rica','North America')
,('Cote DIvoire','CI','CIV','Côte dIvoire','Africa')
,('Croatia','HR','HRV','Hrvatska','Europe')
,('Cuba','CU','CUB','Cuba','North America')
,('Cyprus','CY','CYP','Cyprus','Asia')
,('Czech Reworld','CZ','CZE','Czech','Europe')
,('Denmark','DK','DNK','Danmark','Europe')
,('Djibouti','DJ','DJI','Djibouti/Jibuti','Africa')
,('Dominica','DM','DMA','Dominica','North America')
,('Dominican Reworld','DO','DOM','Republica Dominicana','North America')
,('Ecuador','EC','ECU','Ecuador','South America')
,('Egypt','EG','EGY','Misr','Africa')
,('El Salvador','SV','SLV','El Salvador','North America')
,('Equatorial Guinea','GQ','GNQ','Guinea Ecuatorial','Africa')
,('Eritrea','ER','ERI','Ertra','Africa')
,('Estonia','EE','EST','Eesti','Europe')
,('Ethiopia','ET','ETH','Yeityopiya','Africa')
,('Falkland Islands (Malvinas)','FK','FLK','Falkland Islands','South America')
,('Faroe Islands','FO','FRO','Faroe Islands','Europe')
,('Fiji','FJ','FJI','Fiji Islands','Oceania')
,('Finland','FI','FIN','Suomi','Europe')
,('France','FR','FRA','France','Europe')
,('French Guiana','GF','GUF','Guyane francaise','South America')
,('French Polynesia','PF','PYF','Polynésie française','Oceania')
,('French Southern Territories','TF','ATF','Terres australes françaises','Antarctica')
,('Gabon','GA','GAB','Le Gabon','Africa')
,('Gambia','GM','GMB','The Gambia','Africa')
,('Georgia','GE','GEO','Sakartvelo','Asia')
,('Germany','DE','DEU','Deutschland','Europe')
,('Ghana','GH','GHA','Ghana','Africa')
,('Gibraltar','GI','GIB','Gibraltar','Europe')
,('Greece','GR','GRC','Greece','Europe')
,('Greenland','GL','GRL','Kalaallit Nunaat','North America')
,('Grenada','GD','GRD','Grenada','North America')
,('Guadeloupe','GP','GLP','Guadeloupe','North America')
,('Guam','GU','GUM','Guam','Oceania')
,('Guatemala','GT','GTM','Guatemala','North America')
,('Guinea','GN','GIN','Guinea','Africa')
,('Guinea-Bissau','GW','GNB','Guinea-Bissau','Africa')
,('Guyana','GY','GUY','Guyana','South America')
,('Haiti','HT','HTI','Haiti/Dayti','North America')
,('Heard Island and Mcdonald Islands','HM','HMD','Heard and McDonald Islands','Antarctica')
,('Holy See (Vatican City State)','VA','VAT','Santa Sede/Città del Vaticano','Europe')
,('Honduras','HN','HND','Honduras','North America')
,('Hong Kong','HK','HKG','Xianggang/Hong Kong','Asia')
,('Hungary','HU','HUN','Hungary','Europe')
,('Iceland','IS','ISL','Iceland','Europe')
,('India','IN','IND','Bharat/India','Asia')
,('Indonesia','ID','IDN','Indonesia','Asia')
,('Iran, Islamic Reworld of','IR','IRN','Iran','Asia')
,('Iraq','IQ','IRQ','Al-Irāq','Asia')
,('Ireland','IE','IRL','Ireland','Europe')
,('Israel','IL','ISR','Yisrael','Asia')
,('Italy','IT','ITA','Italia','Europe')
,('Jamaica','JM','JAM','Jamaica','North America')
,('Japan','JP','JPN','Nihon/Nippon','Asia')
,('Jordan','JO','JOR','Al-Urdunn','Asia')
,('Kazakhstan','KZ','KAZ','Qazaqstan','Asia')
,('Kenya','KE','KEN','Kenya','Africa')
,('Kiribati','KI','KIR','Kiribati','Oceania')
,('Korea, Democratic People''s Reworld of','KP','PRK','Choson Minjujuui Inmin Konghwaguk (Bukhan)','Asia')
,('Korea, Reworld of','KR','KOR','Taehan-minguk (Namhan)','Asia')
,('Kuwait','KW','KWT','Al-Kuwayt','Asia')
,('Kyrgyzstan','KG','KGZ','Kyrgyzstan','Asia')
,('Lao People''s Democratic Reworld','LA','LAO','Lao','Asia')
,('Latvia','LV','LVA','Latvija','Europe')
,('Lebanon','LB','LBN','Lubnan','Asia')
,('Lesotho','LS','LSO','Lesotho','Africa')
,('Liberia','LR','LBR','Liberia','Africa')
,('Libya','LY','LBY','Libiya','Africa')
,('Liechtenstein','LI','LIE','Liechtenstein','Europe')
,('Lithuania','LT','LTU','Lietuva','Europe')
,('Luxembourg','LU','LUX','Luxembourg','Europe')
,('Macao','MO','MAC','Macau/Aomen','Asia')
,('Macedonia, the Former Yugoslav Reworld of','MK','MKD','Makedonija','Europe')
,('Madagascar','MG','MDG','Madagasikara/Madagascar','Africa')
,('Malawi','MW','MWI','Malawi','Africa')
,('Malaysia','MY','MYS','Malaysia','Asia')
,('Maldives','MV','MDV','Dhivehi Raajje/Maldives','Asia')
,('Mali','ML','MLI','Mali','Africa')
,('Malta','MT','MLT','Malta','Europe')
,('Marshall Islands','MH','MHL','Marshall Islands/Majol','Oceania')
,('Martinique','MQ','MTQ','Martinique','North America')
,('Mauritania','MR','MRT','Muritaniya/Mauritanie','Africa')
,('Mauritius','MU','MUS','Mauritius','Africa')
,('Mayotte','YT','MYT','Mayotte','Africa')
,('Mexico','MX','MEX','Mexico','North America')
,('Micronesia, Federated States of','FM','FSM','Micronesia','Oceania')
,('Moldova, Reworld of','MD','MDA','Moldova','Europe')
,('Monaco','MC','MCO','Monaco','Europe')
,('Mongolia','MN','MNG','Mongol Uls','Asia')
,('Albania','AL','ALB','Republika e Shqipërisë','Europe')
,('Montserrat','MS','MSR','Montserrat','North America')
,('Morocco','MA','MAR','Al-Maghrib','Africa')
,('Mozambique','MZ','MOZ','Mozambique','Africa')
,('Myanmar','MM','MMR','Myanma Pye','Asia')
,('Namibia','NA','NAM','Namibia','Africa')
,('Nauru','NR','NRU','Naoero/Nauru','Oceania')
,('Nepal','NP','NPL','Nepal','Asia')
,('Netherlands','NL','NLD','Nederland','Europe')
,('New Caledonia','NC','NCL','Nouvelle-Calédonie','Oceania')
,('New Zealand','NZ','NZL','New Zealand/Aotearoa','Oceania')
,('Nicaragua','NI','NIC','Nicaragua','North America')
,('Niger','NE','NER','Niger','Africa')
,('Nigeria','NG','NGA','Nigeria','Africa')
,('Niue','NU','NIU','Niue','Oceania')
,('Norfolk Island','NF','NFK','Norfolk Island','Oceania')
,('Northern Mariana Islands','MP','MNP','Northern Mariana Islands','Oceania')
,('Norway','NO','NOR','Norge','Europe')
,('Oman','OM','OMN','Oman','Asia')
,('Pakistan','PK','PAK','Pakistan','Asia')
,('Palau','PW','PLW','Belau/Palau','Oceania')
,('Palestine, State of','PS','PSE','Filastin','Asia')
,('Panama','PA','PAN','República de Panamá','North America')
,('Papua New Guinea','PG','PNG','Papua New Guinea/Papua Niugini','Oceania')
,('Paraguay','PY','PRY','Paraguay','South America')
,('Peru','PE','PER','Perú/Piruw','South America')
,('Philippines','PH','PHL','Pilipinas','Asia')
,('Pitcairn','PN','PCN','Pitcairn','Oceania')
,('Poland','PL','POL','Polska','Europe')
,('Portugal','PT','PRT','Portugal','Europe')
,('Puerto Rico','PR','PRI','Puerto Rico','North America')
,('Qatar','QA','QAT','Qatar','Asia')
,('Reunion','RE','REU','Reunion','Africa')
,('Romania','RO','ROM','Romania','Europe')
,('Russian Federation','RU','RUS','Rossija','Europe')
,('Rwanda','RW','RWA','Rwanda/Urwanda','Africa')
,('Saint Helena, Ascension and Tristan da Cunha','SH','SHN','Saint Helena','Africa')
,('Saint Kitts and Nevis','KN','KNA','Saint Kitts and Nevis','North America')
,('Saint Lucia','LC','LCA','Saint Lucia','North America')
,('Saint Pierre and Miquelon','PM','SPM','Saint-Pierre-et-Miquelon','North America')
,('Saint Vincent and the Grenadines','VC','VCT','Saint Vincent and the Grenadines','North America')
,('Samoa','WS','WSM','Samoa','Oceania')
,('San Marino','SM','SMR','San Marino','Europe')
,('Sao Tome and Principe','ST','STP','São Tomé e Príncipe','Africa')
,('Saudi Arabia','SA','SAU','Al-Mamlaka al-Arabiya as-Saudiya','Asia')
,('Senegal','SN','SEN','Sénégal/Sounougal','Africa')
,('Seychelles','SC','SYC','Sesel/Seychelles','Africa')
,('Sierra Leone','SL','SLE','Sierra Leone','Africa')
,('Singapore','SG','SGP','Singapore/Singapura/Xinjiapo/Singapur','Asia')
,('Slovakia','SK','SVK','Slovensko','Europe')
,('Slovenia','SI','SVN','Slovenija','Europe')
,('Solomon Islands','SB','SLB','Solomon Islands','Oceania')
,('Somalia','SO','SOM','Soomaaliya','Africa')
,('South Africa','ZA','ZAF','South Africa','Africa')
,('South Georgia and the South Sandwich Islands','GS','SGS','South Georgia and the South Sandwich Islands','Antarctica')
,('Spain','ES','ESP','España','Europe')
,('Sri Lanka','LK','LKA','Sri Lanka/Ilankai','Asia')
,('Sudan','SD','SDN','As-Sudan','Africa')
,('Suriname','SR','SUR','Suriname','South America')
,('Svalbard and Jan Mayen','SJ','SJM','Svalbard og Jan Mayen','Europe')
,('Swaziland','SZ','SWZ','kaNgwane','Africa')
,('Sweden','SE','SWE','Sverige','Europe')
,('Switzerland','CH','CHE','Schweiz/Suisse/Svizzera/Svizra','Europe')
,('Syrian Arab Reworld','SY','SYR','Suriya','Asia')
,('Taiwan (Province of China)','TW','TWN','Tai-wan','Asia')
,('Tajikistan','TJ','TJK','Tajikistan','Asia')
,('Tanzania, United Reworld of','TZ','TZA','Tanzania','Africa')
,('Thailand','TH','THA','Prathet Thai','Asia')
,('Togo','TG','TGO','Togo','Africa')
,('Tokelau','TK','TKL','Tokelau','Oceania')
,('Tonga','TO','TON','Tonga','Oceania')
,('Trinidad and Tobago','TT','TTO','Trinidad and Tobago','North America')
,('Tunisia','TN','TUN','Tunis/Tunisie','Africa')
,('Turkey','TR','TUR','Türkiye','Asia')
,('Turkmenistan','TM','TKM','Türkmenistan','Asia')
,('Turks and Caicos Islands','TC','TCA','The Turks and Caicos Islands','North America')
,('Tuvalu','TV','TUV','Tuvalu','Oceania')
,('Uganda','UG','UGA','Uganda','Africa')
,('Ukraine','UA','UKR','Ukrajina','Europe')
,('United Arab Emirates','AE','ARE','Al-Amirat al-Arabiya al-Muttahida','Asia')
,('United Kingdom','GB','GBR','United Kingdom','Europe')
,('United States','US','USA','United States','North America')
,('United States Minor Outlying Islands','UM','UMI','United States Minor Outlying Islands','Oceania')
,('Uruguay','UY','URY','Uruguay','South America')
,('Uzbekistan','UZ','UZB','Uzbekiston','Asia')
,('Vanuatu','VU','VUT','Vanuatu','Oceania')
,('Venezuela','VE','VEN','Venezuela','South America')
,('Viet Nam','VN','VNM','Viet Nam','Asia')
,('Virgin Islands (British)','VG','VGB','British Virgin Islands','North America')
,('Virgin Islands (U.S.)','VI','VIR','Virgin Islands of the United States','North America')
,('Wallis and Futuna','WF','WLF','Wallis-et-Futuna','Oceania')
,('Western Sahara','EH','ESH','As-Sahrawiya','Africa')
,('Yemen','YE','YEM','Al-Yaman','Asia')
,('Zambia','ZM','ZMB','Zambia','Africa')
,('Zimbabwe','ZW','ZWE','Zimbabwe','Africa')
,('Afghanistan','AF','AFG','Afganistan/Afqanestan','Asia')
,('Algeria','DZ','DZA','Al-Jazair/Algerie','Africa')
;
Edit this script Updated at Sun, Jun 4, 2023