Adding months to a date in PostgreSQL fails or returns the wrong type
Adding months to a date in PostgreSQL gives a syntax errorShort answer
PostgreSQL has no add_months function. Add an interval instead: date + interval '1 month'. The unit must sit inside the quoted literal. For a variable count, multiply interval '1 month' by the number or use make_interval. Date plus interval returns a timestamp, so cast to date if you need a date.
Fix it fast
1Find out which cause you have
Run the shortest correct form and compare it with your expression.
SELECT date '2001-09-28' + interval '1 month';2Match what you see, then jump to its fix
- 1
Expressions like date + 1 month or date + 1 monthsThe month unit is outside the quotes, or a bare number is addedFix - 2
interval '$1 month' or interval 'n month' with a variable inside the quotesThe month count comes from a column or parameterFix - 3
2001-04-30 00:00:00 where you expected a date, or a day that changedThe result carries a time part, or the end of month movedFix
- 1
What does the error look like?
Adding months to a date in PostgreSQL gives a syntax errorThe result of date + interval shows a time part
Cause 1 of 3
The month unit is outside the quotes, or a bare number is added
1Check
An interval literal is a string such as '1 month' or '1 year 2 months'. Date and time literals must be enclosed in single quotes. A date plus a bare integer adds days, not months.
SELECT date '2001-09-28' + 7;2Fix
Write the quantity and unit together inside the quotes.
SELECT date '2001-09-28' + interval '1 month';
SELECT date '2001-09-28' + interval '1 year 2 months';3Verify
The query returns the shifted date.
SELECT date '2001-09-28' + interval '2 months';Confirmed by postgresql.org
Cause 2 of 3
The month count comes from a column or parameter
1Check
Text inside the quotes is read as written, so a variable name there is not substituted. Multiply an interval by a number, or build one with make_interval.
SELECT interval '1 day' * 21;2Fix
Multiply interval '1 month' by the count, or use make_interval with the months argument.
SELECT start_date + interval '1 month' * months_count FROM subscriptions;
SELECT date '2001-03-31' + make_interval(months => 1);3Verify
The second statement returns 2001-04-30.
SELECT date '2001-03-31' + make_interval(months => 1);Confirmed by postgresql.org
Cause 3 of 3
The result carries a time part, or the end of month moved
1Check
date + interval returns a timestamp. A month addition keeps the day number unless the target month lacks it: March 31 plus 1 month becomes April 30, and March 31 plus 2 months becomes May 31.
SELECT date '2001-03-31' + interval '1 month';
SELECT date '2001-03-31' + interval '2 months';2Fix
Cast the result back to date when you need a date, and plan for the end-of-month rule.
SELECT (date '2001-03-31' + interval '1 month')::date;3Verify
The output shows only the date.
SELECT (date '2001-03-31' + interval '1 month')::date;Confirmed by postgresql.org
How do I stop it coming back?
- Keep the whole interval in one quoted literal.
- Use make_interval when the count comes from data.
- Test month-end dates, for example the 31st, in every date calculation.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix