I just started using Postgres and I'm trying to create a sample db to understand its functions, looking around, I found some scripts in pgfoundry.org. I understand the commands, since I previously used both Oracle and MS-SQL, but all the scripts I'm running return errors when they reach the "COPY FROM" instruction. More precisely, the error is thrown at the first element that should be inserted in the given table.

I've tried running the scripts both as queries and as pgScripts, but in both ways I'm getting an error at the first row after the COPY FROM.

I'm using pgAdminIII and I used StackBuilder to install PostgreSQL as a DB Driver. May I be missing some basic configuration that's preventing me from running this command, or I just did not understand they way it works?

The error is:

ERROR:  syntax error at or near "7"
LINE 5600: 7 4 13 37 2012-03-10 16:41:43.797787 2012-03-10 16:41:43.797...

********** Error **********

ERROR: syntax error at or near "7"
SQL status: 42601
Char: 140891`

where the text is:

COPY action_abilitations (id, group_action_id, partecipation_role_id, group_id, created_at, updated_at) FROM stdin;
7   4   13  37  2012-03-10 16:41:43.797787  2012-03-10 16:41:43.797787`
  • 1
    Welcome to dba.SE. You need to include the (verbatim) error messsage with a question like that. If your locale is not non-English, you can reset it in your session before you run the script to get default English error messages: SET lc_messages = C Just run it in your SQL editor window, which "contains" a session. – Erwin Brandstetter Apr 26 '13 at 18:51
  • Thanks, I'm trying to figure out how to change my locale, I'll include the error message as soon as I figure out how to do that. – Eugenio Laghi Apr 26 '13 at 19:05
  • 1
    Oh, I forgot single quotes: SET lc_messages = 'C'. – Erwin Brandstetter Apr 26 '13 at 19:20

pgScript is a local script extension of pgAdmin, which you most probably do not want here.

pgAdmin is a GUI, not a console application - there is no stdin you could easily use. If you need stdin to stream your content, use psql, which is a console application - with the \copy meta-command of psql.

If you have a file (which you obviously do), just use SQL COPY from pgAdmin:

COPY action_abilitations (id, group_action_id, ...)
FROM 'C:\Users\usernexus\Desktop\database05-12-2012.sql';

The file needs to be readable to the postgres system user.

More info in this closely related request to the pgAdmin support list.

  • ok, do you mean I should run the script with a line like: psql postgres -p 5432 -f C:\Users\usernexus\Desktop\database05-12-2012.sql? Also, I'm just trying everything locally, I just installed pgAdmin on my laptop. – Eugenio Laghi Apr 26 '13 at 19:06
  • 1
    @EugenioLaghi: Not exactly. psql -f would execute an SQL script file. You seem to deal with a data file. I updated my answer. – Erwin Brandstetter Apr 26 '13 at 20:21
  • THANKS! At the beginning I was trying to execute a script, but now at least I figured out how to COPY data from a file! Yesterday evening I was just too tired to understand.. :) – Eugenio Laghi Apr 27 '13 at 10:42
  • How do you I make sure that the postgres user can read the file. I made that happen for the txt file, even for my desktop (where the file is located), but the permission is still denied. – user29363 Oct 14 '13 at 13:31
  • @Geo: Please start a new question including Postgres version, OS, where the file lies, the verbatim command you used and more if relevant ... – Erwin Brandstetter Oct 14 '13 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.