Thread
-
COPY with default values?
Jeff Boes <jboes@nexcerpt.com> — 2001-05-25T14:06:37Z
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 -
Re: COPY with default values?
Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-25T14:33:41Z
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
-
Re: COPY with default values?
Jeff Boes <jboes@nexcerpt.com> — 2001-05-25T15:10:01Z
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 -
Compilation fails --with-ssl on Solaris 8
Arcady Genkin <a.genkin@utoronto.ca> — 2001-05-25T15:51:26Z
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 -
Re: [GENERAL] Compilation fails --with-ssl on Solaris 8
Peter Eisentraut <peter_e@gmx.net> — 2001-05-25T18:13:43Z
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
-
Re: [GENERAL] Compilation fails --with-ssl on Solaris 8
Justin Clift <justin@postgresql.org> — 2001-05-26T01:13:45Z
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 -
Re: COPY with default values?
Per-Olof Pettersson <pgsql@peope.net> — 2001-05-28T06:32:06Z
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 -
REPOST: redefining location of the socket file /tmp/.s.PGSQL.5432
Arcady Genkin <a.genkin@utoronto.ca> — 2001-06-07T22:02:53Z
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: [GENERAL] REPOST: redefining location of the socket file /tmp/.s.PGSQL.5432
Martijn van Oosterhout <kleptog@svana.org> — 2001-06-08T00:35:23Z
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/
-
Re: [GENERAL] REPOST: redefining location of the socket file /tmp/.s.PGSQL.5432
Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-08T16:47:54Z
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
-
Re: REPOST: redefining location of the socket file /tmp/.s.PGSQL.5432
Arcady Genkin <a.genkin@utoronto.ca> — 2001-06-12T20:13:17Z
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
-
Re: REPOST: redefining location of the socket file /tmp/.s.PGSQL.5432
Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-12T20:17:42Z
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)
Arcady Genkin <a.genkin@utoronto.ca> — 2001-06-12T20:42:31Z
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)
Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-12T21:39:51Z
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)
Arcady Genkin <a.genkin@utoronto.ca> — 2001-06-14T17:02:58Z
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