Basic POST request with http_post
Example POST request using http_post: select "status", "content"::jsonb from extensions.http_post('https://jsonplaceholder.typicode.com/posts', '{ "title": "foo", "body": "bar", "userId": 1 }', 'application/json');
Supabase · Database · all subjects
postgres extensions
221 notes in this subject, read out of this brain and free to use. This is page 1 of 4.
http extension return values
A successful call to a web URL from the http extension returns a record with the following fields: status (integer), content_type (character varying), headers (http_header[]), and content (character varying). The content can typically be cast to jsonb using content::jsonb.
Enable http extension with SQL
To enable the http extension, use: create extension http with schema extensions; It is good practice to create the extension within a separate schema like extensions to keep the public schema clean. To disable the extension, call: drop extension if exists http;
http extension wrapper functions
The http extension provides 5 wrapper functions for specific HTTP methods: http_get(), http_post(), http_put(), http_delete(), and http_head(). The main generic function is http('http_request').
http extension enables RESTful API calls from Postgres
The http extension allows you to call RESTful endpoints within Postgres functions. This enables Postgres to make HTTP requests to external services and APIs.
Basic GET request with http_get
Example GET request using http_get: select "status", "content"::jsonb from extensions.http_get('https://jsonplaceholder.typicode.com/todos/1');
pg_net API in beta
The pg_net API is in beta and function signatures may change.
pg_net http_request_queue table structure
Waiting requests are stored in the net.http_request_queue UNLOGGED table with columns: id bigint NOT NULL DEFAULT nextval('net.http_request_queue_id_seq'::regclass), method text NOT NULL, url text NOT NULL, headers jsonb NOT NULL, body bytea NULL, timeout_milliseconds integer NOT NULL. Requests are deleted upon execution.
pg_net http_delete function signature
net.http_delete(url text, params jsonb default '{}'::jsonb, headers jsonb default '{}'::jsonb, timeout_milliseconds int default 2000) returns bigint. Creates an HTTP DELETE request, returning the request's ID. HTTP requests are not started until the transaction is committed. This is a SECURITY DEFINER function. The function is strict, volatile, and parallel safe.
pg_net debugging with Postman Echo API
The Postman Echo API returns a response with the same body and content as the request, useful for inspecting data being sent. Send: select net.http_post(url := 'https://postman-echo.com/post', body := '{"key1": "value", "key2": 5}'::jsonb) as request_id; Then inspect response: select content from net._http_response where id = <request_id>;
pg_net inspect failed requests
Find all failed requests: select * from net._http_response where status_code >= 400 or error_msg is not null order by created desc;
pg_net limitations
pg_net has the following limitations: requests and responses are stored in unlogged tables, not preserved during crash or unclean shutdown; response data saved for only 6 hours by default; can only make POST requests with JSON data, no other formats supported; intended to handle at most 200 requests per second; does not support PATCH/PUT requests; can only work with one database at a time, defaults to postgres database.
pg_net http_response table structure
By default, responses are stored for 6 hours in the net._http_response UNLOGGED table with columns: id bigint NULL, status_code integer NULL, content_type text NULL, headers jsonb NULL, content text NULL, timed_out boolean NULL, error_msg text NULL, created timestamp with time zone NOT NULL DEFAULT now().
pg_net extension overview
pg_net is a Postgres extension that enables asynchronous HTTP/HTTPS requests in SQL. It is asynchronous by default, which makes it useful in blocking functions like triggers. It eliminates the need for servers to continuously poll for database changes by allowing the database to proactively notify external resources about significant events.
pg_net http_post function signature
net.http_post(url text, body jsonb default '{}'::jsonb, params jsonb default '{}'::jsonb, headers jsonb default '{"Content-Type": "application/json"}'::jsonb, timeout_milliseconds int default 2000) returns bigint. Creates an HTTP POST request with a JSON body, returning the request's ID. HTTP requests are not started until the transaction is committed. The body's character set encoding matches the database's server_encoding setting. This is a SECURITY DEFINER function. The function is volatile and parallel safe.
Enable pg_net extension via SQL
To enable pg_net, use: create extension pg_net; The extension creates its own schema/namespace named 'net' to avoid naming conflicts. To disable it, use: drop extension if exists pg_net; drop schema net;
pg_net with pg_cron scheduled calls
Schedule regular calls to endpoints using pg_cron extension: select cron.schedule('cron-job-name', '* * * * *', $$select net.http_post(url:='https://project-ref.supabase.co/functions/v1/function-name', headers:='{"apikey": "<SUPABASE_PUBLISHABLE_KEY>"}'::jsonb, body:='{"name": "pg_net"}'::jsonb) as request_id;$$); The cron expression executes every minute with up to a minute precision.
pg_net send multiple table rows example
Send multiple table rows in one request: with selected_table_rows as (select jsonb_agg(to_jsonb(<table_name>.*)) as JSON_payload from <table_name>) select net.http_post(url := 'https://postman-echo.com/post'::text, body := JSON_payload) AS request_id FROM selected_table_rows;
pg_net http_get function signature
net.http_get(url text, params jsonb default '{}'::jsonb, headers jsonb default '{}'::jsonb, timeout_milliseconds int default 2000) returns bigint. Creates an HTTP GET request returning the request's ID. HTTP requests are not started until the transaction is committed. This is a SECURITY DEFINER function. The function is strict, volatile, and parallel safe.
pg_net trigger example
Execute pg_net in a trigger by creating a function that calls net.http_post and attaching it to a trigger: create or replace function <function_name>() returns trigger language plpgSQL as $$begin perform net.http_post('https://postman-echo.com/post'::text, jsonb_build_object('old_row', to_jsonb(old.*), 'new_row', to_jsonb(new.*)), headers:='{"Content-Type": "application/json"}'::jsonb) as request_id; return new; END $$; create trigger <trigger_name> after update on <table_name> for each row execute function <function_name>();
pg_net alter settings example
To alter pg_net settings: alter system set pg_net.ttl to '24 hours'; select net.worker_restart(); This requires superuser privileges granted via: grant alter system on parameter pg_net.ttl to postgres;
pg_net invoke Supabase Edge Function example
Make a POST request to a Supabase Edge Function: select net.http_post(url:='https://project-ref.supabase.co/functions/v1/function-name', headers:='{"Content-Type": "application/json", "apikey": "<SUPABASE_PUBLISHABLE_KEY>"}'::jsonb, body:='{"name": "pg_net"}'::jsonb) as request_id;
pg_net configuration settings
The pg_net extension is configured to reliably execute up to 200 requests per second. Response messages are stored for 6 hours by default to prevent buildup. These settings can be reconfigured in pg_net v0.12.0 and above. Get current settings with: select name, setting from pg_settings where name like 'pg_net%'; Changing settings requires superuser privileges and must be done at system level using alter system set.
pg_graphql extension overview
pg_graphql is a Postgres extension that allows interacting with the database using GraphQL instead of SQL. It reflects a GraphQL schema from the existing SQL schema and exposes it through a SQL function called graphql.resolve(). This enables any programming language that can connect to Postgres to query the database via GraphQL without additional servers, processes, or libraries. The extension is designed to interop with PostgREST so that the graphql.resolve function can be called via RPC to safely and performantly expose the GraphQL API over HTTP/S.
graphql.resolve SQL function
graphql.resolve is a SQL function for executing GraphQL queries against a Postgres database. It takes a GraphQL query string as input and returns the result as JSON.
pg_graphql introspection disabled by default in 1.6.0
Starting from pg_graphql version 1.6.0, schema introspection is disabled by default and must be explicitly enabled per schema. To enable introspection for a schema, use: comment on schema public is e'@graphql({"introspection": true})';
pg_graphql usage example with graphql.resolve
Given a Blog table with id, name, and description columns, you can query it using graphql.resolve with a GraphQL query like: select graphql.resolve($$ { blogCollection(first: 1) { edges { node { id, name } } } } $$); This returns a JSON response with the queried data in the standard GraphQL format.
Enable pg_graphql extension with SQL
To enable the pg_graphql extension, use: create extension pg_graphql;. To disable it, use: drop extension if exists pg_graphql;. Creating the extension is equivalent to enabling it.
pg_partman requires pre-declared partitioned table
pg_partman requires your parent table to already be declared as a partitioned table before calling create_parent(). The table must be created with a PARTITION BY clause specifying the partitioning strategy (e.g., PARTITION BY RANGE).
Enable pg_partman extension
To enable pg_partman, create a dedicated schema for it and enable the extension there using: create schema if not exists partman; create extension if not exists pg_partman with schema partman;
partman.create_parent() function parameters for integer-based partitions
For integer-based partitions, partman.create_parent() takes parameters: p_parent_table (table identifier), p_control (numeric column name, e.g., 'id'), p_type ('range'), and p_interval (integer interval, e.g., '100000'). The p_premake and p_start_partition parameters are optional for integer-based partitioning.
Integer-based partitioning example with pg_partman
Example of setting up integer-based partitions on an events table: create table public.events (id bigint generated by default as identity, inserted_at timestamptz not null default now(), payload jsonb, primary key (id)) partition by range (id); Then call: select partman.create_parent(p_parent_table := 'public.events', p_control := 'id', p_type := 'range', p_interval := '100000');
partman.create_parent() function parameters for time-based partitions
The partman.create_parent() function configures a partitioned table. Parameters include: p_parent_table (table identifier), p_control (column name to partition on), p_type ('range'), p_interval ('7 days' for time-based partitions), p_premake (number of partitions to pre-create, e.g., 7), and p_start_partition (initial partition starting point, e.g., '2025-01-01 00:00:00'). The function briefly takes an ACCESS EXCLUSIVE lock while creating initial partitions.
Automate pg_partman maintenance with pg_cron
To automate pg_partman maintenance, first enable pg_cron with: create extension if not exists pg_cron; Then schedule the maintenance procedure hourly with: select cron.schedule('@hourly', $$call partman.run_maintenance_proc()$$);
Run pg_partman maintenance
Call partman.run_maintenance_proc() to maintain partitions. This function ensures future partitions are pre-created and retention policies are applied. It should be called regularly.
pg_partman extension overview
pg_partman is a Postgres extension that automates the creation and maintenance of partitions for tables using Postgres native partitioning.
Time-based partitioning example with pg_partman
Example of setting up time-based partitions on a messages table: create table public.messages (id bigint generated by default as identity, sent_at timestamptz not null, sender_id uuid, recipient_id uuid, body text, primary key (sent_at, id)) partition by range (sent_at); Then call: select partman.create_parent(p_parent_table := 'public.messages', p_control := 'sent_at', p_type := 'range', p_interval := '7 days', p_premake := 7, p_start_partition := '2025-01-01 00:00:00');
pg_plan_filter: Block queries exceeding cost threshold
pg_plan_filter is a Postgres extension that blocks execution of statements where the query planner's estimated total cost exceeds a threshold. It gives database administrators a way to restrict the contribution an individual query has on database load.
pg_plan_filter enabled by default in Supabase
The pg_plan_filter extension is already enabled by default via the shared_preload_libraries setting in Supabase.
pg_plan_filter error message when limit exceeded
When a query exceeds the statement_cost_limit, the error is: 'plan cost limit exceeded' with hint 'The plan for your query shows that it would probably have an excessive run time. This may be due to a logic error in the SQL, or it maybe just a very costly query. Rewrite your query or increase the configuration parameter "plan_filter.statement_cost_limit".'
pg_plan_filter example: setting statement_cost_limit
To set the statement cost limit, use: set plan_filter.statement_cost_limit = 50; Queries with estimated cost below this value will execute successfully, queries with estimated cost above will fail with the error 'plan cost limit exceeded'.
pg_plan_filter API configuration parameters
pg_plan_filter provides two configuration parameters: plan_filter.statement_cost_limit restricts the maximum total cost for executed statements; plan_filter.limit_select_only restricts filtering to select statements only. Note that limit_select_only = true is not the same as read-only because select statements may modify data through function calls.
pgRouting extension overview and algorithms
pgRouting is a Postgres and PostGIS extension that adds geospatial routing functionality. It provides a set of path finding algorithms including: All Pairs Shortest Path (Johnson's Algorithm and Floyd-Warshall Algorithm), Shortest Path A*, Bi-directional Dijkstra Shortest Path, Bi-directional A* Shortest Path, Shortest Path Dijkstra, Driving Distance, K-Shortest Path (Multiple Alternative Paths), K-Dijkstra (One to Many Shortest Path), Traveling Salesperson Problem, and Turn Restriction Shortest Path (TRSP).
pgr_TSPeuclidean example query
To use pgr_TSPeuclidean, query it with a subquery that selects rows containing id, x, and y coordinates: select * from pgr_TSPeuclidean($$select * from wi29$$)
pgr_TSPeuclidean function for traveling salesman problem
The pgr_TSPeuclidean function solves the traveling salesman problem using Euclidean distance. It takes a SQL query returning rows with id and x, y coordinates as input. The function returns results with columns: seq (sequence order), node (city id), cost (distance to next node), and agg_cost (cumulative distance).
Enable pgRouting extension via Supabase Dashboard
To enable pgRouting via the Supabase Dashboard: navigate to the Database page, click on Extensions in the sidebar, search for 'pgrouting', and enable the extension.
Enable pgRouting extension with SQL
To enable the pgRouting extension, execute the SQL command: create extension pgrouting cascade; To disable the extension, execute: drop extension if exists pgRouting;
pgsodium is deprecated and should not be used
Supabase does not recommend the usage of pgsodium as it will be deprecated. Developers should migrate to Supabase Vault instead. Supabase will reach out to owners of impacted projects to assist with migrations away from pgsodium once the deprecation process begins.
Supabase projects are encrypted at rest by default
Supabase projects are encrypted at rest by default, which likely is sufficient for most compliance needs such as SOC2 and HIPAA.
Vault and pgsodium are separate extensions
Vault and pgsodium are separate extensions. Vault does not depend on pgsodium and is not affected by pgsodium's deprecation. Vault is self-contained and exposes its own interface through the vault.secrets table and decrypted_secrets view.
Vault and pgsodium share the same root key but are independent
Vault and pgsodium share the same per-project root encryption key with the same format and location, but they are independent extensions that expose different interfaces. Switching to Vault does not change how your encryption key is managed.
pgsodium provides SQL access to libsodium cryptographic algorithms
pgsodium is a Postgres extension which provides SQL access to libsodium's high-level cryptographic algorithms.
pgsodium features not recommended on Supabase
Supabase previously documented Server Key Management and Transparent Column Encryption features from pgsodium. These features are not recommended for use on the Supabase platform due to their high level of operational complexity and misconfiguration risk.
pgvector extension name is 'vector'
The Postgres extension provided by pgvector is named 'vector', not 'pgvector'. When enabling the extension in SQL, use 'create extension vector'.
Vector similarity search use cases
pgvector is useful for finding similar items in a dataset by converting items into vectors of numbers using a mathematical model. This works for products, text, images, and other data types. It is particularly useful for AI applications using large language models to create and store embeddings for retrieval augmented generation (RAG).
pgvector filtered queries may return fewer rows than expected
When using IVFFlat or HNSW indexes with filtering on another column, the query may return fewer rows than requested in the LIMIT clause. For example, 'SELECT * FROM items WHERE category_id = 123 ORDER BY embedding <-> '[3,1,2]' LIMIT 5;' may return fewer than 5 rows even if 5 matching rows exist. To get the exact number of requested rows, use iterative search to continue scanning the index until enough results are found.
Store embeddings with Supabase client
To store a vector embedding generated by Transformers.js: Generate the embedding using a pipeline like 'feature-extraction' model. Convert the output to an array with Array.from(output.data). Insert into the database using supabase.from('table_name').insert({title, body, embedding}).
Create table with vector column
To store embeddings, create a table with a vector column using the syntax: create table posts (id serial primary key, title text not null, body text not null, embedding extensions.vector(384));
Enable pgvector extension via SQL
To enable the pgvector extension, use the SQL command: create extension vector with schema extensions; To disable it, use: drop extension if exists vector;
plpgsql_check extension overview
plpgsql_check is a Postgres extension that lints PL/pgSQL code for syntax, semantic, and other related issues. It helps developers identify and correct errors before executing code, and is especially useful for developers working with large or complex SQL codebases.