Postgresql – Snowflake: How do I multiple a column of binaries (cast as integers)?
Lets say I have table with 1 column like this: Col A 1 0 0 1 If I SUM it, then I will get this: Col A 2 My question is: how do I multiply Col A so I get…
Mysql – How can geo query with JOINs be optimized to be faster and more efficient?
I'm currently using PlanetScale for database hosting and I'm trying to monitor my queries' performance so that it doesn't affect pricing too much and I see that my recently added SQL query is hitting the database pretty hard and it…
Postgresql – Having limit [small value] issues with Postgres
Background I'm getting insanely slow queries when using small LIMIT clause values on a relatively simple query. I've already read and re-read PostgreSQL query very slow with limit 1. (not that I couldn't have missed anything, but it's related, and…
Postgresql – Insert Into On Conflict With a Where Clause
I have a SQL query that looks like this: INSERT INTO testing.names ( id, first_name, active_status ) ------------------------------------- SELECT stage.id, stage.first_name, stage.active_status FROM testing.name stage ON CONFLICT ON CONSTRAINT names_pkey DO UPDATE SET active_status = excluded.active_status where active_status <> excluded.active_status…
Mysql – What is wrong with this method for updating multiple rows dynamically?
exports.createTaskDataForNewDay = async function(values) { try { console.log("values", JSON.stringify(values)) let pool = await CreatePool() //[timestamp , requiredTimes , reward , difficulty ,taskId , uid , csn] let query = "update userTaskData set timestamp = ?,requiredTimes=?,timesCompleted=0,reward=?,difficulty=?,state=1,taskId=?,replacedF=0,replacedC=0 where uid =? and suitCase…
JSON Object Query SQL Server
I have a JSON string which is the following: [ { "id": 103001058774, "name": "status", "label": "Status", "description": "Ticket status", "choices": { "2": [ "Open", "Open" ], "3": [ "Pending", "Pending" ], "4": [ "Resolved", "Resolved" ], "5": [ "Closed",…
Mysql – How can I count rows up to a specific value in SQL
I have a database table that looks like this (although it is much larger!) playerid dismissaltype Offset 2 0 0 2 1 1 2 0 2 2 0 3 2 0 4 2 0 6 2 2 7 3 1…
Postgresql – Using CTE in where statement is very slow
I have a following SQL running in PostgreSQL v13.10: WITH stuckable_statuses AS ( SELECT status_id FROM status_descriptions WHERE (tags @> ARRAY['stuckable']::varchar[]) ) SELECT jobs.* FROM jobs WHERE jobs.status = ANY(select status_id from stuckable_statuses) And it is running really slow while…
Postgresql – Getting only the highest counts per type in Postgres
Say I have a table with kids and their toys. CREATE TABLE kids_toys ( kid_name character varying, toy_type character varying, toy_name character varying ); kid_name toy_type toy_name Edward bear Pooh Edward bear Pooh2 Edward bear Simba Edward car Vroom Lydia…
Mysql – How to query with multiple fields in IN clause?
I want to query a table with multiple combined fields in an IN clause. Example: find all records with "john doe" and "marc smith", but ignore all other persons.. SELECT * FROM persons WHERE firstname IN ('john', 'marc') AND lastname…