What I'm trying to do is to have a table that assigns a subscription to a user. All the samples I found so far do this via: (auth.uid() = user_id) However I would like to keep track of reassignments. With…
I need made a subquery with IN statement. Like this (simplify): DELETE FROM table_1 WHERE id_table_1 IN (SELECT string_agg(fk_id_table_1::TEXT,',') FROM table_2 I need made a massive delete, based on second subquery. The problem is if i use IN statement i…
I'm at a loss for how to append a set of values to a two-dimensional array. I've tried both the concat operator (||) as well as array_append(). Both seem to work fine when appending a single value to the end…
I'm using ::text[] to convert an array inside a JSON column, like in the example below: Imagine sometable to have a JSON column called 'object' with a structure like the following: { "properties": ["string", "someotherstring"] } I am querying the…
According to the 43.6.8. Trapping Errors topic in this Postgres Documentation, the correct syntax for handling exceptions is: BEGIN; -- SQL Statement EXCEPTION WHEN Others THEN -- Exception Handling Logic END; This is while when I use this syntax in…
SELECT First_name,last_name,email,ROUND (AVG(Amount),2) AS moneyspent FROM Customer INNER JOIN Payment ON Customer.customer_id= Payment.customer_id WHERE Staff_id=2 GROUP BY First_name,last_name,email HAVING SUM(Amount)>100 ORDER BY Moneyspent DESC first_name last_name email moneyspent Brittany Riley [email protected] 5.83 Arnold Havens [email protected] 5.14 Eleanor Hunt [email protected] 5.04…
I am executing the following query in Python to return the list of all nodes stored in the Apache AGE. with postgresql.cursor() as cursor: query = """SELECT * FROM cypher('graph', $$ MATCH (v) RETURN v $$) as (v agtype);""" cursor.execute(query)…
In a trading platform, users have three different actions: LOGIN, SEARCH, ORDER. Abandoned SEARCH action is defined as: customers LOGIN then do some SEARCH and do not ORDER before next LOGIN. Now I have a table: action_table, recording customer_id, action,…
I am using the following sample query to create a node with a label. SELECT * FROM cypher('vg-graph', $$ CREATE (m:Movie {name: "Movie Name", uid: "12345678", body: "Description"}) $$) as (n agtype); Following query should remove label 'Movie' from the…
I am calculating the average of values in a table CREATE TABLE measurements ( id SERIAL PRIMARY KEY, measurement INTEGER NOT NULL ); import postgres from "https://deno.land/x/[email protected]/mod.js"; const averageMeasurement = async() => { const rows = await sql`SELECT AVG(measurement) AS…