Chapter 4 Exporting

[anthonysim]

When I try Listing 4-8 on page 52

COPY us_counties_2010
TO 'C:\us_counties_export.txt'
WITH (FORMAT CSV, HEADER, DELIMITER '|')

I get the following error message:
ERROR: could not open file "C:\us_counties_export.txt" for writing: Permission denied
HINT: COPY TO instructs the PostgreSQL server process to write a file. You may want a client-side facility such as psql's \copy. SQL state: 42501

I have no idea how to rectify this situation.

[anthonydb]

Hi, @anthonysim ...

This can happen if PostgreSQL doesn't have rights to the folder where you're either reading or writing a file. I would suggest two things:

1. Create a folder on your C: drive for file output.
2. Follow the directions in the NOTE at the bottom of page xxviii of the book's Introduction that explains how to grant permission to folders in Windows. You may have done this already with the folder that contains the code and date from GitHub.

Please let me know if that solves the issue. Also, let me know which version of Windows you're on.

[anthonysim]

Thanks @anthonydb, I appreciate it! It worked and I am using Windows 10.

[anthonydb]

Glad to hear it. I'll keep this issue open in case others run into the same question.

[PaulusJS]

Hi @anthonydb

I am having a similar issue concerning but with the import of the CSV file (see error message below).

I am trying to import the file from a new folder I created on my Mac desktop for which I changed the permissions to write & read (although permission to read should be sufficient) and I did the same for the CSV file itself (read & write).

No matter what I try I still get the same error message:

ERROR: could not open file "/namepath/us_counties_2010.csv" for reading: Permission denied
HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.
SQL state: 42501

[anthonydb]

Hi, @PaulusJS,

Unusual for a Mac. How did you install PostgreSQL?

[crawfordniche]

Is it just me, or are area_land in the CSV floats whaereas area_land is data type bigint? Perhaps that's for didactic purpose and I just need to keep reading?

eg. line 72 'bethel..' and line 86 'north slope'

Well, I got it copied. I must have let Excel get it's hooks into the numbers. I didn't realize maintaining pure csv in excel was still fraught with danger. Dragged it straight from the directory to the raspberry pi and it worked perfectly. Kind regards.

[anthonydb]

@crawfordniche
Hi -- yes, those area_land values are indeed bigint in the CSV here in the repo. Opening CSVs with Excel can cause issues as Excel likes to make assumptions about data types.

After the first printing of the book, we added a note on page xxviii in the Introduction that says:

The repository also contains the public data you’ll use in the exercises. Avoid opening or changing these files with Excel or a text editor, which can lead to data loss or encoding changes. I explain why this is problematic in “Checking for Malformed Values Using length()” on page 135.

[ayangupta-dotcom]

When I try Listing 4-8 on page 52

COPY us_counties_2010 TO 'C:\us_counties_export.txt' WITH (FORMAT CSV, HEADER, DELIMITER '|')

I get the following error message: ERROR: could not open file "C:\us_counties_export.txt" for writing: Permission denied HINT: COPY TO instructs the PostgreSQL server process to write a file. You may want a client-side facility such as psql's \copy. SQL state: 42501

I have no idea how to rectify this situation.
Hey have you rectify this situation ?

[anthonydb]

@ayangupta-dotcom Please take a look at my earlier responses in this ticket or to my answer on #32