Question 211378 in PostgreSQL Question posted in
The official documentation can be found here.

How to rewrite a sql for postgresql

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

Any suggestions how they should be?

Thank you.

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

Any suggestions how they should be?

Thank you.

Tags:

2

Answers


  1. 0 
    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
    Login or Signup to reply.
  2. 0

    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 (Seach 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".

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search