CODEWITHSUNDEEP
sqlpostgresql
Ahmad
Ahmad

Reputation: 4254

PostgreSQL interval error

I run a query like this:

SELECT (SELECT expire_date FROM "License" WHERE license_id = 10)
      - interval '1 mon 133 days 22:19:49.191748'

And everything is well.
But when I try this:

SELECT (SELECT expire_date FROM "License" WHERE license_id = 10)
      - interval ((SELECT expire_date-now() FROM "License" WHERE license_id = 10)
                 + interval '1 months')

I get an error:

ERROR:  syntax error at or near "("
LINE 1: ...FROM "License" WHERE license_id = 10) - interval ((SELECT ex...
                                                             ^

Please help, thanks.

Upvotes: 3

Views: 1672

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656231

You can simplify to:

SELECT expire_date - (expire_date - now()) - interval '1 month'
FROM   "License"
WHERE  license_id = 10

This is valid without additional brackets because subtractions are evaluated from left to right.
In my first version the one necessary pair of brackets was missing, though.

This form of the query also prevents an error in the case that license should not be unique. You would get multiple rows instead. In that case, and if you should need it, add LIMIT 1 guarantee a single value.

@Milen diagnosed the cause of the error correctly. Experiment with these statements to see:

SELECT interval (interval '1 month');   -- error
SELECT (interval '1 month')::interval;  -- no error

However, the cure is simpler. Just don't add another cast, it is redundant.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95512

I think your second query is equivalent to this one. I could be wrong.

SELECT (SELECT expire_date 
        FROM "License" WHERE license_id = 10) 

     - (SELECT expire_date - now() + interval '1 months' 
        FROM "License" WHERE license_id = 10);

Upvotes: 1

Florin Ghita
Florin Ghita

Reputation: 17643

Your first query is equivalent to:

SELECT expire_date - interval '1 mon 133 days 22:19:49.191748' as expire_date
FROM "License" 
WHERE license_id = 10

Second(though probably won't run):

SELECT expire_date - (expire_date-now() + interval '1 months') 
FROM "License" 
WHERE license_id = 10

So, better you write:

SELECT now() - interval '1 months'  
FROM "License" 
WHERE license_id = 10

Upvotes: 1

Milen A. Radev
Milen A. Radev

Reputation: 62563

My understanding is that Postgres allows the type 'string' syntax for specifying the type for simple literal constants. For anything else you have to use ...::type or CAST ( ... AS type ).

Upvotes: 2

Related Questions