PostgreSQL copy statement

Question

I have table in my database

CREATE TABLE user_user (
    id bigint NOT NULL,
    creationdate timestamp without time zone,
    externalid character varying(255),
    installdate timestamp without time zone,
    lastlogindate timestamp without time zone,
    password character varying(255),
    status character varying(255),
    username character varying(255) NOT NULL,
    usertype character varying(255),
    information_fk bigint,
    operator_id bigint,
    loginip character varying(255),
    logged boolean,
    bonuspercent integer DEFAULT 20,
    factor integer DEFAULT 100,
    givefree boolean,
    flashfile character varying(255),
    useraccess integer DEFAULT 0 NOT NULL,
    reportsaccess integer DEFAULT 0 NOT NULL,
    gamesaccess integer DEFAULT 0 NOT NULL,
    transactionsaccess integer DEFAULT 0 NOT NULL
);

Now I need to feed it with data. I have query but it doesn't work. What's wrong? SQL Editor says ERROR: syntax error (the approximate position of "51")

COPY user_user (id, creationdate, externalid, installdate, lastlogindate, password, status, username, usertype, information_fk, operator_id, loginip, logged, bonuspercent, factor, givefree, flashfile, useraccess, reportsaccess, gamesaccess, transactionsaccess) FROM stdin;
51  2011-12-16 15:30:57.901 \N  \N  2013-01-19 09:10:02.789 101918  ENABLED ceco    USER    51  50  \N  f   20  100 f   localhost   0   0   0   0
53  2011-12-16 15:30:57.989 \N  \N  2012-08-17 14:46:32.052 101918  ENABLED kostas  USER    53  50  \N  f   20  100 f   localhost   0   0   0   0
1650    \N  \N  \N  2012-08-08 10:57:07.79  101918  ENABLED lubo    USER    1600    50  \N  f   20  100 f   localhost   0   0   0   0
52  2011-12-16 15:30:57.943 \N  \N  2012-08-19 12:00:42.198 101918  ENABLED vania   USER    52  50  \N  f   20  100 f   localhost   0   0   0   0
10  \N  \N  \N  \N  22222   ENABLED root    GROUP   10  \N  \N  f   20  100 f   localhost   0   0   0   0
21  \N  \N  \N  2012-03-23 06:33:23.38  karakondjoli    ENABLED rootadmin22 ADMIN   21  20  \N  f   20  100 f   localhost   0   0   0   0
20  \N  \N  \N  \N  dsjadsghjghjghsag   ENABLED rootaf  GROUP   20  \N  \N  f   20  100 f   localhost   0   0   0   0
11  \N  \N  \N  2013-01-17 08:52:07.279 101918  ENABLED rootadmin   ADMIN   11  10  127.0.0.1   f   20  100 f   localhost   15  0   0   0
50  2011-12-16 15:30:57.825 \N  \N  2013-01-18 08:43:54.433 opera   ENABLED operator    OPERATOR    50  10  \N  f   0   100 f   192.168.1.103   0   0   0   0
\.

I guess your "SQL Editor" (whichever that is) does not support COPY .. FROM stdin. It sees the statement termination character (;) after the copy and then sends the next lines as a separate statement. — user330315
– user330315, Commented Jan 20, 2013 at 19:02
But how fill data properly? I took this from PostgreSQL database dump — bossman
– bossman, Commented Jan 20, 2013 at 19:05

Roberto · Accepted Answer · 2013-01-20 19:23:08Z

2

Perhaps you are using the copy command from pgAdmin or another editor, use the command line like this:

psql -d yournewdatabase -U username -f yourbackupfile

in windows you must go to c:\Program files (x86)\Postgres\8.3\bin to issue the command (replace 8.3 with the actual version of postgresql you are using)

edited Jan 20, 2013 at 19:23

answered Jan 20, 2013 at 19:16

Roberto

2,18525 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bossman Over a year ago

I dont have access to my old database. I have got onlu that dump file

Roberto Over a year ago

@bossman after -d goes your new database name, not the old one, I stated it more clearly now.

Collectives™ on Stack Overflow

PostgreSQL copy statement

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related