I have a user table users containing id , name and information of type jsonb User Table id name information 1001 Alice {“1″:”Google”,”2″:”1991-02-08″} 1002 Bob {“1″:”StackOverflow”,”3″:”www.google.com”} I have another Table having all the profile fields values named ProfileFields profilefieldid Value 1 Company 2 DateOfBirth 3 ProfileLink The information jsonb column can only have keys present in the ProfileField Table. You
Tag: postgresql
How to add ‘all’ CASE when group by CASES in SQL
I have a query like this: Which gives the result: How can I add a row for ‘all’ requests in CASES? If we add a when sp.provider=’prv1′ with no more detailed condition then all cases become one ‘all’ case because and other cases are ignored. Answer You can’t do that inside the CASE, as it behaves like a series of
How to extract only rows matching condition ‘WHERE column1 ILIKE column2’ (where column1 is integer and column2 is jsonb)?
I am using postgresql. Let’s suppose I have this table name my_table: I want to select only the rows having the value of id equal to one of the elements of the array in stores (of that line). However, the type of stores is not array, it is jsonb. So I want to get something like this: I have tryed
How to extract only rows matching condition ‘WHERE column1 IN column2’ (where column2 is an array)?
I am using postgresql. Let’s suppose I have this table name my_table: I want to select only the rows having the value of idm equal to one of the elements of the array in stores (of that line). So I want to get something like this: I have tryed with but I get a syntax error. Answer You need to
How can I indicate the “current” column in an upsert in PostgreSQL?
I have the following upsert with which I have problems because the subqueries give me more than one result. The problem is that I don’t know how to indicate in the upsert to compare the value of the column that is currently being updated. The problem is after the DO UPDATE. Answer I think you are looking for the excluded
How to get the average from multiple columns
I saw this answer how do I select AVG of multiple columns on a single row but I have some rows which is the value is 0 and it’s throwing an error division by zero when I try to use his ex. Answer In order to resolve the task, you can use CASE expression because you cannot divide by zero:
How to allow a gender field to accept only two values?
The question: Add a gender field. Ensure the field will only accept ‘M’ or ‘F’ and the default value should be ‘F’ PostgresSQL code: ERROR: syntax error at or near “Check” LINE 2: add Gender varchar(1) default ‘F’ ,Check (Gender = ‘M’ or G… How do i fix it? Answer Your approach is good, there is only a small syntax
SQL – count rows between dynamic number of date ranges
I have a table to store events like this: and another table to store posts like this: if I want to get count of posts that happened during a single event I can write: but how can I get the count of posts that happened during multiple events, when the target events are only known at runtime? Edit: the database
PostgreSQL : Hint: No operator matches the given name and argument types
When I execute my query I have this error : Query failed: ERROR: operator does not exist: “GOOD_RECIEPT_ANOMALY_DETECTION_V2_last_alerts_sent_gsheet_histo” > timestamp without time zone Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 176 Here is my SQL query : I work on PostgreSQL database. I think the problem comes from
SQL: extract keys and values of a jsonb field as rows of two separate columns
In my postgresql DB I have the following query returning where json_field is of type JSONB. I am looking for a way to query all the keys and all the values from this json field, so that the output would be Is there a way to do it ? generalizing the question after it got the solution answer When I