Tag Info

New answers tagged

1

1st case You seem to forget the valid_during range. As your third case suggests, there can be multiple entries per (rec_id, val), so you must select the right one: UPDATE master m SET valid_on = f_array_sort(m.valid_on || u.valid_on) -- sorted array, see below FROM updates u WHERE m.rec_id = u.rec_id AND m.valid_during @> u.valid_on -- ...


0

so after some more thought and reading some of the posts i broke up my query into two parts, i think that was the main confusion because there were so many moving parts I created a view that performs the recursion on the target table (see below), thereby removing the need for the other columns I used this table in my 60 column query. Here is the ...


0

Looks like it can be radically simplified with plain joins to a subquery: SELECT u.name AS u_name, c.name AS c_name FROM user_table u JOIN (SELECT u.id, c.id AS content_id FROM ... UNION -- fold duplicates SELECT u.id, c.id AS content_id FROM ... ) uc USING (id) JOIN content_table c ON c.id = uc.content_id If there cannot be ...


1

There are several ways to rewrite the query, even with keeping the CTEs. But please consider that CTEs in Postgres are pre-evaluated, so they cause efficiency problems. first, the LEFT join is equivalent to an inner JOIN, as you have a column from the right-side table in the following WHERE clause. second, the FROM user_table LEFT OUTER JOIN content_table ...


0

Although I prefer a language where I can work with common table expression (cte) and with LATERAL where I can directly use fields from the first from-statement (I'm assuming right that that is not an option?), the answer ypercube provided works for me in the project I'm currently working on. Thanks so much.


2

Without table structure, it's not easy to write code but here is a simplified query: SELECT c.*, i.*, r.max_date FROM customer AS c LEFT JOIN -- or just JOIN ( SELECT customernumber, MAX(revenue_date) AS max_date FROM revenue GROUP BY customernumber ) AS r ON ...



Top 50 recent answers are included