Skip to content
Advertisement

Tag: postgresql

Can I start the quarter of the year from the month I wanted to on PostgreSQL?

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

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

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

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

Advertisement