Thread: surppressing column names in COPY format
in 7.2 doing a data only dump (pg_dump -a -x db > dump) would give
output like:
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'status';
COPY "status" FROM stdin;
0 INFO
1 WARNING
2 CRIT
\.
-- Enable triggers
UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger where
pg_class.oid = tgrelid) WHERE relname = 'status';
however in 7.3, doing the equivalent (pg_dump -a --disable-triggers db >
dump) i get the following output.
UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid =
'status'::pg_catalog.regclass;
COPY status (status_id, description) FROM stdin;
0 INFO
1 WARNING
2 CRIT
\.
-- Enable triggers
UPDATE pg_catalog.pg_class SET reltriggers = (SELECT pg_catalog.count(*)
FROM pg_catalog.pg_trigger where pg_class.oid = tgrelid) WHERE oid =
'status'::pg_catalog.regclass;
the problem is that I am restoring the data to a database with different
column names, which errors out in 7.3 (but didn't under 7.2 since it
didn't produce column names in the COPY output). I checked the docs and
it provided no clue as to how to suppress column names for COPY only
dumps. I also checked the message archives, but the only thing relevant
seemed to be the original patch which didn't appear to allow for a way
to suppress the column names, am I completely missing something here or
did we lose this functionality?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Robert Treat <xzilla@users.sourceforge.net> writes:
> the problem is that I am restoring the data to a database with different
> column names, which errors out in 7.3 (but didn't under 7.2 since it
> didn't produce column names in the COPY output). I checked the docs and
> it provided no clue as to how to suppress column names for COPY only
> dumps.
I think you can still get it to dump as INSERTs without column names.
I don't really agree that we've "lost functionality" here, though ---
you might as well claim that it's a bug that the COPY command forces
you to restore the data into a particular table. If you need to do
data transformation (which includes relabeling columns IMHO), restore
the data into a temp table and then do INSERT ... SELECT ... to massage
it into what you want.
regards, tom lane
I wonder if there's a way to just 'append' a temp table to another table, as long as the column's have the exact same typeand order and don't violate constraints. It would have to be with the db shutdown probably. Tom Lane wrote: > Robert Treat <xzilla@users.sourceforge.net> writes: > >>the problem is that I am restoring the data to a database with different >>column names, which errors out in 7.3 (but didn't under 7.2 since it >>didn't produce column names in the COPY output). I checked the docs and >>it provided no clue as to how to suppress column names for COPY only >>dumps. > > > I think you can still get it to dump as INSERTs without column names. > > I don't really agree that we've "lost functionality" here, though --- > you might as well claim that it's a bug that the COPY command forces > you to restore the data into a particular table. If you need to do > data transformation (which includes relabeling columns IMHO), restore > the data into a temp table and then do INSERT ... SELECT ... to massage > it into what you want. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
On Thu, 2003-07-31 at 15:18, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > the problem is that I am restoring the data to a database with different
> > column names, which errors out in 7.3 (but didn't under 7.2 since it
> > didn't produce column names in the COPY output). I checked the docs and
> > it provided no clue as to how to suppress column names for COPY only
> > dumps.
>
> I think you can still get it to dump as INSERTs without column names.
that was an example table, the actual table has millions of rows, I need
to use COPY on it.
>
> I don't really agree that we've "lost functionality" here, though ---
> you might as well claim that it's a bug that the COPY command forces
> you to restore the data into a particular table. If you need to do
> data transformation (which includes relabeling columns IMHO), restore
> the data into a temp table and then do INSERT ... SELECT ... to massage
> it into what you want.
>
By that logic then what is the point of allowing data dumped as INSERTS
both with and without column names? Obviously there is functionality to
be gained (or lost) doing it with or without column names, and in the
current case I'm not gaining anything by having the columns explicitly
named, so a flag to turn them off would be handy.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Robert Treat <xzilla@users.sourceforge.net> writes:
> On Thu, 2003-07-31 at 15:18, Tom Lane wrote:
>> I don't really agree that we've "lost functionality" here, though ---
>> you might as well claim that it's a bug that the COPY command forces
>> you to restore the data into a particular table.
> By that logic then what is the point of allowing data dumped as INSERTS
> both with and without column names?
None; we just haven't gotten around to removing code that no longer
pulls its weight. The no-column-name variant is just as dangerous as
it was in the COPY case, IMHO.
regards, tom lane
On Thu, 2003-07-31 at 16:50, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > On Thu, 2003-07-31 at 15:18, Tom Lane wrote:
> >> I don't really agree that we've "lost functionality" here, though ---
> >> you might as well claim that it's a bug that the COPY command forces
> >> you to restore the data into a particular table.
>
> > By that logic then what is the point of allowing data dumped as INSERTS
> > both with and without column names?
>
> None; we just haven't gotten around to removing code that no longer
> pulls its weight. The no-column-name variant is just as dangerous as
> it was in the COPY case, IMHO.
>
by dangerous you mean functional right? (I'm not changing the data, just
the column name)
would a patch to remove this dead code (the insert without column case)
be accepted for 7.4?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Robert Treat <xzilla@users.sourceforge.net> writes:
> On Thu, 2003-07-31 at 16:50, Tom Lane wrote:
>> None; we just haven't gotten around to removing code that no longer
>> pulls its weight. The no-column-name variant is just as dangerous as
>> it was in the COPY case, IMHO.
> by dangerous you mean functional right?
:-)
By dangerous I mean "might not restore the table correctly". There are
scenarios involving child tables and ALTER TABLE ADD COLUMN where a
column-name-less INSERT or COPY will dump the data in a different column
order than pg_dump's CREATE TABLE command will create. Before 7.3 it
was in fact not possible to dump and reload the regression-test database
using COPY, because of this problem --- perhaps that causes me to
overstate its importance, but there is a definite risk.
> would a patch to remove this dead code (the insert without column case)
> be accepted for 7.4?
Not for 7.4, because we're past feature freeze, but I wouldn't object to
removing it in 7.5.
regards, tom lane
On Thu, 2003-07-31 at 17:21, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > On Thu, 2003-07-31 at 16:50, Tom Lane wrote:
> >> None; we just haven't gotten around to removing code that no longer
> >> pulls its weight. The no-column-name variant is just as dangerous as
> >> it was in the COPY case, IMHO.
>
> > by dangerous you mean functional right?
>
> :-)
>
> By dangerous I mean "might not restore the table correctly". There are
> scenarios involving child tables and ALTER TABLE ADD COLUMN where a
> column-name-less INSERT or COPY will dump the data in a different column
> order than pg_dump's CREATE TABLE command will create. Before 7.3 it
> was in fact not possible to dump and reload the regression-test database
> using COPY, because of this problem --- perhaps that causes me to
> overstate its importance, but there is a definite risk.
>
You do overstate it's importance IMHO, because I'm not lobbying for
permanent removal of the column names, nor making the default to not
have column names, I'm simply stating that the option to not have them
provides benefits and I'm willing to take the "risks" associated with
them.
At least the work arounds are simple... :-\
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
I have some code from my project that does unload to file select .... and load to file insert ... Which would allow you to change column names or even table names through the load process. It wasn't submitted as a patch as I didn't think it added significant functionality, but apparently there is a need. Dave On Thu, 2003-07-31 at 17:59, Robert Treat wrote: > On Thu, 2003-07-31 at 17:21, Tom Lane wrote: > > Robert Treat <xzilla@users.sourceforge.net> writes: > > > On Thu, 2003-07-31 at 16:50, Tom Lane wrote: > > >> None; we just haven't gotten around to removing code that no longer > > >> pulls its weight. The no-column-name variant is just as dangerous as > > >> it was in the COPY case, IMHO. > > > > > by dangerous you mean functional right? > > > >:-) > > > > By dangerous I mean "might not restore the table correctly". There are > > scenarios involving child tables and ALTER TABLE ADD COLUMN where a > > column-name-less INSERT or COPY will dump the data in a different column > > order than pg_dump's CREATE TABLE command will create. Before 7.3 it > > was in fact not possible to dump and reload the regression-test database > > using COPY, because of this problem --- perhaps that causes me to > > overstate its importance, but there is a definite risk. > > > > You do overstate it's importance IMHO, because I'm not lobbying for > permanent removal of the column names, nor making the default to not > have column names, I'm simply stating that the option to not have them > provides benefits and I'm willing to take the "risks" associated with > them. > > At least the work arounds are simple... :-\ > > Robert Treat -- Dave Cramer <dave@fastcrypt.com> fastcrypt -- Dave Cramer <Dave@micro-automation.net>
I have some code from my project that does unload to file select .... and load to file insert ... Which would allow you to change column names or even table names through the load process. It wasn't submitted as a patch as I didn't think it added significant functionality, but apparently there is a need. Dave On Thu, 2003-07-31 at 17:59, Robert Treat wrote: > On Thu, 2003-07-31 at 17:21, Tom Lane wrote: > > Robert Treat <xzilla@users.sourceforge.net> writes: > > > On Thu, 2003-07-31 at 16:50, Tom Lane wrote: > > >> None; we just haven't gotten around to removing code that no longer > > >> pulls its weight. The no-column-name variant is just as dangerous as > > >> it was in the COPY case, IMHO. > > > > > by dangerous you mean functional right? > > > >:-) > > > > By dangerous I mean "might not restore the table correctly". There are > > scenarios involving child tables and ALTER TABLE ADD COLUMN where a > > column-name-less INSERT or COPY will dump the data in a different column > > order than pg_dump's CREATE TABLE command will create. Before 7.3 it > > was in fact not possible to dump and reload the regression-test database > > using COPY, because of this problem --- perhaps that causes me to > > overstate its importance, but there is a definite risk. > > > > You do overstate it's importance IMHO, because I'm not lobbying for > permanent removal of the column names, nor making the default to not > have column names, I'm simply stating that the option to not have them > provides benefits and I'm willing to take the "risks" associated with > them. > > At least the work arounds are simple... :-\ > > Robert Treat -- Dave Cramer <dave@fastcrypt.com> fastcrypt
On Thu, Jul 31, 2003 at 05:59:37PM -0400, Robert Treat wrote:
> have column names, I'm simply stating that the option to not have them
> provides benefits and I'm willing to take the "risks" associated with
> them.
For what it's worth, I've used that trick in the past, too.
You can do what you need with regular COPY still, though, right?
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110