COPY with default values?

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?

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?

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

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

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

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

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