In PostgreSQL, how to insert data with COPY command?

by 
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. See:

2. Use psql as privileged system user

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. COPY on the db server with privileged DB role

Move 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 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. \copy on any client

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

More Related Contents:

  1. How to import CSV file data into a PostgreSQL table
  2. How to export table as CSV with headings on Postgresql?
  3. docker postgres pgadmin local connection
  4. Unable to connect PostgreSQL to remote database using pgAdmin
  5. pgAdmin III Why query results are shortened?
  6. How should I import data from CSV into a Postgres table using pgAdmin 3?
  7. ERROR: could not stat file “XX.csv”: Unknown error
  8. PostgreSQL: export resulting data from SQL query to Excel/CSV
  9. Why the “Group BY” by month didn’t work? [closed]
  10. Truncating all tables in a Postgres database
  11. How can I drop all the tables in a PostgreSQL database?
  12. How to change PostgreSQL user password?
  13. How to get the count of current month Sunday’s in psql?
  14. Restore a postgres backup file using the command line?
  15. How can I send email from PostgreSQL trigger?
  16. Running PostgreSQL in memory only
  17. Refresh a materialized view automatically using a rule or notify
  18. PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL
  19. How do I login and authenticate to Postgresql after a fresh install?
  20. Insert if not exists, else return id in postgresql
  21. PostgreSQL ERROR: canceling statement due to conflict with recovery
  22. Change type of varchar field to integer: “cannot be cast automatically to type integer”
  23. eliminate duplicate array values in postgres
  24. Cursor based records in PostgreSQL
  25. How to add column if not exists on PostgreSQL?
  26. Docker – Postgres and pgAdmin 4 : Connection refused
  27. Hashing a String to a Numeric Value in PostgreSQL
  28. Spring Docker container cannot access Postgres Docker container
  29. Format specifier for integer variables in format() for EXECUTE?
  30. PostgreSQL composite primary key

Leave a Comment