Search code examples
postgresqlpgadminbulk-loadpostgresql-copy

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

I was create database like this and execute file .sql:

Solution 
  • 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: