[SQL] Which version of PostgreSQL should I use.
Hi there, Let me first preface this with, I am not a PostgreSQL admin. I am a web developer who happens to use PSQL as the back-end for my company's app. We did have a dedicated DBA / system admin - but he has recently resigned. I know enough about psql - to be able to create / drop databases... and enough about SQL to get stuff in and out of the database. Beyond that - I pretty much know , nothing about PostgreSQL - thus this mail. We're currently using psql 8.1 and are on the way to upgrading to 8.4. This is a process that the last DBA had us start. And we've slowly been going through our code, getting rid of implicit casts as errors appear. I have now been asked to start replicating our databases between servers - as a hot-copy / redundancy improvement. And subsequently have some questions, please. Is there are a particular version of PostgreSQL that we should be "aiming" to upgrade to that provides for synching of databases. My initial thought is; We should upgrade to the latest stable version - whatever that is; But is the answer that simple? What we do we also need to take into account? I am "pretty sure" that to get to 8.4 from 8.1 (on our staging server) that we had to upgrade to 8.3 first. So I guess I am hoping that someone might just simply know - or be able to pint me in the correct direction for some information about what's in what version and any upgrade requirements to get to XXX from 8.1 AS always - thanks in advance for any assistance you might be able to give us! Gavin. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] unnest in SELECT
I have a couple of questions regarding unnest. 1) If I issue a select statement "select unnest(vector1) as v from some_table", I cannot seem to use the column alias v in a WHERE or HAVING clause. I can use it in an ORDER BY or GROUP by clause. Is this the way it is supposed to work? 2) If I issue a select statement "select unnest(vector1) as v1, unnest(vector2) as v2 from some_table" and vector1 has a length of 3 and vector2 has a length of 4, the result set will have 12 rows with the data of vector1 repeating 4 times and vector2 repeating 3 times. Shouldn't the content of the shorter array(s) simply be return null in it's respective column and the result set be the size of the longest array? Thanks, Karl -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Which version of PostgreSQL should I use.
> We're currently using psql 8.1 and are on the way to upgrading to 8.4. The tools "pg_dump" & "pg_restore" are used to extract and re-create databases. Can be used for chores such as deploying from development to production, backing up, and upgrading. http://www.postgresql.org/docs/current/static/app-pgdump.html http://www.postgresql.org/docs/9.0/static/app-pgrestore.html The tool "pg_upgrade" is intended to help you upgrade from one version to another. Think of it as a wrapper around pg_dump & pg_restore. Has a bonus feature where you can upgrade a database in place rather than re-create it if you have a huge amount of data and too little time at the moment of officially upgrading to afford re-creating the data. http://www.postgresql.org/docs/current/static/pgupgrade.html You may want to go to version 9 rather than 8.4. a) 9.0 is not a major upgrade in terms of compatibility. Original plans labeled it as 8.5 for that reason. "9" was eventually chosen as a label only because of dramatic new features added. b) Some hooks were added to 9 to allow enhancements to the 'pg_upgrade' tool. It may be actually be easier to upgrade to 9 than 8.4 for that reason. > I have now been asked to start replicating our databases between servers - as > a hot-copy / redundancy improvement. A relatively simple and built-in replication system was one of the major features added to version 9.0. http://www.postgresql.org/docs/current/static/high-availability.html > Is there are a particular version of PostgreSQL that we should be "aiming" to > upgrade to that provides for synching of databases. > My initial thought is; > We should upgrade to the latest stable version - whatever that is; Version 9.0.4 is the current stable version. Version 9.1 is due out soon. > pint me in the correct direction for some information about what's in what > version and any upgrade requirements to get to XXX from 8.1 http://www.postgresql.org/docs/current/static/release.html --Basil Bourque -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] unnest in SELECT
On 05/21/2011 03:13 AM, Karl Koster wrote: I have a couple of questions regarding unnest. 1) If I issue a select statement "select unnest(vector1) as v from some_table", I cannot seem to use the column alias v in a WHERE or HAVING clause. I can use it in an ORDER BY or GROUP by clause. Is this the way it is supposed to work? Yes, and it's what the SQL standard requires. Otherwise, how would this query work? SELECT a/b FROM sometable WHERE b <> 0; ? The SELECT list has to be processed only once the database has already decided which rows it applies to and how. Use unnest in a FROM clause, eg: SELECT v1.* FROM unnest(vector) ... This may require a join and/or subquery to obtain 'vector'. 2) If I issue a select statement "select unnest(vector1) as v1, unnest(vector2) as v2 from some_table" and vector1 has a length of 3 and vector2 has a length of 4, the result set will have 12 rows with the data of vector1 repeating 4 times and vector2 repeating 3 times. Shouldn't the content of the shorter array(s) simply be return null in it's respective column and the result set be the size of the longest array? unnest is a set-returning function, and it doesn't really make that much sense to have them in the SELECT list anyway. Few databases support it, and PostgreSQL's behavior is a historical quirk that I think most people here hope will go quietly away at some point. Use unnest in a FROM clause. -- Craig Ringer -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
