56

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • This kind of error depends on the type of database framework that you are using. You should include such detail in your question. – vitaly-t Aug 28 '15 at 14:02
  • @vitaly-t, I was update gif file method create 1 database and execute file beers.sql but have error. –  Aug 28 '15 at 14:17
  • You seem to have mislabeled your question with node.js and javascript labels, that do not pertain to your question. That's the reason I asked for clarification. – vitaly-t Aug 28 '15 at 15:40
  • 1
    Possible [duplicate](http://stackoverflow.com/a/18737107/1216680) OR you can restore your plain backup (that containes copy from stdin) using psql like shown [here](http://stackoverflow.com/a/22221929/1216680) – Houari Aug 28 '15 at 15:53
  • The animated gif turned out to be helpful. – Erwin Brandstetter Aug 28 '15 at 20:34

3 Answers3

86
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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
6

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

henrycarteruk
  • 12,708
  • 2
  • 36
  • 40
Claudio
  • 121
  • 2
  • 4
1

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%.

PatrickJD
  • 11
  • 2