Thread: COPY with default values?
Let's say I have a table of keywords, with a SERIAL primary key.
CREATE TABLE keywords (
key_id SERIAL PRIMARY KEY,
key_text TEXT
);
Now I would like to initialize this table with a COPY statement, but
without supplying values for the primary key. In other words, how can I
use COPY to perform the same function as
INSERT INTO keywords (keyword_text) VALUES ('foo');
INSERT INTO keywords (keyword_text) VALUES ('bar');
...
I have tried
COPY keywords FROM stdin USING DELIMITERS '|';
|foo
|bar
...
and also
0|foo
0|bar
and even
\N|foo
\N|bar
I even tried creating a view on keywords that has only keyword_text, and
copying into THAT--no luck. Then I wrote a rule to replace inserts on the
view with inserts on the table, but apparently COPY doesn't trigger INSERT
rules. Grumble...
--
Jeff Boes vox 616.226.9550
Database Engineer fax 616.349.9076
Nexcerpt, Inc. jboes@nexcerpt.com
I believe COPY is limited to reading ENTIRE records into a table not pieces
of them.
len morgan
-----Original Message-----
From: Jeff Boes <jboes@nexcerpt.com>
To: Postgres-general <pgsql-general@postgresql.org>
Date: Friday, May 25, 2001 9:20 AM
Subject: [GENERAL] COPY with default values?
>Let's say I have a table of keywords, with a SERIAL primary key.
>
>CREATE TABLE keywords (
> key_id SERIAL PRIMARY KEY,
> key_text TEXT
>);
>
>Now I would like to initialize this table with a COPY statement, but
>without supplying values for the primary key. In other words, how can I
>use COPY to perform the same function as
>
>INSERT INTO keywords (keyword_text) VALUES ('foo');
>INSERT INTO keywords (keyword_text) VALUES ('bar');
>...
>
>I have tried
>
>COPY keywords FROM stdin USING DELIMITERS '|';
>|foo
>|bar
>...
>
>and also
>
>0|foo
>0|bar
>
>and even
>
>\N|foo
>\N|bar
>
>I even tried creating a view on keywords that has only keyword_text, and
>copying into THAT--no luck. Then I wrote a rule to replace inserts on the
>view with inserts on the table, but apparently COPY doesn't trigger INSERT
>rules. Grumble...
>
>
>--
>Jeff Boes vox 616.226.9550
>Database Engineer fax 616.349.9076
>Nexcerpt, Inc. jboes@nexcerpt.com
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>
Jeff Boes <jboes@nexcerpt.com> writes:
> Now I would like to initialize this table with a COPY statement, but
> without supplying values for the primary key.
COPY does not deal with insertion of default values. Sorry.
One rather klugy answer is to COPY to a temp table that has only the
columns you want to supply, and then INSERT ... SELECT into the main
table. Might be faster if there are enough rows involved.
regards, tom lane
On Fri, 25 May 2001 10:33:41 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> COPY does not deal with insertion of default values. Sorry.
This seems odd to me, especially since Pgsql treats
INSERT INTO keywords (key_id, key_text) VALUES (null, 'foo');
differently than
INSERT INTO keywords (key_text) VALUES ('foo');
It's been a while, but I'm pretty sure Oracle will do the same thing for
each of these inserts, namely apply a default value when a null is
detected.
But thanks for the help!
--
Jeff Boes vox 616.226.9550
Database Engineer fax 616.349.9076
Nexcerpt, Inc. jboes@nexcerpt.com
You are correct and if you did your bulk insert with INSERT commands, it
will work just fine. The difference is the COPY command which AFAIK was/is
intended for backup and restore use.
len morgan
-----Original Message-----
From: Jeff Boes <jboes@nexcerpt.com>
To: Postgres-general <pgsql-general@postgresql.org>
Date: Friday, May 25, 2001 10:25 AM
Subject: Re: [GENERAL] COPY with default values?
>On Fri, 25 May 2001 10:33:41 -0400
>Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> COPY does not deal with insertion of default values. Sorry.
>
>This seems odd to me, especially since Pgsql treats
>
>INSERT INTO keywords (key_id, key_text) VALUES (null, 'foo');
>
>differently than
>
>INSERT INTO keywords (key_text) VALUES ('foo');
>
>It's been a while, but I'm pretty sure Oracle will do the same thing for
>each of these inserts, namely apply a default value when a null is
>detected.
>
>But thanks for the help!
>
>--
>Jeff Boes vox 616.226.9550
>Database Engineer fax 616.349.9076
>Nexcerpt, Inc. jboes@nexcerpt.com
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
System:
SunOS 5.8 Generic_108528-07 sun4u sparc SUNW,Ultra-Enterprise
Configuration options:
env CFLAGS='-O2 -pipe' ./configure --prefix=/opt/pgsql \
--with-perl --with-python --with-CXX \
--with-openssl=/local/lib/openssl
Compilation fails with the following error:
In file included from crypt.c:27:
/usr/include/crypt.h:23: conflicting types for `des_encrypt'
/local/lib/openssl/include/openssl/des.h:150: previous declaration of `des_encrypt'
make[3]: *** [crypt.o] Error 1
make[3]: Leaving directory `/admin/src/postgresql-7.1.1/src/backend/libpq'
make[2]: *** [libpq-recursive] Error 2
make[2]: Leaving directory `/admin/src/postgresql-7.1.1/src/backend'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/admin/src/postgresql-7.1.1/src'
make: *** [all] Error 2
--
Arcady Genkin
Arcady Genkin writes: > In file included from crypt.c:27: > /usr/include/crypt.h:23: conflicting types for `des_encrypt' > /local/lib/openssl/include/openssl/des.h:150: previous declaration of `des_encrypt' Upgrade your OpenSSL installation. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Hi Arcady, This problem occurs with some versions of OpenSSL on Solaris and Unixware. There's an installation guide for PostgreSQL 7.1.1 on Solaris 7/8, and includes the fix needed for getting SSL to work in your scenario. The installation guide is at : http://techdocs.postgresql.org/installguides.php#solaris Hope that's helpful. :-) Regards and best wishes, Justin Clift Arcady Genkin wrote: > > System: > > SunOS 5.8 Generic_108528-07 sun4u sparc SUNW,Ultra-Enterprise > > Configuration options: > > env CFLAGS='-O2 -pipe' ./configure --prefix=/opt/pgsql \ > --with-perl --with-python --with-CXX \ > --with-openssl=/local/lib/openssl > > Compilation fails with the following error: > > In file included from crypt.c:27: > /usr/include/crypt.h:23: conflicting types for `des_encrypt' > /local/lib/openssl/include/openssl/des.h:150: previous declaration of `des_encrypt' > make[3]: *** [crypt.o] Error 1 > make[3]: Leaving directory `/admin/src/postgresql-7.1.1/src/backend/libpq' > make[2]: *** [libpq-recursive] Error 2 > make[2]: Leaving directory `/admin/src/postgresql-7.1.1/src/backend' > make[1]: *** [all] Error 2 > make[1]: Leaving directory `/admin/src/postgresql-7.1.1/src' > make: *** [all] Error 2 > > -- > Arcady Genkin > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
Jeff Boes wrote:
> Let's say I have a table of keywords, with a SERIAL primary key.
>
> CREATE TABLE keywords (
> key_id SERIAL PRIMARY KEY,
> key_text TEXT
> );
>
> Now I would like to initialize this table with a COPY statement, but
> without supplying values for the primary key. In other words, how can I
> use COPY to perform the same function as
>
> INSERT INTO keywords (keyword_text) VALUES ('foo');
> INSERT INTO keywords (keyword_text) VALUES ('bar');
> ...
>
> I have tried
>
> COPY keywords FROM stdin USING DELIMITERS '|';
> |foo
> |bar
> ...
>
> and also
>
> 0|foo
> 0|bar
>
> and even
>
> \N|foo
> \N|bar
>
> I even tried creating a view on keywords that has only keyword_text, and
> copying into THAT--no luck. Then I wrote a rule to replace inserts on the
> view with inserts on the table, but apparently COPY doesn't trigger INSERT
> rules. Grumble...
>
>
>
If you just want a line-number for the records you could always on UN*X
do "nl file > newfile".
Best regards
Per-Olof Pettersson
I asked this in -general about ten days ago, but received no replies. Can I redefine location of the UNIX domain socket file to be other than /tmp/.s.PGSQL.5432? The problem is that there is a /tmp cleaning script on the Solaris system where Postgres is installed, which sweeps away anything older than 24 hours. Right now I'm getting away by having a cron job touch the file periodically, but this is not a nice solution. p.s. I'm sending this also to -bugs, because I think that the location of the socket file should be configurable (at least at compile time). Many thanks, -- Arcady Genkin
Re: REPOST: redefining location of the socket file /tmp/.s.PGSQL.5432
From
Martijn van Oosterhout
Date:
On Thu, Jun 07, 2001 at 06:02:53PM -0400, Arcady Genkin wrote: > I asked this in -general about ten days ago, but received no replies. > > Can I redefine location of the UNIX domain socket file to be other > than /tmp/.s.PGSQL.5432? The problem is that there is a /tmp cleaning > script on the Solaris system where Postgres is installed, which sweeps > away anything older than 24 hours. Right now I'm getting away by > having a cron job touch the file periodically, but this is not a nice > solution. Yes, it can be done. For example, the debian package moves it to /var/run iirc. However, to make this work you need to patch the postmaster to put it in a different place and patch the libpq to look for it in the new place. So it will need a recompile at least. > p.s. I'm sending this also to -bugs, because I think that the > location of the socket file should be configurable (at least at > compile time). Maybe it already is a #define. The debian patch should be available from the debian website. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Arcady Genkin <a.genkin@utoronto.ca> writes:
> Can I redefine location of the UNIX domain socket file to be other
> than /tmp/.s.PGSQL.5432?
Yes, in 7.1 this is a supported option. See unix_socket_directory
parameter.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > > Can I redefine location of the UNIX domain socket file to be other > > than /tmp/.s.PGSQL.5432? > > Yes, in 7.1 this is a supported option. See unix_socket_directory > parameter. Thanks a lot, this is what I was looking for. I have another question, though: how are the clients (mod_php4 for e.g.) going to know the non-standard location of the socket file? Many thanks, -- Arcady Genkin i=1; while 1, hilb(i); i=i+1; end
Arcady Genkin <a.genkin@utoronto.ca> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
> Can I redefine location of the UNIX domain socket file to be other
> than /tmp/.s.PGSQL.5432?
>>
>> Yes, in 7.1 this is a supported option. See unix_socket_directory
>> parameter.
> Thanks a lot, this is what I was looking for. I have another
> question, though: how are the clients (mod_php4 for e.g.) going to
> know the non-standard location of the socket file?
They're not, unless you tell them, which is why runtime setting of the
parameter isn't all that useful IMHO. But some people wanted it, so
there it is.
regards, tom lane
PHP's pg_connect() and non-standard location of the socket (was: REPOST: redefining location of the socket file /tmp/.s.PGSQL.5432)
From
Arcady Genkin
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes: > Arcady Genkin <a.genkin@utoronto.ca> writes: > > Tom Lane <tgl@sss.pgh.pa.us> writes: > > Can I redefine location of the UNIX domain socket file to be other > > than /tmp/.s.PGSQL.5432? > >> > >> Yes, in 7.1 this is a supported option. See unix_socket_directory > >> parameter. > > > Thanks a lot, this is what I was looking for. I have another > > question, though: how are the clients (mod_php4 for e.g.) going to > > know the non-standard location of the socket file? > > They're not, unless you tell them, which is why runtime setting of the > parameter isn't all that useful IMHO. But some people wanted it, so > there it is. Hmm... How would I tell that to `psql'? Via "-v" parameter? I wonder if there is a way to tell the PHP script where the socket is... The documentation of pg_connect() function is not exhaustive: there is an "options" field for connection, but nowhere does it explain syntax of specifying the options, nor what sort of options it is expecting. http://www.php.net/manual/en/function.pg-connect.php Is there anyone knowledgeable with PHP in here? PHP's mailing lists have been down for a few weeks because of transition to new server or something else. Many thanks, -- Arcady Genkin i=1; while 1, hilb(i); i=i+1; end
Re: PHP's pg_connect() and non-standard location of the socket (was: REPOST: redefining location of the socket file /tmp/.s.PGSQL.5432)
From
Tom Lane
Date:
Arcady Genkin <a.genkin@utoronto.ca> writes:
>> They're not, unless you tell them, which is why runtime setting of the
>> parameter isn't all that useful IMHO. But some people wanted it, so
>> there it is.
> Hmm... How would I tell that to `psql'? Via "-v" parameter?
IIRC, a hostname parameter that starts with '/' is now interpreted as
a socket path. So you'd do psql -h /path/to/something, or set PGHOST,
to inform the client about a nonstandard socket location.
$ psql -h /nowhere -p 7654
psql: connectDBStart() -- connect() failed: No such file or directory
Is the postmaster running locally
and accepting connections on Unix socket '/nowhere/.s.PGSQL.7654'?
regards, tom lane
Re: PHP's pg_connect() and non-standard location of the socket (was: REPOST: redefining location of the socket file /tmp/.s.PGSQL.5432)
From
Arcady Genkin
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes: > Arcady Genkin <a.genkin@utoronto.ca> writes: > >> They're not, unless you tell them, which is why runtime setting of the > >> parameter isn't all that useful IMHO. But some people wanted it, so > >> there it is. > > > Hmm... How would I tell that to `psql'? Via "-v" parameter? > > IIRC, a hostname parameter that starts with '/' is now interpreted as > a socket path. So you'd do psql -h /path/to/something, or set PGHOST, > to inform the client about a nonstandard socket location. > > $ psql -h /nowhere -p 7654 > psql: connectDBStart() -- connect() failed: No such file or directory > Is the postmaster running locally > and accepting connections on Unix socket '/nowhere/.s.PGSQL.7654'? Cool! Thanks a lot. Just changed the location of the socket to /var/run, and my PHP script was able to connect using "host=/var/run" string. Thanks very much once more. -- Arcady Genkin i=1; while 1, hilb(i); i=i+1; end