I am dealing with a DB whose transaction table could have been split up into 2, but dev team decided it was not worth it. Now I want to retrieve the transactions and their dates. Problem is : table includes 2 different types of transactions, meaning: I have 2 date columns : “transaction_date” and …
Tag: postgresql
Multiply two existing columns and add result as new column in table
I’ve created a new column ‘jahresbeitrag’ and now I’m trying to multiply two numeric columns to save the values from the query result in this column, but for some reason the query doesn’t work. PgAdmin start executing the query, but it stays at …waiting for the query to com…
How to get Postgres to return 0 for empty rows
I have a query which get data summarised between two dates like so: However for dates that do not have any orders, the query returns nothing and I’d like to return 0. I have tried with COALESCE but that doesn’t seem to do the trick? Any suggestions? Answer Please refer to the below script. There i…
How to leave only distinct rows in the table. Postgresql
I need to delete all duplicate in 40kk of rows. I’ve got table: I’ve tried this query but after 1h of executing I’ve gave up on waiting. Is there any other solution to delete duplicates in more optimazed way? UPD: I need to do it just once cause I didn’t handle the duplicates at the st…
fill in missing dates of date_trunc with last row data
I currently have a query for chart using date_trunc and the result is something like this dates with no value are skipped , however I need it to be like this notice how for 7th and 8th it will use the last available data which is in 6th. Will appreciate any help! Answer Use generate_series to generate a row f…
Can I use WHERE with table field on right side of LIKE?
I have a SQL table that contains prefixes of strings that I would like to match against longer strings. Suppose the column containing the prefix is called prefix. I now want to do a simple search for all rows that prefix a longer string. My first thought is to use a query that is something like this: The poin…
How to append nested key-value to json array in postgresql?
My json values in jsonb column are below: I have nested key – “Images” that has array of strings. How to write query that will add to Images new keys/values (“UploadedBy”:null and “Size”:null) and transform array of string to key-value (“https://face.png” …
Create a new double-field based on the existing string-filed
There is an SQL query forming a table. It is necessary to form another double-number field based on one of the string fields in the table. Values of the original string field (BRLOADSTA0): Values of the resulting double-field (BRLOADSTA9): The request which I’ve create: Full snippet: Thanks in advance e…
Postgres Query Using Join Table
I have 3 tables: users, public_pictures, pictures Import properties: users.id public_pictures.user_id public_pictures.picture_id pictures.id public_pictures is a join table between users and pictures with only those two properties above. Both are foreign keys to their respective table. Given a user.id, I want…
Looking for an explanation of this attempted SQL injection query
Looking through my logs I found the following query string as an attempt to perform a SQL injection, probably from an automated tool: From what I can tell, it’s attempting a timing based attack to see if any of the tables in my database start with “a” – the sleep function will only run if the union quer…