All Questions
Tagged with supabase postgresql
403
questions
19
votes
2
answers
9k
views
How can I create an enum column in Supabase?
I want to create an enum column in a Supabase table. Supabase uses Postgres under the hood, so I understand that technically it is possible if I do it manually using SQL. But is there any way I can do ...
- 1,133
18
votes
8
answers
20k
views
Supabase client permission denied for schema public
Whenever I've try to use @supabase/supabase-js to query the db, I get an error.
error: {
hint: null,
details: null,
code: '42501',
message: 'permission denied for schema public'
}
I ...
- 303
18
votes
1
answer
8k
views
How to insert in multiple tables with a single API call in Supabase
This is the simplified structure of my tables - 2 main tables, one relation table.
What's the best way to handle an insert API for this?
If I just have a Client and Supabase:
- First API call to ...
- 407
16
votes
3
answers
7k
views
How to access custom schema from supabase-js client?
I love Supabase but our team needs to use schema functionality that PostgreSQL offers - unfortunately we have been unsuccessfully to make schemas working so far.
Other info:
PostgreSQL 14
newest ...
- 2,042
12
votes
3
answers
5k
views
Supabase: Solutions for column-level security
Currently, I'm using a Supabase database. One of the big roadblocks that I'm facing is column-level security, which seems a lot more complicated than RLS.
Say that I have a column called is_banned, ...
- 914
8
votes
0
answers
2k
views
Is there a way to generate the OpenAPI/Swagger definition from Postgres with object references?
I have a rest api using supabase/postgres(t) which generates it's own OpenAPI/Swagger definition. The problem is that this only contains the id fields but not actual references to the other tables.
...
7
votes
1
answer
3k
views
Can't migrate schema using Prisma with Supabase
When I use the Postgres database on Supabase I run the following command, npx prisma migrate dev --name init, but I get the following error (first command in screenshot):
Error: db error: FATAL: ...
- 1,071
7
votes
2
answers
2k
views
PostgREST - Add authenticated user id on insert
I need to automatically add an author of an inserted row in one of its columns. I am using postgREST (using Supabase cloud service) and I don't want users to add whoever they want as the author. Is ...
- 704
6
votes
2
answers
8k
views
Supabase: how to query users by eMail?
I'm using Supabase in a Node JS middleware. I am developing an invite function thats receives an eMail address of an existing supabase user via a REST Endpoint. Now it should query the users table in ...
- 213
6
votes
3
answers
5k
views
Supabase returns empty array
I have a messages table , under a 'public' schema in Supabase.
I'm trying to fetch all rows from my local dev environment, but nothing is returned.
I'm trying:
const response = await supabase.from('...
- 13.8k
6
votes
5
answers
17k
views
Error: supabaseUrl is required (supabase) postgresql
i'm using supabase postgresql online, in which important create a .env file where is my online DMBS's URL and anon key is saved and a "supabase client" file also create to access DBMS and ...
6
votes
2
answers
7k
views
Error: Get config: Unable to establish a connection to query-engine-node-api library in Prisma and nextjs
I use nextjs fresh project with Prisma and supabase database. I'm using ubuntu.
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 22.04 LTS
Release: 22.04
Codename: ...
6
votes
1
answer
2k
views
Deno / Postgres "invalid peer certificate contents: invalid peer certificate" error
I'm using deno db to connect to a supabase postgres server.
Here's the error from deno deploy.
TLS connection failed with message: invalid peer certificate contents: invalid peer certificate: ...
- 57.4k
6
votes
0
answers
2k
views
Running Supabase locally with DataGrip, 'relation "public.users" does not exist'
I'm running Supabase locally, using Docker, on MacOS. I initialized a Next.js project with Supabase, and connected the locally created database to JetBrains DataGrip. I can see and adjust the data in ...
- 388
5
votes
7
answers
12k
views
Filtering in join in supabase
Im using supabase with a database which have 2 tables (that are implicates in this issue).
Tables are teachers and users. Both have ID and id_teacher/id_user respectively.
Im working in a query where ...
5
votes
2
answers
7k
views
Add aditional user information when signup supabase
I am trying to add additional data in a user record in supabase, I have created a trigger that is called after a record has been inserted in auth user that should add the user id and username in the ...
- 478
5
votes
1
answer
4k
views
Is there a way to perform full text search on multiple columns on Supabase with Javascript?
I've tried using many symbols to separate columns; ||, |, &&, & with and without spaces.
For instance
.textSearch("username, title, description", "...");
.textSearch(&...
- 55
5
votes
2
answers
891
views
Only allow read if user has exact document id postgresql row level security/supabase
Is there a way for a user to only be able to read a document only if they have the exact document ID?
I want to avoid creating users, so the only security is a random guid saved in browser memory - ...
- 1,131
5
votes
1
answer
2k
views
Failed to create and edit tables in Supabase, "Failed to create pg.columns:..."
I have failed to create or edit tables in Supabase. It is showing an error message "Failed to create pg.columns: must be the owner of event trigger pgsodium_trg_mask_update". I think it has ...
- 310
5
votes
1
answer
191
views
Run slow background process when certain conditions in Postgres data are met?
I'm using Postgres (via Supabase) and Node (via NextJS on Vercel).
I have a table report which has columns for an enum region, and generated_text which is content generated by AI.
create type region ...
- 23.2k
4
votes
1
answer
6k
views
Supabase: How to automatically update a timestamp field after updating a row?
What DB I am using?
Supabase hosted version
What do I need?
After I update a row with .update({ name: 'Middle Earth' }) method I need to
automatically update also a timestamp in my table.
How can ...
- 2,042
4
votes
1
answer
5k
views
Update multiple rows in a single query in a Supabase Database (postgres)
I looking to update multiple rows via a single query in Supabase. I'm struggling to find an example of how to do it online.
For this example here is how the data exists the database's 'food' table:
...
- 323
4
votes
1
answer
3k
views
Transactions in supabase
I have two tables that reference each other. For the sake of example, say they're Posts and Comments:
Posts
-----
id bigint
title text
body text
Comments
--------
id ...
- 2,600
4
votes
1
answer
5k
views
How to insert timestamp without timezone using Supabase-JS client?
I want to insert current timestamp without timezone to Supabase.
I know I can set default value of the timestamp column to NOW() but
there can be some delay in the timing and I need it precise.
I ...
- 2,042
4
votes
1
answer
2k
views
"Uncaught Error: More than one relationship was found" with Supabase client query
I'm building a multitenant app and running into an error after adding multiple relations that point to the same table:
Uncaught Error: More than one relationship was found for teams and users
When ...
- 5,274
4
votes
1
answer
1k
views
How to use bigint safely with Supabase JS
Postgres' bigint type holds 64 bit integers. But the Supabase JS library returns those values as JS numbers, which cannot safely store 64 bit integers AFAIK. What would be the correct way to handle 64 ...
- 1,133
4
votes
1
answer
1k
views
Supabase database size issue [closed]
I am quite new to Supabase.
Please clarify this:
I have a table in the database and its only 32KB in size.(2 columns and 2 rows of data only)
So why it is showing a Database Space Usage (under ...
4
votes
1
answer
716
views
Supabase triggers : ERROR: function charindex(unknown, text) does not exist (SQLSTATE 42883) (SQL)
I have two tables auth (this is the default one given by supabase) and profiles
when a new row is added in auth table I want a new row with the data added in auth table to be added in profiles tables ...
- 45
4
votes
1
answer
2k
views
Connecting to postgres on supabase via pgBouncer from prisma
Problem
running out of database connections in prod leading to errors like this;
Error:
Invalid `prisma.queryRaw()` invocation:
Timed out fetching a new connection from the connection pool. More ...
- 1,571
4
votes
1
answer
3k
views
Unable to push local migrations to Supabase through CLI
I tried to push the local migrations to the supabase database through its cli with the command supabase db push. The error it is showing me is:
Error: ERROR: must be owner of table users (SQLSTATE ...
- 306
3
votes
1
answer
2k
views
supabase: `prisma migrate dev` sometimes times out (postgres advisory lock)
I have a supabase database (postgres 15.1.0.88) and I'm using prisma as ORM(latest version [email protected]). When trying to apply migrations with prisma migrate dev, the command most of the times ...
- 116
3
votes
1
answer
206
views
Create recursive view with WHERE clause
I am trying to make a view for my recursive query, and use the view with a where clause to set a starting point.
CREATE TABLE dog (
id int,
name varchar(50)
)
CREATE TABLE dog_parent (
id ...
- 199
3
votes
2
answers
2k
views
Can't reach database server at `aws-0-us-east-1.pooler.supabase.com`:`5432`
Happy Monday! I've recently run into a weird issue and can't seem to figure out what would be causing it.
I have a NextJS project using Prisma and Supabase. When I run the app on my Windows desktop or ...
- 31
3
votes
2
answers
233
views
Supabase puts certain rows at the top
My Supabase JS client performs an INSERT on the messages table using the following.
import { createClient } from "@supabase/supabase-js";
const supabase = createClient(
"MY ...
- 123
3
votes
2
answers
781
views
What's the equivalent of this Supabase query in SQL ( Postgresql )
I have a table "store" and a table "product". Each store has multiple products but a product only has one store ( one to many relationship ). The tables might look something like:
...
- 33
3
votes
2
answers
2k
views
How to reset the auto incremented id column to 1 whenever I seed the database in postgreSQL?
I am using Supabase as a backend which provides postgres as its database and prisma for my next-js app. I wanted to seed some dummy data into the database. The first time I run the seed script, ...
- 29
3
votes
2
answers
1k
views
How to modify a Supabase table from the dashboard UI or the Supabase SQL editor?
Whenever I try to modify an existing table in the Supabase dashboard, I get this error "Failed to pg.columns: must be owner of table <table_name>". This seems to be like a permission ...
- 55
3
votes
1
answer
1k
views
Supabase Self hosting issue
I am trying to host Supabase with a separate PostgreSQL DB. According to their docs, they say that Supabase uses pgjwt for auth. However, AWS RDS or Azure PosrgreSQL doesnt not support pgjwt.
Is ...
- 253
3
votes
4
answers
2k
views
How can I implement Full text search by using Prisma + PostgreSQL ? (I'm using Supabase as the database)
This is my schema file:
generator client {
provider = "prisma-client-js"
previewFeatures = ["fullTextSearch","fullTextIndex"]
}
datasource db {
provider = "...
- 31
3
votes
2
answers
2k
views
supabase auth.uid() = user_id equals false
I have a very strange problem that is driving we up the walls.
I am creating the following table
CREATE TABLE articles(
id BIGINT generated by default as IDENTITY PRIMARY KEY,
user_id uuid ...
- 187
3
votes
0
answers
440
views
Auto-Incrementing IDs in Composite Primary Key
In my app, there are organizations that can create tasks. Instead of having a serial task ID, I would like to have an incrementing task ID for each organization ID. Just like in GitHub, where repo + ...
- 326
2
votes
1
answer
2k
views
Supabase how to query the same table twice
I have been experimenting with Supabase recently and trying to make a twitter like 'replying to @user' comment feature.
So I have a database ERD attached below for reference, As you can see each ...
- 217
2
votes
1
answer
877
views
Row-level-security dual join
I have three tables
journeys
id
user_id
...
sections
id
journey_id
...
stops
id
section_id
...
I want to use row level security to make sure that a user can only insert a stop if the uid()...
- 1,327
2
votes
2
answers
2k
views
Row-level-security based on relation table data
I am getting into Supabase and to practice I am making a suuuper simplified website-builder.
However I am having troubles with the row-level-security policies.
I have three tables:
user → with users' ...
- 824
2
votes
2
answers
2k
views
Match PotsgreSQL timestamptz by exact value
I am using PostgreSQL 13.3 via Supabase.io. I have a table with a field called modified_at which has a type of timestamptz:
CREATE TABLE IF NOT EXISTS knowledge_views (
id uuid NOT NULL DEFAULT ...
- 27.4k
2
votes
1
answer
3k
views
Unable to run pg_dump from Supabase
I intend to backup my postgres database at Supabase
$ pg_dump -h db.PROJECT_REF.supabase.co -U postgres --clean --schema-only > supabase_backup.sql
I ran the command
GRANT ALL ON ALL SEQUENCES IN ...
- 1,639
2
votes
1
answer
646
views
Phonetic Algorithms for Postgresql
please, I am working on a PoC for Person Real-time Identification, and one of the critical aspects of it is to support both minor misspelling and phonetic variations of First, Middle, and Last name. ...
- 311
2
votes
1
answer
4k
views
Count the occurrences of DISTINCT values using supabase
I want alternative supabase code for this SQL command
SELECT order_date,COUNT(*) as count
FROM orders
GROUP BY order_date
ORDER BY order_date Desc;
What I am trying to do is, I want the number of ...
- 89
2
votes
2
answers
35
views
SQL : to_tsvector on a specific value of a jsonB colonne
I want to make a full search query on a spécific value of my jsonb like the format of the json is :
{
"highlights": {
"concise": null,
"complete": null
},
&...
2
votes
2
answers
41
views
After Revoking Select in postgres, RLS stopped working
I ran this query
REVOKE SELECT ON services_connected FROM anon;
and added this policy
create policy "workspace_only"
on "public"."services_connected"
for all
to anon
...
- 11.1k