I have this statement which you can see I am using Rails and connecting to the database raw, but the output removes duplicate named columns eg user_id etc from the second table and is still giving results in the second table in regards to the first even though there is no relation. Eg There is no relation bet…
Tag: postgresql
SQL SELECT WHERE meta_keywords = ‘[”]’
I have been having trouble query after ‘[”]’ in table. The data is inserted from pandas dataframe with datatype object (if it matters). I want to get make a view without the empty lists. Have tried to write the empty list different ways ‘[”]’ , ‘[“””…
Parse text of json object key value postgresql
I have the following kind of objects maintained in a table column. I have tried parsing this object to get the value for this key “1075852262” which has this value “Event=”13″ Description=”(EMOVIES r8)”……….” The SQL I have written is: The resul…
SQL convert rows to one Column
I have a table, Output before pivoting: Expected output: How to do that for three or more rows but always with to one-row column? Answer You can use conditional aggregation: However, I might suggest that you if you want all values in a single row that you just use arrays:
Query a column with jsonb data meeting certain criteria
Problem I have a table like this: product tags (jsonb) P001 [{“name”: “LX”,”active”: true}, {“name”: “TX”,”active”: true}] P002 [{“name”: “LX”,”active”: true}] I am trying to query against this table to…
Why `not deferrable` constraint is deferred when using `with`?
When this script is run in PostgreSQL, why does Statement 1 and 2 work and only statement 3 gives an error even when both the foreign key constraint are explicitly not deferrable? Answer According to the docs non-deferrable unique constraints are checked for each row, contrary to the standards specification t…
How to return only those customer_id who visited Santa Clara after visiting Milpitas or #5 after visiting #2
Customer_ID Region Location Store_Entry ——————————————————– D1 2 Milpitas ‘2020-10-01’ 14:03 D2 5 Santa Clara ‘…
Postgres Nested JSONB Query
I have a JSONB column, data, in the orders table: I’m trying to select “orders where discount_codes contain a code in codes_array”. I could not figure out how to write this query exactly. I read about the [*] operator but am unsure how to use it in this context. This only searches the first …
Aggregation with required columns in GROUP BY clause in postgresSQL
DB-Fiddle Expected Result: To get the expected result I tried to go wiht this query: However, it gives me error: column s.sales_channe” must appear in the GROUP BY clause. The problem is when I add the sales_channel to the GROUP BY clause I am not able to ge the expected result because then it groups bo…
How to use variable column name in filter in Django ORM?
I have two tables BloodBank(id, name, phone, address) and BloodStock(id, a_pos, b_pos, a_neg, b_neg, bloodbank_id). I want to fetch all the columns from two tables where the variable column name (say bloodgroup) which have values like a_pos or a_neg… like that and their value should be greater than 0. H…