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
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:
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
. See:
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:
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.
COPY
on the db server with privileged DB roleMove your data to a separate file on the server, like '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';
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.)
\copy
on any clientRead a file local to the client with the psql meta-command \copy
. See: