I have the following dataset, that have the prices for many products and cities: CREATE TABLE my_table ( the_id varchar(5) NOT NULL, the_date timestamp NOT NULL, the_city varchar(5) NOT …
Tag: postgresql
Provide array of ids and get rows with repeated rows
I have the table where I wand to do a query to get same rows multiply times Races Table: id name location 1 fast race London 2 cool race New York 3 super race Berlin I want to do query where I will search races in table by id and get results with repeated rows something like this And get
LIMIT 1 by Column On Query Returning Multiple Results?
I have a user_certifications table, with these props: So, a user can be listed multiple times with a variety of certs. I have a list of user_ids for which I need to get ONLY the most recently updated record. So for one user it would be: How do I do this efficiently if I need ONLY the last dated entry
Select 2 products per city with most counts in PostgreSQL
I have this dataset: I want the top 2 counts per the_city, so the expected result should be: I have tried this but it’s wrong Answer It’s greatest-n-per-group problem. You can use row_number()over() window function to to serialized city wise product list in descending order of count(*). Then select first two rows from each city. Query: Output: the_city the_product product_count
ERROR cursor does not exist after first loop in PL/pgSQL
I need to load a large number of csv files in to a PostgreSQL database. I have a table source_files which contains the file paths and a flag which indicates whether a file has already been loaded for all the csv files I need to load. I have written the following code which loads the first file correctly but then
How could I speed up this SQL query?
I have this query: With this explain plan: https://explain.depesz.com/s/gJXC I have these indexes: Is it possible to further optimise this? There are only 30 time intervals for this table so I feel like I should be able to get it faster. Answer A main limitation here (at least if you have CPUs to spare) is that GROUPING SETS does not
PostGIS: Transform Linestring to LineString ZM
Problem: I have a series of geometries (LineString) saved in a Postgres database with additional information saved next to the geometry (a column for speed and time). I want to combine the three columns into a LineString ZM type so the geometry goes from a 2d to a 4d vector. id geometry speed time 1 LineString_A Int[] Float[] 2 LineString_B
Inserting large amounts of generated test data into postgresql database
I want to insert a billion rows of floaty data into a postgresql database so that I can test the performance of various postgis functions. My effort below takes ages and seems to be really inefficient …
Postgres changing the query from index Only scan to bit map scan when data set increases
I have two same queries but with different where condition values When I ran this two queries they both producing different result for first query the result and for the 2nd one Can anyone explain Me why Postgres is making a BitMap Scan instead of Index Only scan ? Answer The short version is that Postgres has a cost-based approach,
Using PIVOT in conjunction with a total summation in SQL
I have some data with a general structure: I want to output a single row like: I can see that the way to approach this is with a PIVOT, but how would I do this whilst also calculating the total? Thank Answer You can use conditional aggregation: Note: If you have values other than ‘X’, ‘Y’, and ‘Z’, then you