Selecting records where date range is current year minus #years

I want to select and eventually delete records from a point in time earlier than current year minus "x" years.

I'm unsure of the most efficient way to do this.

You can use extract() to get the actual year and compare those:

DELETE FROM table_name 
WHERE extract(year from change_date) <= extract(year from current_date) - 5;

If today is 2015 this will delete anything that is in 2010 or older.

Replace the - 5 with the "X" you want to use.

Note that extract(year from change_date) will not make use of an index on change_date itself. If performance is critical for this, you might want to create an index on the expression.

StackExchange Database Administrators Q#108670, answer score: 2