Merging Two Queries with Postgres SQL: A Step-by-Step Guide to Combining Identical Results Using Common Table Expressions (CTEs).

Merging Two Queries with Postgres SQL

This article will delve into a common problem that developers face when querying databases, specifically Postgres SQL. We’ll explore how to merge two queries that produce identical results but differ in their conditions.

Understanding the Problem

The provided Stack Overflow question presents a scenario where two queries are used to retrieve data from a Jira database. Both queries fetch data related to ticket transitions between certain statuses. The issue at hand is to combine these two queries into a single one, eliminating the need for manually merging or joining the results.

Query 1 and Query 2

Let’s examine the two queries:

Query 1:

SELECT distinct on (i.issuenum) issuenum, to_char(cg.created, 'DD-MON-YYYY') as datemoved, ci.oldvalue, ci.newvalue 
from changeitem ci 
inner join changegroup cg on cg.id = ci.groupid 
inner join jiraissue i on i.id = cg.issueid 
inner join issuestatus s on s.id = i.issuestatus 
inner join project p on p.id = i.project 
where ci.oldvalue in ('10400') and ci.newvalue in ('10904', '10504', '3', '10000') and project = 11009 and s.id = '10000'
order by i.issuenum asc;

Query 2:

SELECT distinct on (i.issuenum) issuenum, to_char(cg.created, 'DD-MON-YYYY') as datemoved, ci.oldvalue, ci.newvalue 
from changeitem ci 
inner join changegroup cg on cg.id = ci.groupid 
inner join jiraissue i on i.id = cg.issueid 
inner join issuestatus s on s.id = i.issuestatus 
inner join project p on p.id = i.project 
where ci.oldvalue in ('10400') and ci.newvalue in ('10904', '10504', '3', '10000') and project = 11009 and s.id = '10000'
order by i.issuenum asc;

As the author of the Stack Overflow question notes, both queries are very similar but have different conditions.

Merging the Queries

To merge these two queries into a single one, we’ll utilize Common Table Expressions (CTEs) in Postgres SQL. A CTE is a temporary result set that’s defined within a SELECT, INSERT, UPDATE, or DELETE statement. It can be thought of as a view within a view.

Merging the Queries with CTEs

Here’s how you can merge the two queries using CTEs:

WITH q1 AS (
  SELECT distinct on (i.issuenum) issuenum, to_char(cg.created, 'DD-MON-YYYY') as datemoved, ci.oldvalue, ci.newvalue 
  from changeitem ci 
    join changegroup cg on cg.id = ci.groupid 
    join jiraissue i on i.id = cg.issueid 
    join issuestatus s on s.id = i.issuestatus 
    join project p on p.id = i.project
   where ci.oldvalue in ('10400') and ci.newvalue in ('10904', '10504', '3', '10000') and project = 11009 and s.id = '10000'
   order by i.issuenum asc
),
q2 AS (
  -- This is just a copy of your first query, as an example of the form
  SELECT distinct on (i.issuenum) issuenum, to_char(cg.created, 'DD-MON-YYYY') as datemoved, ci.oldvalue, ci.newvalue 
  from changeitem ci 
    join changegroup cg on cg.id = ci.groupid 
    join jiraissue i on i.id = cg.issueid 
    join issuestatus s on s.id = i.issuestatus 
    join project p on p.id = i.project
   where ci.oldvalue in ('10400') and ci.newvalue in ('10904', '10504', '3', '10000') and project = 11009 and s.id = '10000'
   order by i.issuenum asc
)
SELECT q1.* , q2.*
FROM q1, q2 
WHERE q1.issuenum = q2.issuenum;

This will give us the final merged query.

Additional Considerations

When working with CTEs in Postgres SQL, it’s essential to understand that they can be used to improve readability and performance in queries.

One important consideration is the use of distinct on when selecting from a table. This clause ensures that only unique rows are returned based on the specified column.

Another crucial aspect is how we join or combine our CTEs. In this case, we’re simply joining two identical CTEs (q1 and q2) to produce the final merged result.

Finally, note the use of WHERE q1.issuenum = q2.issuenum;. This ensures that only rows with matching issuenum values are included in our final result set.

Conclusion

In this article, we’ve explored how to merge two queries in Postgres SQL using Common Table Expressions (CTEs). We’ve examined the problem presented by the Stack Overflow question and provided a solution to combine the queries into a single one. By utilizing CTEs effectively, developers can simplify their queries and improve performance.

Additional Resources

Postgres SQL documentation: https://www.postgresql.org/docs/current/
Common Table Expressions (CTEs) in PostgreSQL: https://www.postgresql.org/docs/current/sql-with.html
Postgres-SQL join operators: https://www.postgresql.org/docs/current/queries-join.html

Last modified on 2025-01-26