New answers tagged

1

Because both your queries return the same number (and type) of fields, the can be combinied with UNION ALL. The ORDER BY is only needed once at then end of the query. To see the difference between indoor and outdoor this could be added to your query. SELECT 'out' as door,DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_out_temp FROM ...


0

Mysql has no full out join, this is why it hast to be simulated. So iz os quit ugly. SELECT IFNULL(ti.time,tou.time) time,ti.avg_in_temp,tou.avg_out_temp FROM ((SELECT DATE_FORMAT(`timestamp`, '%Y-%m-%d %H') timecon ,MIN(DATE_FORMAT(`timestamp`, '%H:00')) AS time , round(avg(temp), 1) as avg_in_temp FROM indoor WHERE `timestamp` >= now() - INTERVAL 1 ...


0

One way you can achieve this is via federation. In short words, if you want to access db_b from db_a, you catalog db_b in instance of db_a. You then create nicknames in db_a for the tables in db_b that you want to access. The nicknames can with some restrictions be treated as tables, so you can join a table in db_a with a table in db_b.


1

SELECT DISTINCT `int`, SUBSTRING_INDEX(SUBSTRING_INDEX(`set_2`, ',', `mask`), ',', -1) `set` FROM `test_set`, -- generate masks based on SET values list count (SELECT 1 `mask` UNION SELECT 2 UNION SELECT 3) `mask` WHERE `set_2` & 1; fiddle If server version allows build mask in recursive CTE (dynamic count may be used)


1

FROM ... JOIN stations AS subways ... This is called an alias. You aliased stations as subways. So for the rest of the query (ON, SELECT part) the table is called subways and you can only reference it as subways.


2

This issue is already logged @ https://redmine.postgresql.org/projects/pgadmin4. This issue is introduced in psycopg2 library and it's been reported.


5

Never seen a negative OID before. This is a first-class "crime scene"! ... attrelid=-1519044407::oid ... ... rel.oid = -1519044407::oid ... Facts 0. 2^32 - 1519044407 = 2775922889 And we have verified that OID 2775922889 indeed exists in your DB. Tests: SELECT * FROM pg_class WHERE oid = 2775922889; SELECT * FROM pg_class WHERE oid = '-1519044407'; ...


0

You need one row per product. So GROUP BY needed. And the query will look like SELECT product_id, (expression for availability) FROM sourcetable GROUP BY product_id You have 3 general variants for the amount - 0, 1 or >1 (if some record is absent we must assume its amount value is 0). For to formalize we will substract 1 and get the sign (it will compact ...


0

This will work in MySQL regardless of the character set. SELECT 'test' REGEXP BINARY 'TEST' AS RESULT; Putting 'BINARY' forces a binary comparison.


Top 50 recent answers are included