Chapter 9 (Inspecting and modifying data) sample code of list 9-1 has a minor glitch

[bennybhlin]

Greeting

Hi Anthony, my name is Benny Lin, I ask this question because I am translating your book to traditional Chinese, it will be published in Taiwan in Q2 2019. I found a minor issue which may need to fix your sample code of list 9-1.

I used an old Windows 7 64-bit (where I translated ^^)

I'd followed steps described in Intro chapter to install PostgreSQL and pgAdmin; the only difference I made was I chose Chinese (Traditional)_Taiwan.950 as my locale. Which caused my issue, I would explain how it happened below.

I did download the book's code example from GitHub.

The issue is: from the code of list 9-1, if I used original csv file, there would be error below:

ERROR: date/time field value out of range: "6/27/2016" HINT: Perhaps you need a different "datestyle" setting. CONTEXT: COPY meat_poultry_egg_inspect, line 2, column grant_date: "6/27/2016" SQL state: 22008

It seems that date format caused the error above. Since I am not sure which PostgreSQL function could convert format mm/dd/yyyy to yyyy/mm/dd (though I remember Mysql does), so I tried to amend the csv file in Excel and changed date format to yyyy/mm/dd.

Then I ran COPY again & it went well.

Of course I could try to set datestyle from YMD (coz my local is Chinese) to MDY (with ALTER DATABASE "analysis" SET datestyle TO "ISO, MDY"; then restart posgresql service) so your csv file could work smoothly.

I added a comment in my script to remind local readers about this special case. FYI.

[anthonydb]

Hi, @bennybhlin! Very excited to hear you're translating Practical SQL!

I can look at converting those dates to ISO format -- YYYY-MM-DD. Give me a few days to explore whether that creates any issues.

[bennybhlin]

Hi Anthony

Appreciated. I think it's very probably caused by my local setting. Just want to to mention about such possibility & feasible answer to our local readers if they chose same locale like me.

Regards,
Benny

[bennybhlin]

By the way, I installed version 9.6, for your reference.

[anthonydb]

@bennybhlin

Hi,

I've updated the repo here with a revised version of the file MPI_Directory_by_Establishment_Name.csv that contains dates in ISO 8601 international standard format. That should allow you to import regardless of your locale. Please test and let me know if you have any issues.

Regarding the version of PostgreSQL, please note that the regexp_match() function used in Chapter 13 is found beginning in PostgreSQL 10. I note this in the book's introduction.

Best,
Anthony

[bennybhlin]

Hi Anthony

Appreciate for this update.