I have a dataset that tracks user activity, and I need to identify consecutive rows where a specific condition is met. The table structure and sample data are as follows: Table: UserActivity UserID Activity Timestamp 1 Login 2023-11-01 08:00:00 1…
I want to create a column numberofimage. In which each cell in the numberofimage column will display the number of images in the addimage column format of each cell in addimage column like: [{"file":"vstorage.com/sohoa/han01//123.jpg","name":"123.jpg","type":"image/jpeg","size":239709},{"file":"storage.com/sohoa/han01//456.jpg","name":"456.png","type":"image/png","size":1030361}] I would count the number of…
I am implementing an entity-attribute-value model to store nationwide property data that will be updated daily: 130 million properties, will probably store billions of values. We also want to search this data on arbitrary attributes, hence using EAV so we…
I have the following two tables table c id type 1 B1 2 B2 3 B3 table s id s_id s_type 1 2 C1 2 2 C2 3 3 C2 Now I want to insert new rows in the table…
DROP TABLE IF EXISTS A; CREATE TABLE a (id int); CREATE or replace FUNCTION insert_and_return(int) RETURNS int AS $$ BEGIN INSERT INTO a VALUES ($1); RETURN $1; END; $$ LANGUAGE plpgsql; SELECT * FROM insert_and_return(10),A AS y; Above code has…
I'm working with a JPA query in a Spring Data repository that fetches data using DISTINCT and an ORDER BY clause. The query works without DISTINCT, but adding it results in the following error: Expression #1 of ORDER BY clause…
The following works fine: conn = psycopg.connect(self.conn.params.conn_str) cur = conn.cursor() cur.execute(""" SELECT 2, %s; """, (1,), ) But inside a DO: cur.execute(""" DO $$ BEGIN SELECT 2, %s; END$$; """, (1,), ) it causes psycopg.errors.UndefinedParameter: there is no parameter $1…
I have a query like: SELECT DISTINCT ON (services.group_id) services.service_id, ... FROM services WHERE services.group_id IN ('67240181b97477f054f9b1bc', '67240181b97477f054f9b1be') I have an index on group_id. Why does Postgres need to do an extra heap scan? I just want any 1st match…
I have the following data in a PostgreSQL table: id amount opening_amount closing_amount 1 200 1000 1200 2 -500 NULL NULL 3 -200 NULL NULL 4 1000 NULL NULL I want to convert this data into a ledger format, calculating…
I am trying to create a SQL query to update a column in my table as shown here. Driver table: ID TripTotal 1 NULL 2 NULL 3 NULL 4 NULL Trip table: TRIP ID DRIVER ID TRIP 1 1 1…