I am using PostgreSQL 14.5 I can classify the year by quarter using the Postgres keyword QUARTER as illustrated in the documentation just like SELECT EXTRACT(QUARTER FROM TIMESTAMP ‘2001-02-16 20:38:40’);. And it works. The default quarter starts with January. Which makes complete sense. I.e, quarter one is composed of January, February, and March. The others follow then. But, I wanted
Tag: postgresql
Real number comparison for trigram similarity
I am implementing trigram similarity for word matching in column comum1. similarity() returns real. I have converted 0.01 to real and rounded to 2 decimal digits. Though there are rank values greater than 0.01, I get no results on screen. If I remove the WHERE condition, lots of results are available. Kindly guide me how to overcome this issue. I
Query based on JSON object values
If I have a column(lets call it students) in a PostgreSQL db in a JSON/JSONB format like such: How can I query the students who have passed? Ideally, I’d write something like SELECT (students->’student_id’) FROM table_name WHERE (students->’Pass’)=true, but that doesn’t work because it has the missing layer of keys i.e “1”, “2” and “3”. Something that has its logic
Gap-and-island for more than time threshold
I have these tables: I want to investigate sessions of a user having time time difference between successive rows greater than 5 minutes, reporting: session and session mode number of sessions with the gap-and-island problem. My attempt: Error: Expected results: Note: dbfiddle. Answer The problem is we have two user_id and they’re not defined so you need to specifically choose
Query: generated start_date and end_date where it isn’t exist in the table
i want to ask something, so i have a table A that have start_date and end_date column to store the data when there is a failure happened, it look like this rows start_date end_date 1 “2021-08-01 00:04:00” “2021-08-01 02:54:00” 2 “2021-08-01 04:52:00” “2021-08-01 05:32:00” And what i want to do is to have a query so that the response will
Parsing out inconsistent data to grab specific year
I have a TableA with a column Years. Each row value in this column is formatted as a mix of: and The number of years listed in each row varies (could be 1, could be 10). I have a task to pull the latest year for each row. I am not even sure where to start on this, so I
Postgres SQL to convert first letter after Mc to upper case
I have data in Postgres SQL, something like this I am trying to convert first letter after Mc to uppercase , something as below I am using initcap function as below, but its not working as expected Appreciate any help! Answer Use above query it will give desired results.
Is it possible to group sql join results by a common column? Please see the description
I’m super rusty with databases and haven’t got the chance to work too much with them in the past 6-8 years, so there are some things that are uncertain for me. Let’s assume I have a simple bi-directional one-to-many situation: and and I want to perform a query which will return all the parents together with the matching children that
How to join two tables based on a calculated field?
I have two SQL queries that output the same kind of output and have the same grouping and order : I would like to join those two results based on the calculated date field (which is the month), and have a result with 3 columns : date, count_users and count_payment_logs. How can I achieve that? Thanks. Answer Something like this
Error “more than one row returned by a subquery used as an expression”
I have the following 3 tables: airport area booking And I have this query Running the query I get the error: I don’t understand what the problem is. I added an SQL Fiddle to play with: http://sqlfiddle.com/#!17/8a09f/1 Answer A subquery in the SELECT list is only allowed to return a single value. Not multiple rows, nor multiple columns. If you