Обсуждение: Problem with COPY
Hi everyone, I have a table with a surrogate key which is an integer sequence. Is there a way to load a file using COPY and tell postgresqlnot to insert into the primary key column? Thanks! Chris -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230
am 10.07.2006, um 10:21:59 +0200 mailte Christian Rengstl folgendes: > Hi everyone, > > I have a table with a surrogate key which is an integer sequence. Is > there a way to load a file using COPY and tell postgresql not to > insert into the primary key column? Yes: test=# create table foobar (id serial primary key, name text); NOTICE: CREATE TABLE will create implicit sequence "foobar_id_seq" for serial column "foobar.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foobar_pkey" for table "foobar" CREATE TABLE test=*# copy foobar (name) from stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> name1 >> name2 >> name3 >> \. test=*# select * from foobar; id | name ----+------- 1 | name1 2 | name2 3 | name3 (3 rows) HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
Hi again everyone, i got it to work, but came across another question concerning COPY. If i have a column in a text file that i do not want/needin the database, is there a way not to read that column from the file without having to edit the file beforehand? Thanks for your advice! "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote on 07/10/06 11:28 am: > am 10.07.2006, um 10:21:59 +0200 mailte Christian Rengstl folgendes: >> Hi everyone, >> >> I have a table with a surrogate key which is an integer sequence. Is >> there a way to load a file using COPY and tell postgresql not to >> insert into the primary key column? > > Yes: > > test=# create table foobar (id serial primary key, name text); > NOTICE: CREATE TABLE will create implicit sequence "foobar_id_seq" for > serial column "foobar.id" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "foobar_pkey" for table "foobar" > CREATE TABLE > test=*# copy foobar (name) from stdin; > Enter data to be copied followed by a newline. > End with a backslash and a period on a line by itself. >>> name1 >>> name2 >>> name3 >>> \. > test=*# select * from foobar; > id | name > ----+------- > 1 | name1 > 2 | name2 > 3 | name3 > (3 rows) > > > HTH, Andreas > -- > Andreas Kretschmer (Kontakt: siehe Header) > Heynitz: 035242/47215, D1: 0160/7141639 > GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net > === Schollglas Unternehmensgruppe === > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230
am 11.07.2006, um 9:24:06 +0200 mailte Christian Rengstl folgendes: > Hi again everyone, > > i got it to work, but came across another question concerning COPY. If > i have a column in a text file that i do not want/need in the > database, is there a way not to read that column from the file without > having to edit the file beforehand? > > Thanks for your advice! Do you have a UNIX-like operating system? Then you can use tools like 'cut': kretschmer@tux:~$ echo "s1,s2,s3" | cut -d ',' -f 1,3 s1,s3 This result can you pipe into psql. HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
On Tuesday 11. July 2006 10:10, A. Kretschmer wrote: >Do you have a UNIX-like operating system? Then you can use tools like >'cut': > >kretschmer@tux:~$ echo "s1,s2,s3" | cut -d ',' -f 1,3 >s1,s3 > >This result can you pipe into psql. It would be nice, though, if we had something like Oracle's SQL-Loader for PostgreSQL. It's a very powerful tool for transforming and loading data. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE
Leif B. Kristensen wrote: > On Tuesday 11. July 2006 10:10, A. Kretschmer wrote: > >Do you have a UNIX-like operating system? Then you can use tools like > >'cut': > > > >kretschmer@tux:~$ echo "s1,s2,s3" | cut -d ',' -f 1,3 > >s1,s3 > > > >This result can you pipe into psql. > > It would be nice, though, if we had something like Oracle's SQL-Loader > for PostgreSQL. It's a very powerful tool for transforming and loading > data. We do have very powerful tools, even more powerful than SQL-Loader. They require a bit more work to use though. Perl, for example. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Tuesday 11. July 2006 15:22, Alvaro Herrera wrote: >Leif B. Kristensen wrote: >> It would be nice, though, if we had something like Oracle's >> SQL-Loader for PostgreSQL. It's a very powerful tool for >> transforming and loading data. > >We do have very powerful tools, even more powerful than SQL-Loader. >They require a bit more work to use though. Perl, for example. I've done quite a bit of Perl hacking myself to transform data. But given the choice between Perl and SQL-Loader, I still think that I prefer the latter. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE