In PostgreSQL, how to insert data with COPY command?

Question

I have problem when run 1 project NodeJs with PostgreSQL database. I have error when trying to insert data in pgAdmin using the COPY command.

COPY beer (name, tags, alcohol, brewery, id, brewery_id, image) FROM stdin;

Bons Voeux  blonde  9.5 Brasserie Dupont    250 130 generic.png

This data in gist:

This error:

ERROR: syntax error at or near "Bons"
SQL state: 42601
Character: 1967

Answer 1

COPY tbl FROM STDIN;

is not supported by pgAdmin.
You get a plain syntax error because Postgres gets the data as SQL code.

Four possible solutions:

1. Use a multi-row INSERT instead:

INSERT INTO beer(name, tags, alcohol, brewery, id, brewery_id, image)
VALUES 
  ('Bons Voeux', 'blonde', 9.5, 'Brasserie Dupont', 250, 130, 'generic.png')
, ('Boerke Blond', 'blonde', 6.8, 'Brouwerij Angerik', 233, 287 'generic.png')
;

Note the different (SQL) syntax for values as string or numeric literals.

You can generate the data with pg_dump using --inserts. Related:

• Export specific rows from a PostgreSQL table as INSERT SQL script

2. Or call your script on the command line using psql. As system user postgres:

psql -f beer.sql -U my_login_role -d db_name 

Database (-d) and login role (-U for "User") can be omitted if defaults are ok. Syntax examples:

• Create Postgres database using batch file with [template],[encoding],[owner] and a .sql file

Be sure there is an end-of-data marker (\.) for default text format. (You have that.) The manual:

End of data can be represented by a single line containing just backslash-period (\.). An end-of-data marker is not necessary when reading from a file, since the end of file serves perfectly well; it is needed only when copying data to or from client applications using pre-3.0 client protocol.

3. Or move your data to a separate file on the server, say 'beer_data.csv' and use COPY .. FROM 'filename' in your script:

COPY beer (name, tags, alcohol, brewery, id, brewery_id, image)
FROM '/path/to/beer_data.csv';

Which works either way. You need superuser privileges, though. The manual:

[...] COPY naming a file or command is only allowed to database superusers or users who are granted one of the default roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program, since it allows reading or writing any file or running a program that the server has privileges to access.

(pg_read_server_files, pg_write_server_files and pg_execute_server_program are new in Postgres 11.)

4. Or read a file local to the client with the psql meta-command \copy. See:

• How to update selected rows with values from a CSV file in Postgres?
• How to use \copy in postgresql with pgadmin4