Re: PostgreSQL vs Firebird feature comparison finished

Check it out here:

http://www.amsoftwaredesign.com/pg_vs_fb

When comparing in the grid the only major advantage FB has is probably
BLOB support.
PG only suppports 1 gb while FB supports 32gb. Bytea is pretty slow as
well when compared to the FB BLOB support.

The other area is Character sets and collation. They support it at a
field level as well as the database.

Other than that I would say PG kicks butt.

If there is any interest I could also add MySQL 5.0 to the mix as the
third column.

Later,

Tony

Tony Caduto wrote:
> Other than that I would say PG kicks butt.

You're just realising that? :-)

> If there is any interest I could also add MySQL 5.0 to the mix as the
> third column.

I'd be interested to see that.

Regards, Dave

Tony Caduto wrote:
> Check it out here:
>
> http://www.amsoftwaredesign.com/pg_vs_fb

Couple of corrections Tony:

- You don't necessarily need to stop the postmaster to take a filesystem
backup -
http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP.
Obviously that assumes logs will be replayed during recovery.

- The native win32 port will run on FAT32, we just prevent the installer
from initdb'ing on such a partition. You can do it manually however, but
tablespaces won't work.

I'm a little puzzled about why you list multi-threaded architecture as a
feature - on Windows it's a little more efficient of course, but the
multi-process architecture is arguably far more robust, and certainly
used to be more portable (I'm not sure that's still the case for
platforms we actually care about).

Regards, Dave.

On Aug 23, 2007, at 12:00 AM, Tony Caduto wrote:

> Check it out here:
>
> http://www.amsoftwaredesign.com/pg_vs_fb
>
>
> When comparing in the grid the only major advantage FB has is
> probably BLOB support.
> PG only suppports 1 gb while FB supports 32gb. Bytea is pretty
> slow as well when compared to the FB BLOB support.

Actually, Postgres's large object facility allows storage of binary
data up to 2GB in size. http://www.postgresql.org/docs/8.2/
interactive/largeobjects.html

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Dave Page wrote:
> Couple of corrections Tony:
>
> - You don't necessarily need to stop the postmaster to take a filesystem
> backup -
> http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP.
> Obviously that assumes logs will be replayed during recovery.
>
> - The native win32 port will run on FAT32, we just prevent the installer
> from initdb'ing on such a partition. You can do it manually however, but
> tablespaces won't work.
>
> I'm a little puzzled about why you list multi-threaded architecture as a
> feature - on Windows it's a little more efficient of course, but the
> multi-process architecture is arguably far more robust, and certainly
> used to be more portable (I'm not sure that's still the case for
> platforms we actually care about).
>
> Regards, Dave.
>
>
>

Thanks Dave.
Will update ASAP.

I agree with you on the multi-threaded. I think I will add a note
saying the the multi-threaded architecture is only advantageous on Windows.
I have seen instances where the threaded version of Firebird completely
craps out because one of the threads has issues.

Will also make a note that it can run on FAT32 with some limitations.

Later,

Tony

Dave Page wrote:
> Tony Caduto wrote:
>
>> Other than that I would say PG kicks butt.
>>
>
> You're just realising that? :-)
>
>

Ah, I new that around 2004 :-) I just have to convince Delphi users of
that :-)

Later,

Tony

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tony Caduto wrote:
> Dave Page wrote:
>> Tony Caduto wrote:
>>
>>> Other than that I would say PG kicks butt.
>>>
>>
>> You're just realising that? :-)
>>
>>
>
> Ah, I new that around 2004 :-) I just have to convince Delphi users of
> that :-)

My understanding is the Firebird is relatively non-configured though
isn't it? For a large scale client server app there is no question that
PG is going to wipe the universe with Firebird, but I would think that
Firebird may be better suited for embedded shipping that kind of thing.

Sincerely,

Joshua D. Drake

>
>
> Later,
>
> Tony
>
> ---------------------------(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
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGzb0ZATb/zqfZUUQRAttRAJ4mamXurjzMDH9kqD3cWt9EC6RT7wCfRpkE
efUsuyz2f1GQKSs4dfgzr+A=
=JHrY
-----END PGP SIGNATURE-----

--- Tony Caduto <tony_caduto(at)amsoftwaredesign(dot)com> wrote:

> Check it out here:
>
> http://www.amsoftwaredesign.com/pg_vs_fb

One row that you could elaborate on is:
CHECK CONSTRAINTS support for correlated sub-queries.
PostgreSQL doesn't official support this kink of constraint unless it is rolled up in a function.
I am not sure what support FB has for this.

Another Constraint row you could add would be:
CREATE ASSERTION which is a schema level constraint. Currently PostgreSQL doesn't support this,
I am not sure if FB does either.

Also you could mention PostgreSQL support for row-wise comparison:
i.e. WHERE ( last_name, city, gender ) = ( 'Doe', 'Paris', 'female' );

and PostgreSQL support for additional SQL comparison operators:
i.e. WHERE (( last_name, city, gender ) = ( 'Doe', 'Paris', 'female' )) IS UNKNOWN;
-- return all people who might meet this criteria if their null field where known.

Regards,
Richard Broersma Jr.

On 8/23/07, Tony Caduto <tony_caduto(at)amsoftwaredesign(dot)com> wrote:
> Check it out here:
>
> http://www.amsoftwaredesign.com/pg_vs_fb
> If there is any interest I could also add MySQL 5.0 to the mix as the
> third column.

If you do, you should really do it as MySQL-isam and MySQL-innodb.

the limitations of each table handler are often as much different as
to make it another database server. i.e. no full text search on
innodb tables, no foreign keys on isam tables, etc...

If anyone is interested, I could answer the questions for Oracle and
you could add those, too. Be interesting to see a chart like that
(that stays updated after releases) for a large assortment of
databases.

If we add a bunch of different databases, it might be easier to
manipulate if it was stored in a database. MS-Access maybe? ;-)

LewisC

--- Tony Caduto <tony_caduto(at)amsoftwaredesign(dot)com> wrote:

> Check it out here:
>
> http://www.amsoftwaredesign.com/pg_vs_fb
>
>
> When comparing in the grid the only major advantage FB has is
> probably
> BLOB support.
> PG only suppports 1 gb while FB supports 32gb. Bytea is pretty
> slow as
> well when compared to the FB BLOB support.
>
> The other area is Character sets and collation. They support it at
> a
> field level as well as the database.
>
> Other than that I would say PG kicks butt.
>
> If there is any interest I could also add MySQL 5.0 to the mix as
> the
> third column.
>
>
> Later,
>
> Tony
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an
> appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>

-----------------------------------------------------------
Lewis R Cunningham

An Expert's Guide to Oracle Technology
http://blogs.ittoolbox.com/oracle/guide/

LewisC's Random Thoughts
http://lewiscsrandomthoughts.blogspot.com/

EnterpriseDB: The Definitive Reference
http://tinyurl.com/39246e
----------------------------------------------------------

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Lewis Cunningham wrote:
> If anyone is interested, I could answer the questions for Oracle and
> you could add those, too. Be interesting to see a chart like that
> (that stays updated after releases) for a large assortment of
> databases.
>
> If we add a bunch of different databases, it might be easier to
> manipulate if it was stored in a database. MS-Access maybe? ;-)

Let's get this up on the wiki.

Joshua D. Drake

>
> LewisC
>
> --- Tony Caduto <tony_caduto(at)amsoftwaredesign(dot)com> wrote:
>
>> Check it out here:
>>
>> http://www.amsoftwaredesign.com/pg_vs_fb
>>
>>
>> When comparing in the grid the only major advantage FB has is
>> probably
>> BLOB support.
>> PG only suppports 1 gb while FB supports 32gb. Bytea is pretty
>> slow as
>> well when compared to the FB BLOB support.
>>
>> The other area is Character sets and collation. They support it at
>> a
>> field level as well as the database.
>>
>> Other than that I would say PG kicks butt.
>>
>> If there is any interest I could also add MySQL 5.0 to the mix as
>> the
>> third column.
>>
>>
>> Later,
>>
>> Tony
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an
>> appropriate
>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
>> your
>> message can get through to the mailing list cleanly
>>
>
>
> -----------------------------------------------------------
> Lewis R Cunningham
>
> An Expert's Guide to Oracle Technology
> http://blogs.ittoolbox.com/oracle/guide/
>
> LewisC's Random Thoughts
> http://lewiscsrandomthoughts.blogspot.com/
>
> EnterpriseDB: The Definitive Reference
> http://tinyurl.com/39246e
> ----------------------------------------------------------
>
> ---------------------------(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
>

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGzdNoATb/zqfZUUQRAh/sAJ92Ko3lB6eCGSyJJyoPw5sn4VI44QCdGTjc
XzyzrDQKnA7mgoNXDohvUpY=
=Um04
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tony Caduto wrote:
> Dave Page wrote:
>> Couple of corrections Tony:
>>
>> - You don't necessarily need to stop the postmaster to take a filesystem
>> backup -
>> http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP.

>>
>>
>
> Thanks Dave.
> Will update ASAP.
>
> I agree with you on the multi-threaded. I think I will add a note
> saying the the multi-threaded architecture is only advantageous on
> Windows.

And Solaris.

Joshua D. Drake

> I have seen instances where the threaded version of Firebird completely
> craps out because one of the threads has issues.
>
> Will also make a note that it can run on FAT32 with some limitations.
>
> Later,
>
> Tony
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org 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
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGzdyOATb/zqfZUUQRAjYtAJ9GxNvF46JXM34i6Kf0RE7TLwkGggCeN5QD
eELS+fyixPqlB/dYiGkC/vM=
=wN+j
-----END PGP SIGNATURE-----

On Thu, 23 Aug 2007, Tony Caduto wrote:

> If there is any interest I could also add MySQL 5.0 to the mix as the third
> column.

As already mentioned, MyISAM and InnoDB should get their own columns.

This is a really good comparision, focusing on features that I think
people understand rather than so much on technical trivia. Someone else
mentioned moving it onto the Wiki. Questions that pop into my head:

-Tony, would be you be comfortable with your work being assimilated into a
larger table that was hosted somewhere else but credited yours as a
source?

-Is the Wiki the right place to build this table at? Large Wiki tables
get very difficult to manage. It may be easier to build the table in
something else and then have that generate markup instead. I'd rather
edit this in a spreadsheet and write something to massage that into final
form than do all the edits within the Wikipedia editor.

-If this is going to turn into the grand feature comparision table,
everyone might as well be thinking from day one that inevitably there will
be columns for Oracle (with a volunteer to fill out already), SQL Server,
DB2, etc. and plan a useful way to manage all that data from the
beginning. That's another reason why the Wiki is a bad way to cope with
this data; adding another column is a painful and error-prone operation.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Thu, Aug 23, 2007 at 03:30:30PM -0400, Greg Smith wrote:
> On Thu, 23 Aug 2007, Tony Caduto wrote:
>
> >If there is any interest I could also add MySQL 5.0 to the mix as the
> >third column.
>
> As already mentioned, MyISAM and InnoDB should get their own columns.

Yes.

> This is a really good comparision, focusing on features that I think
> people understand rather than so much on technical trivia. Someone else
> mentioned moving it onto the Wiki. Questions that pop into my head:
>
> -Tony, would be you be comfortable with your work being assimilated into a
> larger table that was hosted somewhere else but credited yours as a
> source?
>
> -Is the Wiki the right place to build this table at? Large Wiki
> tables get very difficult to manage.

They're very easy to manage using things like the Firefox/Mozilla
plugin viewsourcewith
<https://addons.mozilla.org/en-US/firefox/addon/394>

> It may be easier to build the table in something else and then have
> that generate markup instead. I'd rather edit this in a spreadsheet
> and write something to massage that into final form than do all the
> edits within the Wikipedia editor.

See above :)

> -If this is going to turn into the grand feature comparision table,
> everyone might as well be thinking from day one that inevitably
> there will be columns for Oracle (with a volunteer to fill out
> already), SQL Server, DB2, etc. and plan a useful way to manage all
> that data from the beginning. That's another reason why the Wiki is
> a bad way to cope with this data; adding another column is a painful
> and error-prone operation.

Could be. Try viewsourcewith with your favorite editor and see
whether it eases the pain :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

Joshua D. Drake wrote:
>> I agree with you on the multi-threaded. I think I will add a note
>> saying the the multi-threaded architecture is only advantageous on
>> Windows.
>
> And Solaris.

I'm not entirely sure what makes multi-threading be advantageous on a
specific operating system, but I think FreeBSD should be added to that
list as well... They've been bench marking their threading support using
multi-threading in MySQL (not for the db, mind you - just for load ;),
and it performs really well.

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

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

// Integrate Your World //

Alban Hertroys wrote:
> Joshua D. Drake wrote:
>>> I agree with you on the multi-threaded. I think I will add a note
>>> saying the the multi-threaded architecture is only advantageous on
>>> Windows.
>> And Solaris.
>
> I'm not entirely sure what makes multi-threading be advantageous on a
> specific operating system, but I think FreeBSD should be added to that
> list as well... They've been bench marking their threading support using
> multi-threading in MySQL (not for the db, mind you - just for load ;),
> and it performs really well.
>

I'm not sure I necessarily agree with those two - we have no real proof
that a multithreaded architecture would be significantly more efficient
than a multi process. It certainly wouldn't be as robust as an error in
one backend thread could bring down the entire server.

Windows is a special case in this regard. The OS has been designed from
the outset as a threaded environment. The important point is not that
Windows threads are necessarily any more efficient than their Solaris or
FreeBSD counterparts, but that the multi-process architecture is alien
to Windows and is inherently slower. Two of the major bottlenecks we
have on Windows as a result are backend startup time and shared memory
access speed - both of which are significantly slower than on *nix.

Regards, Dave

Dave Page wrote:
> Alban Hertroys wrote:
>> Joshua D. Drake wrote:
>>>> I agree with you on the multi-threaded. I think I will add a note
>>>> saying the the multi-threaded architecture is only advantageous on
>>>> Windows.
>>> And Solaris.
>>
>> I'm not entirely sure what makes multi-threading be advantageous on a
>> specific operating system, but I think FreeBSD should be added to that
>> list as well... They've been bench marking their threading support using
>> multi-threading in MySQL (not for the db, mind you - just for load ;),
>> and it performs really well.
>>
>
> I'm not sure I necessarily agree with those two - we have no real proof
> that a multithreaded architecture would be significantly more efficient
> than a multi process. It certainly wouldn't be as robust as an error in
> one backend thread could bring down the entire server.
>
> Windows is a special case in this regard. The OS has been designed from
> the outset as a threaded environment. The important point is not that
> Windows threads are necessarily any more efficient than their Solaris or
> FreeBSD counterparts, but that the multi-process architecture is alien
> to Windows and is inherently slower. Two of the major bottlenecks we
> have on Windows as a result are backend startup time and shared memory
> access speed - both of which are significantly slower than on *nix.
>
> Regards, Dave

Thanks for explaining (again).

So actually the remark shouldn't be that "the multi-threaded
architecture is only advantageous on Windows", but more like "the
multi-process architecture is disadvantageous on Windows and hence a
multi-threaded architecture is preferred (on that particular OS)".

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

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

// Integrate Your World //

Alban Hertroys wrote:
> So actually the remark shouldn't be that "the multi-threaded
> architecture is only advantageous on Windows", but more like "the
> multi-process architecture is disadvantageous on Windows and hence a
> multi-threaded architecture is preferred (on that particular OS)".

Yeah - but I'm not sure thats necessarily something that should have a
place on a bullet point comparison.

Regards, Dave

"Dave Page" <dpage(at)postgresql(dot)org> writes:

> Alban Hertroys wrote:
>> So actually the remark shouldn't be that "the multi-threaded
>> architecture is only advantageous on Windows", but more like "the
>> multi-process architecture is disadvantageous on Windows and hence a
>> multi-threaded architecture is preferred (on that particular OS)".
>
> Yeah - but I'm not sure thats necessarily something that should have a place on
> a bullet point comparison.

Note that while we use the OS's "threads" api we're not really any more
multi-threaded on Windows than we are on Unix. We don't use any shared memory
data structures we don't on Unix using SysV shared memory, we don't use any
mutexes or other threaded programming techniques that we don't use on Unix,
and so on.

It's purely a question of which API we use to create the threads of execution.
Not an architectural change in Postgres.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

On Aug 24, 2007, at 4:09 AM, Alban Hertroys wrote:

> I'm not entirely sure what makes multi-threading be advantageous on a
> specific operating system, but I think FreeBSD should be added to that
> list as well... They've been bench marking their threading support
> using
> multi-threading in MySQL (not for the db, mind you - just for load ;),
> and it performs really well.

Maybe only for FreeBSD >= 6.0. Prior to that, the threading was
rather lackluster. I still think the separate process model is
superior, in that you get private data spaces with them.

On 8/24/07, Dave Page <dpage(at)postgresql(dot)org> wrote:
> Alban Hertroys wrote:
> > So actually the remark shouldn't be that "the multi-threaded
> > architecture is only advantageous on Windows", but more like "the
> > multi-process architecture is disadvantageous on Windows and hence a
> > multi-threaded architecture is preferred (on that particular OS)".
>
> Yeah - but I'm not sure thats necessarily something that should have a
> place on a bullet point comparison.

You solve it by letting the category be labeled "Process/thread
architecture", and letting the columns say "Thread-based" for Firebird
and "Process-based" for PostgreSQL respectively.

Then add a footnote about this row that explains the difference and
how they behave on the different operating systems.

PostgreSQL clearly is at a slight disadvantage on Windows, so this is
relevant, at least to Tony's Delphi-user demographic.

Alexander.

Alexander Staubo wrote on 24.08.2007 23:49:
>>> So actually the remark shouldn't be that "the multi-threaded
>>> architecture is only advantageous on Windows", but more like "the
>>> multi-process architecture is disadvantageous on Windows and hence a
>>> multi-threaded architecture is preferred (on that particular OS)".
>> Yeah - but I'm not sure thats necessarily something that should have a
>> place on a bullet point comparison.
>
> You solve it by letting the category be labeled "Process/thread
> architecture", and letting the columns say "Thread-based" for Firebird
> and "Process-based" for PostgreSQL respectively.

To my understanding Firebird offers a choice between thread-based (Superserver)
and process-based (Classic) at least on the Windows platform.

Thomas

Hi,
Someone mentioned we should put this in the PostgreSQL wiki.

Do you guys think that would be beneficial? If so, I don't mind the
work on the list I have done so far going on the wiki.
It would make it a lot easier to add other DBs to the mix.

Later,

Tony

Greg Smith wrote:
>
>
> This is a really good comparision, focusing on features that I think
> people understand rather than so much on technical trivia. Someone
> else mentioned moving it onto the Wiki. Questions that pop into my head:
>
> -Tony, would be you be comfortable with your work being assimilated
> into a larger table that was hosted somewhere else but credited yours
> as a source?
>
Thanks Greg :-)

I don't have any problem with what I have done so far being assimilated
in a larger work as long as I get credited as a contributer.

Later,

Tony

Postgres can't be embedded or serverless. Firebird has the embedded feature.
Most of the databases have this capability (hsqldb, derby,oracle,mysql,
firebird, and db2). Derby and hsqldb are the only free embedded databases
for commercial use.

I recently ported a schema from postgres to firebird and found name size
limitations. Firebird has a limitation on the size of it's column names,
table names, constraint names and index names. I think the size limitation
on firebird is 31 characters. Postgres doesn't have this limitation.

Steve

Stephen Ince wrote:
> Postgres can't be embedded or serverless. Firebird has the embedded
> feature. Most of the databases have this capability (hsqldb,
> derby,oracle,mysql, firebird, and db2). Derby and hsqldb are the only
> free embedded databases for commercial use.
>

A lot of Firebird users have been saying this as well, but the
comparison if more for Enterprise use.
Plus if you need a embedded database wouldn't it be better to use one
built specifically for that purpose? i.e. SQLite for example.

Good call on the name limit, I remember running into that when porting
something from MS SQL server to Firebird about 4 years ago.
I will have to check and see if this still applies to version 2.0

Later,

Tony

On Aug 27, 2007, at 11:47 , Tony Caduto wrote:

> Good call on the name limit, I remember running into that when
> porting something from MS SQL server to Firebird about 4 years ago.

Just a quick note: PostgreSQL's identifiers are limited to
NAMEDATALEN - 1 (IIRC), which by default is 64 - 1 = 63 characters:

test=# create table a
(a23456789112345678921234567893123456789412345678951234567896123 text
primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"a_pkey" for table "a"
CREATE TABLE
test=# create table b
(a234567891123456789212345678931234567894123456789512345678961234
text primary key);
NOTICE: identifier
"a234567891123456789212345678931234567894123456789512345678961234"
will be truncated to
"a23456789112345678921234567893123456789412345678951234567896123"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"b_pkey" for table "b"
CREATE TABLE

The upshot is that PostgreSQL does have a limit, but it's pretty big
initially and is configurable at compilation by changing the
definition of NAMEDATALEN in in src/include/postgres_ext.h.

Michael Glaesemann
grzm seespotcode net

On 8/27/07, Stephen Ince <since(at)opendemand(dot)com> wrote:
> I recently ported a schema from postgres to firebird and found name size
> limitations. Firebird has a limitation on the size of it's column names,
> table names, constraint names and index names. I think the size limitation
> on firebird is 31 characters. Postgres doesn't have this limitation.

Note that postgresql does have a limit. It's just not as short as the
one in firebird.

Stephen Ince wrote on 27.08.2007 18:02:
> Derby and hsqldb are the only free embedded databases for commercial use.

Well, there are some more:
H2 Database, OneDollarDB (OpenSource version of DaffodilDB), Berkely DB and
McKoi are free as well (although McKoi seems to be dead).

Then there are a couple of other Java based engines (SmallSQL, TinySQL, Axioin)
but they do not compare feature-wise to the "big names".

And of course Firebird is free for commercial use as well.

Thomas

Point taken for the enterprise comparison. The reason for having the
embedded database is to hide the complexity for installing, using, and
configuration of the database from the user of the application. You don't
want a scaled version of the database.

----- Original Message -----
From: "Tony Caduto" <tony_caduto(at)amsoftwaredesign(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Monday, August 27, 2007 12:47 PM
Subject: Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished

> Stephen Ince wrote:
>> Postgres can't be embedded or serverless. Firebird has the embedded
>> feature. Most of the databases have this capability (hsqldb,
>> derby,oracle,mysql, firebird, and db2). Derby and hsqldb are the only
>> free embedded databases for commercial use.
>>
>
> A lot of Firebird users have been saying this as well, but the comparison
> if more for Enterprise use.
> Plus if you need a embedded database wouldn't it be better to use one
> built specifically for that purpose? i.e. SQLite for example.
>
> Good call on the name limit, I remember running into that when porting
> something from MS SQL server to Firebird about 4 years ago.
> I will have to check and see if this still applies to version 2.0
>
> Later,
>
> Tony
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

On Thu, 23 Aug 2007, David Fetter wrote:

>> -Is the Wiki the right place to build this table at? Large Wiki
>> tables get very difficult to manage.
> They're very easy to manage using things like the Firefox/Mozilla
> plugin viewsourcewith
> <https://addons.mozilla.org/en-US/firefox/addon/394>

The kind of issues I'm concerned about here is that the normal way to deal
with Wiki tables is to use the so-called "pipe" syntax for them (see
http://en.wikipedia.org/wiki/Help:Table ). That means that any time you
add a column, you have a laborious process to edit all the rows that are
already there, and it's really a pain. And viewsourcewith presumes one
has a good editor that understands Wiki tables; I'm not aware of one. Am
open to ideas if you know of one.

There's an alternate approach that says to treat everything as straight
HTML tables, then there are all kinds of external editors available that
can be enabled with a tool like you suggest. I don't like doing that
because then it's much harder for people to do spot edits within the
Wikipedia framework because the harder to read HTML table markup is in
there.

Another way to go about this is construct the table in a spreadsheet or
similar grid-oriented application as the primary document, then convert it
to Wiki format using something like http://area23.brightbyte.de/csv2wp.php
This is way easier to get an initial table going than any other approach.
The problem is that it's one-way.

This issue is much bigger than the editing here; if you follow the links
on Help:Table you can see people have been arguing about how to proceed
here for years with little progress. The tables that have been built
within the developer's wiki so far have been reasonable to maintain
because more rows get added, but rarely columns. This feature comparision
table will be the other way around, which is the harder one to cope with.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD