How to combine the LIKE function with a DATE_PART function in PostgreSQL?

Question

Using Postgres, but if someone knows how to do this in standard SQL that would be a great start. I am joining to a table via a character varying column. This column contains values such as:

PC11941.2004
PC14151.2004
PC21213.2003
SPC21434.2003
PC17715.04V1
PC18733.2002
0MRACCT_ALL.GLFUNCT

A lot of the numbers after the periods correspond to years. I want to join the table via the current year. So, for example, I could JOIN on the condition LIKE '%2015'. But I want to create this view and never return to it so I would need to join it against something like (get_fy_part('YEAR', clock_timestamp()).

Not sure how I go about writing that. I haven't had success, yet.

Answer 1

You can get the current year with date_part('year', CURRENT_DATE)

Something like this should work:

SELECT * FROM mytable WHERE mycolumn LIKE ('%' || date_part('year', CURRENT_DATE))

The || operator concatenates the percent-sign with the year.

I hope that helps!

Answer 2

Use the function RIGHT().

SELECT originalColumn, RIGHT(originalColumn,4)
FROM table;

This will get you the years you are interested in.

If you want everything after the dot, then something like:

SELECT originalColumn, RIGHT(originalColumn,len(originalColumn)-position('.' in originalColumn))
FROM table

Answer 3

Depends on the exact rules - and actually implemented CHECK constraints for the column.

If there is always a single dot in your column col and all your years have 4 digits:

Basic solution

SELECT * FROM tbl
WHERE  col LIKE to_char(now(), '"%."YYYY');

Why?

It's most efficient to compare to the same data type. Since the column is a character type (varchar), rather use to_char() (returns text, which is effectively the same as varchar) than EXTRACT or date_part() (return double precision).

More importantly, this expression is sargable. That's generally cheapest and allows (optional) index support. In your case, a trigram index would work:

• PostgreSQL LIKE query performance variations

Optimize

If you want to be as fast (read performance) and accurate as possible, and your table has more than a trivial number of rows, go with a specialized partial expression index:

CRATE INDEX tbl_year_idx ON tbl (cast(right(col, 4) AS int) DESC)
WHERE col ~ '\.\d{4}$';  -- ends with a dot and 4 digits

Matching query:

SELECT * FROM tbl
WHERE  col ~ '\.\d{4}$'  -- repeat index condition
AND    right(col, 4)::int = EXTRACT(year FROM col);

Test performance with EXPLAIN ANALYZE.

You could even go one step further and tailor the index for the current year:

CRATE INDEX tbl_year2015_idx ON tbl (tbl_id)  -- any (useful?) column
WHERE col LIKE '%.2015';

Works with the first "basic" query.
You would have to (re-)create the index for each year. A simple solution would be to create indexes for a couple of years ahead and append another one each year automatically ...

This is also the point where you consider the alternative: store the year as redundant integer column in your table and simplify the rest.
That's what I would do.