In PostgreSQL, how to insert data using the COPY command?

Question

I have a problem starting 1 NodeJs project with a PostgreSQL database. I have an error while trying to insert data into pgAdmin using the COPY .

 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 the histogram :

This error:

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

Answer 1

 COPY tbl FROM STDIN ; 

pgAdmin is not supported.
You get a simple syntax error because Postgres receives the data as SQL code.

Four possible solutions:

1. Instead, use a multi-row INSERT :

 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 string or numeric literal values.

You can generate data using pg_dump using --inserts . Connected with:

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

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

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

The database ( -d ) and login role ( -U for "User") can be omitted if everything is ok by default. Syntax Examples:

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

Make sure that the default text format is the end of the -d ( \. ) Marker. (You have it.) Manual:

The end of the data can be represented as a single line containing only a backslash ( \. ). The end of the -d ata marker is not required when reading from a file, since the end of the file works fine; this is only necessary when copying data to or from client applications using the client protocol up to version 3.0.

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 anyway. You need superuser privileges. Leadership:

[...] COPY of file or COPY names is allowed only to superusers or database 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 you to read or write any file or run a program, pg_write_server_files on the server. privileges for access.

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

4. Or read the local file for the client using the psql \copy meta-command . See:

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

Answer 2

First step:

create belgianbeers database on pgAdmin.

Second step: Open a prompt and run this command line:

psql -U postgres -d belgianbeers -a -f beers.sql

The update tables e update are launched on this command line.

-U = postgres username