Обсуждение: merging 2 dumps
Hi, I need to merge 2 versions of a database manually via pg_dump. I'd like to diff them to see the changes. Unfortunately the data contain newlines. Is it possible to quote them, so that each row will be only 1 row in the dumps ? \n or something like that TIA -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Am Fre, 2003-08-01 um 14.51 schrieb Nabil Sayegh: > Unfortunately the data contain newlines. > > Is it possible to quote them, so that each row will be only 1 row in the > dumps ? > \n or something like that Oops, \n means NULL Any idea ? -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Nabil Sayegh <postgresql@e-trolley.de> writes:
> I need to merge 2 versions of a database manually via pg_dump.
> I'd like to diff them to see the changes.
> Unfortunately the data contain newlines.
> Is it possible to quote them, so that each row will be only 1 row in the
> dumps ?
> \n or something like that
What PG version are you using?? All recent releases *do* dump newline
as \n, for example
COPY fooey (f1) FROM stdin;
hi
hi\nthere
ho
\.
regards, tom lane
Am Fre, 2003-08-01 um 17.01 schrieb Tom Lane: > > \n or something like that > > What PG version are you using?? All recent releases *do* dump newline > as \n, for example pg_dump (PostgreSQL) 7.3.3 > COPY fooey (f1) FROM stdin; > hi > hi\nthere > ho > \. I always use: pg_dump -D ... (I don't really like COPY) Is it also possible to have newlines converted when doing psql -c "SELECT ..." tfyh -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Am Fre, 2003-08-01 um 17.13 schrieb Nabil Sayegh: > Am Fre, 2003-08-01 um 17.01 schrieb Tom Lane: > > > > \n or something like that > > > > What PG version are you using?? All recent releases *do* dump newline > > as \n, for example > > pg_dump (PostgreSQL) 7.3.3 Should I have mentioned, that there are Windows cr/lf ? tia -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Am Fre, 2003-08-01 um 17.01 schrieb Tom Lane: > What PG version are you using?? All recent releases *do* dump newline > as \n, for example > > COPY fooey (f1) FROM stdin; Hm, I checked it and it does convert them to \n BUT only without -D switch Any idea ? I'm kind of afraid to use COPY because of referential integrity, triggers etc. What's your opinion ? Is it as reliable like -D (7.3.3) ? tfyh -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Nabil Sayegh <postgresql@e-trolley.de> writes:
> I'm kind of afraid to use COPY because of referential integrity,
> triggers etc.
COPY used to have some problems, but they are all fixed.
regards, tom lane
Am Sam, 2003-08-02 um 04.27 schrieb Tom Lane: > Nabil Sayegh <postgresql@e-trolley.de> writes: > > I'm kind of afraid to use COPY because of referential integrity, > > triggers etc. > > COPY used to have some problems, but they are all fixed. I migrated from 7.2.? to 7.3.3 lately. So the database in question still has old-style referential integrity constraints. When I dump the db without -D and force errors by manually editing the dump and changing a foreign key to a value that doesn't exist, the import won't recognize it. There is no errormsg. and the data gets inserted into the table with the 'wrong' value. Is that because of the old-style RI ? -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Am Sam, 2003-08-02 um 18.43 schrieb Nabil Sayegh: > There is no errormsg. and the data gets inserted into the table with the > 'wrong' value. > > Is that because of the old-style RI ? Apart from that: Is it possible to pg_dump a specific table from a specific schema ? The db has n schemas. Each schema belongs to a different user. When I do pg_dump -U user_of_specific_schema -a -t table_to_dump I get pg_dump: Attempt to lock table "table_to_dump" failed. ERROR: other_user: permission denied Shouldn't it use the search_path ? Apparently it tries to dump all tables from all schema. It also doesnt accept the table as schema.table. -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de