[GENERAL] PSQL suggested enhancement

[Dean Gibson (DB Administrator)]

PSQL has the option to output the result of queries in several different 
formats, including HTML.  Suggestion:  have an option to output query 
results in XML format.  Suggested format:


row
 field-1-namefield-1 value/field-1-name
 field-2-namefield-2 value/field-2-name
/row

etc.  The user would be responsible for adding the enclosing XML.

NULL values could be output as field-n-name /

Optionally, tags could contain an attribute describing the field type.

Or is there a way to do this I don't know about?

-- Dean


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] NULL != text ?

[CSN]

I was trying this:

IF (OLD.value != NEW.value) THEN
-- 
END IF;

and couldn't get the condition to evaluate to true at
all if OLD.value was NULL. I also tried:

IF (OLD.value NOT LIKE NEW.value) THEN
-- 
END IF;

with the same result. But this works:

IF ((OLD.value is NULL and NEW.value is NOT NULL) or
(OLD.value != NEW.value)) THEN
-- 
END IF;

So, does NULL != 'abc' always evaluate to false? The
manual
(http://www.postgresql.org/docs/8.0/interactive/functions-comparison.html)
states don't compare NULL values using =, but nothing
about using !=

CSN



__ 
Yahoo! Music Unlimited 
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

---(end of broadcast)---
TIP 1: 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: [HACKERS] 'a' == 'a ' (Was: RE: [pgsql-advocacy] [GENERAL]

[Tino Wildenhain]

Am Mittwoch, den 19.10.2005, 22:04 +0200 schrieb Tino Wildenhain:
 Am Mittwoch, den 19.10.2005, 16:29 -0300 schrieb Marc G. Fournier:
  I'm CC'ng this over to -hackers ... Tom?  Comments?
  
 ...

   Then we are broken too :)
  
   # select 'a ' = 'a  ';
 ?column?
   --
 f
   (1 row)
 
 
 experiment=# SELECT 'a '::char = 'a  '::char;
  ?column?
 --
  t
 
Sorry, copied wrong line :)

experiment=# SELECT 'a '::char(10) = 'a  '::char(10);
 ?column?
--
 t

and:

SELECT '|' || 'foo  '::char(10) || '|';
 ?column?
--
 |foo|


vs.


SELECT '|' || 'foo  ' || '|';
 ?column?
--
 |foo  |




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Greg Stark]

Tom Lane [EMAIL PROTECTED] writes:

 Chris Travers [EMAIL PROTECTED] writes:
  If I understand the spec correctly, it seems to indicate that this is 
  specific to the locale/character set.
 
 The spec associates padding behavior with collations, which per spec are
 separate from the datatypes --- that is, you should be able to able to
 specify a collation for each string-type table column (whether char(N)
 or varchar(N)) and even for each literal string constant.  We do not
 currently have that capability, and accordingly fall back to binding
 PAD SPACE behavior to char(N) and NO PAD behavior to varchar(N).
 
 AFAICS this choice is allowed by the spec since the default collation is
 implementation-defined.

Does it even make sense for char(N) to not be space padded? I had the
impression char(N) was always N characters long, not more or less. I can't
picture any other character being used for padding, then you would need a more
flexible rtrim function.

And I can understand the collation order determining whether 'a' and 'a '
compare equal. But surely if you store 'a' in a varchar(N) you have to get 'a'
back out, not some other string! Does the spec really allow varchar to
actually be padded and not just compare ignoring trailing space?


(I can't believe anyone really wants varchar to be space padded. Space padding
always seemed like a legacy feature for databases with fixed record length
data types. Why would anyone want a string data type that can't represent all
strings?)

-- 
greg


---(end of broadcast)---
TIP 1: 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] PSQL suggested enhancement

[Michael Glaesemann]

On Oct 20, 2005, at 14:50 , Dean Gibson (DB Administrator) wrote:

PSQL has the option to output the result of queries in several  
different formats, including HTML.  Suggestion:  have an option to  
output query results in XML format.  Suggested format:


My personal opinion on this is that there are a lot of different  
ideas about how the XML should/could be written, and a the current  
output can be piped to a script in insert favorite scripting  
language here to format to match anyone's wish. Rather than have  
psql decree the one and true format or include so many as to make  
maintainability burdensome, further formatting is best left to the  
end user.


My 2 yen.

Michael Glaesemann
grzm myrealbox com




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Reverse engineering SW

[Tino Wildenhain]

Am Mittwoch, den 19.10.2005, 21:05 +0100 schrieb Ledina Hido:
 On 19 Oct 2005, at 16:05, codeWarrior wrote:
 
  You can also reverse engineer a postgreSQL RDBMS using an ODBC  
  driver and
  MicroSloth's Visio.

Were you sucessfully w/ it? If so, which exact versions of all?
(PG, Visio, ODBC)

btw. DB Visualizer has a very nice reverse engineering.


---(end of broadcast)---
TIP 1: 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] NULL != text ?

[Michael Glaesemann]

On Oct 20, 2005, at 15:04 , CSN wrote:


So, does NULL != 'abc' always evaluate to false? The
manual
(http://www.postgresql.org/docs/8.0/interactive/functions- 
comparison.html)

states don't compare NULL values using =, but nothing
about using !=


The SQL standard way of checking for NULL is using IS NULL or IS NOT  
NULL. NULL is unknown. You can't meaningfully compare with something  
that is unknown, so you can't use = or  (or it's alternate  
spelling !=) to find out if something is NULL. Comparison with NULL  
on one side of the comparison will result in NULL (*not* FALSE). For  
a little fun (OK, I have to be a bit of a geek to call it that...)  
with comparisons, see the end of this email.


I do my best to not allow any NULLs in my database schema, i.e.,  
always use NOT NULL in table definitions, (I can't remember the last  
time I didn't), which neatly avoids this problem entirely :) However,  
given your schema, I'd try


if (OLD.value IS NOT NULL and NEW.value IS NOT NULL and OLD.value   
NEW.value) or OLD.value IS NULL or NEW.value IS NULL


But that's untested and I have a hard time thinking in three-value  
logic.


Hope this helps.

Michael Glaesemann
grzm myrealbox com



test=# select 1 = 1;
?column?
--
t
(1 row)

test=# select 1 = 2;
?column?
--
f
(1 row)

test=# select (1  NULL) IS NULL;
?column?
--
t
(1 row)

test=# select (NULL = NULL) IS NULL;
?column?
--
t
(1 row)

test=# select (0  NULL) IS NULL;
?column?
--
t
(1 row)

test=# select (NULL IS NULL);
?column?
--
t
(1 row)

test=# select (NULL IS NOT NULL);
?column?
--
f
(1 row)



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] NULL != text ?

[Michael Fuhr]

On Wed, Oct 19, 2005 at 11:04:36PM -0700, CSN wrote:
 So, does NULL != 'abc' always evaluate to false?

It never evaluates to false -- it evaluates to NULL.

http://www.postgresql.org/docs/8.0/interactive/functions-comparison.html

  The ordinary comparison operators yield null (signifying unknown)
  when either input is null.  Another way to do comparisons is with the
  IS DISTINCT FROM construct:

  expression IS DISTINCT FROM expression

  For non-null inputs this is the same as the  operator.  However,
  when both inputs are null it will return false, and when just one
  input is null it will return true.  Thus it effectively acts as
  though null were a normal data value, rather than unknown.

Examples:

test= SELECT NULL = 'abc';
 ?column? 
--
 
(1 row)

test= SELECT NULL  'abc';
 ?column? 
--
 
(1 row)

test= SELECT NULL IS DISTINCT FROM 'abc';
 ?column? 
--
 t
(1 row)

test= SELECT NULL IS DISTINCT FROM NULL;
 ?column? 
--
 f
(1 row)

-- 
Michael Fuhr

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] PSQL suggested enhancement

[Roger Hand]

 -Original Message-
 From: [EMAIL PROTECTED]
 [mailto:[EMAIL PROTECTED] Behalf Of Michael
 Glaesemann
 Sent: Wednesday, October 19, 2005 11:24 PM

 On Oct 20, 2005, at 14:50 , Dean Gibson (DB Administrator) wrote:
 PSQL has the option to output the result of queries in several  
 different formats, including HTML.  Suggestion:  have an option to  
 output query results in XML format.  
 
 My personal opinion on this is that there are a lot of different  
 ideas about how the XML should/could be written, and a the current  
 output can be piped to a script in insert favorite scripting  
 language here to format to match anyone's wish. Rather than have  
 psql decree the one and true format or include so many as to make  
 maintainability burdensome, further formatting is best left to the  
 end user.

By that reasoning there shouldn't be html format either.

The beauty of xml output is that it can easily be processed 
via xslt to create whatever format the user wants. So there need not
be one true format but simply one root format that could be
easily transformed.

-Roger

 Michael Glaesemann

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] PSQL suggested enhancement

[Michael Glaesemann]

On Oct 20, 2005, at 15:45 , Roger Hand wrote:


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Michael
Glaesemann
Sent: Wednesday, October 19, 2005 11:24 PM





On Oct 20, 2005, at 14:50 , Dean Gibson (DB Administrator) wrote:


PSQL has the option to output the result of queries in several
different formats, including HTML.  Suggestion:  have an option to
output query results in XML format.



My personal opinion on this is that there are a lot of different
ideas about how the XML should/could be written, and a the current
output can be piped to a script in insert favorite scripting
language here to format to match anyone's wish. Rather than have
psql decree the one and true format or include so many as to make
maintainability burdensome, further formatting is best left to the
end user.



By that reasoning there shouldn't be html format either.


Agreed :) I'd submit a patch to remove it if I thought it'd be  
accepted--and more importantly, if I thought it were worth the time  
it'd take me (I'm still learning C, and the little bit of PostgreSQL  
code I'm familiar with is not the psql client) (excuses excuses :).  
Though justification for removing a feature is different from that of  
adding or extending one: removing one breaks backwards compatibility.


Michael Glaesemann
grzm myrealbox com




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] NULL != text ?

[Michael Glaesemann]

On Oct 20, 2005, at 15:44 , Michael Fuhr wrote:


  expression IS DISTINCT FROM expression

  For non-null inputs this is the same as the  operator.  However,
  when both inputs are null it will return false, and when just one
  input is null it will return true.  Thus it effectively acts as
  though null were a normal data value, rather than unknown.



Interesting! Thanks, Michael. You don't happen to know off the top of  
your head if that's standard SQL, do you?


Michael Glaesemann
grzm myrealbox com




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] Create GLOBAL TABLE

[Richard Huxton]

Marius Cornea wrote:

1.The sintax for create table is :
   CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ 
... ] ] ...


What mean the parameter GLOBAL|LOCAL ??


http://www.postgresql.org/docs/8.0/static/sql-createtable.html

Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. 
This makes no difference in PostgreSQL, but see Compatibility.



2. in pg_class it is a field relisshared how can i use it ?
3.1 how can I use BKI script ?,
3.2 it is posible to modify postgres.bki to create another table with 
initdb script ?

like: create bootstrap shared_relation .. ?


It almost certainly *is* possible to add your own system table. However, 
that's probably not something to do without taking time to make sure you 
know what you're doing. It will also mean your PostgreSQL installation 
will be different from everyone else's.


First thing to do is search the mailing list archives and see if anyone 
else has done the same.

  http://archives.postgresql.org/

Then, I'd see how an existing table was added (say pg_authid which I 
think is new in 8.1 to handle roles). There is a new cvs browser from 
command-prompt - details here.

  http://archives.postgresql.org/pgsql-general/2005-10/msg00852.php

Not sure if you might want to look at whether you need to allocate 
another fixed OID for your new shared table too.


Check it looks possible and then post details of your plan on the 
hackers list and see what they say.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] SQL return value...

[Richard Huxton]

Cristian Prieto wrote:

This is maybe a really newbie question, but, when I have an SQL function
like that:

$$
Insert into mytable (id, name) values ($1, $2);
$$

What return value suppose to return?


Either return void or return boolean and add a SELECT true; 
statement to the end of your function.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] server , client encoding issue

[Richard Huxton]

surabhi.ahuja wrote:

i checked the locale it is giving:
 
 LANG=en_US.iso885915

LC_CTYPE=en_US.iso885915


If you Google for ISO-8859-15 Latin9 the top two results seem to give 
details. Oh - there are two naming systems for character sets, just to 
make things even more complicated.


Now, traditionally you'd have used Latin1 (ISO-8859-1), but the 
introduction of the Euro meant they needed to introduce a new character. 
They took the opportunity to make some other changes too and called the 
results Latin9 (ISO-8859-15).


OK - now the original problem was with a database not having a UNICODE 
encoding. It does look like this is because the environment on this 
machine is Latin9 rather than UTF-8. It's easy to have this problem, and 
I always recommend setting the encoding explicitly when creating a 
database cluster (initdb --encoding=UTF8). If you installed from a 
package, it might have chosen a default for you though.


HTH
--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] From oracle to postgresql...

[Richard Huxton]

Rafael Montoya wrote:
  I need to know if there is a tool that convert oracle procedures and 
triggers to plpgsql syntax. Please, can anybody tell me where do i 
download it from?, i'll thank you a lot.


I don't know of a free tool that will do procedures. Probably worth 
reading through Oracle notes here:

  http://techdocs.postgresql.org/

Also - search for ora2pg - might be useful.

If you have money to spend, it might be worth checking out EnterpriseDB 
- they claim to have Oracle compatibility. News item/company site below.

  http://www.postgresql.org/about/news.367
  http://www.enterprisedb.com/

HTH
--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: 'a' == 'a ' (Was: RE: [pgsql-advocacy] [GENERAL] Oracle buysInnobase)

[Richard Huxton]

Dann Corbit wrote:


I can see plenty of harm and absolutely no return.  We are talking about
blank padding before comparison.  Do you really want 'Danniel '
considered distinct from 'Danniel  ' in a comparison?  In real life,
what does that buy you?


100% YES!

If two values are the same, then any function on those two values should 
return the same result. Otherwise what does equals mean? At the very 
least length() is broken by your argument.


Here it's CHAR that's broken IMHO - spawn of some punch-card spawned 
data processing rules of the 70s.

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Select all invalid e-mail addresses

[Hannes Dorbath]

On 19.10.2005 21:18, Michael Fuhr wrote:

One possibility would be to write a plperlu function that uses the
Email::Valid module.  Here's a trivial example; see the Email::Valid
documentation to learn about its full capabilities:


..and if you don't mind installing pl/php, you can use this function:

http://hannes.imos.net/validmail.html

It performs a MX-lookup, which IMHO is the best way to check for validity.


--
Regards,
Hannes Dorbath

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] log_min_duration_statement oddity

[Csaba Nagy]

Thanks Tom, now at least I can stop chasing what I'm doing wrong :-)

BTW, will be a way to also log the parameter values for prepared
statements ? While debugging performance problems it would be
invaluable, in many cases it would help me reproduce the problem when
only SOME values cause problems.

Thanks,
Csaba.


On Wed, 2005-10-19 at 20:22, Tom Lane wrote:
 Csaba Nagy [EMAIL PROTECTED] writes:
  Now the remote connections are coming from Java (the JDBC driver),
 
 Oh, there's your problem.  8.0 doesn't have very good support for
 logging the extended-query protocol, which is what recent versions
 of the JDBC driver like to use.  8.1 will be better I believe.
 
 If you're desperate to get some info, you could force the driver to
 fall back to V2 frontend protocol.  See the archives for details.
 
   regards, tom lane


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Reverse engineering SW

[Hannes Dorbath]

On 19.10.2005 05:16, Bruno Cochofel wrote:

His there any SW that can do reverse engineering on postgreSQL
databases? I need something that can create entity-relationship models
from an already made db, and something that can create a db from a model.


Casestudio does this very nice.


--
Regards,
Hannes Dorbath

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] PSQL suggested enhancement

[Martijn van Oosterhout]

On Thu, Oct 20, 2005 at 03:23:51PM +0900, Michael Glaesemann wrote:
 My personal opinion on this is that there are a lot of different  
 ideas about how the XML should/could be written, and a the current  
 output can be piped to a script in insert favorite scripting  
 language here to format to match anyone's wish. Rather than have  
 psql decree the one and true format or include so many as to make  
 maintainability burdensome, further formatting is best left to the  
 end user.

IMHO, we should look for an OpenDocument like standard for spreadsheets
and/or tables. If you pick something like that then you have a higher
chance it can be imported directly into applications, which at the end
of the day is what people want, right?

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgphrCL6IDueB.pgp
Description: PGP signature

[GENERAL] How to check is the table system

[Андрей]

Hello!

How can I get table's comment, created like this:
  COMMENT ON TABLE people IS '...comment...' ?
What system table keeps comments on databases, schemas and tables?

   Big Thanks,
   Andrei



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] doc typo sql-reindex.html psql vs. postgres

[Janning Vygen]

At

 http://www.postgresql.org/docs/8.0/static/sql-reindex.html

it says:

 Rebuild all system indexes in a particular database, 
 without trusting them to be valid already: 

 $ export PGOPTIONS=-P
 $ psql broken_db

It should be:

 Rebuild all system indexes in a particular database, 
 without trusting them to be valid already: 

 $ export PGOPTIONS=-P
 $ postgres broken_db
   
,shouldn't it?

kind regards,
janning

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Reverse engineering SW

[Tino Wildenhain]

Hannes Dorbath schrieb:

On 19.10.2005 05:16, Bruno Cochofel wrote:


His there any SW that can do reverse engineering on postgreSQL
databases? I need something that can create entity-relationship models
from an already made db, and something that can create a db from a model.



Casestudio does this very nice.


Yes, the latter it does very nice. But the former not so - it lacks
an autorouter and you have a hard time to move table objects around
until you can see something ;)


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] NULL != text ?

[Alban Hertroys]

Michael Glaesemann wrote:
if (OLD.value IS NOT NULL and NEW.value IS NOT NULL and OLD.value   
NEW.value) or OLD.value IS NULL or NEW.value IS NULL


But that's untested and I have a hard time thinking in three-value  logic.


For completeness sake; Because of lazy evaluation, that boils down to:

if (OLD.value IS NULL OR NEW.value IS NULL OR OLD.value  NEW.value)

The last part of the expression is only evaluated if both OLD.value and 
NEW.value aren't NULL.


--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

//Showing your Vision to the World//

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] server , client encoding issue

[surabhi.ahuja]

Title: Re: [GENERAL] server , client encoding issue






how can i change the client 
encoding to LATIN1?
i know it can be done by changing the 
postgresql.conf

but i want to include it in the code 
...

is it possible that PQconnectdb can take a 
parameter for client encoding if yes how??

if PQconnectdb can not handle it ..please 
tell as to how which is the method i can use for doing the 
above


From: Richard Huxton 
[mailto:[EMAIL PROTECTED]Sent: Thu 10/20/2005 1:00 PMTo: 
surabhi.ahujaCc: pgsql-general@postgresql.orgSubject: Re: 
[GENERAL] server , client encoding issue

***Your mail has been scanned by 
InterScan VirusWall.***-***surabhi.ahuja 
wrote: i checked the locale it is giving: 
LANG=en_US.iso885915 LC_CTYPE="en_US.iso885915"If you Google for 
"ISO-8859-15 Latin9" the top two results seem to givedetails. Oh - there are 
two naming systems for character sets, just tomake things even more 
complicated.Now, traditionally you'd have used Latin1 (ISO-8859-1), but 
theintroduction of the Euro meant they needed to introduce a new 
character.They took the opportunity to make some other changes too and 
called theresults Latin9 (ISO-8859-15).OK - now the original problem 
was with a database not having a UNICODEencoding. It does look like this is 
because the environment on thismachine is Latin9 rather than UTF-8. It's 
easy to have this problem, andI always recommend setting the encoding 
explicitly when creating adatabase cluster (initdb --encoding=UTF8). If you 
installed from apackage, it might have chosen a default for you 
though.HTH-- Richard Huxton Archonet 
Ltd

[GENERAL] versions of oDBC driver

[Zlatko Matić]

Hello.

Could someone say which versions of ODBC drivers 
are recommended for PostgreSQL/MS Access 2003 combination, for:
a) Postgres 8.0.4 
b) Postgres 8.1 beta

Namely, I was not able to connect from my Access 
front-end when I migrated from Postgres 8.0.4 to Postgres 8.1 
beta3
Arethere any significant changes that could 
cause such problems in connection strings ?

Zlatko

Re: [GENERAL] server , client encoding issue

[Martijn van Oosterhout]

On Thu, Oct 20, 2005 at 03:41:51PM +0530, surabhi.ahuja wrote:
 how can i change the client encoding to LATIN1?
 i know it can be done by changing the postgresql.conf

Send the query:

set client_encoding=latin1;

Have a nice day,

-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpD01K3pQByJ.pgp
Description: PGP signature

Re: [GENERAL] PostgreSQL on Dual Processors, Dual-Core AMD Chips

In article [EMAIL PROTECTED],
Tony Caduto [EMAIL PROTECTED] wrote:

I believe that as each process(backend) is created it will get assigned 
to a CPU.

At least with Linux the process can run on any CPU. It isn't
restricted to some assignment at the time of its creation.

--
http://yosemitenews.info/

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Checking Multiplicity Constraints and Retrieving Data from Error Messages

[Ledina Hido]

As part of my 4th Year Group Design Project, I am required to build a  
database system that will validate and then store the data. As such I  
am currently investigating different DB, to choose the most suitable  
one. I liked many features of PostgreSQL (eg deferring transactions)  
but there are a couple of things I am not very clear about that I  
would need for the project.


First of all, is there any way of limiting the number of rows in a  
table, referencing to the same element of another table? For example,  
force a manager not to have more than 10 employees under his control.  
In a way this can be seen as checking the multiplicity of the  
relation between the two tables. I know one way would be using  
triggers, but I was wondering if there was a way of specifying this  
when the table is constructed.


Second, is there any way of getting more details out of an error  
message? So for example, when doing a bulk upload to the database,  
rather than just getting Cannot add or update a child row: a foreign  
key constraint fails I would like to know which particular insert  
statement (out of the 1000 I have) caused the problem, or which field  
in this statement broke the constraint.


Any help would be much appreciated.

Ledina



---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] difficulty formating interval datatypes in 7.4

[Chris Matheson]

Hello list,

I am working to format an interval in using the to_char() SQL function 
on postgresql 7.4.8.  I've had nothing but disapointment so far.
My confusion occurs when I'm trying to format using days where the days 
output would be more than 99.  For example:

I would like to do something like
SELECT to_char('01-JAN-2001'::timestamp - '01-JAN-2000'::timestamp, 
'ddd days hh24 hours');

To get this
   '365 days 00 hours'
Currently I get this
   '335 days 00 hours'
I know that 'ddd' doesn't exist in the sense I'm thinking, but, it seems 
that either I've missed something or that to_char function wasn't 
designed with intervals in mind.  The 7.4 manual lists the following
Warning: |to_char|(interval, text) is deprecated and should not be used 
in newly-written code. It will be removed in the next version.

But doesn't say what the alternative is.  Thanks for your help.

Chris M.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] Select all invalid e-mail addresses

[Edmund]

Andrus [EMAIL PROTECTED] writes:

 I have a database of e-mail addresses.
 
 I want to select the email addresses which are not valid:
 
 do not contain exactly one @ character,
 contain ;' , characters or spaces etc.
 
 What is the WHERE clause for this ?
 

There was a thread here not so long ago about matching valid email addresses.
It's not so simple.  You probably want to do a regex match - e.g.

select ... where email ~ 'regex'

However the regex for all valid email possibilities is *VERY* complex.
see: http://www.ex-parrot.com/~pdw/Mail-RFC822-Address.html

You should probably search the maillist archives.  ISTR that there were
some suggestions on how one might simplify the search space.



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Checking Multiplicity Constraints and Retrieving Data from Error Messages

[Martijn van Oosterhout]

On Wed, Oct 19, 2005 at 03:25:25PM +0100, Ledina Hido wrote:
 First of all, is there any way of limiting the number of rows in a  
 table, referencing to the same element of another table? For example,  
 force a manager not to have more than 10 employees under his control.  
 In a way this can be seen as checking the multiplicity of the  
 relation between the two tables. I know one way would be using  
 triggers, but I was wondering if there was a way of specifying this  
 when the table is constructed.

You may be looking for CHECK constraints, although they are really just
a kind of trigger.

Note, there are two sides to such a trigger. You need a trigger on the
employees table to check that the limit is not exceeded during an
insert (presumably you don't need to check deletes). OTOH, you need a
trigger on the manager table so if someone changes the limit down, you
don't get caught out.

You can specify CHECK during table creation, but not triggers.

 Second, is there any way of getting more details out of an error  
 message? So for example, when doing a bulk upload to the database,  
 rather than just getting Cannot add or update a child row: a foreign  
 key constraint fails I would like to know which particular insert  
 statement (out of the 1000 I have) caused the problem, or which field  
 in this statement broke the constraint.

What version? At least some recent versions specify the row that failed
and even the character, though I couldn't say when that was added...

Hope this helps,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpyfK7EKhC0Z.pgp
Description: PGP signature

Re: [GENERAL] Checking Multiplicity Constraints and Retrieving Data from Error Messages

[Ledina Hido]

On 20 Oct 2005, at 12:31, Martijn van Oosterhout wrote:

On Wed, Oct 19, 2005 at 03:25:25PM +0100, Ledina Hido wrote:


First of all, is there any way of limiting the number of rows in a
table, referencing to the same element of another table? For example,
force a manager not to have more than 10 employees under his control.
In a way this can be seen as checking the multiplicity of the
relation between the two tables. I know one way would be using
triggers, but I was wondering if there was a way of specifying this
when the table is constructed.



You may be looking for CHECK constraints, although they are really  
just

a kind of trigger.

Note, there are two sides to such a trigger. You need a trigger on the
employees table to check that the limit is not exceeded during an
insert (presumably you don't need to check deletes). OTOH, you need a
trigger on the manager table so if someone changes the limit down, you
don't get caught out.

You can specify CHECK during table creation, but not triggers.



I am not sure if CHECK constraints will work, as I don't think you  
can reference another table in one of those. And I think it might  
even not let you have a subquery (ie a select inside the check  
statement). So I don't know whether I would be able to use CHECK for  
that. Or am I being stupid and you actually can?




Second, is there any way of getting more details out of an error
message? So for example, when doing a bulk upload to the database,
rather than just getting Cannot add or update a child row: a foreign
key constraint fails I would like to know which particular insert
statement (out of the 1000 I have) caused the problem, or which field
in this statement broke the constraint.



What version? At least some recent versions specify the row that  
failed

and even the character, though I couldn't say when that was added...



It wasn't a particular version. I haven't really used PostgreSQL yet,  
I only set it up on my system yesterday. I do now have the latest  
version for Macs, not exactly sure which one it is. But if it is true  
that it does specify the row and character as you said, that's great  
news.


Thanks for your help.
Ledina

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] Select all invalid e-mail addresses

[Douglas McNaught]

Hannes Dorbath [EMAIL PROTECTED] writes:

 On 19.10.2005 21:18, Michael Fuhr wrote:
 One possibility would be to write a plperlu function that uses the
 Email::Valid module.  Here's a trivial example; see the Email::Valid
 documentation to learn about its full capabilities:

 ..and if you don't mind installing pl/php, you can use this function:

 http://hannes.imos.net/validmail.html

 It performs a MX-lookup, which IMHO is the best way to check for validity.

But that's expensive and slow, and doesn't tell you whether the user
part of the address is valid (and in general, there's no way to
determine that short of actually sending a message).  So what's the
point?

-Doug

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Checking Multiplicity Constraints and Retrieving Data from Error Messages

[Martijn van Oosterhout]

On Thu, Oct 20, 2005 at 12:44:53PM +0100, Ledina Hido wrote:
 I am not sure if CHECK constraints will work, as I don't think you  
 can reference another table in one of those. And I think it might  
 even not let you have a subquery (ie a select inside the check  
 statement). So I don't know whether I would be able to use CHECK for  
 that. Or am I being stupid and you actually can?

Well, you'll need to make a function that does the work and specify
that function as the check constraint. It's true that directly you
can't specify other tables. PostgreSQL can see inside functions to see
what you are doing...

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpQpYklyksS9.pgp
Description: PGP signature

Re: [GENERAL] Select all invalid e-mail addresses

[Hannes Dorbath]

On 20.10.2005 14:00, Douglas McNaught wrote:

But that's expensive and slow


Sure, that isn't meant to be used in a WHERE condition on a 100k row 
table.. more to be bound via check constraint on a user table, so 
incomming data is validated.



and doesn't tell you whether the user part of the address is valid (and in 
general, there's no way to
determine that short of actually sending a message). So what's the
point?


The point is to validate as good as possible, and as you said yourself, 
the user part can't be validated further.



--
Regards,
Hannes Dorbath

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Jan Wieck]

On 10/20/2005 2:17 AM, Greg Stark wrote:


(I can't believe anyone really wants varchar to be space padded. Space padding
always seemed like a legacy feature for databases with fixed record length
data types. Why would anyone want a string data type that can't represent all
strings?)


They must have buried that bow to COBOL so deep in the code that they 
had no choice but to abuse their power and stuff this cruft into the 
standard.



Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

[GENERAL] NEW in Rule makes another nextval call?

[Sven Willenberger]

On a table (customer) I have a rule set up that is designed to update
a contacts table  with a customer id once the customer is added to the
customer table. (Yes, this does seem backwards but it has to do with the
way this system of web-based signups gets translated into a customer
record).

CREATE TABLE customer (
custid serial primary key,
custname text not null,
startdate timestamp,
agent int);

CREATE RULE newcustomer AS
ON INSERT TO customer DO
UPDATE contacts SET custid = NEW.custid
WHERE contact.custname = NEW.custname;

Now when a new record is inserted into customer it gets a custid from
the nextval(sequence name) call; let's say it gets the value 296. What
I notice is that in the contacts table, the customer id ends up being
297; it's as if the rule's SET command is using the nextval() call
instead of the value retrieved by the actual insert command when
updating the contacts table. Is this intended behavior? or is the NEW
acting as a macro that is replace by nextval(sequence name) ?

Sven


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] How to check is the table system

[Michael Glaesemann]

On Oct 20, 2005, at 18:22 , Андрей wrote:


How can I get table's comment, created like this:
  COMMENT ON TABLE people IS '...comment...' ?
What system table keeps comments on databases, schemas and tables?


Here are a couple of links to documents that might help.

http://www.postgresql.org/docs/8.0/interactive/functions- 
info.html#FUNCTIONS-INFO-COMMENT-TABLE

http://www.postgresql.org/docs/8.0/interactive/catalogs.html

Michael Glaesemann
grzm myrealbox com




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Andrew Kelly]

On Thu, 2005-10-20 at 08:14 -0400, Jan Wieck wrote:
 On 10/20/2005 2:17 AM, Greg Stark wrote:
 
  (I can't believe anyone really wants varchar to be space padded. Space 
  padding
  always seemed like a legacy feature for databases with fixed record length
  data types. Why would anyone want a string data type that can't represent 
  all
  strings?)
 
 They must have buried that bow to COBOL so deep in the code that they 
 had no choice but to abuse their power and stuff this cruft into the 
 standard.
 

MOVE SPACES TO 0101

You just gotta love it

Andy


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] NEW in Rule makes another nextval call?

[Richard Huxton]

Sven Willenberger wrote:

 Is this intended behavior? or is the NEW
acting as a macro that is replace by nextval(sequence name) ?


Well, it's understood behaviour even if not quite intended.

You are quite right, rules basically act like macros with all the 
limitations they have. What is actually happening behind the scenes is 
that the query is being rewritten to alter the query-plan tree. You 
should be able to get your rule to work by referring to 
currval(sequence-name) rather than NEW.custid.


However, in this particular case I think you want an after insert 
trigger on customer rather than a rule.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] doc typo sql-reindex.html psql vs. postgres

[Tom Lane]

Janning Vygen [EMAIL PROTECTED] writes:
 At
  http://www.postgresql.org/docs/8.0/static/sql-reindex.html

 it says:

  Rebuild all system indexes in a particular database, 
  without trusting them to be valid already: 

  $ export PGOPTIONS=-P
  $ psql broken_db

 It should be:

  Rebuild all system indexes in a particular database, 
  without trusting them to be valid already: 

  $ export PGOPTIONS=-P
  $ postgres broken_db

 ,shouldn't it?

No, it's correct as it stands.  You used to need a standalone backend
for that, but not anymore.  (PGOPTIONS isn't recognized by a standalone
backend anyway, IIRC, so there would be more wrong with the example than
that if it were wrong.)

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] NULL != text ?

[Tom Lane]

Alban Hertroys [EMAIL PROTECTED] writes:
 Michael Glaesemann wrote:
 if (OLD.value IS NOT NULL and NEW.value IS NOT NULL and OLD.value   
 NEW.value) or OLD.value IS NULL or NEW.value IS NULL
 
 But that's untested and I have a hard time thinking in three-value  logic.

 For completeness sake; Because of lazy evaluation, that boils down to:

 if (OLD.value IS NULL OR NEW.value IS NULL OR OLD.value  NEW.value)

 The last part of the expression is only evaluated if both OLD.value and 
 NEW.value aren't NULL.

Wrong.  SQL doesn't guarantee lazy evaluation.  The above will work,
but it's because TRUE OR NULL is TRUE, not because anything is promised
about evaluation order.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] server , client encoding issue

[Tom Lane]

Martijn van Oosterhout kleptog@svana.org writes:
 On Thu, Oct 20, 2005 at 03:41:51PM +0530, surabhi.ahuja wrote:
 how can i change the client encoding to LATIN1?

 Send the query:
 set client_encoding=latin1;

Also, whatever client-side library you're using may have alternative
ways to specify the same thing.  libpq for instance looks for a
PGCLIENTENCODING environment variable.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: 'a' == 'a ' (Was: RE: [pgsql-advocacy] [GENERAL] Oracle buysInnobase)

[Rick Morris]

Richard Huxton wrote:

Dann Corbit wrote:



I can see plenty of harm and absolutely no return.  We are talking about
blank padding before comparison.  Do you really want 'Danniel '
considered distinct from 'Danniel  ' in a comparison?  In real life,
what does that buy you?



100% YES!

If two values are the same, then any function on those two values should 
return the same result. Otherwise what does equals mean? At the very 
least length() is broken by your argument.


I agree completely. I would much rather be precise than intuitive. And, 
I have done applications where the padding length was important, 
especially when working with remote batch processing in the credit 
industries and the like. Writing queries to create and process 
fixed-width batch files is much easier if you can rely on these kinds of 
behaviors.




Here it's CHAR that's broken IMHO - spawn of some punch-card spawned 
data processing rules of the 70s.

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster





---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] NEW in Rule makes another nextval call?

[Sven Willenberger]

On Thu, 2005-10-20 at 15:01 +0100, Richard Huxton wrote:
 Sven Willenberger wrote:
   Is this intended behavior? or is the NEW
  acting as a macro that is replace by nextval(sequence name) ?
 
 Well, it's understood behaviour even if not quite intended.
 
 You are quite right, rules basically act like macros with all the 
 limitations they have. What is actually happening behind the scenes is 
 that the query is being rewritten to alter the query-plan tree. You 
 should be able to get your rule to work by referring to 
 currval(sequence-name) rather than NEW.custid.
 
 However, in this particular case I think you want an after insert 
 trigger on customer rather than a rule.
 

As as AFTER INSERT trigger, I can safely assume here that NEW.custid wil
now properly use the actual value of the custid rather than nextval()? I
have dropped the rule and created the AFTER INSERT trigger so I guess I
will find out shortly enough :-)

Thanks,

Sven


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] NEW in Rule makes another nextval call?

[Richard Huxton]

Sven Willenberger wrote:

On Thu, 2005-10-20 at 15:01 +0100, Richard Huxton wrote:
However, in this particular case I think you want an after insert 
trigger on customer rather than a rule.


As as AFTER INSERT trigger, I can safely assume here that NEW.custid wil
now properly use the actual value of the custid rather than nextval()? I
have dropped the rule and created the AFTER INSERT trigger so I guess I
will find out shortly enough :-)


An AFTER TRIGGER does nothing to the plan-tree, it just calls a function 
once per row (for a per-row trigger). You have two structures (NEW,OLD) 
with ordinary values in them.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] NULL != text ?

[Michael Fuhr]

On Thu, Oct 20, 2005 at 03:57:41PM +0900, Michael Glaesemann wrote:
 On Oct 20, 2005, at 15:44 , Michael Fuhr wrote:
   expression IS DISTINCT FROM expression
 
   For non-null inputs this is the same as the  operator.  However,
   when both inputs are null it will return false, and when just one
   input is null it will return true.  Thus it effectively acts as
   though null were a normal data value, rather than unknown.
 
 Interesting! Thanks, Michael. You don't happen to know off the top of  
 your head if that's standard SQL, do you?

IS DISTINCT FROM is defined in SQL:1999 and SQL:2003.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Lincoln Yeoh]

At 05:33 PM 10/19/2005 -0700, Dann Corbit wrote:

If there is a significant performance benefit to not expanding text 
columns in comparison operations, then it seems it should be OK.


I probably read the standard wrong, but it seems to me that varchar, char, 
and bpchar columns should all behave the same (e.g. if you do not expand 
with blank or the PAD character (whatever that is) then all char type 
columns should behave the same.  I guess that there could be different 
default collations for different column


I am not a DB guru. BUT IMO they should NOT behave the same.

Varchars should NOT be padded.

For the very reason when you select text out of varchar fields the result 
is not padded.


If I insert a string with a single trailing space into a varchar, I _want_ 
that single trailing space to still be there when I retrieve it, and not 
followed by more spaces. Otherwise I will have to pick a different database ;).


So similarly, I would expect that varchars 'a ' and 'a' when compared 
should be different.


However, in the case of _chars_ which are padded, then 'a ' should be 
padded so that it can be compared with 'a '.


Otherwise there will be no reason to do equality comparisons of char(5) 
fields with char(8) fields - they can NEVER be the same :).


But would that mean that when one does equality comparisons of varchars 
with chars, one would probably want padding? Or only varchars of the same 
length as the char would have a chance of matching?


Hmm.. I think I better leave this one to the DB gurus :). But I really 
don't ever want 'a ' to be the same as 'a   ' for varchars.


Link.


---(end of broadcast)---
TIP 1: 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: [HACKERS] 'a' == 'a ' (Was: RE: [pgsql-advocacy] [GENERAL]

[Kevin Grittner]

Dann Corbit wrote:
 Try this query in Oracle, SQL*Server, DB/2, Informix, etc.:
 
 connxdatasync=# select 1 where cast('a' as varchar(30)) = cast('a ' as
 varchar(30));
  ?column?
 --
 (0 rows)

For what it's worth, on Sybase ASE I get:

   
---
1  
(1 row affected)



---(end of broadcast)---
TIP 1: 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] Select all invalid e-mail addresses

[Andrus]

Guy Rouillier [EMAIL PROTECTED] wrote in message 
news:[EMAIL PROTECTED]
 Andrus wrote:
 I have a database of e-mail addresses.

 I want to select the email addresses which are not valid:

 do not contain exactly one @ character,
 contain ;' , characters or spaces etc.

 What is the WHERE clause for this ?

 Please see a long, detailed thread in the archives titled Email
 Verification Regular Expression on Sept 7, 2005.

Guy Rouillier,

thank you.

I have emails in CHARACTER(60) type columns in database, total 3000 emails.
I need to check email addresses for most frequent typos before send.
I have only plpgsql language installed, no perl.

From this thread I got the regular expression

 /[EMAIL PROTECTED]@(?:[EMAIL 
PROTECTED])?[a-z0-9-_]+\.(?:a[defgilmnoqrstuwz]|b[abdefghijmnorstvwyz]|c[acdfghiklmnoruvxyz]|d[ejkmoz]|e[ceghrst]|f[ijkmorx]|g[abdefhilmnpqrstuwy]|h[kmnrtu]|i[delnoqrst]|j[mop]|k[eghimnprwyz]|l[abcikrstuvy]|m[acdghklmnopqrstuvwxyz]|n[acefgilopruz]|om|p[aefghklmnrtwy]|qa|r[eouw]|s[abcdeghijklmnortvyz]|t[cdfghjkmnoprtvwz]|u[agkmsyz]|v[aceginu]|w[fs]|y[etu]|z[amw]|edu|com|net|org|gov|mil|info|biz|coop|museum|aero|name|pro)$/How
 I can use this in where clause ? I havent never used regular expressionsin 
Postgres.How to exclude top-level domain names from this regex ?Andrus.


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Select all invalid e-mail addresses

[Brian Mathis]

On 10/20/05, Douglas McNaught [EMAIL PROTECTED] wrote:
 It performs a MX-lookup, which IMHO is the best way to check for validity.But that's expensive and slow, and doesn't tell you whether the userpart of the address is valid (and in general, there's no way to
determine that short of actually sending a message).So what's thepoint?-Doug
That's why I think the better term for this is well formed.
Validity can only be determined by sending to it, but you can tell if
an address at least conforms to the specs programmatically. In
the end, when talking about a valid address in this context, that is
what most people are talking about.

The point is to weed out malformed email addresses, just like you would
enforce any other data formatting standards in other types of data.

Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase

[Andrew Sullivan]

On Wed, Oct 19, 2005 at 01:02:15PM -0300, Marc G. Fournier wrote:

 that idiocy is that a string with two blank characters is not equal to a
 string with a single blank character in Oracle.  'a  ' is not equal to 'a
 '.  'a ' is not equal to 'a'.  Port that to another database.  Seen the
 JOIN syntax? *sigh*
 
 Wait, I've lost something here, apparently ... but that is the case with 
 PostgreSQL as well:
 
 ams=# select ' a' = '  a';

Well, you didn't pick the same example, because leading blanks are
significant in the char() datatype:

andrewtest=# SELECT 'a '::char='a'::char;
 ?column? 
--
 t
(1 ligne)

But is it the case that Oracle doesn't treat that one any differently
from this:

andrewtest=# SELECT 'a'||NULL::char='a'::char;
 ?column? 
--
 
(1 ligne)

If that's the case, it's pretty odd.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
When my information changes, I alter my conclusions.  What do you do sir?
--attr. John Maynard Keynes

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

3-state logic (was: Re: [GENERAL] NULL != text ?)

[Alban Hertroys]

Tom Lane wrote:

Wrong.  SQL doesn't guarantee lazy evaluation.  The above will work,
but it's because TRUE OR NULL is TRUE, not because anything is promised
about evaluation order.


Learned something new again, then.

I also noticed FALSE OR NULL is NULL, which went against my intuition. I 
think I understand why:


- TRUE OR unknown can only evaluate to TRUE again; unknown is not 
relevant for the operation.
- FALSE OR unknown remains unknown, because unknown may be TRUE or 
it may not. If it is, then the result would be TRUE, but if it isn't it 
would be FALSE, but we don't know...


This 3-state logic can have some interesting results...

--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

//Showing your Vision to the World//

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] Select all invalid e-mail addresses

[Andrew Sullivan]

On Thu, Oct 20, 2005 at 06:10:40PM +0300, Andrus wrote:
 From this thread I got the regular expression

[snipped]

Note that that regular expression, which appears to be validating
TLDs as well, is incredibly fragile.  John Klensin has actually
written an RFC about this very problem.  Among other problems, what
do you do when a country code ceases to be?  (There's a similar
problem that the naming bodies struggke with from time to time.)

I suggest that if you want to validate TLDs, you pull them off when
you write the data in your database, and use a lookup table to make
sure they're valid (you can keep the table up to date regularly by
checking the official IANA registry for them).  At least that way you
don't have to change a regex every time ICANN decides to add another
TLD.  (The regex is wrong anyway, I think: it doesn't have .mobi,
which has been announced although isn't taking registrations yet, and
it doesn't appear to have arpa, either.)

A


-- 
Andrew Sullivan  | [EMAIL PROTECTED]
Information security isn't a technological problem.  It's an economics
problem.
--Bruce Schneier

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Select all invalid e-mail addresses

[Andrew Sullivan]

On Thu, Oct 20, 2005 at 11:22:25AM -0400, Brian Mathis wrote:

 That's why I think the better term for this is well formed. Validity can
 only be determined by sending to it, but you can tell if an address at least

In fact, it can only be determined by sending to it over and over
again, because whether a mail address works may change over time (and
may have nothing to do with the poor schmuck whose email
administrators don't know how to spell MX record).

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
--Dennis Ritchie

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] doc typo sql-reindex.html psql vs. postgres

[Janning Vygen]

Am Donnerstag, 20. Oktober 2005 16:04 schrieb Tom Lane:
 Janning Vygen [EMAIL PROTECTED] writes:
  it says:
   $ export PGOPTIONS=-P
   $ psql broken_db
 
  It should be:
   $ export PGOPTIONS=-P
   $ postgres broken_db

 No, it's correct as it stands.  You used to need a standalone backend
 for that, but not anymore.  (PGOPTIONS isn't recognized by a standalone
 backend anyway, IIRC, so there would be more wrong with the example than
 that if it were wrong.)

ok, sorry. I had some corrupted indexes and just did
 $ postgres -P broken_db 
and got a shell to send 
 $ REINDEX DATATBASE broken_db
as far as i can remember, so i thought i could throw in my two cents to 
improve postgresql.

By the way: What i really miss is a troubleshooting document in the docs. I 
run postgresql for over 4 years now and i have come across many situations 
where i really would need something like this. You can find most solutions by 
reading the documentation entirely but it is not very helpful if your 
production database went wrong and you are quite a bit in a hurry. 

kind regards,
janning

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] PSQL suggested enhancement

[Dean Gibson (DB Administrator)]

On 2005-10-19 23:52, Michael Glaesemann wrote:


On Oct 20, 2005, at 15:45 , Roger Hand wrote:




On Oct 20, 2005, at 14:50 , Dean Gibson (DB Administrator) wrote:

PSQL has the option to output the result of queries in several 
different formats, including HTML.  Suggestion:  have an option to 
output query results in XML format.




My personal opinion on this is that there are a lot of different 
ideas about how the XML should/could be written, and a the current 
output can be piped to a script in insert favorite scripting 
language here to format to match anyone's wish. Rather than have 
psql decree the one and true format or include so many as to make 
maintainability burdensome, further formatting is best left to the 
end user.




By that reasoning there shouldn't be html format either.


Agreed :) I'd submit a patch to remove it if I thought it'd be 
accepted--and more importantly, if I thought it were worth the time 
it'd take me (I'm still learning C, and the little bit of PostgreSQL 
code I'm familiar with is not the psql client) (excuses excuses :). 
Though justification for removing a feature is different from that of 
adding or extending one: removing one breaks backwards compatibility.


Michael Glaesemann
grzm myrealbox com


So, which other features THAT YOU DONT PERSONALLY WANT OR SEE THE NEED 
FOR, would you remove from PostgreSQL ???  Do you personally not use the 
expanded, or unaligned outputs ???  By all means rip them out !!!  
(sarcasm off)


While not every suggested feature needs to be in software, the idea that 
you'd remove a useful feature that someone else found valuable enough to 
spend the time coding, testing, etc, is anathema to me.  Remind me to 
NEVER let you touch the open-source projects I control.  I'm looking for 
contributors, not hackers or saboteurs.  Not to mention people that are 
open to new ideas.


Second, surrounding field values with XML tags having the name of the 
field, would be BY FAR the most natural way of representing the data.  
The only variable in what I suggested was the name of the row tag, 
which could either be a formatting parameter, or (my preference) easily 
changed by the user in the script which added the surrounding XML (which 
I left off because THAT PORTION is highly variable).


I just find it surprising that XML is not one of the formats provided, 
considering that XML is considered a data interchange format (much more 
than HTML, which is a representation format).


-- Dean


---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Tables

[Bob Pawley]

I am new to databases.

I have table 1, a primary source, which generates a serial number to make 
each item unique.

I want to use this number to generate a row in table 2 linking the two rows 
and allowing specific information on each item to be developed..

I have a number of books, including one specifically for Postgres.

WhatI don't have is the language to look this function up. 

Concepts like JOIN appear to used to create views not new rows on other 
tables.

Help will be appreciated.

Bob Pawley

Re: [GENERAL] From oracle to postgresql...

[Chris Browne]

dev@archonet.com (Richard Huxton) writes:
 If you have money to spend, it might be worth checking out
 EnterpriseDB - they claim to have Oracle compatibility. News
 item/company site below.
http://www.postgresql.org/about/news.367
http://www.enterprisedb.com/

It would be quite useful to have some sort of general idea as to what
the closer compatibility to Oracle of EnterpriseDB means.
-- 
output = reverse(gro.mca @ enworbbc)
http://cbbrowne.com/info/sgml.html
Referring to undocumented  private communications allows one to  claim
virtually anything: we discussed this idea in  our working group last
year, and concluded that it was totally brain-damaged.
-- from the Symbolics Guidelines for Sending Mail

---(end of broadcast)---
TIP 1: 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] Tables

[Michael Fuhr]

On Thu, Oct 20, 2005 at 09:37:07AM -0700, Bob Pawley wrote:
 I have table 1, a primary source, which generates a serial number
 to make each item unique.

Do you mean that the table has a serial column (which is just a
convenient way to declare an integer column that takes its default
value from a sequence)?  That's what one could infer from generates
a serial number but I'd like to make sure.

 I want to use this number to generate a row in table 2 linking
 the two rows and allowing specific information on each item to be
 developed..

What do you mean by generate a row?  Do you want to insert a row
into table 1, then use that row's sequence number in an insert into
table 2?  If so then see the nextval() and currval() functions.

http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html

You'll probably also want a foreign key constraint in table 2.

http://www.postgresql.org/docs/8.0/interactive/tutorial-fk.html
http://www.postgresql.org/docs/8.0/interactive/ddl-constraints.html#DDL-CONSTRAINTS-FK

If that doesn't help then please provide more information.  An
example that illustrates what you're trying to do might be useful.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] doc typo sql-reindex.html psql vs. postgres

[David Fetter]

On Thu, Oct 20, 2005 at 06:04:53PM +0200, Janning Vygen wrote:

 By the way: What i really miss is a troubleshooting document in the
 docs. I run postgresql for over 4 years now and i have come across
 many situations where i really would need something like this. You
 can find most solutions by reading the documentation entirely but it
 is not very helpful if your production database went wrong and you
 are quite a bit in a hurry. 

That's a great idea.  Please post a doc patch with some of the
troubleshooting tips you have in mind :)

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Tables

[Sean Davis]

On 10/20/05 12:37 PM, Bob Pawley [EMAIL PROTECTED] wrote:

 I am new to databases.
 
 I have table 1, a primary source, which generates a serial number to make each
 item unique.
 
 I want to use this number to generate a row in table 2 linking the two rows
 and allowing specific information on each item to be developed..
 
 I have a number of books, including one specifically for Postgres.
 
 What I don't have is the language to look this function up.
 
 Concepts like JOIN appear to used to create views not new rows on other
 tables.

This was confusing to me at first, also.  There is no generic function to
create rows in two tables simultaneously.  One simply creates the row in the
first table and then creates rows in the second table in a second step.  The
link between the two tables is a single column that contains the same id.
See any of MANY online tutorials on SQL for an introduction or get a book on
SQL.  I found that I had to sit and type verbatim from multiple sources
before I really understood what was going on, so you may want to try that.

Google for SQL tutorial for starters.

Sean


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Dann Corbit]

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
 [EMAIL PROTECTED] On Behalf Of Greg Stark
 Sent: Wednesday, October 19, 2005 11:17 PM
 To: Tom Lane
 Cc: Chris Travers; josh@agliodbs.com; pgsql-hackers@postgresql.org;
Dann
 Corbit; Stephan Szabo; Terry Fielder; Tino Wildenhain; Marc G.
Fournier;
 [EMAIL PROTECTED]; pgsql-general@postgresql.org
 Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
 
 Tom Lane [EMAIL PROTECTED] writes:
 
  Chris Travers [EMAIL PROTECTED] writes:
   If I understand the spec correctly, it seems to indicate that this
is
   specific to the locale/character set.
 
  The spec associates padding behavior with collations, which per spec
are
  separate from the datatypes --- that is, you should be able to able
to
  specify a collation for each string-type table column (whether
char(N)
  or varchar(N)) and even for each literal string constant.  We do not
  currently have that capability, and accordingly fall back to binding
  PAD SPACE behavior to char(N) and NO PAD behavior to varchar(N).
 
  AFAICS this choice is allowed by the spec since the default
collation is
  implementation-defined.
 
 Does it even make sense for char(N) to not be space padded? I had the
 impression char(N) was always N characters long, not more or less. I
can't
 picture any other character being used for padding, then you would
need a
 more
 flexible rtrim function.
 
 And I can understand the collation order determining whether 'a' and
'a '
 compare equal. But surely if you store 'a' in a varchar(N) you have to
get
 'a'
 back out, not some other string! Does the spec really allow varchar to
 actually be padded and not just compare ignoring trailing space?
 
 
 (I can't believe anyone really wants varchar to be space padded. Space
 padding
 always seemed like a legacy feature for databases with fixed record
length
 data types. Why would anyone want a string data type that can't
represent
 all
 strings?)

Let me make something clear:
When we are talking about padding here it is only in the context of a
comparison operator and NOT having anything to do with storage.

Given two strings of different in a comparison, most database systems
(by default) will blank pad the shorter string so that they are the same
length before performing the comparison.

Hence, you will see that 'Danniel' = 'Danniel ' is true in most cases.

Now, this really does not have any connection with storage or varchar or
bpchar or char or text or anything like that.

It is only the action to be taken when a comparison operation is
performed.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] From oracle to postgresql...

[Joshua D. Drake]

On Thu, 2005-10-20 at 12:35 -0400, Chris Browne wrote:
 dev@archonet.com (Richard Huxton) writes:
  If you have money to spend, it might be worth checking out
  EnterpriseDB - they claim to have Oracle compatibility. News
  item/company site below.
 http://www.postgresql.org/about/news.367
 http://www.enterprisedb.com/
 
 It would be quite useful to have some sort of general idea as to what
 the closer compatibility to Oracle of EnterpriseDB means.

Well that would be a question for EnterpriseDB and their marketing/tech
staff. There product is completely closed and there is no documentation
online.

Sincerely,

Joshua D. Drake


-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/


---(end of broadcast)---
TIP 1: 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] [HACKERS] 'a' == 'a '

[Chris Travers]

Dann Corbit wrote:


Let me make something clear:
When we are talking about padding here it is only in the context of a
comparison operator and NOT having anything to do with storage.
 

IIrc, varchar and bpchar are stored in a similar way, but are presented 
differently when retrieved.  I.e. storage is separate from presentation 
in this case.  I.e. the padding in bpchar occurs when it is presented 
and stripped when it is stored.


Again, I am happy solving this simply by documenting it since any 
questions of interpretation and implimentation of the standard would be 
answered.  So far what I (and I am sure others) have not heard is a 
strong case for changing the behavior, given that it is in line with a 
reasonable interpretation of the standards.



Given two strings of different in a comparison, most database systems
(by default) will blank pad the shorter string so that they are the same
length before performing the comparison.
 

Understood, but what gain do you have in a case like this that might 
justify the effort that would go into making it, say, an initdb option?  
How often does this behavior cause problems?


Best Wishes,
Chris Travers
Metatron Technology Consulting

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Dann Corbit]

 -Original Message-
 From: Chris Travers [mailto:[EMAIL PROTECTED]
 Sent: Thursday, October 20, 2005 11:53 AM
 To: Dann Corbit
 Cc: Greg Stark; Tom Lane; Chris Travers; josh@agliodbs.com; pgsql-
 [EMAIL PROTECTED]; Stephan Szabo; Terry Fielder; Tino Wildenhain;
 Marc G. Fournier; [EMAIL PROTECTED]; pgsql-
 [EMAIL PROTECTED]
 Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
 
 Dann Corbit wrote:
 
 Let me make something clear:
 When we are talking about padding here it is only in the context of a
 comparison operator and NOT having anything to do with storage.
 
 
 IIrc, varchar and bpchar are stored in a similar way, but are
presented
 differently when retrieved.  I.e. storage is separate from
presentation
 in this case.  I.e. the padding in bpchar occurs when it is presented
 and stripped when it is stored.
 
 Again, I am happy solving this simply by documenting it since any
 questions of interpretation and implimentation of the standard would
be
 answered.  So far what I (and I am sure others) have not heard is a
 strong case for changing the behavior, given that it is in line with a
 reasonable interpretation of the standards.

I believe that this is a reasonable response.  In order to comply with
the standard, bpchar and varchar would have to be stored with different
default collating sequences (which is fine with me).  If (indeed) that
is the case, the only action needed would be to document the collating
sequences used.
 
 Given two strings of different in a comparison, most database systems
 (by default) will blank pad the shorter string so that they are the
same
 length before performing the comparison.
 
 
 Understood, but what gain do you have in a case like this that might
 justify the effort that would go into making it, say, an initdb
option?
 How often does this behavior cause problems?

I do not even know if it is a good idea.  I was just pointing out that
the behavior of PostgreSQL is different from all the big database
vendors in this area and according to my reading of the standard, the
behavior was not compliant.

As to how often it causes a problem, I can't say.  It has caused me
puzzlement on a few occasions, but no end of the world disasters.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Select all invalid e-mail addresses

[Andrus]

Andrew Sullivan [EMAIL PROTECTED] wrote in message

 I suggest that if you want to validate TLDs, you pull them off when
 you write the data in your database, and use a lookup table to make
 sure they're valid (you can keep the table up to date regularly by
 checking the official IANA registry for them).  At least that way you
 don't have to change a regex every time ICANN decides to add another
 TLD.  (The regex is wrong anyway, I think: it doesn't have .mobi,
 which has been announced although isn't taking registrations yet, and
 it doesn't appear to have arpa, either.)

Andrew, thank you.

I understand now that I do'nt want to validate TLDs at all.

I have an existing database of e-mail addresses. Those addesses are copied
from letters so they contain   chars, points, commas etc. stupid
characters.
Sometimes two email addresses are copied to this field (contains two @
sings, spaces or commas). Sometimes web addresses starting with www. and
without @ are present in email column.
I want simply to allow user to view those addresses and make manual
corrections before starting large mailing session in night.

How to write a WHERE clause which selects e-mail addresses which
are surely wrong ?

Andrus.


 ---(end of broadcast)---
 TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match





---(end of broadcast)---
TIP 1: 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] Select all invalid e-mail addresses

[Steve Atkins]

On Thu, Oct 20, 2005 at 09:54:13PM +0300, Andrus wrote:
 Andrew Sullivan [EMAIL PROTECTED] wrote in message
 
  I suggest that if you want to validate TLDs, you pull them off when
  you write the data in your database, and use a lookup table to make
  sure they're valid (you can keep the table up to date regularly by
  checking the official IANA registry for them).  At least that way you
  don't have to change a regex every time ICANN decides to add another
  TLD.  (The regex is wrong anyway, I think: it doesn't have .mobi,
  which has been announced although isn't taking registrations yet, and
  it doesn't appear to have arpa, either.)
 
 Andrew, thank you.
 
 I understand now that I do'nt want to validate TLDs at all.
 
 I have an existing database of e-mail addresses. Those addesses are copied
 from letters so they contain   chars, points, commas etc. stupid
 characters.
 Sometimes two email addresses are copied to this field (contains two @
 sings, spaces or commas). Sometimes web addresses starting with www. and
 without @ are present in email column.
 I want simply to allow user to view those addresses and make manual
 corrections before starting large mailing session in night.
 
 How to write a WHERE clause which selects e-mail addresses which
 are surely wrong ?

... WHERE email !~ '...insert previously mentioned regex here...';

Cheers,
  Steve

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Select all invalid e-mail addresses

[Steve Atkins]

On Thu, Oct 20, 2005 at 11:52:40AM -0400, Andrew Sullivan wrote:
 On Thu, Oct 20, 2005 at 06:10:40PM +0300, Andrus wrote:
  From this thread I got the regular expression
 
 [snipped]
 
 Note that that regular expression, which appears to be validating
 TLDs as well, is incredibly fragile.  John Klensin has actually
 written an RFC about this very problem.  Among other problems, what
 do you do when a country code ceases to be?  (There's a similar
 problem that the naming bodies struggke with from time to time.)
 
 I suggest that if you want to validate TLDs, you pull them off when
 you write the data in your database, and use a lookup table to make
 sure they're valid (you can keep the table up to date regularly by
 checking the official IANA registry for them).  At least that way you
 don't have to change a regex every time ICANN decides to add another
 TLD. 

You need to maintain the data, certainly. To argue that it must
be in a table to be maintained is, well, wrong. My preference would
be to keep it in a table and regenerate the regex periodically, and
in the application layer I do exactly that, but to try and do that
in a check constraint would be painful. A cleaner approach would
be to have a regex that checks for general syntax and extracts the
TLD, which is then compared to a lookup table, perhaps, but that
adds a lot of complexity for no real benefit.

 (The regex is wrong anyway, I think: it doesn't have .mobi,
 which has been announced although isn't taking registrations yet, and
 it doesn't appear to have arpa, either.)

While there are valid deliverable email addresses in .arpa, you really
don't want to be accepting them from end users...

Cheers,
  Steve

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] Precompiled win32 binary for getCurrentTransactionID?

[Steve V]

Does anyone by any chance have a win32 binary compiled for the code
found in the below thread? I have been trying and for the life of me
cannot get it(or pg from source) to compile in my windows environment.
I'm sure it's user error on my part, but I don't have the time at the
moment to figure it out.

http://archives.postgresql.org/pgsql-general/2005-06/msg00709.php

Thanks,
Steve

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Richard_D_Levine]

I will happily reiterate that I am the troll who started this mess by
whining about how *Oracle* handles this.  Tom's explanation that CHAR is
has a PAD collation and VARCHAR has a NO PAD collation have restored my
faith that there is goodness in the world.  My whining was out of
ignorance.  I wouldn't change the proper way PostgreSQL works.  Documenting
it is good.  I will use this new found knowledge from now on in my database
designs.

Cheers,

Rick

Chris Travers [EMAIL PROTECTED] wrote on 10/20/2005 01:52:36 PM:

 Dann Corbit wrote:

 Let me make something clear:
 When we are talking about padding here it is only in the context of a
 comparison operator and NOT having anything to do with storage.
 
 
 IIrc, varchar and bpchar are stored in a similar way, but are presented
 differently when retrieved.  I.e. storage is separate from presentation
 in this case.  I.e. the padding in bpchar occurs when it is presented
 and stripped when it is stored.

 Again, I am happy solving this simply by documenting it since any
 questions of interpretation and implimentation of the standard would be
 answered.  So far what I (and I am sure others) have not heard is a
 strong case for changing the behavior, given that it is in line with a
 reasonable interpretation of the standards.

 Given two strings of different in a comparison, most database systems
 (by default) will blank pad the shorter string so that they are the same
 length before performing the comparison.
 
 
 Understood, but what gain do you have in a case like this that might
 justify the effort that would go into making it, say, an initdb option?
 How often does this behavior cause problems?

 Best Wishes,
 Chris Travers
 Metatron Technology Consulting


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Select all invalid e-mail addresses

[Andrew Sullivan]

On Thu, Oct 20, 2005 at 09:54:13PM +0300, Andrus wrote:
 How to write a WHERE clause which selects e-mail addresses which
 are surely wrong ?

Then I think the validating function someone else sent here
(http://www.databasejournal.com/img/email_val.sql) is a good start. 
You probably want the opposite behaviour -- emailinvalidate(), I
guess -- but that seems like a good obviously wrong tester.  It
might not be fast, though -- that loop at the special character
check looks pretty painful.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] [HACKERS] 'a' == 'a '

[John D. Burger]

[Removed all the non-list addresses]

Dann Corbit wrote:


Let me make something clear:
When we are talking about padding here it is only in the context of a
comparison operator and NOT having anything to do with storage.

Given two strings of different in a comparison, most database systems
(by default) will blank pad the shorter string so that they are the 
same

length before performing the comparison.

Hence, you will see that 'Danniel' = 'Danniel ' is true in most cases.

Now, this really does not have any connection with storage or varchar 
or

bpchar or char or text or anything like that.


Is this really true??? My understanding of the spec was that this was 
=exactly= the difference between char(N) and varchar(N) - the former is 
padded to length N when you store it, or at least the DB has to act as 
if this is the case.  Can someone quote the appropriate chapter and 
verse?


Thanks.

- John D. Burger
  MITRE


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Dann Corbit]

Look back in the stack and you will find that I have quoted chapter and
verse (see the attached html file in a previous email that I sent).

This is in relation to the comparison operator.

 -Original Message-
 From: John D. Burger [mailto:[EMAIL PROTECTED]
 Sent: Thursday, October 20, 2005 12:53 PM
 To: Dann Corbit
 Cc: pgsql-hackers@postgresql.org; pgsql-general General
 Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
 
 [Removed all the non-list addresses]
 
 Dann Corbit wrote:
 
  Let me make something clear:
  When we are talking about padding here it is only in the context of
a
  comparison operator and NOT having anything to do with storage.
 
  Given two strings of different in a comparison, most database
systems
  (by default) will blank pad the shorter string so that they are the
  same
  length before performing the comparison.
 
  Hence, you will see that 'Danniel' = 'Danniel ' is true in most
cases.
 
  Now, this really does not have any connection with storage or
varchar
  or
  bpchar or char or text or anything like that.
 
 Is this really true??? My understanding of the spec was that this was
 =exactly= the difference between char(N) and varchar(N) - the former
is
 padded to length N when you store it, or at least the DB has to act as
 if this is the case.  Can someone quote the appropriate chapter and
 verse?

Again, the operations that I was talking about literally have nothing to
do with the internal data type of the string, as long as it is
fundamentally a character type.  I was referring to what happens when
two strings are involved in a comparison operation.  The standard spells
it out very clearly.  Whether something is blank padded or not during a
comparison has nothing whatever to do with the type of the string and
everything to do with the collating sequence.

 Thanks.
 
 - John D. Burger
MITRE


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PSQL suggested enhancement

[Martijn van Oosterhout]

On Thu, Oct 20, 2005 at 09:28:25AM -0700, Dean Gibson (DB Administrator) wrote:
 I just find it surprising that XML is not one of the formats provided, 
 considering that XML is considered a data interchange format (much more 
 than HTML, which is a representation format).

All jokes aside, saying output to XML is like saying all our
documentation will use words from the english dictionary. Yes, you
made a constraint but until you decide grammer, syntax and style, you
havn't decided anything yet.

Realistically, psql is only ever going to support one XML format, we
can't add more styles every time someone asks for one. Hence, we should
pick the one that is going to be most easily loaded into other programs
(the entire point of XML, right?). To that end, we should probably aim
for something like the OpenDocument table format, which looks a bit
like below. At least that way you stand a chance of being able to
import it and/or display it.

At least, unless someone has some better idea? Outputting complete
gnumeric spreadsheets is going a bit far, no?

Have a nice day,

office:body
office:spreadsheet
   table:table table:name=TableName
 table:table-header-rows
 table:table-row
table:table-cell table:style-name=heading
 text:pLast Name/text:p
/table:table-cell
   ...for each column...
 /table:table-row
 /table:table-header-rows
 table:table-row
   table:table-cell office:value-type=float 
office:value=$value
   text:p$value/text:p
   /table:table-cell
 /table:table-row
   ...for each row and column...
   /table:table
/office:spreadsheet
/office:body

-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpiZYEvym5pO.pgp
Description: PGP signature

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Tom Lane]

Chris Travers [EMAIL PROTECTED] writes:
 IIrc, varchar and bpchar are stored in a similar way, but are presented 
 differently when retrieved.  I.e. storage is separate from presentation 
 in this case.  I.e. the padding in bpchar occurs when it is presented 
 and stripped when it is stored.

This is not so, although I've occasionally wondered whether we shouldn't
try to make it so.  Certainly we don't rely on char(N) to be physically
fixed-width (and can't, at least not with variable-width character
encodings) so there can be no performance advantage to actually storing
the insignificant spaces.  The hard part would be in figuring out how
the output routine could know how many spaces to add back.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Select all invalid e-mail addresses

[Dann Corbit]

Interesting article:
http://coveryourasp.com/ValidateEmail.asp

See also:
http://search.cpan.org/~cwest/Email-Address-1.80/lib/Email/Address.pm
http://www.faqs.org/rfcs/rfc2822.html
http://docs.python.org/lib/module-rfc822.html

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:pgsql-general-
 [EMAIL PROTECTED] On Behalf Of Steve Atkins
 Sent: Thursday, October 20, 2005 12:35 PM
 To: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Select all invalid e-mail addresses
 
 On Thu, Oct 20, 2005 at 11:52:40AM -0400, Andrew Sullivan wrote:
  On Thu, Oct 20, 2005 at 06:10:40PM +0300, Andrus wrote:
   From this thread I got the regular expression
 
  [snipped]
 
  Note that that regular expression, which appears to be validating
  TLDs as well, is incredibly fragile.  John Klensin has actually
  written an RFC about this very problem.  Among other problems, what
  do you do when a country code ceases to be?  (There's a similar
  problem that the naming bodies struggke with from time to time.)
 
  I suggest that if you want to validate TLDs, you pull them off when
  you write the data in your database, and use a lookup table to make
  sure they're valid (you can keep the table up to date regularly by
  checking the official IANA registry for them).  At least that way
you
  don't have to change a regex every time ICANN decides to add another
  TLD.
 
 You need to maintain the data, certainly. To argue that it must
 be in a table to be maintained is, well, wrong. My preference would
 be to keep it in a table and regenerate the regex periodically, and
 in the application layer I do exactly that, but to try and do that
 in a check constraint would be painful. A cleaner approach would
 be to have a regex that checks for general syntax and extracts the
 TLD, which is then compared to a lookup table, perhaps, but that
 adds a lot of complexity for no real benefit.
 
  (The regex is wrong anyway, I think: it doesn't have .mobi,
  which has been announced although isn't taking registrations yet,
and
  it doesn't appear to have arpa, either.)
 
 While there are valid deliverable email addresses in .arpa, you really
 don't want to be accepting them from end users...
 
 Cheers,
   Steve
 
 ---(end of
broadcast)---
 TIP 4: Have you searched our list archives?
 
http://archives.postgresql.org

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase

[Doug Quale]

Guy Rouillier [EMAIL PROTECTED] writes:

 Doug Quale wrote:
 
 # select 'a'::char(8) = 'a '::char(8);
  ?column?
 --
  t
 (1 row)
 
 Trailing blanks aren't significant in fixed-length strings, so the
 question is whether Postgresql treats comparison of varchars right. 

 This result is being misinterpreted.   

 select length('a'::char(8)) == 1
 select length('a '::char(8)) == 1

 So it isn't that the two different strings are comparing equal.  The
 process of casting them to char(8) is trimming the blanks, so by the
 time they become fixed length strings, they are indeed equal.

Huh??? What version of PG are you using?  On 7.4.9,


test=# select length('a'::char(8));
 length 

  8
(1 row)

test=# select length('a '::char(8));
 length 

  8
(1 row)

The truncation you describe would simply be wrong.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Select all invalid e-mail addresses

[Andrew Sullivan]

On Thu, Oct 20, 2005 at 12:34:39PM -0700, Steve Atkins wrote:
 
 While there are valid deliverable email addresses in .arpa, you really
 don't want to be accepting them from end users...

You know, as someone who has been bitten hundreds of times by the
decision of some application designer who thought s/he knew better
than I what my email address could possibly be, I respectfully submit
that you're mistaken.  We call it a bug when other databases accept
dates like '-00-00'; but we'd just as surely call it a bug if
PostgreSQL refused to accept valid leap year dates or leap seconds. 
It's one thing to say you should not accept known-bad data; it's
quite another to refuse data that is improbable but nevertheless
perfectly good.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
I remember when computers were frustrating because they *did* exactly what 
you told them to.  That actually seems sort of quaint now.
--J.D. Baldwin

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Strange order of execution with rule

[han . holl]

Hello,

I have something like this:
CREATE or replace rule update_rule as on update
to aview
do instead (
  select func_display(new, old);
  select rubriek('reset', 0, '', 0);
);

(Postgres 8.0.3).

I tried all kinds of variations (one select with two functions, and two 
different update rules with a select each) but in _all_ cases the second 
function is executed before the first, or possibly at the same time (but I 
didn't think the backend is threaded).

Is this known behaviour, and what can I do to change it?

Thanks in advance for any suggestions,

Han Holl

P.S. I don't know if it's relevant but what func_display and rubriek do has 
nothing to do with the database. Let's say they do some kind of logging.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] NULL != text ?

[Michael Glaesemann]

On Oct 20, 2005, at 23:45 , Michael Fuhr wrote:


On Thu, Oct 20, 2005 at 03:57:41PM +0900, Michael Glaesemann wrote:


On Oct 20, 2005, at 15:44 , Michael Fuhr wrote:


 expression IS DISTINCT FROM expression

 For non-null inputs this is the same as the  operator.  However,
 when both inputs are null it will return false, and when just one
 input is null it will return true.  Thus it effectively acts as
 though null were a normal data value, rather than unknown.



Interesting! Thanks, Michael. You don't happen to know off the top of
your head if that's standard SQL, do you?



IS DISTINCT FROM is defined in SQL:1999 and SQL:2003.


Thanks!

Michael Glaesemann
grzm myrealbox com




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Richard_D_Levine]

Tom Lane [EMAIL PROTECTED] wrote on 10/20/2005 03:11:23 PM:
snip
 The hard part would be in figuring out how
 the output routine could know how many spaces to add back.

The length is in the metadata for the column, or am I being dense?


  regards, tom lane


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Checking Multiplicity Constraints and Retrieving

[Simon Riggs]

On Wed, 2005-10-19 at 15:25 +0100, Ledina Hido wrote:

 First of all, is there any way of limiting the number of rows in a  
 table, referencing to the same element of another table? For example,  
 force a manager not to have more than 10 employees under his control.  
 In a way this can be seen as checking the multiplicity of the  
 relation between the two tables. I know one way would be using  
 triggers, but I was wondering if there was a way of specifying this  
 when the table is constructed.

That's a lack of function within the SQL standard and PostgreSQL doesn't
implement that as an extension.

XML Schema allows you to define minoccurs and maxoccurs, but not the
SQL:2003 standard.

It would be very cool if it did, I grant you. You have to do this
yourself via a trigger. Just make sure you create an index on the
columns and you'll be fine - apart from the extra cost of inserting
rows, so keep maxoccurs small.

Best Regards, Simon Riggs


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Tom Lane]

[EMAIL PROTECTED] writes:
 Tom Lane [EMAIL PROTECTED] wrote on 10/20/2005 03:11:23 PM:
 The hard part would be in figuring out how
 the output routine could know how many spaces to add back.

 The length is in the metadata for the column, or am I being dense?

The output routine hasn't got access to that metadata.  In general,
in fact, a random Datum can't be linked to any column since it may have
been generated on-the-fly.

It'd be easy if we stuck the column width into the stored representation
of bpchar, but this would defeat one of the purposes, ie, to make the
stored representations of text and bpchar alike.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Strange order of execution with rule

[Tom Lane]

[EMAIL PROTECTED] writes:
 I have something like this:
 CREATE or replace rule update_rule as on update
 to aview
 do instead (
   select func_display(new, old);
   select rubriek('reset', 0, '', 0);
 );

 I tried all kinds of variations (one select with two functions, and two 
 different update rules with a select each) but in _all_ cases the second 
 function is executed before the first, or possibly at the same time (but I 
 didn't think the backend is threaded).

Give us a test case to demonstrate this assertion.  It works as expected
AFAICT.

regression=# create function noti(text) returns int as $$
regression$# begin
regression$#   raise notice '%', $1;
regression$#   return 1;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# CREATE or replace rule update_rule as on update to zview
regression-# do instead (
regression(#   select noti('foo');
regression(#   select noti('bar');
regression(# );
CREATE RULE
regression=# update zview set q1 = q2;
NOTICE:  foo
NOTICE:  bar
 noti
--
1
(1 row)


regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Unique index with Null value in one field

[Chris Travers]

Hrishi Joshi wrote:


Hi,

I need to define a Unique index on 3 non-PK fields (composite key) on my
table in PostgreSQL 8.0.3.

The problem is, if any of those 3 fields is Null, PostgreSQL allows
duplicate rows to be inserted. While searching through archives, I found
more information about this.

But I need to know how can I make PostgreSQL throw error on attempt to
insert second record having same 3 field values, one of them being Null.



myid |  field1 |  field2 |  field3 | description
PK   |  ---  Unique Index ---| 


100  | ABC | XYZ | null  | Record 1   - This is ok.
101  | ABC | XYZ | null  | Record 2   - * This should error!
 

Why?  PostgreSQL knows that we cannot determine whether these records 
are different or not.  See the discussion on what NULL means...


IMO, people who use NULL to mean not applicable are misusing the 
value. Not applicable should always be equal to not applicable, but it 
is always unknown whether unknown is equal to another unknown.  I would 
suggest using a different table for that column if it is not always 
applicable.  The typical example is:


You have 500 employees.  Some employees have salaries, some have wages.  
Some people might:


create table employee(
employee_id serial,
ssn varchar not null,
...
wage numeric,
salary numeric
);

but here NULL could either mean unknown or not applicable so we 
don't really know which is which and it can create data management issues.


In this case it is better to:

create table employee(
employee_id serial,
ssn varchar not null,
...
);
create table wage (
employee_id,
wage numeric
);
create table salary (
employee_id,
salary numeric
);




Fields {field1, field2, field3} have unique index on them and myid is
the primary key of my table.
 



Oracle 9i throws exception in such case, but PostgreSQL does not.
 

You could use a custom trigger, or a custom function and a functional 
index  Or even an index on COALESCE...


But Oracle's handling of NULL's is broken, especially in string fields 
(varchar, char, etc) because it wrongly assumes that an empty string and 
NULL are equivalent.  Search for prior discussions on this list


Best Wishes,
Chris Travers
Metatron Technology Consulting

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PSQL suggested enhancement

[Chris Travers]

Martijn van Oosterhout wrote:


On Thu, Oct 20, 2005 at 09:28:25AM -0700, Dean Gibson (DB Administrator) wrote:
 

I just find it surprising that XML is not one of the formats provided, 
considering that XML is considered a data interchange format (much more 
than HTML, which is a representation format).
   



All jokes aside, saying output to XML is like saying all our
documentation will use words from the english dictionary. Yes, you
made a constraint but until you decide grammer, syntax and style, you
havn't decided anything yet.

Realistically, psql is only ever going to support one XML format, we
can't add more styles every time someone asks for one. Hence, we should
pick the one that is going to be most easily loaded into other programs
(the entire point of XML, right?). To that end, we should probably aim
for something like the OpenDocument table format, which looks a bit
like below. At least that way you stand a chance of being able to
import it and/or display it.

At least, unless someone has some better idea? Outputting complete
gnumeric spreadsheets is going a bit far, no?
 

OTOH, this idea might be really kinda cool.  Maybe a good idea for 
client applications  Or are there other programs out there that can 
save db queries to static (stand-alone) Gnumeric spreadsheets?


This would actually be a killer feature for one of my customers

Best Wishes,
Chris Travers

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] PSQL suggested enhancement

[Roger Hand]

On Thursday, October 20, 2005 1:01 PM, Martijn van Oosterhout wrote:

 On Thu, Oct 20, 2005 at 09:28:25AM -0700, Dean Gibson (DB
Administrator) wrote:
 I just find it surprising that XML is not one of the formats
provided, 
 considering that XML is considered a data interchange format (much
more 
 than HTML, which is a representation format).
 
 All jokes aside, saying output to XML is like saying all our
 documentation will use words from the english dictionary. Yes, you
 made a constraint but until you decide grammer, syntax and style, you
 havn't decided anything yet.
 
 Realistically, psql is only ever going to support one XML format, we
 can't add more styles every time someone asks for one. Hence, we
should
 pick the one that is going to be most easily loaded into other
programs
 (the entire point of XML, right?). To that end, we should probably aim
 for something like the OpenDocument table format, which looks a bit
 like below. At least that way you stand a chance of being able to
 import it and/or display it.

If pg outputs a simple xml format, it can easily be transformed via xslt
into OpenDoc table format, alternate html formats, or the alternate xml
format of your choice. 

I would argue against outputting this one specific OpenDoc format,
even though it is the flavor of the month right now.

-Roger

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] NULL != text ?

[CSN]

BTW, it (the SQL spec I presume) has always seemed
contradictory to me that you can't do:

select * from table where field=null;

but can do:

update table set field=null;

(as opposed to 'update table set field to null' or
similar).


CSN




__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

---(end of broadcast)---
TIP 1: 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] NULL != text ?

[Tom Lane]

CSN [EMAIL PROTECTED] writes:
 BTW, it (the SQL spec I presume) has always seemed
 contradictory to me that you can't do:
 select * from table where field=null;
 but can do:
 update table set field=null;

This only seems contradictory if you fail to make the distinction
between = used as a comparison operator and = used to mean
assignment.

Personally I prefer programming languages that actually spell the
two concepts differently ... but enough don't that one has to learn
to live with it.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase

[Marc G. Fournier]

On Thu, 20 Oct 2005, Doug Quale wrote:


Guy Rouillier [EMAIL PROTECTED] writes:


Doug Quale wrote:


# select 'a'::char(8) = 'a '::char(8);
 ?column?
--
 t
(1 row)

Trailing blanks aren't significant in fixed-length strings, so the
question is whether Postgresql treats comparison of varchars right.


This result is being misinterpreted.

select length('a'::char(8)) == 1
select length('a '::char(8)) == 1

So it isn't that the two different strings are comparing equal.  The
process of casting them to char(8) is trimming the blanks, so by the
time they become fixed length strings, they are indeed equal.


Huh??? What version of PG are you using?  On 7.4.9,


test=# select length('a'::char(8));
length

 8
(1 row)

test=# select length('a '::char(8));
length

 8
(1 row)

The truncation you describe would simply be wrong.


ams=# select length('a '::char(8));
 length

  1
(1 row)

ams=# select version();
   version
--
 PostgreSQL 8.0.2 on i386-portbld-freebsd4.11, compiled by GCC 2.95.4
(1 row)

ams=#



Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] NULL != text ?

[Jan Wieck]

On 10/20/2005 6:10 AM, Alban Hertroys wrote:


Michael Glaesemann wrote:
if (OLD.value IS NOT NULL and NEW.value IS NOT NULL and OLD.value   
NEW.value) or OLD.value IS NULL or NEW.value IS NULL


But that's untested and I have a hard time thinking in three-value  logic.


For completeness sake; Because of lazy evaluation, that boils down to:

if (OLD.value IS NULL OR NEW.value IS NULL OR OLD.value  NEW.value)


That would result in TRUE if both, OLD and NEW are NULL. Is that what 
you intended?



Jan



The last part of the expression is only evaluated if both OLD.value and 
NEW.value aren't NULL.





--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings