PostgreSQL version 8. has been released
PostgreSQL version 8 has been released. The biggest change is that Windows is now a supported platform.
For someone like me who mainly works with Oracle databases, PostgreSQL is a much better fit than MySQL. PostgreSQL supports many features which i use daily on Oracle databases: stored procedures, schema’s, views, sequences, etc.
I’m currently using MySQL 4.0 for the Jumpteam website, but i’d rather use PostgreSQL. One reason: Unions. The site started on MySQL 3. No support for unions. Now we’re using MySQL 4.0.18. MySQL 4.0’s union is seriously broken. This has been fixed in newer versions, but unfortunately i can’t upgrade MySQL on this machine.
Update Initially i wrote here that PostgreSQL supports inline views, but some google research seems to indicate otherwise. And apparently MySQL supports inline views as of version 4.1. Seems it’s time to upgrade MySQL to version 4.1 instead of moving to PostgreSQL.
Another update Joseph commented that PostgreSQL does support inline views. I guess they’re not called inline views in PostgreSQL though. When you search in the PostgreSQL documentation for inline view nothing helpfull comes up.
Another update Inline views are called sub-selects in PostgresSQL.
January 20th, 2005 at 3:03 am
I don’t know where you got the idea that PostgreSQL doesn’t support inline views. Just to verify this I tried it out PostgreSQL 7.4.6 and it worked fine. The only thing that PostgreSQL wants you do is give the subselect FROM replacement (inline view) an alias, which makes perfect sense because you could need this to specify table.column_name references in your SELECT. Below is the rather contrived query that I tried this out with.
SELECT username FROM ( SELECT * FROM staff WHERE staff_id = 1 ) usersJanuary 20th, 2005 at 7:45 am
I found a couple of sites which stated that postgresql doesn’t support inline views, e.g.: http://www.compiere.org/technology/database/postgreSQL.html.
January 20th, 2005 at 7:47 am
And more importantly, i tried searching for “inline view” in the postgresql 8.0 documentation which doesn’t result in anything helpfull: http://search.postgresql.org/www.search?ul=http%3A%2F%2Fwww.postgresql.org%2Fdocs%2F8.0%2Finteractive%2F%25&q=inline+view
January 20th, 2005 at 6:14 pm
Hmmm, you said that you searched on Google for this info. A Google search for postgresql inline view comes up with plenty of references. The first one is a archived email on one of the postgresql mailing lists that gives an example of using an “inline view”.
Some of the info at the Compiere site seems like they only researched some of the info, either that or this is a very, very old page. Like the ‘Functions’ section, decode can be written using case.
Rewriting PL/SQL in PL/pgSQL is probably the most work, dumping an Oracle schema into something that PostgreSQL can import is pretty straight forward (I wrote a PHP script to do it).
January 22nd, 2005 at 11:46 am
I think this is a terminology question: what you’re looking for are (in PostgreSQL terms) derived tables generated from a subselect / subquery, see:
http://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-SUBQUERIES
Note MySQL 4.1’s subqery support is not quite complete, e.g.
“DELETE FROM t1 WHERE id IN (SELECT MIN FROM t1)” won’t work, See:
http://sql-info.de/mysql/notes/subquery-support-in-4.1-overview.html
May 16th, 2005 at 2:08 am
Does anyone here using Postgresql with Oracle BC4J in windows 2000? I found out that the Postgresql is not working well with BC4J.