Thread: COPY is not working
Hi, COPY is not working on latest HEAD? """ regression=# select * from a;aa ----3256 (2 rows) regression=# COPY a TO '/tmp/copy_test'; COPY 0 """ -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Jaime Casanova <jcasanov@systemguards.com.ec> wrote: > COPY is not working on latest HEAD? > """ > regression=# select * from a; > aa > ---- > 32 > 56 > (2 rows) > > regression=# COPY a TO '/tmp/copy_test'; > COPY 0 > """ > > -- Please send the actual test pattern and your environment information to reproduce the misbehavior. It works fine on my machine. regression=# CREATE TABLE a (aa integer); CREATE TABLE regression=# INSERT INTO a VALUES(32), (56); INSERT 0 2 regression=# select * from a;aa ----3256 (2 rows) regression=# COPY a TO '/tmp/copy_test'; COPY 2 regression=# \! cat /tmp/copy_test 32 56 $ uname -a Linux xxx 2.6.29.4-167.fc11.x86_64 #1 SMP Wed May 27 17:27:08 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux $ postgres --version postgres (PostgreSQL) 9.0beta1 Regards, --- Takahiro Itagaki NTT Open Source Software Center
2010/4/30 Jaime Casanova <jcasanov@systemguards.com.ec>: > Hi, > > COPY is not working on latest HEAD? > """ > regression=# select * from a; > aa > ---- > 32 > 56 > (2 rows) > > regression=# COPY a TO '/tmp/copy_test'; > COPY 0 > """ > ah! this is because COPY doesn't follow inherited tables... should it? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
On Fri, Apr 30, 2010 at 12:56 AM, Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> wrote: > > Jaime Casanova <jcasanov@systemguards.com.ec> wrote: > >> COPY is not working on latest HEAD? >> """ >> regression=# select * from a; >> aa >> ---- >> 32 >> 56 >> (2 rows) >> >> regression=# COPY a TO '/tmp/copy_test'; >> COPY 0 >> """ >> >> -- > > Please send the actual test pattern and your environment information > to reproduce the misbehavior. It works fine on my machine. > it's the regression database generated by "make installcheck", there is an "a" table that is parent table (it has children), seems like COPY is not getting the data from the inherited tables but only from the parent -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Jaime Casanova <jcasanov@systemguards.com.ec> wrote: > ah! this is because COPY doesn't follow inherited tables... should it? Yes. You can use "COPY (SELECT * FROM a) TO " instead to copy all tuples. http://developer.postgresql.org/pgdocs/postgres/sql-copy.html | COPY can only be used with plain tables, not with views. | However, you can write COPY (SELECT * FROM viewname) TO .... Should we add "or parent tables" after "not with views"? To be exact, it would be "'COPY a parent table TO' only copies tuples in the parent table and does not copy inherited child tables". regression=# CREATE TABLE a (aa integer); CREATE TABLE regression=# CREATE TABLE b () INHERITS (a); CREATE TABLE regression=# INSERT INTO b VALUES(32), (56); INSERT 0 2 regression=# select * from a;aa ----3256 (2 rows) regression=# COPY a TO '/tmp/copy_test'; COPY 0 regression=# COPY (SELECT * FROM a) TO '/tmp/copy_test'; COPY 2 Regards, --- Takahiro Itagaki NTT Open Source Software Center
On Fri, Apr 30, 2010 at 1:13 AM, Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> wrote: > > Jaime Casanova <jcasanov@systemguards.com.ec> wrote: > >> ah! this is because COPY doesn't follow inherited tables... should it? > > Yes. You can use "COPY (SELECT * FROM a) TO " instead to copy all tuples. > > http://developer.postgresql.org/pgdocs/postgres/sql-copy.html > | COPY can only be used with plain tables, not with views. > | However, you can write COPY (SELECT * FROM viewname) TO .... > > Should we add "or parent tables" after "not with views"? > To be exact, it would be "'COPY a parent table TO' only copies > tuples in the parent table and does not copy inherited child tables". > +1 on make this clear on the docs -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
2010/4/30 Jaime Casanova <jcasanov@systemguards.com.ec>: > On Fri, Apr 30, 2010 at 1:13 AM, Takahiro Itagaki > <itagaki.takahiro@oss.ntt.co.jp> wrote: >> >> Jaime Casanova <jcasanov@systemguards.com.ec> wrote: >> >>> ah! this is because COPY doesn't follow inherited tables... should it? >> >> Yes. You can use "COPY (SELECT * FROM a) TO " instead to copy all tuples. >> >> http://developer.postgresql.org/pgdocs/postgres/sql-copy.html >> | COPY can only be used with plain tables, not with views. >> | However, you can write COPY (SELECT * FROM viewname) TO .... >> >> Should we add "or parent tables" after "not with views"? >> To be exact, it would be "'COPY a parent table TO' only copies >> tuples in the parent table and does not copy inherited child tables". >> > > +1 on make this clear on the docs It works with parent table. Depends if the parent table have data or not. COPY is more like SELECT FROM ONLY table, isn't it ? > > -- > Atentamente, > Jaime Casanova > Soporte y capacitación de PostgreSQL > Asesoría y desarrollo de sistemas > Guayaquil - Ecuador > Cel. +59387171157 > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Cédric Villemain
Jaime Casanova <jcasanov@systemguards.com.ec> writes:
> COPY is not working on latest HEAD?
I've added this to the COPY notes section:
<para> <command>COPY</command> only deals with the specific table named; it does not copy data to or from child
tables. Thus for example <literal>COPY <replaceable class="parameter">table</> TO</literal> shows the same data as
<literal>SELECT* FROM ONLY <replaceable class="parameter">table</></literal>. But <literal>COPY (SELECT * FROM
<replaceableclass="parameter">table</>) TO ...</literal> can be used to dump all of the data in an inheritance
hierarchy. </para>
regards, tom lane
On 4/30/2010 1:56 AM, Jaime Casanova wrote: > 2010/4/30 Jaime Casanova <jcasanov@systemguards.com.ec>: >> Hi, >> >> COPY is not working on latest HEAD? >> """ >> regression=# select * from a; >> aa >> ---- >> 32 >> 56 >> (2 rows) >> >> regression=# COPY a TO '/tmp/copy_test'; >> COPY 0 >> """ >> > > ah! this is because COPY doesn't follow inherited tables... should it? The TRUNCATE command's behavior was changed in that regard. What TRUNCATE did in 8.3 is now TRUNCATE ONLY in 8.4. I don't see a reason why COPY should not follow suit. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin
Jan Wieck <JanWieck@Yahoo.com> writes:
> On 4/30/2010 1:56 AM, Jaime Casanova wrote:
>> ah! this is because COPY doesn't follow inherited tables... should it?
> The TRUNCATE command's behavior was changed in that regard. What
> TRUNCATE did in 8.3 is now TRUNCATE ONLY in 8.4. I don't see a reason
> why COPY should not follow suit.
How about "because it's inappropriate for dump/reload, which is the main
use-case for COPY"? In any case it'd be weird for COPY FROM and COPY TO
to behave differently on this point, but I don't really see how COPY
FROM would do anything intelligent for inheritance.
regards, tom lane
On 5/3/2010 10:51 PM, Tom Lane wrote: > Jan Wieck <JanWieck@Yahoo.com> writes: >> On 4/30/2010 1:56 AM, Jaime Casanova wrote: >>> ah! this is because COPY doesn't follow inherited tables... should it? > >> The TRUNCATE command's behavior was changed in that regard. What >> TRUNCATE did in 8.3 is now TRUNCATE ONLY in 8.4. I don't see a reason >> why COPY should not follow suit. > > How about "because it's inappropriate for dump/reload, which is the main > use-case for COPY"? In any case it'd be weird for COPY FROM and COPY TO > to behave differently on this point, but I don't really see how COPY > FROM would do anything intelligent for inheritance. Dump would have to switch to the more consistent syntax using the COPY ONLY <table> ... form. As simple as that. You are right that COPY ONLY ... FROM makes no sense at all. But I would consider that the corner case, not the defining general rule. One could make exactly the save inconsistency argument about the fact that there is a SELECT ... FROM ONLY, UPDATE ONLY and DELETE FROM ONLY, but no INSERT INTO ONLY. As of right now, all commands that retrieve or affect existing rows, except for COPY, do honor the ONLY keyword to stop inheritance. And while pg_dump may be one of the main users of COPY, I think there are quite a few other. I don't really see why COPY is so special. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin