Assume I want to store via sqlite3 a simple list of strings: or a python object with properties and methods. What I tried so far is to serialize (pickle) my_list, and the returned byte representation is b’x80x03]qx00(Xx01x00x00x00aqx01Xx01x00x00x00bqx02Xx01x00x00x00cqx03e.’. However, cannot be st…
Tag: sql
Postgres – How to achieve UNION behaviour with UNION ALL?
I have a table with parent and child ids. Each parent can have multiple children and each child can have multiple children again. The data looks like this. I’d like to find all children. I can use a recursive cte with a UNION ALL. This gives me the result I’d like to achieve. However I’d lik…
How to pivot in postgresql
I have table like following,and I would like to transform them. I’d like to pivot like following. I tried like following statement, but it returned a lot of null columns. And I wonder I must add columns one by one when new type will be added. If someone has opinion, please let me know. Thanks Answer dem…
How to create a derived attribute column in sql which is sum of other two existing columns
I want to have a column selling_price which is sum of scost and dcost. Answer You can use MySQL generated columns for it. Reference Doc
SQL: How to divide column by counts?
So I have this query This will print something like device_id paper_count 1 5000 2 10000 And I have this other other query that will return the number of times the toner has been changed according to some date. I used count to count the number of times it’s been changed. This will print something like d…
Group items by 2 columns or similarity with another column
Let’s say we have a Sqlite table containing: How to group by h=CONCAT(city,age) but also add in the group the users that have a column foo identical to someone in the group? As seen in Group items by 2 columns, here is how to group by h: How to add this second condition on foo? Example: here Alice and B…
Different sort results between Oracle and Postgres with Special Characters
*Apologies as I know this question has been asked before, but tried the previously suggested approaches and none fixed my issue. I am trying to compare a table found in two databases. Unfortunately, the table does not have a primary key, so I am trying to order by the same column and compare that way. When I …
Finding the intersection between two integer arrays in postgres
In postgres documentation I found that if I have two intarrays I can use the & operator to get the common elements between the two arrays(intersection), but this statement: raises this error: I have postgresql version 13 I am wondering why doesn’t this work, and how can I fix the query or try anothe…
TypeError: Argument 2 must be Tuple or List
I have built a GUI using TKinter. There I want to output the stored data and change them individually. Using the UPDATE command, I want to update the data in the table. I use MariaDB as database. I’m getting this error: Here is the complete function: Any suggestions where I’m doing wrong? Answer A…
How to Return Separate Sums of Columns if Initial Value in row is 0 in SQL
I am trying to write a SQL query that will return unique rows of sums of shipped part numbers per customer depending on if the value is negative or positive. For example: If we shipped 20 of part Z to customer A twice, and they returned 6 parts back to us, it would display: After running query, expected resul…