This has been asked here and a few other places before but seems like the suggested answers either don’t apply to postgres or don’t work in this situation. I’m looking to select distinct column names, eg: SELECT DISTINCT column_name FROM table_name WHERE … ORDER BY column_name however I’m looking to eliminate case sensitive duplicates (eg A and a should be
Tag: postgresql
waitlist in postgres, deadlock
I’m trying to create a waiting list in Postgres. Minimal code: status is important, because I want to be able to notify users when they are applied after waitlisted. But don’t want to notify them based on that they are in the first n position if they wasn’t waitlisted at all. Services are limited to a given number of users.
how can i loop insert query?
I have a problem like this: I need to optimize the application, with db (postgreSQL), the table looks like this: I have more than a thousand such voters, and I need to put all of them in the database, but among them there are several duplicates that could vote several times (from 2 to infinity), and I need to, when
In a postgres loop, will the delete actually be performed at the end of the loop?
I need to delete a table, but I want it to be really delete at the end of the loop. Could you confirm that if I code this way, the table will actually be deleted at the end of the loop? the tata table contains data that comes from a CSV, with the COPY. This table is updated every 24
Combine 2 GIN indexes with gin_trgm_ops into one | Postgres
I have a flowing table Columns text_id and customer_unit_id basically store codes like K-2021-8524 and similar. In a web interface I use search window to filter out entries by text_id or customer_unit_id. Typical search would be like this I have created to GIN indexes to facilitate search I have tried to make 2 column GIN index in order to have
How to sum the minutes of each activity in Postgresql?
The column “activitie_time_enter” has the times. The column “activitie_still” indicates the type of activity. The column “activitie_walking” indicates the other type of activity. Table example: What I need is to sum up the total minutes for each activity separately. Any suggestions or solution? Answer First calculate the duration for each activity (the with CTE) and then do conditional sum. BTW
how to split multilinestring into overlapping 2-point subsets (2-point linestrings)?
I want to split a multilinestring or linestring like LINESTRING(0 0, 1 0, 1 1, 0 1) into linestrings consisting of two points. So the linestring above would become: LINESTRING(0 0, 1 0) LINESTRING(1 0, 1 1) LINESTRING(1 1, 0 1) I would also like to be able to do the same with multilinestrings. Answer Use a LATERAL with ST_DumpPoints
SQL query for top N people as per spending in each state for top M states
I have a table with 4 columns (about 10M rows) I want to get the “Top N people as per spending in each state for top M states” using an SQL query on the table. I am using PostgreSQL. Can someone help me out? Note: There are multiple entries per person and per state. However, 1 person can live only
update join with where
I need to update a certain alert on a loan where the obligor and obligation is something, but I can’t figure out what I’m doing wrong in the join. This seems to update all alerts in the table rather than the specific one I want. We are using postgresql db Answer Workbench usually refers to MySQL. If so, then the
PostgreSQL get results in current time zone
as said in the title I would like to have a query that returns the value of the time stamp in my current time zone (even according summer time!). my_table is: (Don’t ask me why I cannot put this table directly in markdown…prob cause the dates) Now for example if I have to select the 24h corresponding to the entire