Chapter 4: Try it Yourself #1

[ghost]

Using the following code in pgAdmin4 v3.4:

COPY actor
FROM '/Users/myusername/Desktop/movies.txt'
WITH (FORMAT CSV, HEADER, DELIMITER ':', QUOTE '#');

returns the following error:

ERROR: relation "actor" does not exist 
SQL state: 42P01

My txt file looks like this:

id:movie:actor
50:#Mission: Impossible#:Tom Cruise

Please help on a resolution. TIA!

[anthonydb]

Hello, @ikoval!

For anyone coming to this issue in the future, we're referencing question 1 on page 54 of the book in the "Try it Yourself" section. The text of the question is:

1. Write a WITH statement to include with COPY to handle the import of an
imaginary text file that has a first couple of rows that look like this:

id:movie:actor
50:#Mission: Impossible#:Tom Cruise

Now, this is intended to be a hypothetical exercise. If you received a text file with lines in this format, what settings would you need to include in the WITH statement? Given that it's hypothetical, in the book I did not include code to create a table for actually importing this data -- and that's the reason you get the error when running the code. Your database doesn't contain a table named actor.

That said, it's easy enough to make a table and test the COPY statement. Here's the code for the table:

CREATE TABLE actors (
    id integer,
    movie text,
    actor text    
);

Run that statement in pgAdmin, and then try your import. It should work fine.

Please let me know if that makes sense. I've added this CREATE statement to the Try It Yourself answer in the repo to help others as well.