search

anthonydb / practical-sql

Code and Data for the First Edition of "Practical SQL" by Anthony DeBarros, published by No Starch Press (2018).
Other
650 stars 399 forks source link

regexp_matches queries returning no data #4

Open jberkus opened 6 years ago

jberkus commented 6 years ago

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 commented 6 years ago

Sounds good -- thanks.

anthonydb commented 6 years ago

@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 commented 6 years ago

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 commented 6 years ago

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

anthonydb commented 6 years ago

@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 commented 6 years ago

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