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 401 forks source link

Chapter 13 regexp error #27

Closed MatMel16 closed 3 years ago

MatMel16 commented 3 years ago

Hi, I'm working on Win 10 I've installed PosgreSQL as described in book I'm using version 4.28 Yes, I did download it from GitHub

When Running Listing 13-9 on page 225, chapter 13, UPDATE crime_reports SET date_1 = ( (regexp_match(original_text, '\d{1,2}\/\d{1,2}\/\d{2}'))[1] || ' ' || (regexp_match(original_text, '\/\d{2}\n(\d{4})'))[1] ||' US/Eastern' )::timestamptz;

pg announces error: ERROR: date/time field value out of range: "4/16/17 2100 US/Eastern" HINT: Perhaps you need a different "datestyle" setting. SQL state: 22008

Code is definitely right, I've copied it and all the previous steps as well

anthonydb commented 3 years ago

Hello @MatMel16,

Would you please run the following command and report the results?

SHOW DateStyle;

Thank you.

MatMel16 commented 3 years ago

Hello,

Sure, the result is: ISO, DMY

anthonydb commented 3 years ago

OK, if you run this command in your session, you should be able to then complete the exercises:

SET DateStyle TO 'ISO, MDY';

The issue is that with a Day/Month/Year format, a date of April 16, 2017, is not valid. Setting your session to Month/Day/Year will allow you to match the input format.

I apologize you had these issues. I clearly need to make some updates to this chapter to account for International users! I will do that.

MatMel16 commented 3 years ago

Thank you for your full support in solving my problems. You might be the only author that takes care of his readers/students the way you are!

anthonydb commented 3 years ago

@MatMel16 You are very welcome. Helping people learn is one of my biggest joys in life.