COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). COPY TO can also copy the results of a SELECT query.
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:
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:
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:
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
My Resolution: Put the SQL file in your root directory, e.g. C:\
.
For example my database is called cities
and my SQL file is cidade.sql
.
Open cmd
inside the BIN folder in your PostgreSQL directory and type:
psql -U postgres -d cities < C:\cidade.sql
The arguments to psql
are:
-U [user]
-d [database]
You will be prompted for your password. Do not forget to check if psql
is in your environment variables %PATH%
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With