regexp_matches queries returning no data

[jberkus]

Taking this issue up based on chapter notes.

SELECT crime_id,
       regexp_matches(original_text, '-\d{1,2}\/\d{1,2}\/\d{2}')
FROM crime_reports;

Returned no data when I ran it, and checking the underlying data it was correct not to return any data.

More details to follow.

[anthonydb]

Sounds good -- thanks.

[anthonydb]

@jberkus Please note that during the chapter revisions I switched to the new PostgeSQL 10 function regexp_match() in these examples. The code in question is at:

https://github.com/anthonydb/practical-sql/blob/master/Chapter_13/Chapter_13.sql#L88

[jberkus]

OK, figured this out. Every other import you do has a header (WITH ( FORMAT CSV, HEADER ON)), and as a result I imported this with HEADER ON instead of HEADER OFF. This stripped off the first row, which is the only row with two dates.

[jberkus]

Are you sure you want to use a Postgres 10 only function?

[anthonydb]

@jberkus Whew ... glad it was something simple and not a mysterious hidden character issue.

I do realize that relying on a PostgreSQL 10 function may pose an issue for a reader who'll need to do work on 9.x or earlier. I think a note in the chapter on using regexp_matches() in that case would be helpful, so I can add that.

Thanks again for this and for all your help on this project!

[jberkus]

You can also offer up the regexp_matches versions of the queries on this git repo.