As long as you are good with that yes

Yes @User, removing

not null

would've made that work, but then you'd have existing data which does not meet your constraint requirement. Postgres helps you prevent that by giving the error. Once you have the error, you can reconsider whether the constraint is necessary, and if so, do one of the following: 1) add a default value to the column (which will populate for all existing rows); or 2) add the new column without the constraint, manually populate that column for your existing rows, and then re-add the constraint

Is possible to batch update with a filter? One request with 1000 rows to update with own match() filters.

I want to return all values from a table only if the value in a date column is in the future. I think the simplest way to do this is to get a value for what

now

is, like below. My question: is there a built-in way of doing

now

in PSQL?

ts
const now = new Date().getTime();

export const listPosts = async (): Promise<PostgrestResponse<PostTable>> => {
  return supabase
    .from<PostTable>('posts')
    .select('*')
    .gt('expires_at', now)
};

How to get current date in SQL

Hi, I just started playing around with function and triggers... I created a trigger and function that copies new users to a 'profiles' table. I started looking at encrypting data with pgp_sym_encrypt (again, just to see what's possible). It works in other functions, but I can't seem to make it work here when a user signs up. create or replace function public.handle_new_user() returns trigger as $$ begin insert into public.profiles (id, username) values (new.id, pgp_sym_encrypt(new.email, 'some_key')); return new; end; $$ language plpgsql security definer; This is the error message in the logs: (SQLSTATE 25P02): ERROR: function pgp_sym_encrypt(text, text) does not exist (SQLSTATE 42883)"

pgp_sym_encrypt

Trying to create an unique username on new user.

with username as select 'user' || round(EXTRACT (EPOCH FROM now())::float*1000);

Is there a way to pass this as a variable?

create or replace function public.handle_new_user() 
returns trigger as $$
with username as select 'user' || round(EXTRACT (EPOCH FROM now())::float*1000);
begin
  insert into public.users (id, username, email)
  values (new.id, username, new.email);
  
  return new;
end;
$$ language plpgsql security definer;

ahh,

(select 'user' || round(EXTRACT (EPOCH FROM now())::float*1000))

need to do this

hi, how can parse the data from the raw_user_meta_data to seprate colums in my public.users table?

m capturing the id and email correclty, but the user_metadata column comes as json, so m not sure how to parse it into separete columns

Can you open a question on the discussion board so we can have a reference for others to find when they ask this question too? https://github.com/supabase/supabase/discussions

yea sure

Ping me here when you have done it and I will provide an answer, also can you keep the SQL as code in the question rather than a screenshot.

@User here https://github.com/supabase/supabase/discussions/3216

Old data with new restrictions in Db, lead to data corruption, sometimes we solve with shortcuts (not null), but the errors remain and then they take the bill, and when they arrive we correct them in the same way and so on. (Datos antiguos con restricciones nuevas en Db, llevan a corromprer los datos, a veces resolvemos con atajos(not null), pero los errores quedan y despues pasan la factura, y cuando llega los corregimos de la misma manera y asi sucesivamente.)

What are the sql commands, to obtain the description of a project including tables with keys, restrictions and relationships, etc. Thank you

SQL commands to obtain project information

Hi, i have a table called "profiles". I want users to be able to create profiles if they are logged in OR

if they are not logged in an send a uuid

ignore me....while i am writing this i see that this is stupid....

Hi, new to Postgres and curious: why is the Next.js Quickstart using

WITH CHECK

for insert, but

USING

for update operations? ``` create policy "Users can insert their own profile." on profiles for insert with check ( auth.uid() = id ); create policy "Users can update own profile." on profiles for update using ( auth.uid() = id ); ``

Is there any reason I can't create a table with a primary key of type text in the UI, but it works fine through the SQL editor?

It never occurred to me to use text for primary key or foreign key, but by forcing my hand I would use it with where with like, which would be very inefficient.

I'm not a database guy, so you have to bare with me on the questions 😉 I've followed along the NextJS + Supabase + Stripe starter, and built on top of it. I saw that the authors of that repo has been using text as references, so I just thought that it would be possible: https://github.com/vercel/nextjs-subscription-payments/blob/main/schema.sql

UUID is technically a type of VARCHAR right?

According to https://www.postgresonline.com/article_pfriendly/179.html, UUID is a native type in Postgres and not a subset of another type, or inferred from another type. You could say it's comparable to a 128-bit varchar, but it's not the same native type.

Is it possible to filter a query based from a column with jsonb structured data? I would like to query this table and filter on the company property from the metadata-column.

This should work:

SELECT * FROM my_table WHERE metadata -> 'company' = '65'

The

->

essentially tells it to look inside the contents of the column (

metadata

- named before it), and find the property (

company

- named after it) that matches the clause

Hey. I have changed a view's owner to

authenticated

so that it uses RLS but now my secret JWT role can't access the view. How do I do both?

ALTER VIEW my_view OWNER TO authenticated;