COPY command postgres syntax error

Question

I am trying to export from a large postgres 8.1 table a couple of rows using

copy (select * from tablename limit 100) to 'absolute path to file';

but I get

ERROR:  syntax error at or near "(" at character 6.

Any ideas what might be going wrong ? By the way I am not super user in the database but I believe that would have produced a different kind of error. If you have any other idea to dump a couple of rows from SQL (in a format to insert them easily, without coding) other than using copy I open to suggestions.

Please copy-and-paste the exact command you're using, including the actual table name and file name. — Jonathan Hall
– Jonathan Hall, Commented Nov 28, 2012 at 10:35
You are aware that 8.1 has been deprecated and de-supported for a long time? — user330315
– user330315, Commented Nov 28, 2012 at 11:45

Erwin Brandstetter · Accepted Answer · 2012-11-28 11:39:54Z

3

To overcome the shortcoming of version 8.1 you can create a TEMPORARY TABLE and use it in COPY TO:

CREATE TEMP TABLE t_tmp AS
SELECT * FROM tablename LIMIT 100;

COPY t_tmp TO '/absolute/path/to/file';

Temp. tables are dropped at the end of a session automatically. If you want to keep the connection open you could drop the table explicitly or wrap it in a transaction which you roll back (what's already written to the file is never rolled back.)

BEGIN;
CREATE ...;
COPY ...;
ROLLBACK;

Or you upgrade to a more recent version, which would be a very good idea in general.
PostgreSQL 8.1 has reached end of life in Nov. 2010.

edited Nov 28, 2012 at 11:39

answered Nov 28, 2012 at 11:34

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

user1845360 Over a year ago

Thanks that worked fine. As far as the newer version I know but as a developer I am told to keep using that.

Erwin Brandstetter Over a year ago

@user1845360: You have my sympathies. ;)

Ihor Romanchenko · Accepted Answer · 2012-11-28 11:00:02Z

0

In postgresql 8.1 you cant COPY from SELECT. It was added in 8.2

Look at the differences here http://www.postgresql.org/docs/8.1/static/sql-copy.html and here http://www.postgresql.org/docs/8.2/static/sql-copy.html

answered Nov 28, 2012 at 11:00

Ihor Romanchenko

29k9 gold badges55 silver badges45 bronze badges

Collectives™ on Stack Overflow

COPY command postgres syntax error

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related