It doesn't seem like supabase supports numeric type. How are we supposed to store price data?

Postgres supports monetary types (https://www.postgresql.org/docs/9.1/datatype-money.html). However, a better solution is to store the value in the smallest possible denomination as

int

. For example, 100 cents would be 1 dollar. That provides you with the most flexibility, and avoids issues with some languages like JS where

1.00

would get truncated to

1

, or

1.10

would become

1.1

(which looks strange in most currencies, compared to

1.10

).

Yes but when I'm using the dashboard to create the table schema, it isn't an option in the dropdown menu. Do I just create it with another sql client and the supabase client will be able to handle it?

Just starting out with supabase, I'm trying to implement something like this:

SELECT agent_code, 
SUM (advance_amount) 
FROM orders 
GROUP BY agent_code;

But is seems supabase.js does not have SUM or GROUP BY. Is the only way to accomplish this via stored procedures?

Figured it out:

create or replace function get_top() 
returns table (author_id int, upvotes int) as
$$
  SELECT author_id, 
  SUM (upvotes) 
  FROM karma 
  GROUP BY author_id;
$$
language sql;

Something like that works. Had to use

DROP FUNCTION get_top()

that was the key.

Hey nice people! Did anyone ever created sql function for getting the trending posts on feed in last say one week?

Trending posts

How would I protect my cart table in supabase. When user adds items to their cart how am I suppose to have the backend verify the calculations and not rely on the user client to send that data? Or is that more than Supabase is able to handle and I would need to develop my own backend.

Does anyone know how to use the "Search logs via query" functionality in the Database logs (Settings -> Logs -> Database) view of the Dashboard?

Before checkout you should also check the values of the products

You could use a serverless functions. Netlify is easy to setup with those

thanks. So there isn't a way to validate the cart with just Supabase? I will need to use something like Netlify to do the validation?

Protecting a cart table

hello, I made this function on sql:

CREATE FUNCTION is_member_of(_user_email text, _channel_id int8) RETURNS bool AS $$
SELECT EXISTS (
  SELECT 1
  FROM channel_users cu
  WHERE cu.channel_id::int8 = _channel_id::int8
  AND cu.user_mail::text = _user_email::text
);
$$ LANGUAGE sql SECURITY DEFINER;

then I created a policy for the table channel_users on SELECT using:

is_member_of(auth.email(), channel_id)

then I created another policy always for the same table on UPDATE:

(auth.email() = (user_mail)::text)

the result I expect is: Im able to update all channel_users rows contain my email Im able to select all channel_users rows contain an channel_id where Im in but... into reality... I select, I update, but at moment to set an realtime taks on UPDATE

this.supabase
      .from(`channel_users:channel_id=eq.${this.data.channelID}`)
      .on('UPDATE', payload => {
        console.log(payload)
        if (payload.new.user_mail !== this.data.user_loged.user.email) {
          this.data.responderIsTyping = payload.new.is_typing
        }
      })
      .subscribe()

He never trigger it (yes replication is enable), if I change the Select policy from

is_member_of(auth.email(), channel_id)

to

true

then the realtime update subscription receive the trigger.... any suggestion?

i have a similiar problem, in my case i set the policy to check for the user id (

(auth.uid() = id)

). things did not work until the check is removed. when i ran

supabase.auth.session()

or

supabase.auth.user()

, they both returned null even though i'm logged in, so i suspect the issue is related to this somehow.

Say, does anyone know why my bigints are getting rounded in Supabase? If I insert a 19 digit number into a bigint column, then query it, the 3rd-to-last digit is rounded, leaving the final 2 digits at 00. e.g,

select test_column
from test_table
where test_column = 1234567890123456789;

on supabase.io, and

await supabase.from('test_table').select().match({test_column:1234567890123456789})

in my JS code will both give me back the value 1234567890123456**800**

whereas the same query on pgAdmin returns the expected value

my current workaround is to cast the value to text, but that doesn't seem like the savvy thing to do

JavaScript does NOT have the notion of integers - a value of type "number" in JavaScript actually corresponds to a value of type "double precision" in PostgreSQL. A value of type double precision (or just "number in JavaScript) is a floating point number (you can search for the IEEE 754 stardard to more detailed info). The bottom line is - you CANNOT represent all integer values form a bigint in a JavaScript number because some of the bits in a javascript number are reserved for the fractional part. I think this is explained relatively well here: https://stackoverflow.com/questions/1848700/biggest-integer-that-can-be-stored-in-a-double

JavaScript has added support for such big integers using the BigInt object wrapper, so you might be able to use that (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/BigInt)

Ah ha, super helpful. Thanks! At some point I should probably learn javascript 🤔 . What threw me most was that the in-app SQL query builder was also returning rounded values -- in retrospect, of course it would.

"What threw me most was that the in-app SQL query builder was also returning rounded values -- in retrospect, of course it would." - are you talking about the Supabase SQL Editor? If so, I would file a bug in GitHub, since as I mentioned there are still ways to go around that even in JS and having the SQL editor show a wrong value seems like a pretty bad bug to me

FYI, it seems like this is also a limitation of PostgREST, which is used by the Supabase JS client, see https://github.com/PostgREST/postgrest/issues/498 and https://github.com/supabase/postgrest-js/issues/201

Hello! 👋 I'm running into an issue with realtime + RLS policies. if I have RLS policy enabled for a table, I stop receiving

INSERT

and

UPDATE

events for that table. I go into detail in this github issue: https://github.com/supabase/realtime/issues/213 basically: my table

board_cards

has this policy check for all operations:

is_member_of_list(uid(), list_id)

. that is calls a custom postgres function defined as:

sql
SELECT EXISTS (
  SELECT 1
  FROM team_memberships tm
  WHERE tm.user_id = uid
  AND tm.team_id = (
    SELECT team_id
    FROM team_boards tb
    WHERE tb.id = (
      SELECT board_id
      FROM board_lists bl
      WHERE bl.id = list_id
    )
  ) 
)

This works correctly via REST calls, however it seems to break realtime. If I turn OFF RLS for the table, I am correctly receiving

INSERT

and

UPDATE

events.

I have a very similar issue: https://discord.com/channels/839993398554656828/869405720934744086/923585745418092594

I think the custom functions break the Realtime, but maybe @User can answer us on that, dunno

Just a hunch I haven't tried Realtime myself yet though do you want to try to remove

SECURITY DEFINER;

from your function definition. As I understand it that may change the RLS behaviour.

already tried, the result is the same, database queries work, but realtime no

tomorrow from office I will try to inspect better the situation, if I will not find an solution I will open an issue on the repository (now are 00.53 am in italy so I need sleep lmao )

but anyway is pretty the same of @User from what I read