New answers tagged select
COALESCE will let you get away with a lot of interesting things when you're dealing with the situation of "if it's null do one thing, otherwise do something else".. SELECT c.id, CASE WHEN COALESCE(bv.affidavit, '') = '' THEN FALSE ELSE TRUE END AS emptyish FROM component c JOIN base_vee bv ON c.id = bv.component_id WHERE c.id IN (SELECT component_id FROM ...
Use the CASE function: SELECT c.id, CASE WHEN COALESCE(bv.affidavit, '') = '' THEN False ELSE True END FROM component c, base_vee bv WHERE c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199) AND c.id = bv.component_id;
You have almost 40000 rows where word='Facebook*'. I suppose that these rows are somewhat randomly scattered on the whole table. This means that Postgres has to read tens of thousands of random blocks from a disk, which might be slow. You can try to cluster rows that have the same word together. You can use the command cluster misspelled using test_scanid;. ...
Since the number of distinct job_id is small, you can try this way of writing this type of queries. I've often used in MySQL that doesn't have window functions - and you can't even have a subquery with a IN (SELECT ... LIMIT x). Try this with an index on (job_id, run_number, status): SELECT jr.* FROM ( SELECT DISTINCT job_id FROM job_runs ...
Top 50 recent answers are included