Mysql – Unique row no matter the column
Is there a way to make an entire row unique no matter what value the column is in? So, if 1 is in col a, and 2 is in col b, then if you were to insert 2 into col…
Postgresql – Query Optimization for Large Dataset with Complex Join and Aggregation
Table A: (columns: id (integer), name (varchar), ...) Table B: (columns: id (integer), a_id (integer), value (numeric), ...) Table C: (columns: id (integer), a_id (integer), b_id (integer), date (date), ...) SELECT A.name, SUM(B.value) AS total_value, COUNT(DISTINCT C.date) AS distinct_dates FROM…
How do I get records with different values in same Postgresql column AND condition on another column?
We are trying to get filtered custom field data out of the Moodle database via SQL query. Unfortunately it's necessary to join multiple tables to get the data we need, because Moodle keeps its data this way. Also we can't…
Compare 1st day of “commented” to 1st day of “approved” using “submittedat”?
I'm trying to find a way to calculate the average of the first commented day vs the first approved day of this data set. I've narrowed it down to one PR that I'd like to work on but I'd like…
Can MySQL extract name values from records with overlapping dates?
I have a table containing areas whose size changes over time. The timeframe (in years) for which an area size is valid is stored in the columns "VALID_FROM" and "VALID_UNTIL". The timeframes for one area must not overlap. I want…
Is Postgresql’s Unique Constraint used to group similar values?
Let's say I have a database with columns name, last_name and type They're all strings, though type can only be TYPE_A, TYPE_B or TYPE_C I want a row in the database to be Unique when name and last_name already exist.…
Why doesn't get_json_object() work to extract a value from JSON stored in a Hive SQL table?
In my hive table T the field A is a string in json format and A stores a value of {"c_e_i":"{"e_c_f":1}"}. I want to get c_e_i."e_c_f" So I used get_json_object(T.A, '$.c_e_i.\"e_c_f\"') but it doesn't work. what should I do? I…
Can Postgresql merge users w/ multiple refs & count their collective assets?
There is a set of users. A person can have multiple users, but ref1 and ref2 might be alike and can therefore link users together. ref1 and ref2 does not overlap, one value in ref1 does not exist in ref2.…
Can jsonb_array_elements be used in SQLAlchemy’s select_from and join?
I am using SQLAlchemy ORM and trying to figure out how to produce a PostgreSQL query like the following: SELECT resources.* FROM histories, jsonb_array_elements_text(histories.reported_resources) as report_resource_name JOIN resources ON resources.resource_name = report_resource_name WHERE histories.id = :id So far I got…
Postgresql – why references not working when using dbschema tool?
I use DBSCHEMA tool and want to execute a query but I got this error: syntax error at or near "id" position 209 Code: CREATE TABLE user_info ( id UUID PRIMARY KEY, full_name VARCHAR NOT NULL, birthday VARCHAR NOT NULL,…