All About Databases

All About Databases

#qanda

Articles tagged with #qanda

Custom Aggregate Function (to use with GROUP BY)
Question: I'm wondering if it's possible to create a custom SQL group by function. For example, I often need to find the most common value within a group. While I can achieve this using CTEs or subqueries, having a custom group by function like the e...
Jul 10, 20252 min read1
Update table according with JSON/JSONB
Question: I have a table books : Name of columnType idint (PK) nametext nb_pagesint And a table book_modifications : Name of columnType idint (PK) old_datajsonb new_datajsonb book_idint (FK) Now let's imagine I have a row in my...
Jul 10, 20252 min read5
Query run fast from MySQL CLI but very slow on R2dbcTemplate Code
Question: Currently, I having a query SELECT al.category, IFNULL(jt.name, 'Ungrouped Devices') AS device_group_name, IFNULL(jt.id, '00000000-0000-0000-0000-000000000000') AS device_group_id, jt.color_code AS color_code, COUNT(*) AS total FR...
Jul 10, 20253 min read4
How can I SELECT rows where a JSON key is bigger/smaller than a certain date?
Question: My table contains a value json-column: { "id":"string", "user":int, "history":{ "unixtimestamp":{ "progress":"string", "editorId":int }, "unixtimestamp":{ "progress":"s...
Jul 10, 20251 min read3
How to simulate a deadlock on a row in MySQL?
Question: To simulate a lock in mysql I can grab the row with the following: BEGIN; SELECT * FROM table WHERE id=1 FOR UPDATE; Now, if I try and update that row (from another connection) it will raise the following error after innodb_lock_wait_timeo...
Jul 10, 20252 min read3
SQL BULK INSERT Format Issues
Question: I'm using SQL Server and Management Studio. I was getting data conversion errors when performing a BULK INSERT into a table, that I could not resolve and switching to using a format file allowed data to be inserted, but the data inserted wa...
Jul 10, 20254 min read3