I have a table in my database which has the following columns: ID Data 1 [{“na”:false,”answer”:1.5},{“na”:false,”answer”:0.5}] 2 [{“na&…
Tag: sql
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 th…
String table name not working in a query with psycopg2
I would like to generate dynamic queries with variables for column names and table name. This is my code: query = sql.SQL(“select {fields} from {table}”).format(fields=sql.SQL(‘,’).join([ …
Separate columns by commas ignoring nulls
I have the below table: A B C D E A1 null C1 null E1 A2 B2 C2 null null null null C3 null E3 I would like the below output (separated by commas, if any value is null, then do not add a comma): F A1, C1, E1 A2, B2, C2 C3, E3 Answer You can concatenate all columns
PYTHON – Dynamically update multiple columns using a custom MariaDB connector
While reading this question: SQL Multiple Updates vs single Update performance I was wondering how could I dynamically implement an update for several variables at the same time using a connector like MariaDB’s. Reading the official documentation I did not find anything similar. This question is similar…
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 LineStrin…
How to subtract Expenses from Gross Profit from sql plus query?
I have to calculate Net profit So i have following query. How can i subtract “Total Expenses” from “Total Gross Profit”: SELECT SUM(orders.quantity * orders.price) AS “…
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 …
How to test PL/SQL procedure with varray input
I’ve made a procedure that uses a varray as input, and while everything compiles correctly, I can’t figure out how exactly to test/execute the procedure that actually works. The varray takes in at …
How to retrieve the properties stored in SQL with multiple inheritance
I’m storing the records in SQL that represent a multiple inheritance relationship similar to the one in C++. Like that: CREATE TABLE Classes ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); …