Skip to content

Tag: postgresql

Convert JSONB Keys to Columns

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 anot…

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 in…

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 You…

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