In PostgreSQL, how to insert data with COPY command?
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
Solution 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 rolespg_read_server_files
,pg_write_server_files
, orpg_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
Solution 2:
First step is to create the Database belgianbeers
on pgAdmin.
Then open prompt and run psql -U postgres -d belgianbeers -a -f beers.sql
This command line running e update database tables.
Note: -U postgres
= specifies postgres
as the username