RE: problems with copy command

Hi Paolo:

Just went thru this exercise moving some 40 tables comprising more than 100k
records from Win95 Sybase SQL Studio to Postgresql. I wanted the same data
structures in Postgresql that I had in Sybase.
Overview:
Using PostgreSQL 6.5.3 in SuSE 6.4
You must be a Postgresql user with create table privileges. If you log in as
'postgres' you can set these users up in pgaccess.
Used the Sybase unload to dump structures and data to their 'reload.sql' and
data files. The 'create table' commands required extensive editing because of
quote marks and other Sybase things. Used kpsql to create the tables.

The data files were not usable for Postgresql, so I unloaded each table's data
into an ascii file with semi-colon (;) delimiters. Named them 'tablex.txt'.

Executed this copy command under kpsql;
copy tablex from '/dir/tablex.txt' using delimiters ';';

Problems-----
copy will not format blank dates, so edited date fields to pseudo date or
'\N'
copy will not format blank numeric fields, so built my tables as float8.
lots of other problems having to do with Sybase output, like a spurious
character at the end of the ascii file, troubles reading its own data while
unloading.
after every 'create tablex' script in kpsql, I had to exit kpsql before I
could execute another SQL.

Once over these problems the process worked very well. You don't need pipes
or inserts or other workarounds.
Let me know if I can help.

Regards,
Bill Barnes

>===== Original Message From "Paolo Zampieri" <pzampier(at)it(dot)ip-plus(dot)net> =====
>hello everybody,
>I tried to copy a file into a table from the postgres terminal, and all
>worked fine.
>But when I tryed the same with the php pg_exec() command the result was:
>
>PostgresSQL query failed: ERROR: You must have Postgres superuser privilege
>to do a COPY directly to or from a file. Anyone can COPY to stdout or from
>stdin. Psql's \copy command also works for anyone.
>
>I can't use \copy from php because there is no \copy interface in php, only
>pg_exec(sql command), so the only way is to use stdin!
>Someone has ideas about how to pipe the file and than read the pipe from the
>copy sql command?
>
>hope will interest
>bye

------------------------------------------------------------
This e-mail has been sent to you courtesy of OperaMail, a
free web-based service from Opera Software, makers of
the award-winning Web Browser - http://www.operasoftware.com
------------------------------------------------------------