Re: [GENERAL] alter table add column - specify where the column will go?

Alexander Farber, 24.11.2010 08:49: Why do you want to do anything like that? Easier to read... login, logout I understand the "easier to read" part. But what do you mean with "login, logout"? Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to y

Re: [GENERAL] alter table add column - specify where the column will go?

Please note, that in cases when you can't do simple dump - fix - restore (e.g. in production) you can always create view(s) with ordinal positions of columns convenient for you. 2010/11/24 Dmitriy Igrishin > Hey Alexander, > > Ordinal positions of columns can't be set manually by ALTER TABLE. >

[GENERAL] Getting current and average on a single row

What is best practice when extracting both current and average from a table? Demonstration table here contains data from a cheap weather station. I can obviously get the current reading like this: select temp_out, dewpoint from weather where datetime between (now() - '10 minutes'::interval) and

Re: [GENERAL] alter table add column - specify where the column will go?

just never use SELECT *, but always call columns by names. You'll avoid having to depend on the order of columns, which is never guaranteed, even if the table on disk is one order, the return columns could be in some other. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] alter table add column - specify where the column will go?

Grzegorz Jaśkiewicz, 24.11.2010 10:37: just never use SELECT *, but always call columns by names. You'll avoid having to depend on the order of columns, which is never guaranteed, even if the table on disk is one order, the return columns could be in some other. I always try to convince people

[GENERAL] Optimizing query

Hello. I have a query which works a bit slow. It's runned on desktop computer: AMD Athlon X2 2GHz , Win Xp sp2, 1GB ram. Postgres 8.4.5 with some changes in config: shared_buffers = 200MB # min 128kB # (change requires restart) temp_buffers = 8MB

Re: [GENERAL] Debug advice when postgres connection maxing out

On 2010-11-23 20.56, anypossibility wrote: Thank you for your advice.I reviewed the query and it is the most simple one column value update with primary key query. I would like to share this with you and would like to receive advice as to whether I am on the right track. Facts: the connecti

Re: [GENERAL] alter table add column - specify where the column will go?

On Wed, Nov 24, 2010 at 09:37:02AM +, Grzegorz Jaśkiewicz wrote: > just never use SELECT *, but always call columns by names. You'll > avoid having to depend on the order of columns, which is never > guaranteed, even if the table on disk is one order, the return columns > could be in some other

[GENERAL] FTS is taking "

I found that FTS is taking "') as c; c --- (1 row) select * from ts_debug('english', '') as c ; alias | description | token | dictionaries | dictionary | lexemes ---+-+--+--++- tag | XML tag | |

Re: [GENERAL] alter table add column - specify where the column will go?

It is easy to create view based on SELECT which explicitly specifies the columns names. IMO it is better to hide tables structures behind views and work with them, since views are not materialized and it is easy to drop and recreate or just add another view into the database. With this approach you

Re: [GENERAL] alter table add column - specify where the column will go?

On 2010-11-24 10.43, Thomas Kellerer wrote: Grzegorz Jaśkiewicz, 24.11.2010 10:37: just never use SELECT *, but always call columns by names. You'll avoid having to depend on the order of columns, which is never guaranteed, even if the table on disk is one order, the return columns could be in

[GENERAL] Streaming processing of result sets

Unless you use COPY, libpq loads the complete query result into memory. In some cases, this is not desirable. I know that with non-MVCC databases, it is important to load the result from the database server in a non-blocking fashion because you can easily stall other transactions or even deadlock

Re: [GENERAL] alter table add column - specify where the column will go?

* Grzegorz Jaśkiewicz: > just never use SELECT *, but always call columns by names. You'll > avoid having to depend on the order of columns, which is never > guaranteed, even if the table on disk is one order, the return columns > could be in some other. This can't be true because several SQL fea

Re: [GENERAL] alter table add column - specify where the column will go?

2010/11/24 Florian Weimer : > * Grzegorz Jaśkiewicz: > >> just never use SELECT *, but always call columns by names. You'll >> avoid having to depend on the order of columns, which is never >> guaranteed, even if the table on disk is one order, the return columns >> could be in some other. > > This

Re: [GENERAL] alter table add column - specify where the column will go?

* Grzegorz Jaśkiewicz: > 2010/11/24 Florian Weimer : >> * Grzegorz Jaśkiewicz: >> >>> just never use SELECT *, but always call columns by names. You'll >>> avoid having to depend on the order of columns, which is never >>> guaranteed, even if the table on disk is one order, the return columns >>>

[GENERAL] Just 2 days left to register for PGDay.EU 2010

Yup, you heard correctly - there are just two (and a bit) days left to register for the annual European PostgreSQL Conference, pgDay.EU 2010, being held in Stuttgart on December 6th and 7th, with a day of training sessions on the 8th. http://2010.pgday.eu/ With over 40 talks in a mix of Engli

Re: [GENERAL] alter table add column - specify where the column will go?

On 11/24/2010 12:31 PM, Florian Weimer wrote: * Grzegorz Jaśkiewicz: 2010/11/24 Florian Weimer: * Grzegorz Jaśkiewicz: just never use SELECT *, but always call columns by names. You'll avoid having to depend on the order of columns, which is never guaranteed, even if the table on disk is one

Re: [GENERAL] alter table add column - specify where the column will go?

Fredric Fredricson wrote: > But if you change the column names in the second SELECT in the UNION this is > ignored: > # SELECT c1,c2 FROM (SELECT 1 AS c1, 2 AS c2 UNION SELECT 2 AS c3, 1 AS c4) > AS x; > c1 | c2 > + > 1 | 2 > 2 | 1 > Apparently, in a UNION the column names

[GENERAL] Postgres 9 and postgis1.5.2

Hi All - Did any one of you build Postgres 9 and postgis package on solaris 10? I am planning to do one. Any suggestions? Regards

Re: [GENERAL] Postgres 9 and postgis1.5.2

On Wed, Nov 24, 2010 at 10:47:18AM -0500, akp geek wrote: [..] > Did any one of you build Postgres 9 and postgis package on > solaris 10? I am planning to do one. Any suggestions? Postgres 9.0 yes, PostGIS no. Sun Studio compiler 12.2, with CFLAGS="-m64 -xO3 -xarch=native" src/in

[GENERAL] diagram tools?

Can anyone recommend a PostgreSQL compatible free tool that I can use to generate some schema diagrams of an existing database? Thanks Dan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting current and average on a single row

On Wednesday 24 November 2010 1:08:27 am Mark Morgan Lloyd wrote: > What is best practice when extracting both current and average from a > table? Demonstration table here contains data from a cheap weather station. > > I can obviously get the current reading like this: > > select temp_out, dewpoin

Re: [GENERAL] Postgres 9 and postgis1.5.2

Thanks for sharing the info. I will post my experiences Regards On Wed, Nov 24, 2010 at 10:59 AM, Sebastian Jaenicke < sjaen...@cebitec.uni-bielefeld.de> wrote: > On Wed, Nov 24, 2010 at 10:47:18AM -0500, akp geek wrote: > [..] > > Did any one of you build Postgres 9 and postgis

Re: [GENERAL] Postgres 9 and postgis1.5.2

One more question. What version of perl do you have? and do we have to install perl at all or we can use the one that comes with OS? Regards On Wed, Nov 24, 2010 at 11:14 AM, akp geek wrote: > Thanks for sharing the info. I will post my experiences > > Regards > > > On Wed, Nov 24, 2010 at 10:5

Re: [GENERAL] Postgres 9 and postgis1.5.2

Hi, On Wed, Nov 24, 2010 at 11:34:42AM -0500, akp geek wrote: > One more question. What version of perl do you have? and do we have to > install perl at all or we can use the one that comes with OS? I compiled without perl support; however, the OS-provided perl version should be ok (if you're goi

Re: [GENERAL] alter table add column - specify where the column will go?

On Wed, Nov 24, 2010 at 4:43 AM, Thomas Kellerer wrote: > Grzegorz Jaśkiewicz, 24.11.2010 10:37: > > just never use SELECT *, but always call columns by names. You'll >> avoid having to depend on the order of columns, which is never >> guaranteed, even if the table on disk is one order, the retu

Re: [GENERAL] alter table add column - specify where the column will go?

On 11/24/2010 03:32 AM, Peter Bex wrote: > On Wed, Nov 24, 2010 at 09:37:02AM +, Grzegorz Jaśkiewicz wrote: >> just never use SELECT *, but always call columns by names. You'll >> avoid having to depend on the order of columns, which is never >> guaranteed, even if the table on disk is one orde

Re: [GENERAL] Getting current and average on a single row

Adrian Klaver wrote: On Wednesday 24 November 2010 1:08:27 am Mark Morgan Lloyd wrote: What is best practice when extracting both current and average from a table? Demonstration table here contains data from a cheap weather station. I can obviously get the current reading like this: select tem

Re: [GENERAL] alter table add column - specify where the column will go?

Stuart McGraw writes: > This is the first time I've ever looked at the 1000+ page spec and I > haven't tried to chase down all the definitions so I don't pretend to > be authoritative but it sure sounds to me (as your observation above > implies) that SQL *does* have an explicit notion of column

[GENERAL] Postgres 9.01 and WAL files issue

Hi All, pg_stop_backup is not stopping... Postgresql version 9.01 OS: Centos Postgresql.conf ==> default configuration did not change anything Steps 1. Initiated psql -c "SELECT pg_start_backup('label', true)" 2. Started RSYNC job to sync slave box rsync -a /var/lib/pgsql/data/ dbpg9-test-

Re: [GENERAL] diagram tools?

There are many of them, I use SchemaSpy java based - easy to generate. http://schemaspy.sourceforge.net/ Thanks Deepak On Wed, Nov 24, 2010 at 8:08 AM, Dan Armbrust < daniel.armbrust.l...@gmail.com> wrote: > Can anyone recommend a PostgreSQL compatible free tool that I can use > to generate so

Re: [GENERAL] Postgres 9.01 and WAL files issue

Deepak, Does your backup script exclude the pg_xlog directory? There is no point backing up the WAL files but you will want to make sure the archived WAL files are added to the backup set afterwards. How many files are in pg_xlog at the point where you stop the backup? It may actually be that i

Re: [GENERAL] Postgres 9.01 and WAL files issue

Hi Mathew, Thanks for your reply. Its a test machine, I am testing streaming replication with archive. I figured out he problem, the /mnt/nfs didnt had enough space. Thanks for looking into this issue. Thanks Deepak On Wed, Nov 24, 2010 at 10:36 AM, Matthew Walden wrote: > Deepak, > > Does

Re: [GENERAL] alter table add column - specify where the column will go?

On Nov 24, 2010, at 9:42 AM, Derrick Rice wrote: > > Even if an example doesn't exist, you can at least imagine a scenario where > an improvement to postgresql is made such that the column order is decided > internally, rather than by table definition. Not when SQL compatibility requires that t

[GENERAL] PG 9.0.1 StackBuilder wants to install pgJDBC v8.4

I've been running PG 9.0 beta 4 on my local workstation, and decided to finally upgrade tonight to the released version 9.0.1-1 on Windows. I used the one-click installer; many thanks to EnterpriseDB for providing these. Installation completed without issue, then offered to run StackBuilder.

[GENERAL] is any of the SQL parser exposed ?

Hi We are running a mixture of 8.3 and 8.4 server versions. We are putting together a historical log of statement patterns of long running statement output ( via log_min_duration_statement), extracted from csv files in pg_log. I would like to take a statement of form, say, "select columns from m

Re: [GENERAL] is any of the SQL parser exposed ?

On 25/11/10 13:04, Mark Rostron wrote: Hi We are running a mixture of 8.3 and 8.4 server versions. We are putting together a historical log of statement patterns of long running statement output ( via log_min_duration_statement), extracted from csv files in pg_log. I would like to take a state

Re: [GENERAL] plpyhton

On Wed, Nov 24, 2010 at 11:56:16AM +0530, c k wrote: > Hello, > Does calling a pl/python function from each database connection load the > python interpreter each time? what are the effects of using pl/python > function in a environment where no. of concurrent connections are more and > each user c

Re: [GENERAL] diagram tools?

On Wed, Nov 24, 2010 at 10:23:15AM -0800, DM wrote: > There are many of them, I use SchemaSpy java based - easy to generate. Here are several other possibilities, which I've taken from the helpful pg_docbot that lives on Freenode in #postgresql. For whatever it's worth, I don't know that I've used

[GENERAL] Use of search path in plpgsql functions, PG 8.3.12

Hello, At present i have the following Schema 1 - Table: events Public Schema - Table: events Function CREATE OR REPLACE FUNCTION "public"."recurring_events_for" ( "range_start" timestamp, "range_end" timestamp, "time_zone" varchar, "events_limit" integer )