Problem with COPY

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/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!

"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