Re: [GENERAL] PostgreSQL vs. MySQL benchmarks on SMP FreeBSD 7.0

[Greg Smith]

On Wed, 5 Mar 2008, [EMAIL PROTECTED] wrote:


b) PostgreSQL is in general 35%-45% faster.


And this is using sysbench, which is an old MySQL benchmark with 
rudimentary PostgreSQL support bolted on.  Last time I checked it didn't 
even put statements into transaction blocks correctly under PostgreSQL 
when you used the write tests (because the MySQL it was written for can't 
do that at all).  The FreeBSD tests only run the read tests which avoid 
that, but still it's clearly not a benchmark optimized for good PostgreSQL 
performance.  I suspect if the sysbench schema/queries were ported to 
pgbench the gap would widen considerably.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

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

[GENERAL] PostgreSQL vs. MySQL benchmarks on SMP FreeBSD 7.0

[[EMAIL PROTECTED]]

Apologizes if this has already been announced -

http://www.scribd.com/doc/551889/Introducing-Freebsd-70

a presentation of the SMP in FreeBSD 7.0 using PostgreSQL and MySQL to
produce benchmarks.

Notable quotes -

a) MySQL degrades after utilizing all CPUs, while PostgreSQL does not
(the explanation is that MySQL has scalability problem).

b) PostgreSQL is in general 35%-45% faster.

I can't tell if all is true, just wanted to make it public, if it hasn't
been already made.

Iv


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

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

Re: [GENERAL] postgresql vs mysql

[Richard Huxton]

Andreas 'ads' Scherbaum wrote:

Hello,

On Thu, 22 Feb 2007 00:16:24 +0800
Lincoln Yeoh  wrote:

Want transactions? Use innoDB. Want to restore a multi-gigabyte 
database fast from backups, sure use MyISAM (too many people seem to 
have probs doing that with innoDB).


sure you want to do this?

http://bugs.mysql.com/bug.php?id=11151

I won't trust a database who prefers speed over data integrity even if
it's named "transaction".


"
[6 Apr 2006 20:50] Chad MILLER

This is behavior that we can not fix.  Extremely large transactions cause
several storage engines to behave very poorly, and we consider that more
important than making the rare "load data infile" transaction-safe.

This must be documented, since it is surprising behavior.
"

I'll say it's surprising. I must say I thought they'd left the 
"transactions not important" approach behind some time ago. Clearly not. 
Ho hum - doesn't affect me much any more I suppose.


--
  Richard Huxton
  Archonet Ltd

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

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[brian]

While no one in thier right mind should be using wikipgedia, I'm sympathetic 
to those who might still be stuck on it for some reason, so if you guys can 
produce a patch against the wikipgedia cvs, I'd be happy to apply it.




I'd like to patch that name.

---(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: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Robert Treat]

On Friday 23 February 2007 16:43, Chad Wagner wrote:
> On 2/23/07, Bill Moran <[EMAIL PROTECTED]> wrote:
> > > In any case if anyone is interested I was able to reproduce the changes
> >
> > that
> >
> > > wikipgedia made and applied those changes (as well as others) all the
> >
> > way up
> >
> > > to the 1.6.10 codebase.  The only reason I mention this is because
> > > 1.6is the only choice for PHP4 users.  If anyone is interested I can
> > > provide
> >
> > the
> >
> > > codebase, the schema still has to be created manually as was the case
> >
> > with
> >
> > > wikipgedia.
> >
> > I would be interested.  I'm probably expected to maintain this thing ...
>
> You can download it from:
>
> http://www.postgresqlforums.com/downloads/pgmediawiki-1.6.10.tar.gz
>
> Again, like wikipgedia you have to create a schema (manually) named
> mediawiki and like wikipgedia (because the port more or less used some of
> the same mods they made) MySQL support is probably broken.

While no one in thier right mind should be using wikipgedia, I'm sympathetic 
to those who might still be stuck on it for some reason, so if you guys can 
produce a patch against the wikipgedia cvs, I'd be happy to apply it.

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

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

Re: [GENERAL] postgresql vs mysql

[Andreas 'ads' Scherbaum]

On Fri, 23 Feb 2007 13:49:06 +1300
"Andrej Ricnik-Bay" <[EMAIL PROTECTED]> wrote:

> On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
> > That depends greatly on what you're doing with it. Generally, as soon
> > as you start throwing a multi-user workload at it, MySQL stops
> > scaling. http://tweakers.net recently did a study on that.
> I think I recall that wikipedia uses MySQL ... they get quite a few
> hits, too, I believe.

Wikipedia is, like ./, heavily cached. Almost every answer you get
comes from a proxy, not from the database itself.


Kind regards

-- 
Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
 (Ferenc Mantfeld)

---(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] postgresql vs mysql

[Andreas 'ads' Scherbaum]

Hello all,

On Wed, 21 Feb 2007 10:02:04 -0600
Scott Marlowe <[EMAIL PROTECTED]> wrote:

> It swallows column level foreign key contraints and does nothing with
> them, no errors nothing, even if you're defining innodb tables.  I.e.
> this produces not errors:
> 
> mysql> create table a (id int primary key) engine=innodb;
> Query OK, 0 rows affected (0.02 sec)
>  
> mysql> create table b (a_id int references a(id)) engine=innodb;
> Query OK, 0 rows affected (0.03 sec)
>  
> mysql> insert into a values (1);
> Query OK, 1 row affected (0.03 sec)
>  
> mysql> insert into b values (1);
> Query OK, 1 row affected (0.03 sec)
>  
> mysql> insert into b values (2);
> Query OK, 1 row affected (0.03 sec)
>  
> That last statement should fail.  Or the creation of table b should
> throw a warning.  Or something.

It will not fail, cause REFERENCES without FOREIGN KEY get's ignored :-(
Thats documented somewhere, but not really fixed, cause standard '92
says, just writing REFERENCE is ok. Oh, and no warning at all, since it
is a valid (but ignored) language thing of Mysql.


Kind regards

-- 
Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
 (Ferenc Mantfeld)

---(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] postgresql vs mysql

[Andreas 'ads' Scherbaum]

Hello,

On Thu, 22 Feb 2007 00:16:24 +0800
Lincoln Yeoh  wrote:

> Want transactions? Use innoDB. Want to restore a multi-gigabyte 
> database fast from backups, sure use MyISAM (too many people seem to 
> have probs doing that with innoDB).

sure you want to do this?

http://bugs.mysql.com/bug.php?id=11151

I won't trust a database who prefers speed over data integrity even if
it's named "transaction".


> Want foreign keys to work? Use innoDB. MyISAM tables allow you to 
> specify foreign keys but ignores AND forgets them.

As example, you have to say 

FOREIGN KEY ... REFERENCES

cause REFERENCES itself is (was?) even in innodb just syntax sugar and
get's ignored. Standard tells, REFERENCES as an alias for the full
syntax is just fine, but in Mysql you won't even get an error.


> You can mix MyISAM tables with innoDB tables in the same database. 
> That's a minus.

Thats a feature.

You can even mix both table types in a transaction: thats a real bug.


> ** D'oh level release gotchas
> Example: Before MySQL 5.0.13, GREATEST(x,NULL) and
> LEAST(x,NULL) return x when x is a non-NULL value. As of 5.0.13,
> both functions return NULL if any argument is NULL, the same as
> Oracle.  This change can cause problems for applications that rely
> on the old behavior.

Between 5.0.24a and 5.0.27 the behaviour of SELECT COUNT(1) has changed
and now returns 1 as expected. Previous versions returned 0 but of
course behaviour changes in minor releases and no announcement was
made. This one seems easy on the first look but i was told that it is
only a result of a bigger change somewhere else in the code which
will or will not interfere with other results as well.


> Not saying Postgresql is perfect - rather that MySQL makes Postgresql 
> look really good.

Hehe, sure ;-)


Kind regards

-- 
Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
 (Ferenc Mantfeld)

---(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] postgresql vs mysql

[Andreas 'ads' Scherbaum]

On Thu, 22 Feb 2007 17:14:11 -0600
Jim Nasby <[EMAIL PROTECTED]> wrote:

> On Feb 20, 2007, at 11:59 PM, Adam Rich wrote:
> > "As of 5.0.2, the server requires that month and day values
> > be legal, and not merely in the range 1 to 12 and 1 to 31,
> > respectively."
> 
> Yes, but any session is free to change that setting and insert  
> whatever garbage they want. AFAIK there's absolutely no way to  
> prevent that. So your data is still very much subject to getting  
> trashed.

Even if you activate the strict mode, you cannot be sure, that Mysql
will not result invalid data from the table inserted by another session
not using strict mode.


Kind regards

-- 
Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
 (Ferenc Mantfeld)

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

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

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Chad Wagner]

On 2/25/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:


> For the record, anyone using wikipgedia deserves the pain they
> get: it is deprecated. The latest version of MediaWiki itself is what
> should now be used: it will detect if you have Postgres upon
> installation. :)

Perhaps the project should be *gasp* deleted then? ;-) Or is there
actual historical information there that someone would be interested in?



As I said in my other mail, some folks are still using PHP4 -- which is why
MediaWiki still maintains the 1.6 branch.  I am more than willing to
contribute the most recent 1.6.10 codebase w/ PostgreSQL modifications to
the foundry.  I am actively maintaining my own codebase for my site.

I agree with Greg, if you are already using PHP5 then use the MediaWiki
distribution, but if your stuck on PHP4 like me then you really don't have a
choice other than what is being offered on pgfoundry.  :)

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Chad Wagner]

On 2/25/07, Greg Sabino Mullane <[EMAIL PROTECTED]> wrote:


For the record, anyone using wikipgedia deserves the pain they
get: it is deprecated. The latest version of MediaWiki itself is what
should now be used: it will detect if you have Postgres upon
installation. :)



Some of us are still using php4  :)

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Magnus Hagander]

> For the record, anyone using wikipgedia deserves the pain they
> get: it is deprecated. The latest version of MediaWiki itself is what
> should now be used: it will detect if you have Postgres upon
> installation. :)

Perhaps the project should be *gasp* deleted then? ;-) Or is there
actual historical information there that someone would be interested in?

//Magnus

---(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: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> How is the Postgres port of the Wikipedia doing this days anyway? 
> Is it in a shape where one would consider it "competitive"?

The port of MediaWiki is going well: it is certainly usable, and 
is already being used by a number of sites. I would not say it is 
quite "competitive" yet as far as being ready to run Wikipedia, as 
the codebase has a lot of very mysql-specific stuff that has yet to 
be fixed/coded around. There are also a few lingering bugs, most 
related to the fact that the MediaWiki on Mysql stores dates as 
char(14).

For the record, anyone using wikipgedia deserves the pain they 
get: it is deprecated. The latest version of MediaWiki itself is what 
should now be used: it will detect if you have Postgres upon 
installation. :)

http://www.mediawiki.org/

- --
Greg Sabino Mullane [EMAIL PROTECTED]
End Point Corporation
PGP Key: 0x14964AC8 200702250925
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iD8DBQFF4ZyDvJuQZxSWSsgRA8c6AJ95oTX9YQ38VyPvFyhd54S3rHAZSACgh/tC
uqcAmRFuRnMUdPL7sO/eoP0=
=w2KL
-END PGP SIGNATURE-



---(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] postgresql vs mysql

[CaT]

On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote:
> In postgres, to stop an empty blank string:
> 
> create table a(a text not null check (char_length(a) > 0));

What's wrrong with using

a <> ''

sd the check? Or is this just a flavour thing?

-- 
"To the extent that we overreact, we proffer the terrorists the
greatest tribute."
- High Court Judge Michael Kirby

---(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] postgresql vs mysql

[Richard Broersma Jr]

> That's why you make a table for every device or every measurement, and
> then use a view to consolidate it.  With update-able views, there's no
> excuse not to.

I would be interested on here some of your experiences on this?

I've built and made use of table hierarchies three levels deep and about twenty 
classification
types wide that I rolled up into separate update-able view.  However, I found 
the process of
cascading the updates to all three levels of each classification type using the 
"TID" rather
tedious.

Regards,
Richard Broersma Jr.

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

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

Re: [GENERAL] postgresql vs mysql

[Martijn van Oosterhout]

On Fri, Feb 23, 2007 at 02:50:48PM -0800, Glen Parker wrote:
> I can and do solve the problem by simply not using NULL in character 
> fields, and by the rather gratuitous use of coalesce() in queries.  

I'm confused. If you don't use NULLs then you don't need coalesce
either.

> The 
> problem is, it places a burden on people doing ad hoc queries who, 
> because of the type of data they work with, have no reason to understand 
> the concept of NULL as it exists in standard SQL.  These aren't computer 
> scientists, they are accountants and managers.  The result is queries 
> that either return bad data, or that appear much more complex than 
> should be required to people who can't see why NULL == zero is NULL.

Is it really that hard to understand that UNKNOWN == zero is UNKNOWN?

And again, if NULL is confusing on your systems, don't use it. They
don't appear out of nowhere. Outer joins are really the only place you
can't avoid them.

> And as I said, I really don't know what a fully functional solution 
> would look like, I just know that it would be useful to a large cross 
> section of users.

Useful, maybe. Confusing, absolutly. I'm just wondering how it would
interact with foreign keys for example. Different people can't have
different ideas about '' = NULL, else you'd get constraints that are
violated depending on who's looking.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] postgresql vs mysql

[Peter Eisentraut]

Ron Johnson wrote:
> Each of the daily/hourly/etc temperature readings are independent.
> Therefore they should each have their own row in the "meteorology
> readings" table.  I *think* that breaks 3NF.

If everything is, as you say, independent, then there can be no 3NF 
violation, because that only happens when you have functional 
dependencies within a table.

The question that you raise is more a matter of deciding which aspects 
of a problem are data and which are data structure.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

Re: [GENERAL] postgresql vs mysql

[Glen Parker]

Ben wrote:
What I read was that you have no use for NULLs, and that they're 
equivilant to zero or an empty string or some other known value. Sorry 
if I misunderstood that.


Equivalent, yes, because NULL doesn't usually mean UNKNOWN in this 
system, just NOT ENTERED.  I do still have use for NULL in data types 
that don't inherently have a blank value (numerics, dates, etc.)


I can and do solve the problem by simply not using NULL in character 
fields, and by the rather gratuitous use of coalesce() in queries.  The 
problem is, it places a burden on people doing ad hoc queries who, 
because of the type of data they work with, have no reason to understand 
the concept of NULL as it exists in standard SQL.  These aren't computer 
scientists, they are accountants and managers.  The result is queries 
that either return bad data, or that appear much more complex than 
should be required to people who can't see why NULL == zero is NULL.


And as I said, I really don't know what a fully functional solution 
would look like, I just know that it would be useful to a large cross 
section of users.


-Glen


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

Re: [GENERAL] postgresql vs mysql

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/23/07 15:47, Peter Eisentraut wrote:
> Brandon Aiken wrote:
>> That's why you make a table for every device or every measurement,
>> and then use a view to consolidate it.  With updatable views, there's
>> no excuse not to.
> 
> No, you put them all on one table and put nulls in places where no data 
> is available.  With real database systems, there's no excuse not to.

Each of the daily/hourly/etc temperature readings are independent.
Therefore they should each have their own row in the "meteorology
readings" table.  I *think* that breaks 3NF.

This "should" be 3NF:

CREATE TABLE T_READING_TYPE (
READING_CODECHAR(4) PRIMARY KEY,
READING_DESCRIP  TEXT );

CREATE TABLE T_MET_READINGS (
_DATE DATE,
_HOUR SMALLINT CHECK (HOUR BETWEEN 0 AND 23),
READING_CODE  CHAR(4) REFERENCES T_READING_TYPE(READING_CODE),
READING_VALUE NUMERIC(8,3),
PRIMARY KEY (_DATE, _HOUR)
);


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF32j3S9HxQb37XmcRAgsgAKC7m74VtyU5rnOI0gF2VXjHxk9kXgCfVY86
i5hgysDkC7EUJWlbGL+vyZM=
=RN+L
-END PGP SIGNATURE-

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

Re: [GENERAL] postgresql vs mysql

[Glen Parker]

It cannot already do what I want, unless you blatantly ignore what I 
wrote.  Putting coalesce() calls *everywhere* counts as more work, don't 
you agree?


-Glen

Ben wrote:
But, why do you need an extension when the existing system can already 
do what you want?




---(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] postgresql vs mysql

[Peter Eisentraut]

Brandon Aiken wrote:
> That's why you make a table for every device or every measurement,
> and then use a view to consolidate it.  With updatable views, there's
> no excuse not to.

No, you put them all on one table and put nulls in places where no data 
is available.  With real database systems, there's no excuse not to.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Chad Wagner]

On 2/23/07, Bill Moran <[EMAIL PROTECTED]> wrote:


> In any case if anyone is interested I was able to reproduce the changes
that
> wikipgedia made and applied those changes (as well as others) all the
way up
> to the 1.6.10 codebase.  The only reason I mention this is because 1.6is
> the only choice for PHP4 users.  If anyone is interested I can provide
the
> codebase, the schema still has to be created manually as was the case
with
> wikipgedia.

I would be interested.  I'm probably expected to maintain this thing ...



You can download it from:

http://www.postgresqlforums.com/downloads/pgmediawiki-1.6.10.tar.gz

Again, like wikipgedia you have to create a schema (manually) named
mediawiki and like wikipgedia (because the port more or less used some of
the same mods they made) MySQL support is probably broken.

Re: [GENERAL] postgresql vs mysql

[Tino Wildenhain]

Glen Parker schrieb:

Ben wrote:
I'm sorry maybe I missed something, but if you don't need NULLs and 
feel they just add extra work, why don't you just declare all your 
columns to be not null and have them default to zero or an empty string?


Because I DO need NULLS for non text fields, and I still want NULL to 
compare equal to, say, '' and 0.  I don't think you read what I wrote...


Put another way, I would like to redefine NULL to mean BLANK or NOT 
ENTERED.  Totally different concept.


Not wise concept, but here you go:

WHERE coalesce(sometimesnull,'') = ''  or

WHERE coalesce(sometimesnull,0 ) = 0  ...

Regards
Tino

---(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] postgresql vs mysql

[Glen Parker]

That's absolutely correct.  What I want is a totally non standard 
*optional* extension, recognizing that many, even if not most, 
applications could benefit from it.  I think there's a clean way to do it.


I would never ask for such a thing if I thought it would effect an out 
of the box installation.


-Glen


If that works for your app, great. But in many (most?) cases it doesn't.
A survey, for example, might ask for age or income. Some people will
decline to answer one or both of those questions.




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

Re: [GENERAL] postgresql vs mysql

[Glen Parker]

Ben wrote:
I'm sorry maybe I missed something, but if you don't need NULLs and feel 
they just add extra work, why don't you just declare all your columns to 
be not null and have them default to zero or an empty string?


Because I DO need NULLS for non text fields, and I still want NULL to 
compare equal to, say, '' and 0.  I don't think you read what I wrote...


Put another way, I would like to redefine NULL to mean BLANK or NOT 
ENTERED.  Totally different concept.


-Glen


---(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] postgresql vs mysql

[Tino Wildenhain]

Steve Crawford schrieb:

Mark Walker wrote:

I'm not sure what you're trying to do but, it appears that you database
design is incorrect.  What you need is something like

CREATE TABLE temp_readings
(
 _date Date,
 temperature double,
 source varchar(20),
)

No reading, no record.  Are you suggesting that you would have a weekly
set of records for each row?

CREATE TABLE temp_readings
(
 weekstart date,
 sun double,
   mon double,
tues, double
etc
)

Not such a great way to do it.


Ummm, I'm not trying to make a temperature database. I was responding to
the previous poster with an extremely simple example of usefulness of
the _concept_ of "null".  I'm afraid I hadn't considered the possibility
that it would be mistaken as an example of an actual table.

But since you bring it up, simply omitting rows isn't necessarily an
option. A common scenario for weather observation is to take regular
snapshots or a bunch of measurements (air-temperature, humidity,
wind-speed, soil-temperature, leaf-wetness, UV radiation, etc.) which
can easily be represented in a table with a timestamp and a column for
each of the measurements. In a modular weather station where a specific
instrument can be out of service, one or more of those measurements
could be missing (null) for a period of time while the remaining
measurements are still being inserted.


Well I indeed have such a weather database, taking about 2 minute
snapshots of a couple of sensors. If one sensor does not respond
or is ignored due to error constraint, I just dont insert
a row: timestamp, sensor_id, sensorvalue, errorvalue

To do something usefull w/ the data you need to interpolate
anyway.

Just an example of how you can indeed avoid null values :-)

Regards
Tino

---(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] postgresql vs mysql

[Susemail]

On Thursday 22 February 2007 05:10, Rich Shepard wrote:
> On Thu, 22 Feb 2007, Tim Tassonis wrote:
> > I do still think it is a bit of an oddity, the concept of the null
> > column. From my experience, it creates more problems than it actually
> > solves and generally forces you to code more rather than less in order to
> > achieve your goals.
>
> Tim,
>
>Long ago, a lot of database applications used 99, or 999, or -1 to
> indicate an unknown value. However, those don't fit well with a textual
> field and they will certainly skew results if used in arithmetic
> calculations in numeric fields.
>
>The concept of NULL representing an unknown value, and therefore one
> that cannot be compared with any other value including other NULLs, is no
> different from the concept of zero which was not in mathematics for the
> longest time until some insightful Arab 

Indian, the Arabs learned of zero from the Indians.

> mathematician saw the need for a 
> representation of 'nothing' in arithmetic and higher mathematics.
>
>There was probably resistance to that idea, too, as folks tried to wrap
> their minds around the idea that 'nothing' could be validly represented by
> a symbol and it was actually necessary to advance beyond what the Greeks
> and Romans -- and everyone else -- could do. Now, one would be thought a
> bit strange to question the validity of zero.
>
>NULL solves as many intransigent problems with digital data storage and
> manipulation in databases as zero did in the realm of counting.
>
> HTH,
>
> Rich

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

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

Re: [GENERAL] postgresql vs mysql

[Steve Crawford]

Glen Parker wrote:
> Buy the same token, some application have no use whatsoever for the
> distinction between NULL and ''.  In that case, the distinction just
> adds work.

True, I suppose. But if I need that, I can live with a one-time "...not
null default ''..." addition to my table definition. Or a
coalesce(mycolumn, '') if I only need the null to equal '' in specific
queries or views.

> I would love to see different ways to handle NULL implemented by the
> server.  For what I do, NULL could always compare equal to zero and ''.
>  I have no use for NULL in text values.  I do need it for numerics,
> however it doesn't mean "unknown", it just means "not entered", which is
> different because I always treat it as zero.

If that works for your app, great. But in many (most?) cases it doesn't.
A survey, for example, might ask for age or income. Some people will
decline to answer one or both of those questions.

When someone asks for the average age of respondents, they want exactly
what avg() returns - the sum of the non-null ages divided by the count
of non-null ages. If the nulls were treated as zeros, the answer could
be severely skewed.

Cheers,
Steve

---(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] postgresql vs mysql

[Brandon Aiken]

That's why you make a table for every device or every measurement, and
then use a view to consolidate it.  With updatable views, there's no
excuse not to.

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Steve Crawford
Sent: Friday, February 23, 2007 1:04 PM
To: Mark Walker
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql vs mysql

Mark Walker wrote:
> I'm not sure what you're trying to do but, it appears that you
database
> design is incorrect.  What you need is something like
> 
> CREATE TABLE temp_readings
> (
>  _date Date,
>  temperature double,
>  source varchar(20),
> )
> 
> No reading, no record.  Are you suggesting that you would have a
weekly
> set of records for each row?
> 
> CREATE TABLE temp_readings
> (
>  weekstart date,
>  sun double,
>mon double,
> tues, double
> etc
> )
> 
> Not such a great way to do it.

Ummm, I'm not trying to make a temperature database. I was responding to
the previous poster with an extremely simple example of usefulness of
the _concept_ of "null".  I'm afraid I hadn't considered the possibility
that it would be mistaken as an example of an actual table.

But since you bring it up, simply omitting rows isn't necessarily an
option. A common scenario for weather observation is to take regular
snapshots or a bunch of measurements (air-temperature, humidity,
wind-speed, soil-temperature, leaf-wetness, UV radiation, etc.) which
can easily be represented in a table with a timestamp and a column for
each of the measurements. In a modular weather station where a specific
instrument can be out of service, one or more of those measurements
could be missing (null) for a period of time while the remaining
measurements are still being inserted.

Cheers,
Steve


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

   http://archives.postgresql.org/



** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and 
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged, 
confidential or proprietary information. If you are not the intended 
recipient(s), or the employee or agent responsible for delivery of 
this message to the intended recipient(s), you are hereby notified 
that any dissemination, distribution or copying of this e-mail 
message is strictly prohibited. If you have received this message in 
error, please immediately notify the sender and delete this e-mail 
message from your computer.

---(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] postgresql vs mysql

[Steve Crawford]

Mark Walker wrote:
> I'm not sure what you're trying to do but, it appears that you database
> design is incorrect.  What you need is something like
> 
> CREATE TABLE temp_readings
> (
>  _date Date,
>  temperature double,
>  source varchar(20),
> )
> 
> No reading, no record.  Are you suggesting that you would have a weekly
> set of records for each row?
> 
> CREATE TABLE temp_readings
> (
>  weekstart date,
>  sun double,
>mon double,
> tues, double
> etc
> )
> 
> Not such a great way to do it.

Ummm, I'm not trying to make a temperature database. I was responding to
the previous poster with an extremely simple example of usefulness of
the _concept_ of "null".  I'm afraid I hadn't considered the possibility
that it would be mistaken as an example of an actual table.

But since you bring it up, simply omitting rows isn't necessarily an
option. A common scenario for weather observation is to take regular
snapshots or a bunch of measurements (air-temperature, humidity,
wind-speed, soil-temperature, leaf-wetness, UV radiation, etc.) which
can easily be represented in a table with a timestamp and a column for
each of the measurements. In a modular weather station where a specific
instrument can be out of service, one or more of those measurements
could be missing (null) for a period of time while the remaining
measurements are still being inserted.

Cheers,
Steve


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

   http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Jim C. Nasby]

On Fri, Feb 23, 2007 at 01:49:06PM +1300, Andrej Ricnik-Bay wrote:
> On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
> >That depends greatly on what you're doing with it. Generally, as soon
> >as you start throwing a multi-user workload at it, MySQL stops
> >scaling. http://tweakers.net recently did a study on that.
> I think I recall that wikipedia uses MySQL ... they get quite a few
> hits, too, I believe.

And wikipedia has a massive distributed caching layer the spans the glob
(IIRC there's 128 cache machines).

I think a better example might be livejournal; the last time I ran the
numbers it should have been very reasonable to handle the entire update
load with a single database server and add slony slaves for read access
as needed. Instead they have a very, very complex system of spreading
user load across multiple clusters, etc. Because of that and mysql in
general, they've suffered a lot of pain and some lost data.
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(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] postgresql vs mysql

[Scott Ribe]

> In that case, the distinction just
> adds work.

In that case you declare the column not null and don't use nulls.

-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



---(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] postgresql vs mysql

[Tommy Gildseth]

Ben wrote:
I'm sorry maybe I missed something, but if you don't need NULLs and 
feel they just add extra work, why don't you just declare all your 
columns to be not null and have them default to zero or an empty string?


which is what mySQL does by default :-)
The statement
CREATE TABLE foo (bar INTEGER NOT NULL, rab VARHCAR(123) NOT NULL, oof 
DATETIME NOT NULL,);

will be rewritten automatically by mySQL to
CREATE TABLE foo (bar INTEGER NOT NULL DEFAULT 0, rab VARHCAR(123) NOT 
NULL DEFAULT '', oof DATETIME NOT NULL DEFAULT '-00-00 00:00');


Maybe if you really want to enforce a NOT NULL constraint in mySQL, you 
have to declare a column as NOT NULL DEFAULT NULL, explicitly as was 
suggested somewhere else in this thread. Fascinating how they probably 
thought that was a good idea.


--
Tommy


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

  http://archives.postgresql.org/

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[cedric]

Le vendredi 23 février 2007 16:37, Ian Harding a écrit :
> On 2/22/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > Joshua D. Drake escribió:
> > > Andrej Ricnik-Bay wrote:
> > > > On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
> > > >> That depends greatly on what you're doing with it. Generally, as
> > > >> soon as you start throwing a multi-user workload at it, MySQL stops
> > > >> scaling. http://tweakers.net recently did a study on that.
> > > >
> > > > I think I recall that wikipedia uses MySQL ... they get quite a few
> > > > hits, too, I believe.
> > >
> > > And outages if you watch :)
> >
> > Does this mean that we believe the Wikipedia would not suffer any
> > outages if it ran on Postgres?
> >
> > How is the Postgres port of the Wikipedia doing this days anyway?  Is it
> > in a shape where one would consider it "competitive"?
>
> I use mediawiki with postgres and it works fine, except for a bug
> regarding timestamps.  That bug is due to mysqlism of the code.  Once
> that's fixed, it will be ready as far as I'm concerned.
I get an error with tsearch2 query parser, and patch that. 
( http://bugzilla.wikimedia.org/show_bug.cgi?id=8958 , thanks Greg )
>
> There have been some tragic and embarrassing data losses by
> some big sites that should know better because they used mysql without
> the heroic measures that are needed to make it safe.  I don't care
> that much that big sites use it, big sites start small and don't
> always start with the best tools.  Once started, it's hard to switch
> over to better tools.  If you used enough volkswagen beetles you could
> move the same number of passengers on the same routes as Greyhound
> does with buses, but that doesn't mean they are the right
> tool.
>
>
> - Ian
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend

---(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: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Ian Harding]

On 2/22/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:

Joshua D. Drake escribió:
> Andrej Ricnik-Bay wrote:
> > On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
> >> That depends greatly on what you're doing with it. Generally, as soon
> >> as you start throwing a multi-user workload at it, MySQL stops
> >> scaling. http://tweakers.net recently did a study on that.
> > I think I recall that wikipedia uses MySQL ... they get quite a few
> > hits, too, I believe.
>
> And outages if you watch :)

Does this mean that we believe the Wikipedia would not suffer any
outages if it ran on Postgres?

How is the Postgres port of the Wikipedia doing this days anyway?  Is it
in a shape where one would consider it "competitive"?



I use mediawiki with postgres and it works fine, except for a bug
regarding timestamps.  That bug is due to mysqlism of the code.  Once
that's fixed, it will be ready as far as I'm concerned.

There have been some tragic and embarrassing data losses by
some big sites that should know better because they used mysql without
the heroic measures that are needed to make it safe.  I don't care
that much that big sites use it, big sites start small and don't
always start with the best tools.  Once started, it's hard to switch
over to better tools.  If you used enough volkswagen beetles you could
move the same number of passengers on the same routes as Greyhound
does with buses, but that doesn't mean they are the right
tool.


- Ian

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

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Bill Moran]

In response to "Chad Wagner" <[EMAIL PROTECTED]>:

> On 2/23/07, Bill Moran <[EMAIL PROTECTED]> wrote:
> >
> > I installed wikipgdia for the WPLUG wiki:
> > http://wplug.ece.cmu.edu/wiki/
> 
> Isn't that the same wikipgedia that is found at pgFoundry?

Yes.

> The only issue I
> really had the the wikipgedia port is that the codebase is 1.6alpha, and it
> seemed like it wasn't being actively maintained anymore (infact that is what
> the description says), so I am not sure it has all of the bug fixes up to
> 1.6.10.

I installed it as an experiment, then (while my back was turned) a bunch of
people started using it ... now it's a mission-critical part of the WPLUG
organization ...

Hopefully there aren't any serious bugs hiding anywhere ...

> In any case if anyone is interested I was able to reproduce the changes that
> wikipgedia made and applied those changes (as well as others) all the way up
> to the 1.6.10 codebase.  The only reason I mention this is because 1.6 is
> the only choice for PHP4 users.  If anyone is interested I can provide the
> codebase, the schema still has to be created manually as was the case with
> wikipgedia.

I would be interested.  I'm probably expected to maintain this thing ...

-- 
Bill Moran
Collaborative Fusion Inc.

---(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: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Chad Wagner]

On 2/23/07, Bill Moran <[EMAIL PROTECTED]> wrote:


I installed wikipgdia for the WPLUG wiki:
http://wplug.ece.cmu.edu/wiki/



Isn't that the same wikipgedia that is found at pgFoundry?  The only issue I
really had the the wikipgedia port is that the codebase is 1.6alpha, and it
seemed like it wasn't being actively maintained anymore (infact that is what
the description says), so I am not sure it has all of the bug fixes up to
1.6.10.

In any case if anyone is interested I was able to reproduce the changes that
wikipgedia made and applied those changes (as well as others) all the way up
to the 1.6.10 codebase.  The only reason I mention this is because 1.6 is
the only choice for PHP4 users.  If anyone is interested I can provide the
codebase, the schema still has to be created manually as was the case with
wikipgedia.

Re: [GENERAL] postgresql vs mysql

[Joshua D. Drake]

John Smith wrote:
> sounds like you aren't happy with one of the products your company
> offers at
> http://www.commandprompt.com/community/plphp/ - "plphp stands for
> procedural
> language php. the language has the php engine at its core and provides php
> scripting support for procedures and functions in postgresql. written by
> command prompt, inc. plphp is open source and licensed under the php
> license
> and the postgresql (bsd) license."
> 
> maybe you should also remove php 5.1.2 from
> http://planetpostgresql.org/-"hosting provided by the postgresql
> company, command prompt, inc".
> 
> our very own http://www.postgresql.org/ also uses php 5.2.0.
> 
> yeah yeah tco blah blah blah. try stopping support for php and watch the
> use
> plummet.


*cough*, you will note that I have already made the argument *for* PHP
if you read my posts.

Joshua D. Drake

-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

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

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Bill Moran]

Alvaro Herrera <[EMAIL PROTECTED]> wrote:
>
> Joshua D. Drake escribió:
> > Andrej Ricnik-Bay wrote:
> > > On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
> > >> That depends greatly on what you're doing with it. Generally, as soon
> > >> as you start throwing a multi-user workload at it, MySQL stops
> > >> scaling. http://tweakers.net recently did a study on that.
> > > I think I recall that wikipedia uses MySQL ... they get quite a few
> > > hits, too, I believe.
> > 
> > And outages if you watch :)
> 
> Does this mean that we believe the Wikipedia would not suffer any
> outages if it ran on Postgres?
> 
> How is the Postgres port of the Wikipedia doing this days anyway?  Is it
> in a shape where one would consider it "competitive"?

I installed wikipgdia for the WPLUG wiki:
http://wplug.ece.cmu.edu/wiki/

We haven't had a lick of trouble with it since it went up.  I don't
believe it's experienced any downtime in many months.

-- 
Bill Moran
Collaborative Fusion Inc.

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

   http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[John Smith]

sounds like you aren't happy with one of the products your company offers at
http://www.commandprompt.com/community/plphp/ - "plphp stands for procedural
language php. the language has the php engine at its core and provides php
scripting support for procedures and functions in postgresql. written by
command prompt, inc. plphp is open source and licensed under the php license
and the postgresql (bsd) license."

maybe you should also remove php 5.1.2 from
http://planetpostgresql.org/-"hosting provided by the postgresql
company, command prompt, inc".

our very own http://www.postgresql.org/ also uses php 5.2.0.

yeah yeah tco blah blah blah. try stopping support for php and watch the use
plummet.

and all this crap about php not having a firm design philosophy. hey, it
works well and is very widely adopted. so maybe that's what a good language
design should have- no firm set-in-stone straitjacket philosophy.

somebody else made a point about how jsp is better than php and then went
onto prove the reverse. if you design a language that has such a high tco
that common isps can't use it, it ain't good design- design doesn't work in
isolation.

speaking of obfuscatingly bloated 90s code- there're quite a few jsp
programmers who can't stomach getting kicked around by php, ajax and flash.

to tie this back to postgresql - want to grab the market share from mysql?
fork your strategy - postgresql v oracle and postgresqlite v mysql.
jzs

On 2/22/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote:

John Smith wrote:
> On 2/21/07, Lincoln Yeoh  wrote:
>> MySQL: the PHP of databases.
>
> 'd appreciate if you stick to the subject.

Oops he probably should not have used MySQL because it is trademarked...

mysql: The PHP of databases

;)

Sincerely,

Joshua D. Drake

> jzs

Re: [GENERAL] postgresql vs mysql

[Joshua D. Drake]

Ben wrote:
> I'm sorry maybe I missed something, but if you don't need NULLs and feel
> they just add extra work, why don't you just declare all your columns to
> be not null and have them default to zero or an empty string?

Stop making sense!

Joshua D. Drake

> 
> On Feb 22, 2007, at 5:11 PM, Glen Parker wrote:
> 
>> Buy the same token, some application have no use whatsoever for the
>> distinction between NULL and ''.  In that case, the distinction just
>> adds work.
>>
>> I would love to see different ways to handle NULL implemented by the
>> server.  For what I do, NULL could always compare equal to zero and
>> ''.  I have no use for NULL in text values.  I do need it for
>> numerics, however it doesn't mean "unknown", it just means "not
>> entered", which is different because I always treat it as zero.
>>
>> I haven't put enough thought into this to make any sort of
>> comprehensive proposal, but it occurs to me that perhaps it could be
>> integrated into the type system.  If I were able to specify, for any
>> given type, a value that should compare equal to NULL ('' for varchar,
>> 0 for int4, for example), that, in combination with NOT NULL
>> constraints, might just do it for me.
>>
>> -Glen
>>
>>> Well, your mileage must vary. The absence of nulls would make my life
>>> difficult.
>>> Just substitute "unknown" for "null" as mentioned above and the various
>>> operations with "null" make sense. For example, take some days and
>>> low-temperatures:
>>> Mon: 30
>>> Tue: 10
>>> Wed: 0
>>> Thu: unknown
>>> Fri: 0
>>> Sat: unknown
>>> Sun: -5
>>> Was the low temperature the same on:
>>> Mon/Tue: no
>>> Wed/Fri: yes
>>> Thu/Fri: unknown
>>> Thu/Sat: unknown <- the always seemingly confusing null=null is null.
> 
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

Re: [GENERAL] postgresql vs mysql

[Ben]

I'm sorry maybe I missed something, but if you don't need NULLs and  
feel they just add extra work, why don't you just declare all your  
columns to be not null and have them default to zero or an empty string?


On Feb 22, 2007, at 5:11 PM, Glen Parker wrote:

Buy the same token, some application have no use whatsoever for the  
distinction between NULL and ''.  In that case, the distinction  
just adds work.


I would love to see different ways to handle NULL implemented by  
the server.  For what I do, NULL could always compare equal to zero  
and ''.  I have no use for NULL in text values.  I do need it for  
numerics, however it doesn't mean "unknown", it just means "not  
entered", which is different because I always treat it as zero.


I haven't put enough thought into this to make any sort of  
comprehensive proposal, but it occurs to me that perhaps it could  
be integrated into the type system.  If I were able to specify, for  
any given type, a value that should compare equal to NULL ('' for  
varchar, 0 for int4, for example), that, in combination with NOT  
NULL constraints, might just do it for me.


-Glen


Well, your mileage must vary. The absence of nulls would make my life
difficult.
Just substitute "unknown" for "null" as mentioned above and the  
various

operations with "null" make sense. For example, take some days and
low-temperatures:
Mon: 30
Tue: 10
Wed: 0
Thu: unknown
Fri: 0
Sat: unknown
Sun: -5
Was the low temperature the same on:
Mon/Tue: no
Wed/Fri: yes
Thu/Fri: unknown
Thu/Sat: unknown <- the always seemingly confusing null=null is null.



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

Re: [GENERAL] postgresql vs mysql

[Andrej Ricnik-Bay]

On 2/23/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote:

Andrej Ricnik-Bay wrote:
> On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
>> That depends greatly on what you're doing with it. Generally, as soon
>> as you start throwing a multi-user workload at it, MySQL stops
>> scaling. http://tweakers.net recently did a study on that.
> I think I recall that wikipedia uses MySQL ... they get quite a few
> hits, too, I believe.

And outages if you watch :)

Well, there is that ... I didn't throw that in because I think MySQL is
great, on the contrary - we're having some issues with it here at
work (don't ask), but it is being used for large installations, too.
And then of
course there is this
http://www.linuxquestions.org/questions/showthread.php?p=2141344#post2141344
post where this guy (no idea whether he is (or was) what he claimed
to be) rambling on about how MySQL is better than PostgreSQL.



Joshua D. Drake

Cheers,
Andrej

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

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Joshua D. Drake]

Alvaro Herrera wrote:
> Joshua D. Drake escribió:
>> Alvaro Herrera wrote:
>>> Joshua D. Drake escribió:
 Andrej Ricnik-Bay wrote:
> On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
>> That depends greatly on what you're doing with it. Generally, as soon
>> as you start throwing a multi-user workload at it, MySQL stops
>> scaling. http://tweakers.net recently did a study on that.
> I think I recall that wikipedia uses MySQL ... they get quite a few
> hits, too, I believe.
 And outages if you watch :)
>>> Does this mean that we believe the Wikipedia would not suffer any
>>> outages if it ran on Postgres?
>> I believe it would suffer less outage yes.
> 
> And how is SourceForge doing these days, by the way?

Wonderful of course :)

Sincerely,

Joshua D. Drake



-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(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: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Alvaro Herrera]

Joshua D. Drake escribió:
> Alvaro Herrera wrote:
> > Joshua D. Drake escribió:
> >> Andrej Ricnik-Bay wrote:
> >>> On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
>  That depends greatly on what you're doing with it. Generally, as soon
>  as you start throwing a multi-user workload at it, MySQL stops
>  scaling. http://tweakers.net recently did a study on that.
> >>> I think I recall that wikipedia uses MySQL ... they get quite a few
> >>> hits, too, I believe.
> >> And outages if you watch :)
> > 
> > Does this mean that we believe the Wikipedia would not suffer any
> > outages if it ran on Postgres?
> 
> I believe it would suffer less outage yes.

And how is SourceForge doing these days, by the way?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(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] postgresql vs mysql

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/22/07 19:04, Mark Walker wrote:
> I'm not sure what you're trying to do but, it appears that you database
> design is incorrect.  What you need is something like
> 
> CREATE TABLE temp_readings
> (
>  _date Date,
>  temperature double,
>  source varchar(20),
> )
> 
> No reading, no record.  Are you suggesting that you would have a weekly
> set of records for each row?

But you still need NULL/UNKNOWN for outer joins.

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF3kkVS9HxQb37XmcRAv7yAJ9Py3X/WGt+qe7R8WF1zyFIO38tNQCfZBaN
GCWtuQ48h1dh5eTL5TSv0cA=
=oFIk
-END PGP SIGNATURE-

---(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: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Joshua D. Drake]

Alvaro Herrera wrote:
> Joshua D. Drake escribió:
>> Andrej Ricnik-Bay wrote:
>>> On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
 That depends greatly on what you're doing with it. Generally, as soon
 as you start throwing a multi-user workload at it, MySQL stops
 scaling. http://tweakers.net recently did a study on that.
>>> I think I recall that wikipedia uses MySQL ... they get quite a few
>>> hits, too, I believe.
>> And outages if you watch :)
> 
> Does this mean that we believe the Wikipedia would not suffer any
> outages if it ran on Postgres?

I believe it would suffer less outage yes.

> 
> How is the Postgres port of the Wikipedia doing this days anyway?  Is it
> in a shape where one would consider it "competitive"?

I don't know, I believe citizideum or whatever it is called is
PostgreSQL based.

Joshua D. Drake

> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

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

Re: [GENERAL] postgresql vs mysql

[Glen Parker]

Buy the same token, some application have no use whatsoever for the 
distinction between NULL and ''.  In that case, the distinction just 
adds work.


I would love to see different ways to handle NULL implemented by the 
server.  For what I do, NULL could always compare equal to zero and ''. 
 I have no use for NULL in text values.  I do need it for numerics, 
however it doesn't mean "unknown", it just means "not entered", which is 
different because I always treat it as zero.


I haven't put enough thought into this to make any sort of comprehensive 
proposal, but it occurs to me that perhaps it could be integrated into 
the type system.  If I were able to specify, for any given type, a value 
that should compare equal to NULL ('' for varchar, 0 for int4, for 
example), that, in combination with NOT NULL constraints, might just do 
it for me.


-Glen


Well, your mileage must vary. The absence of nulls would make my life
difficult.

Just substitute "unknown" for "null" as mentioned above and the various
operations with "null" make sense. For example, take some days and
low-temperatures:
Mon: 30
Tue: 10
Wed: 0
Thu: unknown
Fri: 0
Sat: unknown
Sun: -5

Was the low temperature the same on:
Mon/Tue: no
Wed/Fri: yes
Thu/Fri: unknown
Thu/Sat: unknown <- the always seemingly confusing null=null is null.





---(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

Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Alvaro Herrera]

Joshua D. Drake escribió:
> Andrej Ricnik-Bay wrote:
> > On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
> >> That depends greatly on what you're doing with it. Generally, as soon
> >> as you start throwing a multi-user workload at it, MySQL stops
> >> scaling. http://tweakers.net recently did a study on that.
> > I think I recall that wikipedia uses MySQL ... they get quite a few
> > hits, too, I believe.
> 
> And outages if you watch :)

Does this mean that we believe the Wikipedia would not suffer any
outages if it ran on Postgres?

How is the Postgres port of the Wikipedia doing this days anyway?  Is it
in a shape where one would consider it "competitive"?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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

Re: [GENERAL] postgresql vs mysql

[Mark Walker]

I'm not sure what you're trying to do but, it appears that you database 
design is incorrect.  What you need is something like


CREATE TABLE temp_readings
(
 _date Date,
 temperature double,
 source varchar(20),
)

No reading, no record.  Are you suggesting that you would have a weekly 
set of records for each row?


CREATE TABLE temp_readings
(
 weekstart date,
 sun double,
   mon double,
tues, double
etc
)

Not such a great way to do it.




Well, your mileage must vary. The absence of nulls would make my life
difficult.

Just substitute "unknown" for "null" as mentioned above and the various
operations with "null" make sense. For example, take some days and
low-temperatures:
Mon: 30
Tue: 10
Wed: 0
Thu: unknown
Fri: 0
Sat: unknown
Sun: -5

Was the low temperature the same on:
Mon/Tue: no
Wed/Fri: yes
Thu/Fri: unknown
Thu/Sat: unknown <- the always seemingly confusing null=null is null.

So what do we do without a null? Does the "helpful" app convert the
unknowns to zero? That's not right. Are we forced to specify a "special"
value like 999 for the unknown data? Then we have to add extra code to
create that value when the value is unknown and more code still to check
for that value when, say, looking for the lowest or average
temperatures. And we're set up for disaster when someone starts
measuring furnace temps instead of outdoor temps.

Look no further than Y2K to see what happened to those apps that gave
special meaning to 12/31/99.

Cheers,
Steve

---(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 3: Have you checked our extensive FAQ?

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

Re: [GENERAL] postgresql vs mysql

[Joshua D. Drake]

Andrej Ricnik-Bay wrote:
> On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
>> That depends greatly on what you're doing with it. Generally, as soon
>> as you start throwing a multi-user workload at it, MySQL stops
>> scaling. http://tweakers.net recently did a study on that.
> I think I recall that wikipedia uses MySQL ... they get quite a few
> hits, too, I believe.

And outages if you watch :)

Joshua D. Drake

> 
> 
> Cheers,
> Andrej
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>   http://archives.postgresql.org/
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

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

Re: [GENERAL] postgresql vs mysql

[Steve Crawford]

Tim Tassonis wrote:
> Chris wrote:
>> Erick Papadakis wrote:
>>> So how should I make a database rule in MySQL to not allow blank
>>> strings. Basically to REQUIRE a value for that column, whether it is
>>> NULL or NADA or VOID or whatever you wish to call it. I just want to
>>> make sure that something, some value, is entered for a column. Would
>>> appreciate any thoughts or pointers.
>>>
>>> Does PostgreSQL suffer from this oddity as well? This distinction
>>> between an empty string and a NULL? Could you also please give me an
>>> example of where this would be useful from a business logic
>>> standpoint? Why should a NULL be different from an empty string,
>>> what's the big mysterious difference?
>>
>> It's not an oddity.
>>
>> An empty string is a KNOWN value. You know exactly what that value is
>> - it's an empty string.
>>
>> A NULL is UNKNOWN - it doesn't have a value at all.
> 
> I do still think it is a bit of an oddity, the concept of the null
> column. From my experience, it creates more problems than it actually
> solves and generally forces you to code more rather than less in order
> to achieve your goals.

Well, your mileage must vary. The absence of nulls would make my life
difficult.

Just substitute "unknown" for "null" as mentioned above and the various
operations with "null" make sense. For example, take some days and
low-temperatures:
Mon: 30
Tue: 10
Wed: 0
Thu: unknown
Fri: 0
Sat: unknown
Sun: -5

Was the low temperature the same on:
Mon/Tue: no
Wed/Fri: yes
Thu/Fri: unknown
Thu/Sat: unknown <- the always seemingly confusing null=null is null.

So what do we do without a null? Does the "helpful" app convert the
unknowns to zero? That's not right. Are we forced to specify a "special"
value like 999 for the unknown data? Then we have to add extra code to
create that value when the value is unknown and more code still to check
for that value when, say, looking for the lowest or average
temperatures. And we're set up for disaster when someone starts
measuring furnace temps instead of outdoor temps.

Look no further than Y2K to see what happened to those apps that gave
special meaning to 12/31/99.

Cheers,
Steve

---(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] postgresql vs mysql

[Andrej Ricnik-Bay]

On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:

That depends greatly on what you're doing with it. Generally, as soon
as you start throwing a multi-user workload at it, MySQL stops
scaling. http://tweakers.net recently did a study on that.

I think I recall that wikipedia uses MySQL ... they get quite a few
hits, too, I believe.


Cheers,
Andrej

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

  http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Steve Crawford]

Ron Johnson wrote:
> On 02/21/07 18:09, Erick Papadakis wrote:
>> How would you like to use a database that has nuances like these --
>> http://forums.mysql.com/read.php?20,141120,141120#msg-141120
> 
> Huh?
> 
> A blank string (does that mean '' or ' '?) is not NULL, so of
> *course* it should pass the NOT NULL constraint.
> 
> Or am I missing something?
> 

Not sure what the OP was getting at but of course an empty string is not
the same as a NULL. You have to follow that MySQL forum thread a few
messages to see the real screwup. Upon learning that an empty string is
not the same a a NULL, he adds a constraint:

ALTER TABLE `tbl`
CHANGE `col`
`col` VARCHAR( 3 ) CHECK (`col` <>'')
NOT NULL

Unfortunately for him, this does not work either. The reason is
clarified in the following message:

"Currently MySQL accepts CHECK syntax but does not implement them. You
can enforce such a rule at database level with a trigger, or at
application level as another contributor suggested."

Huh?

Cheers,
Steve

---(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] postgresql vs mysql

[Scott Marlowe]

On Thu, 2007-02-22 at 18:48 -0500, Brandon Aiken wrote:
> Digg and Slashdot use MySQL databases, so clearly they *can* be made to
> support a high-load, high-performance, limited-write style web
> application.  
> 
> You might remember a few months back when SlashDot had to turn off
> threaded replies because the schema for the parent-child field was still
> an UNSIGNED INT4 instead of an UNSIGNED INT8, and they reached the
> maximum value of the field (16.7 million).  Obviously, I have no
> knowledge of the server configuration, hardware configuration, or
> schema, but in-the-wild examples of high performance MySQL installations
> are trivial to find (as are PostgreSQL installations such as the .org
> DNS TLD root).

Actually this has been mentioned before, Slashdot is a good example of
how poorly MySQL scales.  Almost every page you view on slashdot is
actually a static page harvested every x minutes by another process
because dynamically generating those pages is very expensive.

If slashdot was able to run on top of MySQL without all that hand
holding, then it would be a showcase for it.

> I'd like to see a tuned MySQL vs a similarly tuned PostgreSQL system
> (that is, fsync in the same state and with the same level of ACID
> compliance) subject to a battery of test schema types (OLTP, OLAP,
> etc.).

Me too.  But I gave up on using MySQL for serious uses some time ago
when I realized that updates for serious bug fixes took years to come
out of the pipeline, and some will simply never be fixed.

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

Re: [GENERAL] postgresql vs mysql

[Brandon Aiken]

Digg and Slashdot use MySQL databases, so clearly they *can* be made to
support a high-load, high-performance, limited-write style web
application.  

You might remember a few months back when SlashDot had to turn off
threaded replies because the schema for the parent-child field was still
an UNSIGNED INT4 instead of an UNSIGNED INT8, and they reached the
maximum value of the field (16.7 million).  Obviously, I have no
knowledge of the server configuration, hardware configuration, or
schema, but in-the-wild examples of high performance MySQL installations
are trivial to find (as are PostgreSQL installations such as the .org
DNS TLD root).

I'd like to see a tuned MySQL vs a similarly tuned PostgreSQL system
(that is, fsync in the same state and with the same level of ACID
compliance) subject to a battery of test schema types (OLTP, OLAP,
etc.).

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: Jim Nasby [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 22, 2007 6:28 PM
To: Brandon Aiken
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql vs mysql

On Feb 21, 2007, at 2:23 PM, Brandon Aiken wrote:
> IMX, the only things going for MySQL are:
> 1. It's fast.

That depends greatly on what you're doing with it. Generally, as soon  
as you start throwing a multi-user workload at it, MySQL stops  
scaling. http://tweakers.net recently did a study on that.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)





** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and 
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged, 
confidential or proprietary information. If you are not the intended 
recipient(s), or the employee or agent responsible for delivery of 
this message to the intended recipient(s), you are hereby notified 
that any dissemination, distribution or copying of this e-mail 
message is strictly prohibited. If you have received this message in 
error, please immediately notify the sender and delete this e-mail 
message from your computer.

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

Re: [GENERAL] postgresql vs mysql

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/22/07 17:17, Jim Nasby wrote:
> On Feb 21, 2007, at 10:26 AM, Scott Marlowe wrote:
>> The only thing I can think of that rewrites a whole postgresql table
>> would be reindexing it, or an update without a where clause (or a where
>> clause that includes every row).  Normal operations, like create index,
>> add column, drop column, etc do not need to rewrite the table and happen
>> almost instantly.
> 
> Reindexing won't re-write a table; clustering will. Also some ALTER
> TABLE commands will (such as changing the data type of a column, or
> creating a new column that's NOT NULL).

Man, that's just Not Right.


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF3itiS9HxQb37XmcRAqnWAJ0R9MmdmlUR92B3F81vlGI/D7Es8ACgmiKI
F6BrBA/ZeTsciqJAdiYfTCo=
=KJDL
-END PGP SIGNATURE-

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

Re: [GENERAL] postgresql vs mysql

[Jim Nasby]

On Feb 21, 2007, at 2:23 PM, Brandon Aiken wrote:

IMX, the only things going for MySQL are:
1. It's fast.


That depends greatly on what you're doing with it. Generally, as soon  
as you start throwing a multi-user workload at it, MySQL stops  
scaling. http://tweakers.net recently did a study on that.

--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



---(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] postgresql vs mysql

[Jim Nasby]

On Feb 21, 2007, at 10:26 AM, Scott Marlowe wrote:

The only thing I can think of that rewrites a whole postgresql table
would be reindexing it, or an update without a where clause (or a  
where
clause that includes every row).  Normal operations, like create  
index,
add column, drop column, etc do not need to rewrite the table and  
happen

almost instantly.


Reindexing won't re-write a table; clustering will. Also some ALTER  
TABLE commands will (such as changing the data type of a column, or  
creating a new column that's NOT NULL).

--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



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

  http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Jim Nasby]

On Feb 20, 2007, at 11:59 PM, Adam Rich wrote:

"As of 5.0.2, the server requires that month and day values
be legal, and not merely in the range 1 to 12 and 1 to 31,
respectively."


Yes, but any session is free to change that setting and insert  
whatever garbage they want. AFAIK there's absolutely no way to  
prevent that. So your data is still very much subject to getting  
trashed.


Of course, there's also plenty of ways to do that on accident, mostly  
involving mistakenly ending up with a MyISAM table instead of an  
InnoDB one.

--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



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

  http://archives.postgresql.org/

Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql

[Shashank Tripathi]

 I would do a CHECK (trim(a) <> '')



TRIM() would add some processing time, so I'd include it only if there
was a chance of spaces getting added. From a puritanical point of
view, it is definitely a good idea.

To the original poster, this syntax should work in MySQL as well:

  create table mytable (mycol text not null check (mycol <> ''));

Problem is, if you created your table before MySQL 5, and now simply
want to ALTER your table (which is what I gather you wish to do, as
you already have the table) then adding the CHECK condition may not
work.

I cannot help in this case, and from the turn this thread has taken,
not many others I suppose. Why not try a MySQL experts list instead of
PostgreSQL, but be prepared to have to recreate the table in MySQL 5
with the CHECK constraint, and then importing your data in to it.

Good luck!

---(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: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql

[Shashank Tripathi]

On 22/02/07, Shashank Tripathi <[EMAIL PROTECTED]> wrote:

>  I would do a CHECK (trim(a) <> '')


TRIM() would add some processing time, so I'd include it only if there
was a chance of spaces getting added. From a puritanical point of
view, it is definitely a good idea.

To the original poster, this syntax should work in MySQL as well:

   create table mytable (mycol text not null check (mycol <> ''));

Problem is, if you created your table before MySQL 5, and now simply
want to ALTER your table (which is what I gather you wish to do, as
you already have the table) then adding the CHECK condition may not
work.

I cannot help in this case, and from the turn this thread has taken,
not many others I suppose. Why not try a MySQL experts list instead of
PostgreSQL, but be prepared to have to recreate the table in MySQL 5
with the CHECK constraint, and then importing your data in to it.

Good luck!




Sorry, I spoke too soon. MySQL does not do the constraints jig yet.

"The CHECK  clause is parsed but ignored by all storage engines."
- From http://dev.mysql.com/doc/refman/5.0/en/create-table.html

So you may want to adopt some kludges, such as updatable views:
http://arjen-lentz.livejournal.com/49881.html

If I were you, I'd just stick to error-checking in the application
layer for now, or consider slowly switching to PostgreSQL. (No plug
intended)

Shanx

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

Re: [GENERAL] postgresql vs mysql

[Mark Walker]

If you don't know something, why are you trying to record it?  From a strict 
relational sense, the existence of NULL values in your fields indicates that 
your primary keys are not

truly candidate keys for all your fields.  That means your database isn't [BCNF] 
normalized.<<<

I agree that there are very few times when NULL is appropriate in a database.  I can't think of a single concrete example to use it in a database field. 


It has its use in programming, mainly as a memory management/trash collection 
mechanism.  Basically, you don't want to delete something that doesn't exist.

For example:

Statement *st = NULL;
ResultSet *rs = NULL;
try
{
st = prepareStatement("select * from customers");
rs = st->executeQuery();
while (rs->next())
{
do something
}
delete st;
delete rs;
}
catch (Exception e)
{
if (st != NULL)
   delete st;
if (rs != NULL)
   delete rs;
}



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

Re: [GENERAL] postgresql vs mysql

[Brandon Aiken]

If you can remove NULLs without breaking OUTER JOIN, more power to you.

In the vast majority of cases, all fields in a table should have a NOT
NULL constraint.  Storing a NULL value makes little sense, since you're
storing something you don't know.  If you don't know something, why are
you trying to record it?  From a strict relational sense, the existence
of NULL values in your fields indicates that your primary keys are not
truly candidate keys for all your fields.  That means your database
isn't [BCNF] normalized.

Arguments about de-normalization generally result in the basic
limitation in nearly all RDBMS's that they do not allow you to optimize
how data is physically stored on disk.  That is, a generalized SQL
database like Oracle, MySQL, PostgreSQL, etc. sacrifice the ability to
control how data is physically store in order to be a generalized
database that can store generic domains in the form of the most common
datatypes that computer programs use.  

This is a basic limitation of using a generalized database engine, and
if your application demands higher performance than you can get with a
general RDBMS, you'll have to develop your own task-specific RDBMS or
modify your schema so that the problem can be mitigated.  Schema
de-normalization is a way of purposefully degrading the normal quality
of your schema in order to make up for shortcomings of the database
engine and limitations of computerized data storage.  As long as you
understand that de-normalization is a practical workaround and never a
wise logical design choice from the get-go, you shouldn't feel too bad
about doing it.

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tim Tassonis
Sent: Thursday, February 22, 2007 10:31 AM
To: Rich Shepard
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql vs mysql

Rich Shepard wrote:
> On Thu, 22 Feb 2007, Tim Tassonis wrote:
> 
>> I do still think it is a bit of an oddity, the concept of the null 
>> column.
>> From my experience, it creates more problems than it actually solves
and
>> generally forces you to code more rather than less in order to
achieve
>> your goals.
> 
> Tim,
> 
>   Long ago, a lot of database applications used 99, or 999, or -1 to
> indicate an unknown value. However, those don't fit well with a
textual
> field and they will certainly skew results if used in arithmetic
> calculations in numeric fields.

I remember, my first database to write stuff for was an IMB IMS 
hierarchical/network one.

> 
>   The concept of NULL representing an unknown value, and therefore one
that
> cannot be compared with any other value including other NULLs, is no
> different from the concept of zero which was not in mathematics for
the
> longest time until some insightful Arab mathematician saw the need for
a
> representation of 'nothing' in arithmetic and higher mathematics.
> 
>   There was probably resistance to that idea, too, as folks tried to
wrap
> their minds around the idea that 'nothing' could be validly
represented 
> by a
> symbol and it was actually necessary to advance beyond what the Greeks
and
> Romans -- and everyone else -- could do. Now, one would be thought a
bit
> strange to question the validity of zero.

That's one point for me, then!. NULL exactly is _not_ the equivalent the

the number 0, but the mentioned strange symbol that has to be treated 
specially and does not allow normal calculation, like '0' does in 
mathematics. I don't know how many times I had to write a query that 
ends with:

- or column is null
- and column is not null

exactly because it is a special symbol. In mathematics, the only special

case for zero that springs to my mind is the division of something by 
zero (I'm by no means a mathematician).

As a completely irrelevant sidenote to the discussion, I'm greek and not

arabic, but I certinly do accept the superiority of the arabic notation.

> 
>   NULL solves as many intransigent problems with digital data storage
and
> manipulation in databases as zero did in the realm of counting.

As I said, I don't deny it solves some problems (that could be solved in

a different way, too), but in my opinion, it creates more (that also can

  be solved, as above examples show).

Tim


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

   http://archives.postgresql.org/



** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and 
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged, 
confidential or proprietary 

Re: [GENERAL] postgresql vs mysql

[Chad Wagner]

On 2/22/07, Martijn van Oosterhout  wrote:


On Thu, Feb 22, 2007 at 12:05:20PM +1100, Chris wrote:
> >SELECT foo, bar, COUNT(*)
> >FROM baz
> >GROUP BY foo

> That one actually comes in handy ;) Especially in older versions (4.0)
> that don't support subselects..

I must say I don't see any reasonable way of interpreting the above
query. Is the value of bar selected randomly?



The value of bar happens to be the first value fetched based on the GROUP BY
of foo, not sure how predictable and repeatable it is.

Re: [GENERAL] postgresql vs mysql (OT: perl)

[Kevin Murphy]

Randal L. Schwartz wrote:

Russ> Take perl for example. I have still yet to see readable Perl code.

You can't read it if you're not familiar with it. 
Seconded.  Perl is like the churkendoose -- hybrid strength, ugly as 
hell, only poultry known that can scare off a fox every time, whole 
barnyard loves having it around.  The better I know it, the better I 
like it.


A language's appeal always depends on your prior language and operating 
system experience, not to mention what you're trying to use it for, how 
you learn it, the support infrastructure around you while learning it, 
how much you're getting paid (in fame, fortune, and/or fun) to program 
in it, how long you've used it, how often you use it, what other 
languages you use alongside of it, who your friends are, how much of a 
sucker you are for what you read in the tech press, how patient you are, 
how creative you are, your tolerance for abstraction, the number and 
quality of programmers you want to be able to potentially contribute to 
or maintain your code, etc, etc.


It's a big barnyard, and it all stinks.  So let's just roll around in 
the mud and have some fun.


-Kevin Murphy


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

Re: [GENERAL] postgresql vs mysql

[Lincoln Yeoh]

At 10:22 PM 2/22/2007, Tim Tassonis wrote:

Chris wrote:
An empty string is a KNOWN value. You know exactly what that value 
is - it's an empty string.

A NULL is UNKNOWN - it doesn't have a value at all.


I do still think it is a bit of an oddity, the concept of the null 
column. From my experience, it creates more problems than it 
actually solves and generally forces you to code more rather than 
less in order to achieve your goals.


But as it is a fundamental, defined part of the sql standard, one 
just has to live with it.


Well it can be useful to have a column like:
foo integer not null default null

That means someone/something must specify a value for foo when doing 
an insert. They can't just hope for the best that there's a default...


I think that works on postgresql but not on MySQL (see back on topic :) ).

Have fun!
Link.


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

Re: [GENERAL] postgresql vs mysql

[Lincoln Yeoh]

At 01:11 PM 2/22/2007, John Smith wrote:

On 2/21/07, Lincoln Yeoh  wrote:

MySQL: the PHP of databases.


'd appreciate if you stick to the subject.
jzs


OK sorry... That was more of a footnote.

Link.





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

Re: [GENERAL] postgresql vs mysql

[Randal L. Schwartz]

> "Russ" == Russ Brown <[EMAIL PROTECTED]> writes:

Russ> Take perl for example. I have still yet to see readable Perl code.

I could say the same for greek, and pl/pgsql.

You can't read it if you're not familiar with it.  Please stop bashing Perl
until you've read at least Learning Perl or the equivalent.  Please.  You have
no right.  It's pure prejudice, and usually just parroted from others.

-- 
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
 http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

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

   http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Tim Tassonis]

Rich Shepard wrote:

On Thu, 22 Feb 2007, Tim Tassonis wrote:

I do still think it is a bit of an oddity, the concept of the null 
column.

From my experience, it creates more problems than it actually solves and
generally forces you to code more rather than less in order to achieve
your goals.


Tim,

  Long ago, a lot of database applications used 99, or 999, or -1 to
indicate an unknown value. However, those don't fit well with a textual
field and they will certainly skew results if used in arithmetic
calculations in numeric fields.


I remember, my first database to write stuff for was an IMB IMS 
hierarchical/network one.




  The concept of NULL representing an unknown value, and therefore one that
cannot be compared with any other value including other NULLs, is no
different from the concept of zero which was not in mathematics for the
longest time until some insightful Arab mathematician saw the need for a
representation of 'nothing' in arithmetic and higher mathematics.

  There was probably resistance to that idea, too, as folks tried to wrap
their minds around the idea that 'nothing' could be validly represented 
by a

symbol and it was actually necessary to advance beyond what the Greeks and
Romans -- and everyone else -- could do. Now, one would be thought a bit
strange to question the validity of zero.


That's one point for me, then!. NULL exactly is _not_ the equivalent the 
the number 0, but the mentioned strange symbol that has to be treated 
specially and does not allow normal calculation, like '0' does in 
mathematics. I don't know how many times I had to write a query that 
ends with:


- or column is null
- and column is not null

exactly because it is a special symbol. In mathematics, the only special 
case for zero that springs to my mind is the division of something by 
zero (I'm by no means a mathematician).


As a completely irrelevant sidenote to the discussion, I'm greek and not 
arabic, but I certinly do accept the superiority of the arabic notation.




  NULL solves as many intransigent problems with digital data storage and
manipulation in databases as zero did in the realm of counting.


As I said, I don't deny it solves some problems (that could be solved in 
a different way, too), but in my opinion, it creates more (that also can 
 be solved, as above examples show).


Tim


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

  http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Rich Shepard]

On Thu, 22 Feb 2007, Tim Tassonis wrote:


I do still think it is a bit of an oddity, the concept of the null column.
From my experience, it creates more problems than it actually solves and
generally forces you to code more rather than less in order to achieve
your goals.


Tim,

  Long ago, a lot of database applications used 99, or 999, or -1 to
indicate an unknown value. However, those don't fit well with a textual
field and they will certainly skew results if used in arithmetic
calculations in numeric fields.

  The concept of NULL representing an unknown value, and therefore one that
cannot be compared with any other value including other NULLs, is no
different from the concept of zero which was not in mathematics for the
longest time until some insightful Arab mathematician saw the need for a
representation of 'nothing' in arithmetic and higher mathematics.

  There was probably resistance to that idea, too, as folks tried to wrap
their minds around the idea that 'nothing' could be validly represented by a
symbol and it was actually necessary to advance beyond what the Greeks and
Romans -- and everyone else -- could do. Now, one would be thought a bit
strange to question the validity of zero.

  NULL solves as many intransigent problems with digital data storage and
manipulation in databases as zero did in the realm of counting.

HTH,

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [GENERAL] postgresql vs mysql

[Clodoaldo]

2007/2/22, Russ Brown <[EMAIL PROTECTED]>:

Ron Johnson wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> My definition is, "toy used/trumpeted by pseudo-professionals as a
> professional tool, when it just doesn't measure up".
>

/me Tries really hard to resist responding

/me Fails

I'm sorry, but having just been described as a 'pseudo-professional' I
simply have to defend myself, and my colleagues.

I don't want to start a language war here, and this is the last post
that I will make on this subject, but it's perfectly possible to
criticise pretty much any programming language out there with a whole
raft of reasons as to why it's not a good language to use
professionally. Take perl for example. I have still yet to see readable
Perl code. Unless you're very careful C lets you write code that leaks
or overwrites memory left right and centre with all kinds of security
flaws possible as a result. I could go on with other languages but I
think you get my point.

There is however a point in saying that while PHP does have its numerous
inconsistencies and oddities, it is *perfectly* possible to write good
quality, well performing, maintainable, clean and well designed code in
PHP. Without blowing my own trumpet, I and my colleagues do it every
day, and we work very hard at it too. Oh yes, we've seen plenty of bad
code, but that's the skill of a good developer (to spot the bad code)
and the challenge of a professional one (taking the care to deal with
the bad code rather than just leaving it be). You just have to be more
careful in PHP not to fall into the traps (that a good developer knows
about), which is exactly like a C developer knowing how not to introduce
buffer overruns etc.


I think you made a very good point and I want to second it.

Although I have been a Python enthusiast for some years and today I do
all new developments with Python, I have written somethings with PHP
(and ASP) which were very succesfull.

Indeed when I have to install sofware I look at the final product not
at the language in which it was written. As an example I have
installed Mediawiki (php) in instead of Moinmoin (python) because
Mediawiki is years ahead of Moinmoin.

Regards,
--
Clodoaldo Pinto Neto

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

Re: [GENERAL] postgresql vs mysql

[Martijn van Oosterhout]

On Thu, Feb 22, 2007 at 12:05:20PM +1100, Chris wrote:
> >SELECT foo, bar, COUNT(*)
> >FROM baz
> >GROUP BY foo

> That one actually comes in handy ;) Especially in older versions (4.0) 
> that don't support subselects..

I must say I don't see any reasonable way of interpreting the above
query. Is the value of bar selected randomly?

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql

[brian]

Zoltan Boszormenyi wrote:


I would do a CHECK (trim(a) <> '')




If you were ok with a string consisting soley of whitespace.



I meant NOT NULL CHECK(trim(a) <> ''), keeping the context of the
above example.


Right. I plead that it was late when i replied. I honestly don't know
what i was thinking.


trim() trims whitespace only from the beginning and the end of the
strings but not from the middle:

# select trim('   a b c   ');


I realise that. I was thinking of this case:

select trim('');

Though, i'm not sure what my point was. Again, it was late. Carry on!

brian

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

Re: [GENERAL] postgresql vs mysql

[Tim Tassonis]

Chris wrote:

Erick Papadakis wrote:

So how should I make a database rule in MySQL to not allow blank
strings. Basically to REQUIRE a value for that column, whether it is
NULL or NADA or VOID or whatever you wish to call it. I just want to
make sure that something, some value, is entered for a column. Would
appreciate any thoughts or pointers.

Does PostgreSQL suffer from this oddity as well? This distinction
between an empty string and a NULL? Could you also please give me an
example of where this would be useful from a business logic
standpoint? Why should a NULL be different from an empty string,
what's the big mysterious difference?


It's not an oddity.

An empty string is a KNOWN value. You know exactly what that value is - 
it's an empty string.


A NULL is UNKNOWN - it doesn't have a value at all.


I do still think it is a bit of an oddity, the concept of the null 
column. From my experience, it creates more problems than it actually 
solves and generally forces you to code more rather than less in order 
to achieve your goals.


But as it is a fundamental, defined part of the sql standard, one just 
has to live with it.


Tim


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

Re: [GENERAL] postgresql vs mysql

[Tim Tassonis]

Ron Johnson wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

My definition is, "toy used/trumpeted by pseudo-professionals as a
professional tool, when it just doesn't measure up".


Boah, here surely speaks a true professional playing in the league of 
Donald Knuth or even Alan Kay, as opposed to all the pseudos like me out 
there.


Is it Assembler or Smalltalk you write your web pages with?

PHP absolutely is a professional tool as a scripting language, of course 
with all the downsides of any scripting language. I'll choose php over 
Perl any day, as it is syntactically much cleaner and performs 
sufficiently well for usual scripting needs.


Of course, I wouldn't write an operating system with it.

Tim

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

Re: [GENERAL] postgresql vs mysql

[Russ Brown]

Ron Johnson wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

My definition is, "toy used/trumpeted by pseudo-professionals as a
professional tool, when it just doesn't measure up".



/me Tries really hard to resist responding

/me Fails

I'm sorry, but having just been described as a 'pseudo-professional' I 
simply have to defend myself, and my colleagues.


I don't want to start a language war here, and this is the last post 
that I will make on this subject, but it's perfectly possible to 
criticise pretty much any programming language out there with a whole 
raft of reasons as to why it's not a good language to use 
professionally. Take perl for example. I have still yet to see readable 
Perl code. Unless you're very careful C lets you write code that leaks 
or overwrites memory left right and centre with all kinds of security 
flaws possible as a result. I could go on with other languages but I 
think you get my point.


There is however a point in saying that while PHP does have its numerous 
inconsistencies and oddities, it is *perfectly* possible to write good 
quality, well performing, maintainable, clean and well designed code in 
PHP. Without blowing my own trumpet, I and my colleagues do it every 
day, and we work very hard at it too. Oh yes, we've seen plenty of bad 
code, but that's the skill of a good developer (to spot the bad code) 
and the challenge of a professional one (taking the care to deal with 
the bad code rather than just leaving it be). You just have to be more 
careful in PHP not to fall into the traps (that a good developer knows 
about), which is exactly like a C developer knowing how not to introduce 
buffer overruns etc.


This again applies to any language (so no language war!)

OK, I've done. I won't say any more. But please consider who might be 
reading before making unprofessional sweeping statements like that.




On 02/22/07 02:08, Tyarli wrote:

he he. what does "the PHP of databases" mean?

Joshua D. Drake wrote:

John Smith wrote:
  

On 2/21/07, Lincoln Yeoh  wrote:


MySQL: the PHP of databases.
  

'd appreciate if you stick to the subject.


Oops he probably should not have used MySQL because it is trademarked...

mysql: The PHP of databases

;)

Sincerely,

Joshua D. Drake


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF3WioS9HxQb37XmcRAvs3AJ48X+8O+0pXa7ynUrkoiJ4hxXM73ACgixHV
HD5+J/MQMk+mlTHYG7thNnM=
=wW6Y
-END PGP SIGNATURE-

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



---(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: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql

[CaT]

On Thu, Feb 22, 2007 at 11:27:18AM +0100, Zoltan Boszormenyi wrote:
> >>I would do a CHECK (trim(a) <> '')
> >
> >Whitespaces are values too, you know.
> 
> Yes, I know. But e.g. for a real people name, would you store
> accidentally entered spaces before or after the actual name, too?
> Which would also ruin sorting by name. But of course, it doesn't
> make sense in every case.

Yeah but if you're going down that path then you either trim on the
insert or use a trigger (rule?) to automatically trim your data for
you. Doing it in a check wont do much of anything for you in the case
you describe.

-- 
"To the extent that we overreact, we proffer the terrorists the
greatest tribute."
- High Court Judge Michael Kirby

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

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

Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql

[Zoltan Boszormenyi]

CaT írta:

On Thu, Feb 22, 2007 at 09:13:13AM +0100, Zoltan Boszormenyi wrote:
  

Chris ?rta:


CaT wrote:
  

On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote:


create table a(a text not null check (char_length(a) > 0));
  

What's wrrong with using

a <> ''


Nothing, I just thought of the other way first :)
  

I would do a CHECK (trim(a) <> '')



Whitespaces are values too, you know.
  


Yes, I know. But e.g. for a real people name, would you store
accidentally entered spaces before or after the actual name, too?
Which would also ruin sorting by name. But of course, it doesn't
make sense in every case.


---(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] postgresql vs mysql

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

My definition is, "toy used/trumpeted by pseudo-professionals as a
professional tool, when it just doesn't measure up".

On 02/22/07 02:08, Tyarli wrote:
> he he. what does "the PHP of databases" mean?
> 
> Joshua D. Drake wrote:
>> John Smith wrote:
>>   
>>> On 2/21/07, Lincoln Yeoh  wrote:
>>> 
 MySQL: the PHP of databases.
   
>>> 'd appreciate if you stick to the subject.
>>> 
>> Oops he probably should not have used MySQL because it is trademarked...
>>
>> mysql: The PHP of databases
>>
>> ;)
>>
>> Sincerely,
>>
>> Joshua D. Drake

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF3WioS9HxQb37XmcRAvs3AJ48X+8O+0pXa7ynUrkoiJ4hxXM73ACgixHV
HD5+J/MQMk+mlTHYG7thNnM=
=wW6Y
-END PGP SIGNATURE-

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

Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql

[CaT]

On Thu, Feb 22, 2007 at 09:13:13AM +0100, Zoltan Boszormenyi wrote:
> Chris ?rta:
> >CaT wrote:
> >>On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote:
> >>>create table a(a text not null check (char_length(a) > 0));
> >>
> >>What's wrrong with using
> >>
> >>a <> ''
> >
> >Nothing, I just thought of the other way first :)
> 
> I would do a CHECK (trim(a) <> '')

Whitespaces are values too, you know.

-- 
"To the extent that we overreact, we proffer the terrorists the
greatest tribute."
- High Court Judge Michael Kirby

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

Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql

[brian]

Zoltan Boszormenyi wrote:

Chris írta:


CaT wrote:


On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote:


In postgres, to stop an empty blank string:

create table a(a text not null check (char_length(a) > 0));



What's wrrong with using

a <> ''

sd the check? Or is this just a flavour thing?



Nothing, I just thought of the other way first :)

Probably better doing it as a <> '' otherwise postgres might have to 
run the char_length function every time you do an insert (ie might be 
a very slight performance issue).




I would do a CHECK (trim(a) <> '')




If you were ok with a string consisting soley of whitespace.

---(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

Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql

[Zoltan Boszormenyi]

Chris írta:

CaT wrote:

On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote:

In postgres, to stop an empty blank string:

create table a(a text not null check (char_length(a) > 0));


What's wrrong with using

a <> ''

sd the check? Or is this just a flavour thing?


Nothing, I just thought of the other way first :)

Probably better doing it as a <> '' otherwise postgres might have to 
run the char_length function every time you do an insert (ie might be 
a very slight performance issue).




I would do a CHECK (trim(a) <> '')


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

Re: [GENERAL] postgresql vs mysql

[Tyarli]

he he. what does "the PHP of databases" mean?

Joshua D. Drake wrote:
> John Smith wrote:
>   
>> On 2/21/07, Lincoln Yeoh  wrote:
>> 
>>> MySQL: the PHP of databases.
>>>   
>> 'd appreciate if you stick to the subject.
>> 
>
> Oops he probably should not have used MySQL because it is trademarked...
>
> mysql: The PHP of databases
>
> ;)
>
> Sincerely,
>
> Joshua D. Drake
>
>
>   
>> jzs
>>
>> ---(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
>>
>> 
>
>
>   


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

   http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Joshua D. Drake]

John Smith wrote:
> On 2/21/07, Lincoln Yeoh  wrote:
>> MySQL: the PHP of databases.
> 
> 'd appreciate if you stick to the subject.

Oops he probably should not have used MySQL because it is trademarked...

mysql: The PHP of databases

;)

Sincerely,

Joshua D. Drake


> jzs
> 
> ---(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
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

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

Re: [GENERAL] postgresql vs mysql

[brian]

Chris wrote:


It's not an oddity.

An empty string is a KNOWN value. You know exactly what that value is - 
it's an empty string.


A NULL is UNKNOWN - it doesn't have a value at all.



Just to expand on that (and to drag this thread out a little longer), i 
find that a pretty good way to get across this difference is to refer to 
it as *the* empty string. I prefer this description because it is 
implicit that it is still a string. NULL has no type, regardless of the 
column it is inserted into. Thus, the empty string is NOT NULL.


brian

---(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] postgresql vs mysql

[John Smith]

On 2/21/07, Lincoln Yeoh  wrote:

MySQL: the PHP of databases.


'd appreciate if you stick to the subject.
jzs

---(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] postgresql vs mysql

[Chris]

CaT wrote:

On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote:

In postgres, to stop an empty blank string:

create table a(a text not null check (char_length(a) > 0));


What's wrrong with using

a <> ''

sd the check? Or is this just a flavour thing?


Nothing, I just thought of the other way first :)

Probably better doing it as a <> '' otherwise postgres might have to run 
the char_length function every time you do an insert (ie might be a very 
slight performance issue).


--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] postgresql vs mysql

[Richard Broersma Jr]

> Does PostgreSQL suffer from this oddity as well? This distinction
> between an empty string and a NULL? Could you also please give me an
> example of where this would be useful from a business logic
> standpoint? Why should a NULL be different from an empty string,
> what's the big mysterious difference?


Nulls are also useful for data that is imported where there is not always a way 
to determine
certain field values for a record.

Now suppose in a query you want to return all record that meet your criteria as 
well as any that
might meet your criteria depending if the null value was actually known.

Select * from table
Where ((field1,field2,field3) = ('farmer','baker','shoemaker')) is unknown;

Now you can get all records that may meet your criteria.

Regards,
Richard Broersma Jr.

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

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

Re: [GENERAL] postgresql vs mysql

[Chris]

Erick Papadakis wrote:

So how should I make a database rule in MySQL to not allow blank
strings. Basically to REQUIRE a value for that column, whether it is
NULL or NADA or VOID or whatever you wish to call it. I just want to
make sure that something, some value, is entered for a column. Would
appreciate any thoughts or pointers.

Does PostgreSQL suffer from this oddity as well? This distinction
between an empty string and a NULL? Could you also please give me an
example of where this would be useful from a business logic
standpoint? Why should a NULL be different from an empty string,
what's the big mysterious difference?


It's not an oddity.

An empty string is a KNOWN value. You know exactly what that value is - 
it's an empty string.


A NULL is UNKNOWN - it doesn't have a value at all.



In postgres, to stop an empty blank string:

create table a(a text not null check (char_length(a) > 0));

though that allows a single space in..

See http://www.postgresql.org/docs/8.2/interactive/ddl-constraints.html

and

http://www.postgresql.org/docs/8.2/interactive/ddl-alter.html#AEN2302


No idea about how to do this in mysql, search their documentation.

--
Postgresql & php tutorials
http://www.designmagick.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] postgresql vs mysql

[Rich Shepard]

On Thu, 22 Feb 2007, Erick Papadakis wrote:


Why should a NULL be different from an empty string, what's the big
mysterious difference?


  Long ago and far away, when I was in the Army, we had quite a few soldiers
whose name took the form 'John NMI Doe.' That's because the Army -- even
before computers were ubiquitous -- needed something in the forms for middle
initials. If you did not have a middle name or initial, one was assigned to
you: NMI (No Middle Initial). This did not mean that the middle initial was
unknown (NULL), but that it did not exist (blank).

  If you have a credit card, would you prefer the balance to be blank or
unknown? How would the bank select all those with no balance rather than
those for whom a balance is unknown?

  I'm sure you can think of dozens of more situations like these.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [GENERAL] postgresql vs mysql

[Rodrigo Gonzalez]

Erick Papadakis wrote:

So how should I make a database rule in MySQL to not allow blank
strings. Basically to REQUIRE a value for that column, whether it is
NULL or NADA or VOID or whatever you wish to call it. I just want to
make sure that something, some value, is entered for a column. Would
appreciate any thoughts or pointers.

Does PostgreSQL suffer from this oddity as well? This distinction
between an empty string and a NULL? Could you also please give me an
example of where this would be useful from a business logic
standpoint? Why should a NULL be different from an empty string,
what's the big mysterious difference?


Just an example:

middle name = '' mean no middle name
middle name = NULL mean "dB does not know if there is or not middle name"

I hope you understand the difference between empty and null. and for 
numbers is other thingin average for example null is not considered, 
 other values, yes




Thanks.



On 2/22/07, Ron Johnson <[EMAIL PROTECTED]> wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/21/07 18:09, Erick Papadakis wrote:
> How would you like to use a database that has nuances like these --
> http://forums.mysql.com/read.php?20,141120,141120#msg-141120

Huh?

A blank string (does that mean '' or ' '?) is not NULL, so of
*course* it should pass the NOT NULL constraint.

Or am I missing something?


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

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





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

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

Re: [GENERAL] postgresql vs mysql

[Erick Papadakis]

So how should I make a database rule in MySQL to not allow blank
strings. Basically to REQUIRE a value for that column, whether it is
NULL or NADA or VOID or whatever you wish to call it. I just want to
make sure that something, some value, is entered for a column. Would
appreciate any thoughts or pointers.

Does PostgreSQL suffer from this oddity as well? This distinction
between an empty string and a NULL? Could you also please give me an
example of where this would be useful from a business logic
standpoint? Why should a NULL be different from an empty string,
what's the big mysterious difference?

Thanks.



On 2/22/07, Ron Johnson <[EMAIL PROTECTED]> wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/21/07 18:09, Erick Papadakis wrote:
> How would you like to use a database that has nuances like these --
> http://forums.mysql.com/read.php?20,141120,141120#msg-141120

Huh?

A blank string (does that mean '' or ' '?) is not NULL, so of
*course* it should pass the NOT NULL constraint.

Or am I missing something?


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

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

Re: [GENERAL] postgresql vs mysql

[Michael Fuhr]

On Wed, Feb 21, 2007 at 01:45:08PM -0600, Ron Johnson wrote:
> On 02/21/07 08:42, Michael Fuhr wrote:
> > Not as good as "ERROR: hey bonehead, there ain't no such date" but
> 
> But it *inserts the "data"*!

I didn't say otherwise and I'm not defending MySQL's behavior.  I was
simply refuting the statement that "it doesn't warn you that it didn't
like the input format."

> > at least it's something :-)
> 
> Sure, at the interactive command line.
> 
> What kind of error code does this return to applications?  Can a PHP
> or C programmer catch this warning, or does MySQL return a success code?

Beats me; I care about my data so I don't use MySQL.  Since it's a
warning I expect the query returns success.  The C API has a
mysql_warning_count() function that appears to be exposed in PHP's
mysqli extension as mysqli_warning_count.  That C function doesn't
appear in the source code for any of the other MySQL extensions in
PHP 5.2.1.

-- 
Michael Fuhr

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

   http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Chris]

Erick Papadakis wrote:

How would you like to use a database that has nuances like these --
http://forums.mysql.com/read.php?20,141120,141120#msg-141120


Err - an empty string is not the same as null, so that is perfectly valid.

Null means unknown, an empty string is not unknown - it's a known value 
(which happens to be nothing, but it's still known).


--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] postgresql vs mysql

[Brusser, Michael]

 
>> How would you like to use a database that has nuances like these -- 
>> http://forums.mysql.com/read.php?20,141120,141120#msg-141120

---

> Huh?
> A blank string (does that mean '' or ' '?) is not NULL, so of
> *course* it should pass the NOT NULL constraint.
> Or am I missing something?

---

I agree with you, although Oracle won't, they really blurred the line
between the empty string and NULL.

As for MySQL maybe this article makes more sense:
http://www.databasejournal.com/features/mysql/article.php/3519116

Mike.



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

Re: [GENERAL] postgresql vs mysql

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/21/07 19:01, Brusser, Michael wrote:
>  
>>> How would you like to use a database that has nuances like these -- 
>>> http://forums.mysql.com/read.php?20,141120,141120#msg-141120
> 
> ---
> 
>> Huh?
>> A blank string (does that mean '' or ' '?) is not NULL, so of
>> *course* it should pass the NOT NULL constraint.
>> Or am I missing something?
> 
> ---
> 
> I agree with you, although Oracle won't, they really blurred the line
> between the empty string and NULL.

Well that bites.  We don't use NULLs a lot, but still: NULL is null,
not a blank string.

How do "they" represent NULL in a numeric field?

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF3O8cS9HxQb37XmcRAgk3AJ9uB0Z0X9tUOdSgeBggC1UivekXhACgsaLr
ZVYsQ5NaFCuCwgER6mboH70=
=2arU
-END PGP SIGNATURE-

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

Re: [GENERAL] postgresql vs mysql

[Chris]

Adam Rich wrote:
It's got a query parser that's dumb as a brick. 


While we're on this topic...  I have a question on these series
of queries:

-- Query A
select count(*) from customers c
where not exists ( select 1 from orders o
where o.customer_id = c.customer_id )

-- Query B
select count(*) from customers c
where customer_id not in ( select customer_id from orders)


I had a similar sort of query and got told that work_mem is the most 
important thing here.


See http://archives.postgresql.org/pgsql-general/2007-02/msg00986.php

--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] postgresql vs mysql

[Chris]

Chad Wagner wrote:
On 2/20/07, *gustavo halperin* <[EMAIL PROTECTED] 
> wrote:


I  have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?


How about the fact that MySQL accepts the following query as legal:

SELECT foo, bar, COUNT(*)
FROM baz
GROUP BY foo

And produces, naturally, an unexpected result instead of an error.  
Totally annoying, I don't know if it was ever fixed.  It seems that 
MySQL's parser is generally weak at syntax validation in it's default 
configuration.


That one actually comes in handy ;) Especially in older versions (4.0) 
that don't support subselects..


--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] postgresql vs mysql

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/21/07 18:09, Erick Papadakis wrote:
> How would you like to use a database that has nuances like these --
> http://forums.mysql.com/read.php?20,141120,141120#msg-141120

Huh?

A blank string (does that mean '' or ' '?) is not NULL, so of
*course* it should pass the NOT NULL constraint.

Or am I missing something?

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF3OFHS9HxQb37XmcRAsdwAJ9Ew3pb2huydeP14Bn8NsWuWn1TnACgw+Ru
qD0UuPJcJukugpER51HMXDs=
=DHkn
-END PGP SIGNATURE-

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