Let’s say that I have a table called queries, which holds hundreds of predefined SQL queries in JSON format. Then let’s say that I have two different databases, the original being a 200+ table DB and the new one being a 50~ table DB. This new DB is a subset of the original, so every table it has e…
Tag: postgresql
Add Sequential Letter column to table
In C I can switch between int and char to build a list of sequential integers and then convert them to ASCII characters that will be sequential according to the alphabet. Is there a way to do this in …
postgres – aggregate items in text array
I have an array of text items, each text item in the array is a delimited string, here is a sample: drop table if exists tmp; create table tmp(x text); insert into tmp select (‘1~41.5~50~1|2~43.72~…
compute time difference between rows and conditional group by using PostgreSQL
I have data like as shown below: +——-+——-+ | index | time | +——-+——-+ | 1 | 09.00 | | 2 | 09.02 | | 3 | 09.03 | | 4 | 09.05 | | 5 | 09.11 | | 6 | 09.12 | | …
PostgreSQL – Get count of items in a table grouped by a datetime column for N intervals
I have a User table, where there are the following fields. | id | created_at | username | I want to filter this table so that I can get the number of users who have been created in a datetime range, …
find the highest occurrence of a point type
How to get the highest repetition for a point type data in PgSQL Need to calculate the most repeated coordinate (more precisely within a range of 100 meters) Sample data column name coordinates …
How do I get the matching id for every record?
My table is called platform_statuses, here is its schema: And this is my query, I would like to also get the matching id for the returned records. Also note that the abs function you see in the query is a custom one I got off this answer. Here is its definition: Answer I understand that, for each account and …
Querying for users who have NOT completed a survey today and Receiving an error
I have a feature I am working on and I need my PostgreSQL db to return all the users that have not completed a survey today. I am using a left join to join on my survey results table and filtering by customer_id and where the survey_results.created < today but I am using a date part and extract to get
Postgres totals by classification per group
for example i have an orders table with a classification column. i want to get the total per classification for each city in a state for certain identified cities, for specific date. I have done the …
Get most common value in column [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 2 years ago. Improve this question I have next table: How I can get most common (common means that the count of 2 is 3, count o…