I have this below query to fetch books of a random author and order by the books last modified date. Because I am new to SQL something tells me this query can be improved and simplified. I would appreciate the help of the SQL experts, if this is already simplified then I will consider myself an SQL expert :).…
Tag: postgresql
Postgresql weird outcome after joining on dates with interval of 1 month
I have a table like this: The table indicates the date a user accessed a website and user_id is the user id. I wanted to check whether the user logged in a month and in the previous month. I wrote the next query: However, when I run this query table a shows the previous month and table b is the
Writing SQL query : getting the average value per minute
I want to make a query getting the average created value per minute. I don’t come up with any idea but using subquery like below. Would there be any other ways using only one query (without subquery) …
PostgreSQL DETECT age’s groups
Let’s consider a part of table like this: id_client | age ———–+—– 16052 | 65 25027 | 29 25659 | 44 33297 | 32 38750 | 53 43063 | 28 56311 | 48 …
How to use LOWER() on elements of a jsonb column in PostgreSQL?
I have a PostgreSQL table like this one: Table t id | keys (jsonb) —+—————- 1 | [“Key1”, “Key2”] My goal is to query this table to find out if one of the keys of a …
Flatten a subarray in postgresql
Input: postgres=> select sets from matches limit 2; sets ———————- {{6,4},{6,2}} {{6,3},{5,7},{10,4}} (2 rows) Expected (Sum of the value in each array) postgres=…
PSQL Constraint based on column value
Is it possible to have a Constraint but only when one column is set to a particular value. For example take this pseudo-code example of a President which checks to make sure there is never more than 1 …
Filtering multiple XML nodes using XPath in PostgreSQL
There is an XML with the structure: – Item – Documents – Document – Records – Record – Category – Code – Value And here is the SQL query that …
How to update row by id using case?
Is it possible to update by id other row values? I try like this But nothig.. UPDATE era SET id = CASE WHEN id = 3 THEN gender = ‘Female’–or maybe it is possible to call insert command here? …
PostgreSQL cross join using max returns null
I want to select the max() value of the modified_at column in each of several tables: This works correctly as long as each of the tables has at least 1 row. The problem is that when just one of the tables has 0 rows, null is returned for all tables: What is a solution that returns the correct values for