2

I dumped my postgres db using pgAdmin in plain format because I wanted to search for some text on it. After doing the search, I decided to import the db back from the sql query window but I get this error

ERROR: syntax error at or near "330" LINE 48882: 330 643 644 1 2012-01-11 16:04:20.468 2012-01-17 22:33:23.99...

This is the part that gives the error. I know I didn't modify anything here. It is what it dumped for me

COPY account_account (id, parent_left, parent_right, create_uid, create_date, write_date, write_uid, code, reconcile, currency_id, user_type, active, name, level, company_id, shortcut, note, parent_id, currency_mode, type) FROM stdin;
330 643 644 1   2012-01-11 16:04:20.468 2012-01-17 22:33:23.993 1   40300010    f   \N  8   t   Rent Income 5   1   \N  \N  329 current other
55  89  94  1   2012-01-05 23:50:08.524 2012-01-10 12:32:51.934 1   12000000    f   \N  18  t   Long Term Investments   3   1   \N  \N  24  current view

Here is the code that creates the table

CREATE TABLE account_account (
    id integer NOT NULL,
    parent_left integer,
    parent_right integer,
    create_uid integer,
    create_date timestamp without time zone,
    write_date timestamp without time zone,
    write_uid integer,
    code character varying(64) NOT NULL,
    reconcile boolean,
    currency_id integer,
    user_type integer NOT NULL,
    active boolean,
    name character varying(128) NOT NULL,
    level integer,
    company_id integer NOT NULL,
    shortcut character varying(12),
    note text,
    parent_id integer,
    currency_mode character varying(16) NOT NULL,
    type character varying(16) NOT NULL
);

I am so confused here. Please what could be the problem

|improve this question
  • I think the empy lines are the problem (you may not have an empty line after the COPY statement and also not between each "data line"). Is it possible your editor messed up the line endings? – a_horse_with_no_name Feb 22 '12 at 9:54
  • Actually there are no line. I am the one that put the <br> tags that made those lines appear. I have removed them now – yankitwizzy Feb 22 '12 at 10:11
  • Have you previous COPY statement ended with \.? – Jan Marek Feb 22 '12 at 12:19
  • @Jan Marek I don't have. That is first COPY statement in the sql query – yankitwizzy Feb 22 '12 at 14:28
  • May be you can dump the data as insert sql instead, just have a try. – francs Feb 23 '12 at 8:02
2

The problem is that you cannot COPY FROM stdin with pgAdmin (no stdin available in the query window), but you can easily restore the dump with psql

psql <database> -h <host> -p <port> -f <dump-file>

Hope that helps.

|improve this answer
  • ok thanks. The thing is that I don't have psql in my postgres installation. Is there a way I can install it as an addon to the existing postgres installation – yankitwizzy Feb 24 '12 at 19:23
  • Sure, what OS are you running on? On Debian it's as simple as apt-get install postgresql-client-9.1 to install the current version of PostgreSQL client programs including psql. – tscho Feb 24 '12 at 19:55
  • I am running Windows 7 64 bits – yankitwizzy Feb 25 '12 at 8:05
  • Simple try to search psql.exe, you will have it in the directory, where is Postgres installed in, in the 'bin' subdirectory. Then try to use it for importing dump, as I or tscho wrote. I've tried to download installation package for Win 64bit and there was psql.exe binary. – Jan Marek Mar 1 '12 at 11:59
  • please note, that on PostgreSQL 9.3 on Windows you should use psql.exe -h <host> -p <port> -f <file> db_name user_name so options first, than db_name and user name – Krystian Aug 19 '14 at 12:03

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.