Skip to content
Advertisement

Tag: postgresql

Join two tables with no relation postgres?

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 between table m

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 ‘[”]’ , ‘[“””]’ etc. but i cant get it to work. ex where meta_keywords <> ‘[”]’ Answer

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 result it gives is like this: Now, I want to fetch only Description value out of it i.e. (EMOVIES r8).

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 get a list of products with tags that are the same. I found the following to be insufficient for my query since it will match all products with at least

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 that they are checked only at the end of a

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 element of discount_codes: This only searches

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 both by campaign and sales_channel.

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. How can I write ORM for the same? SQL query is

Advertisement