How to rewrite SQL for PostgreSQL

Question

I'm trying to rewrite two pieces of SQL Server code to PostgreSQL.

The 1st one is:

WHERE
    (DATEADD(S, Submit_Date, '1970-01-01')) >= DATEADD(d, -3, GETDATE())
    OR
    (DATEADD(S, Last_Changed_Date, '1970-01-01')) >= DATEADD(d, -3, GETDATE())

I keep getting an error regarding the S after the DATEADD.

The 2nd one is this:

WHERE (dbo.Test.[Log] LIKE '%%Status: Closed%%')
GROUP BY dbo.PYR.ID_Number

I need help for the mentioned SQL Server code lines to be modified for PostreSQL.

Any suggestions how they should be?

Thank you.

https://www.sqlines.com/postgresql/how-to/dateadd should help you solve the first issue — siggemannen, Jul 19 '23 at 13:49
`where to_timestamp(submit_date) >= current_date - interval '3 days' or to_timestamp(last_changed_date) >= current_date - interval '3 days'` — Stefanov.sm, Jul 19 '23 at 14:23
`where test."Log" ~* 'Status: Closed' group by pyr.id_number` — Stefanov.sm, Jul 19 '23 at 14:26
Only tag the rdbms you want your solution in, and provide a [mre] to explain what the query should do. — Dale K, Jul 19 '23 at 20:14

score 1 · Accepted Answer · answered Jul 19 '23 at 14:08

WHERE
('1970-01-01'::timestamp + interval '1 second' * submit_date)  >= now()+interval '-3 days'
OR
('1970-01-01'::timestamp + interval '1 second' * Last_Changed_date)  >= now()+interval '-3 days'

WHERE  (dbo.Test."Log" ILIKE '%%Status: Closed%%')
GROUP BY dbo.PYR.ID_Number

score 0 · Answer 2 · edited Jul 19 '23 at 20:21

If submit_date and Last_Changed_date each have indexes, then the following is likely to perform better than the original first query:

WHERE submit_date >= extract(epoch FROM now() - interval '3 days')
   OR Last_Changed_date >= now() - interval '3 days')

Avoid operations on columns in the WHERE clause so that the condition will be sargable (Search ARGument ABLE); i.e., the database engine is able to scan an index when one is available instead if sequentially reading the whole table.

I chose to use - interval '3 days' instead of + interval '-3 days' for readability. Subtracting an interval rather than adding a negative interval imposes less cognitive load on the reader, especially when the - is buried in a string somewhat distance from the +.

The second query can be rewritten as follows:

WHERE Test."Log" ILIKE '%%Status: Closed%%'
GROUP BY dbo.PYR.ID_Number

While dbo.Test."Log" also works as the column reference, it is better to only include the schema in the table reference in the FROM clause since this minimizes the number of changes that have to be made to the query if the schema changes.

If this is a frequent query condition and it's a large table, then explore options to index Test."Log".

How to rewrite SQL for PostgreSQL

2 Answers2