All Questions
96
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
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
9
votes
3
answers
8k
views
Supabase, filter by column value of foreign key row
I am trying to figure out how to implement a query in supabase:
Schema
CREATE TABLE cars
(
id SERIAL PRIMARY KEY,
brand TEXT
);
CREATE TABLE stores
(
id SERIAL PRIMARY KEY,
car INT ...
- 857
8
votes
3
answers
9k
views
delete associated records in Supabase
When using Supabase, is there a clean / simple way in which I can delete all associated records together with an item? Say I have Posts and a Post can have many Comments. When I delete the Post I want ...
- 2,600
4
votes
1
answer
2k
views
Supabase dynamic RPC function
I am looking to create an RPC for grabbing distinct values of a column but I am going to be doing this across multiple tables and I don't want to have to write this function over and over for each ...
- 1,124
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
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
2
votes
1
answer
3k
views
Supabase: filter rows based on if any of their one to many relations has property
In supabase I have a list of tracks and each track can have multiple artists (kept in a separate table). I only want tracks in which at least one of the artists associated with that track contains a ...
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
1
answer
2k
views
Supabase SQL Database Functions and Parameters
In my supabase database, I have a list of words along with their uuids in the form of
uuid
word
unique_chars
found_count
x1
apple
4
0
y2
banana
3
0
I want to return all words, which contain a ...
- 316
2
votes
1
answer
4k
views
Database Error Saving New User error with Postgres Function?
For my website (backend support by Supabase), I have the built-in auth.users table, and a public.profiles table that includes text fields for email, username, and school. I have created a trigger and ...
- 503
2
votes
0
answers
39
views
Get entity field as entity from another table
I'm working on a PostgreSQL function in SQL Editor in Supabase to fetch data in my Swift app.
I have Place entity with category: PlaceCategory
+-----------------------------------+
| ...
- 83
2
votes
1
answer
1k
views
Failed to run sql query: must be member of role "supabase_admin" showing in SQL editor
How to get supabase admin role?
I am new to Sql editor So I don't know anything
- 21
1
vote
2
answers
1k
views
Supabase RLS insert policy: only authenticated user, owner of the record
I have a table called ebooks with rows a foreign key row called user. and i want to create a policy which checks only the users can only insert if the user column is same as the id of the user who is ...
- 460
1
vote
2
answers
2k
views
How to queries in supabase realtime?
Most of the blogs and stacks suggests below database for chat.
message_table
-id
-message
-conversationId
-sender
-receiverId
conversation_table
-id
-conversationId
Now message_table look like this.
...
- 393
1
vote
1
answer
37
views
How to group by in plpgsql
I'm having trouble in grouping my data where I am grabing records and grouping them by employer_type and date. Both have same date and same employer type but doesnt group instead returns 2 different ...
- 117
1
vote
1
answer
217
views
How to select and order rows based on number of matched elements in array from foreign table in postgREST
I'm creating simple supabase application and I want to construct postgREST query to select recipes and order them ascending by number of matched_ingredients
Right now I'm stuck on this:
https://URL....
- 55
1
vote
2
answers
57
views
How to Use AND Operator with OR Condition in Supabase Query?
I'm working on a query in Supabase where I need to select specific columns from a table based on multiple conditions. Specifically, I need to select columns a, b, and c from a table where column e is ...
- 11
1
vote
1
answer
55
views
How to design a transaction that calls nested functions and only commits if all called nested functions commit, e.g. abort if one subfunction fails?
How to design a transaction that calls nested functions and only commits if all called nested functions commit, e.g. abort if one subfunction fails?
For example, this transaction:
CREATE OR REPLACE ...
1
vote
1
answer
1k
views
Batch delete rows matching multiple columns in postgres using an array
i'd like to delete these rows from a table in a postgres function as an array parameter:
{id1: 4, id2: 8}
{id1: 4, id2: 9}
{id1: 5, id2: 8}
that is, something like this:
delete from mytable
...
- 2,025
1
vote
1
answer
352
views
Need help parsing a value into a Supabase fetch data call
I am trying to parse a value from an HTML page:
<td><a href="/Event?id=<%=event.id%>" title="event"><%=event.event_name%></a></td>
to a ...
- 21
1
vote
1
answer
957
views
ERROR: syntax error at or near "FOREIGN" (SQLSTATE 42601)
I'm trying to add to a column that already exists, in an orders table, the constraint that it is a foring key:
ALTER TABLE IF EXISTS public.orders DROP COLUMN IF EXISTS location_id;
ALTER TABLE IF ...
- 17
1
vote
1
answer
58
views
PostgreSQL function returning public table based on a private table
I'm new to SQL. Can someone tell why this doesn't work and what to do to fix it? What I want is basically create a public table based on the values from a private one. Thank you very much in advance! :...
- 11
1
vote
1
answer
714
views
PL/PGSQL query is returning only the first row (Supabase)
I need to query data from a jobs table and insert into a clients table, into two different columns.
First I grab the customer name to put on name column, then I grab the list of unpaid/partially paid ...
- 33
1
vote
0
answers
48
views
Encountering Foreign Key Constraint Error When Deleting Rows in PostgreSQL
I have a PostgreSQL database with two tables, types and order_items where order_items has a foreign key constraint referencing the types table. When I attempt to delete a row from the types table, I ...
- 1,729
1
vote
0
answers
192
views
Pulling data from one to many relation ship supabase
Currently in my app I am facing the issue of pulling the categories table, item in category table has one-to-many relationship with items from subcategory table.
This is my current code:
@override
...
- 121
1
vote
0
answers
556
views
How can I make a Supabase CRON job to delete specific data every day?
I want to give users a period of time in which they can undo their account deletion. Someone suggested that I should use a CRON job that (1) checks my Supabase table daily for users who deleted their ...
- 179
1
vote
1
answer
2k
views
Unable to add foreign key in Supabase
I am following Build a SaaS product with Next.js, Supabase and Stripe course by Jon Meyers. I am facing issue in Add Relationships Between Tables in Supabase Using Foreign Keys part. I have created a ...
- 203
1
vote
1
answer
54
views
how to join 2 related tables using postgres functions?
I have a recipes table with columns recipe_id, product_name, instructions and I have instructions table with columns recipe_id, instructions.
columns instructions and recipe_id in recipes table is the ...
- 763
1
vote
1
answer
789
views
Postgres RLS Policy to show items based on its permissions listed on another table
I have in my database tables for posts and users (with user-id as primary key and it's role on second column). I'm trying to create a new policy which allow users with admin role to read, edit and ...
- 95
1
vote
1
answer
385
views
Generating HMAC in Postgres/Supabase and security
I need to generate hmac for my users using Postgres' function on Supabase. I am running the following command to generate it at runtime when the user requests for it.
create or replace function ...
- 163
1
vote
0
answers
2k
views
Nested query in postgrest (supabase) denies existence of relationship between two tables
I have the following structure
create table organisation (
id uuid primary key,
organisation_name varchar not null
);
create table organisation_member (
id uuid primary key,
email varchar not ...
- 1,101
1
vote
1
answer
205
views
Column identification when search on multiple index columns
I'm using supabase(PostgreSQL). I'm using full text search(on multiple columns using indexing) of PostgreSQL and its working fine but i want to get column identification(which means i want to get a ...
- 11
0
votes
3
answers
439
views
Postgresql: remove ability to query every row in a table
I have the following table called "profile":
id, name, created_at
Every user ( with SupaBase ) has his own row in the "profile" table with his user_id as the key ( id )
I added RLS ...
- 56
0
votes
3
answers
3k
views
PostgreSQL query to Supabase Query
How can i make once of this PostgreSQL querys in supabase?
I tried reading supabase doc but it doesn't work
select rooms.id as room_id, members.id as member_id, user_id from rooms
inner join members
...
0
votes
1
answer
45
views
Syntax error near 'WHEN place.price > 3000 THEN' in PostgreSQL function
I'm working on a PostgreSQL function in SQL Editor in Supabase that filters places based on price categories provided by array of strings. For example ['$', '$$']. Here's my code:
CREATE OR REPLACE ...
- 83
0
votes
1
answer
543
views
Trigger Function Permissions Issue in Supabase: Access Denied for Function Called by Trigger
I'm encountering an issue while working with Supabase triggers and function execution permissions. I have set up a trigger to fire after an insert into the auth.users table. The trigger calls a ...
- 85
0
votes
1
answer
86
views
Supabase raw_user_meta_data values stored with quotation marks (" ")
I have a function in Supabase like this:
declare
username text;
name text;
begin
insert into public.users (id, email, username, name, role)
values (new.id, new.email, new.raw_user_meta_data -&...
- 149
0
votes
1
answer
99
views
How to create a function that returns a value if id is in an array?
I'm trying to create a new function in supabase. I'm quit new to SQL and postgreSQL so I'm not sure what I'm doing.
Anyway I want to make a function that checks if one value is in an array of ints. If ...
- 25
0
votes
1
answer
1k
views
Supabase: Query all rows in which either of two child relations contains property
Let's say I own a bunch of malls that have stores and restaurants. I want to query for all malls in which at least 1 store OR at least 1 restaurants is open
There are three tables, malls, stores and ...
0
votes
2
answers
43
views
How to populate a table from a json array column in another table?
In my Postgres database I naively created a column in table Foos as a json[] that looks like this:
TABLE Foos
id | ... | baas
--------------------------------
1 | ... | [
| ... | {
| ... | ...
- 1,924
0
votes
1
answer
23
views
Postgres/Supabase increment count of 'items' in 'collections'
I have the following scenario.
Every row in collections table has an items_total column.
This items_total should be updated (incremented/decremented) every time a new row is added to the items table, ...
- 1,369
0
votes
1
answer
78
views
Create view with join depending on table of another join
My tables:
create table Article (
ID int generated by default as identity primary key,
Name text);
create table Client (
ID int generated by default as identity primary key,
Name text);
...
- 9,581
0
votes
2
answers
67
views
PostgreSQL Trigger Function Call Error: "function <function> doesn't exist"
I have a PostgreSQL trigger that fires after an INSERT on the auth.users table, calling a function handle_new_user() which, in turn, calls insert_into_profiles() to add user data to another table.
The ...
- 85
0
votes
1
answer
42
views
Aggregate functions cannot be nested PostgreSQL
In this query I want to receive 10 records of machine locations that each have a bunch of meals connected to them. These meal records are connected to a certain dish type. And I want to see the live ...
- 51
0
votes
1
answer
53
views
Querying multiple rows of a table shows an [object] [object] instead of the actual data. How to fix?
I have this query:
const {data, error} = await supabase
.from('profiles')
.select(`
*,
station (
station_name, user_id
),
type (
...
- 1,729
0
votes
1
answer
261
views
Issue with Row Level Security in Supabase
I have following tables.
create table
public.records (
id uuid not null default gen_random_uuid (),
created_at timestamp with time zone not null default now(),
content text null,
...
- 96
0
votes
2
answers
108
views
How to look for values in a Postgres column that begin with INPUT and contain X instances of a character?
I have a COLUMN in a Postgres table which has values with the following pattern.
INPUT-X
INPUT-X-Y
INPUT-X-Y-Z
INPUT-X-Y-Z-A-...
I am trying to count the number of entries in COLUMN where the ...
- 327
0
votes
2
answers
135
views
PostgreSQL: Remove element from JSON data
I am fairly new to SQL and wondering how to remove a specific element from the JSONB data below:
[
{"id":101,"content":"lorem ipsum", "username":"user_1&...
- 40
0
votes
1
answer
76
views
Supabase query to get all songs from the artists a user follows
I'm rather to new to Supabase and i'm trying to find a way to structure and to retrieve all the songs from the artists that a user follows.
With the code below i can retrieve all artists a user ...
- 167