[GENERAL] PostgreSQL v7.1.2 Now Available ...
Just a quick announcement that v7.1.2 is now available for download ...
primarily a bug fix release, the following changes have been made:
Changes
---
Fix PL/PgSQL SELECTs when returning no rows
Fix for psql backslash core dump
Referential integrity permission fix
Optimizer fixes
pg_dump cleanups
Like we try and maintain with all minor releases, this one is a purely
'plug-n-play' upgrade ... no changes were made that would require a
dump/restore of an existing v7.1.x system ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Trouble with strange OUTER JOIN syntax
Hi Tom, > > How would you translate the following SELECT statements with > > outer joins to PostgreSQL syntax? > > > SELECT ... > > FROM tab1 alias_tab1, tab2 alias_tab2, > >OUTER ot1 alias_ot1, > >OUTER ot2 alias_ot2 > > WHERE ... > > > SELECT > > FROM tab1 alias_tab1, tab2 alias_tab2, > >OUTER (ot1 alias_ot1, ot2 alias_ot2) > > WHERE ... > > Tell me what that means, and I'll tell you how to translate it. > What is being joined to what, on what keys, and which side is > allowed to become null in the join? Having examined the statements in more detail, it seems to me like some kind of "multijoin" is required here: * outer-joining more than one table to a primary table: SELECT ... FROM tabmain, OUTER tab2, OUTER tab3 Here, I'll guess that both tab2 and tab3 are being outer-joined to tabmain. Therefore tab2 and tab3 columns are allowed to be null, whereas tabmain column's are not. * outer-joining one (or more than one) table to a cartesian product of other tables: SELECT ... FROM tab1, tab2, OUTER tab3 SELECT ... FROM tab1, tab2, OUTER (tab3, tab4) SELECT ... FROM tab1, tab2, OUTER tab3, OUTER tab4 In the first example, tab3 is being joind to the cartesian product (inner join) of both tab1 and tab2. Therefore tab1 x tab2 is not allowed to be null, whereas tab3 is allowed to be. The next examples seem to generalize this: two tables (tab3 and tab4) are being outer-joined to existing cartesian product tab1 x tab2. I'm not sure what the difference may be between: OUTER (tab3, tab4) and OUTER tab3, OUTER tab4. If PostgreSQL doesn't support this feature (yet?), I'll have to simulate it programatically. I just hoped to avoid the trouble of doing so, because the program I'm porting contains a lot of such "multijoins". Thanks for your help, -Farid. -- Farid Hajji -- Unix Systems and Network Admin | Phone: +49-2131-67-555 Broicherdorfstr. 83, D-41564 Kaarst, Germany | [EMAIL PROTECTED] - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - One OS To Rule Them All And In The Darkness Bind Them... --Bill Gates. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] COPY with default values?
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 <[EMAIL PROTECTED]>
To: Postgres-general <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]> 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. [EMAIL PROTECTED]
>
>
>---(end of broadcast)---
>TIP 2: you can get off all lists at once with the unregister command
>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Username with a hyphen character (grant failing?)
> > What's the scoop with username constraints? I setup and > > created a user with a '-' character in the middle (worked). > > Double quotes are your friend when dealing with names that don't > follow the usual constraints for identifiers. Are single quotes treated differently than double? I tried single, but didn't have any luck... -sc -- Sean Chittenden PGP signature ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] COPY with default values?
On Fri, 25 May 2001 10:33:41 -0400
Tom Lane <[EMAIL PROTECTED]> 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. [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] COPY with default values?
Jeff Boes <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] [URGENT] How to generata a unique string id
Hi, I need to generate a unique four letters string (p.e ,AAAB) for any tuple I insert into a table, any idea ? How can I do this ? Thanks. -- Manuel Durán Aguete ALSERNET 2000 S.L http://www.alsernet.es Tlf: 902 187 187 Fax: 981 179 121 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Username with a hyphen character (grant failing?)
Sean Chittenden <[EMAIL PROTECTED]> writes: > What's the scoop with username constraints? I setup and > created a user with a '-' character in the middle (worked). Double quotes are your friend when dealing with names that don't follow the usual constraints for identifiers. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 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 <[EMAIL PROTECTED]>
To: Postgres-general <[EMAIL PROTECTED]>
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. [EMAIL PROTECTED]
>
>
>---(end of broadcast)---
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[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. [EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] pl-perl setup?
Hello, I'm fairly new to PostgreSQL. Does anyone have a resource for setting up plperl from scratch? The online documentation doesn't offer much help. -- Jeff Boes vox 616.226.9550 Database Engineer fax 616.349.9076 Nexcerpt, Inc. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Transactions and *ABORT STATE*
I were testing out (using psql) transactions and locking in postgresql using only BEGIN/UPDATE(on a specific table)/COMMIT&ROLLBACK and notices several time that instead of waiting it went into *ABORT STATE*. Why is this? Also I notice that COMMIT'ing a deadlock'ed transaction did nothing but also did not return any error message. Why? Thank you very much! --- Daniel Akerud, [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Username with a hyphen character (grant failing?)
What's the scoop with username constraints? I setup and created a user with a '-' character in the middle (worked). When I tried to grant the new user some privs, grant choked on the '-' w/ a parse error. foo=# GRANT SELECT ON test TO www-foo; ERROR: parser: parse error at or near "-" foo=# update pg_shadow set usename = 'www_foo' where usename = 'www-foo'; UPDATE 1 foo=# GRANT SELECT ON maildir TO www_foo; CHANGE Did I miss a page someplace that said I couldn't use a '-' in a username, or is this a bug? -sc -- Sean Chittenden PGP signature ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] JDBC
Can someone confirm this as a bug or slap me for missing something obvious? # \d foo Table "foo" Attribute | Type | Modifier ---++-- bar | bigint | "UPDATE foo SET bar = 0" works from psql but when I try the same thing via the JDBC driver I get dtoi4: integer out of range ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Implicit row locking during an UPDATE
On Fri, May 25, 2001 at 02:23:50AM -, Dr. Evil wrote: > > I'm still not understanding this, and it's vitally important to the > project I'm working on, so I have a question: > > >From my understanding, this: > > UPDATE account SET value = 10 WHERE number = 99; > > actually implies all of this: > > BEGIN; > SELECT value FOR UPDATE FROM account WHERE number = 99; > UPDATE account SET value = 10 WHERE number = 99; > COMMIT; > END; > > Is this correct? If so, there's something I don't understand. No. Maybe you want to read the manual. Specifically look at transaction isolation level under MVCC. Hmm, SET TRANSACTION ISOLATION LEVEL doesn't seem to be taking in 7.1.1... Is that a bug? Guess the only way to be sure is to use the transaction semantics above... -- Eric G. Miller <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
