I have a table with a jsonb column with data from one row like [ { “a”: [], “c_id”: 624, “ps”: [{“”: 0, “pr”: “73”, “f”: “M”, “s”: “M”}], “g_n”: “K L Mish”, …
Tag: postgresql
Is there a better way I can structure my tables, do a 4 table join, or will I have to perform multiple queries?
What is the correct way to perform this GET request? I currently have 4 tables: events, event_comments, event_photos, event_users_going The last 3 tables, event_comments, event_photos, and …
LEFT JOIN ON string match Very slow
I have to perform this LEFT JOIN but it’s very slow (30.000ms) because of the match on text string. How can I make it faster? SELECT * FROM table_a LEFT JOIN table_b ON ( table_b.state = ‘…
Query for array values within jsonb column
I have a jsonb column called chores in my work table with data looking something like this: [{“task”: “feed_dog”, “value”: “Daily”},{“task”: “mop_floor”, “value”: “Weekly”] There could be zero to dozens of tasks in…
How can I add condition based on select value (JSONB) on PostgreSQL 12?
I want to filter rows that have gold badges. I am using PG12 and I use new path feature of it. To clarify, I have a users_tbl table like this: CREATE TABLE users_tbl ( ID serial NOT NULL PRIMARY …
Group by multiple columns and limit per group – Postgres
I’m creating a messaging app as a side project and I’m trying to query a user’s conversations efficiently. The messages table structure is basic right now with some dummy data: I’d like to be able to query the DB and group by “conversation” – A.K.A any rows that have …
Postgres Compare string to number return correct result
Trying to understand how postgres compare string to number because following queries return true, Can someone please explain? Answer PostgreSQL applies implicit conversion where necessary and possible. How it does this is no further explained as far as I know. (https://www.postgresql.org/docs/9.1/typeconv.htm…
Replace with single quote to double single quote not working in PostgreSQL 12
Replace with single quote to double single quote not working properly in PostgreSQL 12, it was working fine in PostgreSQL 11. PostgreSQL 12 Query: SELECT REPLACE(patient.note,””,”””), * FROM patient Output Text: Medicare Secondary Veteran�s Administration PostgreSQL 11 Query: SEL…
Postgres get root of self-referential table
Given the table: If I get a name from the table (and thus the Pkey), how can I get the name of it’s “root”-level parent: the name where parent is NULL which is referenced to (indirectly) by the given name. Say I have these values (I don’t know which values I will get): The function/wha…
Pivoting SQL table with crosstab function
I made this query that lists all real estate prices per m2 per year in a city. It works nice yet all years end up in rows, with the prices behind them. I would prefer seeing the years in columns with …