Re: [GENERAL] Triggering a function on table overwrite

[Misa Simic]

Hi Bob,

I guess with "overwrite the table" you mean to fill some columns with your
values in trigger...

If that is the case, in docs is example:

http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html

Kind Regards,

Misa

Sent from my Windows Phone
--
From: Bob Pawley
Sent: 06/02/2012 06:57
To: Postgresql
Subject: [GENERAL] Triggering a function on table overwrite

  Hi

Normally when I need to run a function during an insert I make it a trigger
function of that table.

However, in this case, I need to overwrite the table with which the trigger
is attached.

I would appreciate any suggestions on how to do this.

Bob

Re: [GENERAL] Triggering a function on table overwrite

[Thomas Kellerer]

Misa Simic, 06.02.2012 10:35:

Hi Bob,

I guess with "overwrite the table" you mean to fill some columns with your 
values in trigger...

If that is the case, in docs is example:

http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html

Kind Regards,



Please do not link to outdated documentation, use "current" instead of the 
version number:

http://www.postgresql.org/docs/current/static/plpgsql-trigger.html


--
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] Triggering a function on table overwrite

[Chris Angelico]

On Mon, Feb 6, 2012 at 8:42 PM, Thomas Kellerer  wrote:
> Misa Simic, 06.02.2012 10:35:
>
>> Hi Bob,
>>
>> I guess with "overwrite the table" you mean to fill some columns with your
>> values in trigger...
>>
>> If that is the case, in docs is example:
>>
>> http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html
>>
>
> Please do not link to outdated documentation, use "current" instead of the
> version number:
>
> http://www.postgresql.org/docs/current/static/plpgsql-trigger.html

It seems that Googling for documentation often brings up old versions.
Would it be difficult to have all those pages have a little banner up
the top saying something like "NOTE: This documentation pertains to a
previous version of Postgres. Documentation for the latest version of
Postgres can be found at http://blah/blah/current/blah.html";?

Chris Angelico

-- 
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] Triggering a function on table overwrite

[Misa Simic]

+1

I could undertsand the need to point to current version... but my goal was
just to give someone a hint for his problem... I have answered from bed,
from my mobile phone... so it was shortcut - few taps copy/paste, and
google pointed me to the link...

In case I needed to worry about "is it for current version" (Though I have
no idea which version user is on)... I would probably no answer at all...
too much effort for simple help...

2012/2/6 Chris Angelico 

> On Mon, Feb 6, 2012 at 8:42 PM, Thomas Kellerer 
> wrote:
> > Misa Simic, 06.02.2012 10:35:
> >
> >> Hi Bob,
> >>
> >> I guess with "overwrite the table" you mean to fill some columns with
> your
> >> values in trigger...
> >>
> >> If that is the case, in docs is example:
> >>
> >> http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html
> >>
> >
> > Please do not link to outdated documentation, use "current" instead of
> the
> > version number:
> >
> > http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
>
> It seems that Googling for documentation often brings up old versions.
> Would it be difficult to have all those pages have a little banner up
> the top saying something like "NOTE: This documentation pertains to a
> previous version of Postgres. Documentation for the latest version of
> Postgres can be found at http://blah/blah/current/blah.html";?
>
> Chris Angelico
>
> --
> 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] Error while importing CSV file

[Alban Hertroys]

On 6 February 2012 07:37, Lockas  wrote:
> I've tried a lot of sizes
> but I still have messages in my log saying:
>
> *  ---
>  ERROR:  value too long for type character varying(200)
>  --- *
>
> Why is this? There are no other varchar(200) columns in my DB at all,
> no other table. Only this column used to be 25 characters and using
> the alter type command I changed it to 200. It looks alright, the column
> also
> accepts the value, but the error is logged.
>
> I'm confused. How to stop the error?

Perhaps your CSV file doesn't put each record on a new line or
contains an escape at the end of a line that makes the next line part
of it?

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

-- 
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] Why extract( ... from timestamp ) is not immutable?

[Jasen Betts]

On 2012-01-25, hubert depesz lubaczewski  wrote:
> On Wed, Jan 25, 2012 at 02:07:40PM -0800, Adrian Klaver wrote:
>> Finally dawned on me. When you use 'at time zone' on a timestamp
>> with tz it strips the tz which then allows the value to be indexed
>> because:
>> 
>> -[ RECORD 5 
>> ]---+-
>> Schema  | pg_catalog
>> Name| date_part
>> Result data type| double precision
>> Argument data types | text, timestamp without time zone
>> Type| normal
>> Volatility  | immutable
>> Owner   | postgres
>> Language| internal
>> Source code | timestamp_part
>> Description | extract field from timestamp
>
> yes, but it is not correct - the value is actually stable, and not
> immutable.

it's immutable for all date parts except "epoch".

epoch is backwards to the other date parts.
immutable for timestamptz and stable for timestamp

-- 
⚂⚃ 100% natural


-- 
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] Why this regexp matches?!

[Jasen Betts]

On 2012-02-04, hubert depesz lubaczewski  wrote:
> select 'depesz depeszx depesz' ~ E'^(.*)( \\1)+$';
>
> what's worse:
> $ select regexp_replace( 'depesz depeszx depesz', E'^(.*)( \\1)+$', E'\\1' );
>  regexp_replace 
> 
>  depesz
> (1 row)
>
> I know that Pg regexps are limited, but even grep's regexps match this
> correctly:

whose grep?
 
Postgres is BSD licence and that means they can't use the latest and
greatest GPL libraries.

-- 
⚂⚃ 100% natural


-- 
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] Why extract( ... from timestamp ) is not immutable?

[Jasen Betts]

On 2012-01-25, Adrian Klaver  wrote:
> On Wednesday, January 25, 2012 7:48:34 am hubert depesz lubaczewski wrote:
>> On Wed, Jan 25, 2012 at 07:44:14AM -0800, Adrian Klaver wrote:
>> > > I thought that this is what I will achieve with extract(epoch from
>> > > now() at time zone 'UTC') but clearly it doesn't work.
>> > > So what options do I have?
>> > 
>> > Isn't extract(epoch from now()) getting what you want?
>> 
>> you can't make index on it.
>
> I am afraid I am not following. So you can make an index on?:
>
> extract(epoch from now() at time zone 'UTC')

that one gets you the wrong result.

-- 
⚂⚃ 100% natural


-- 
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] Why this regexp matches?!

[hubert depesz lubaczewski]

On Mon, Feb 06, 2012 at 11:29:23AM +, Jasen Betts wrote:
> On 2012-02-04, hubert depesz lubaczewski  wrote:
> > select 'depesz depeszx depesz' ~ E'^(.*)( \\1)+$';
> >
> > what's worse:
> > $ select regexp_replace( 'depesz depeszx depesz', E'^(.*)( \\1)+$', E'\\1' 
> > );
> >  regexp_replace 
> > 
> >  depesz
> > (1 row)
> >
> > I know that Pg regexps are limited, but even grep's regexps match this
> > correctly:
> 
> whose grep?
>  
> Postgres is BSD licence and that means they can't use the latest and
> greatest GPL libraries.

yes, I did use gnu grep. but it's hardly "latest and greatest" - there
is nothing very special about this regexp, aside from the fact, that
according to pg docs (how I read them) - it shouldn't match, but it
does.

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.com/

-- 
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] vacuumlo fails pgsql ver 8.3

[Pat Heuvel]

On 6/02/2012 4:39 AM, Tom Lane wrote:

Pat Heuvel  writes:

[ vacuumlo fails ]
When I added the -v option, there were many "removing lo x" messages
before the above messages appeared. I have previously tried to reindex
pg_largeobject, but that process failed as well.

You need to get the index consistent before trying vacuumlo; that
program is not designed to deal with inconsistent catalogs.

What exactly happens when you try to reindex pg_largeobject?

regards, tom lane


ERROR:  could not create unique index "pg_largeobject_loid_pn_index"
DETAIL:  Table contains duplicated values.

** Error **

ERROR: could not create unique index "pg_largeobject_loid_pn_index"
SQL state: 23505
Detail: Table contains duplicated values.

Re: [GENERAL] Error while importing CSV file

[Lockas]

*
ok then if I want to except that row from copying. how i can write it ?*

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5459976.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
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] Error while importing CSV file

[David Johnston]

On Feb 6, 2012, at 7:26, Lockas  wrote:

> *
> ok then if I want to except that row from copying. how i can write it ?*
> 
> 

Copy is all or nothing.  Either fix the problem row(s) or remove them from the 
file manually.

David J.
-- 
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] Error while importing CSV file

[Alban Hertroys]

On 6 February 2012 13:26, Lockas  wrote:
> *
> ok then if I want to except that row from copying. how i can write it ?*

You can either remove the offending line(s) from the csv file or copy
to a staging table that doesn't have those limitations on field
lengths first.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Backup database remotely

[Fanbin Meng]

I installed the PostgreSql9.0 in windows 7 with "one click installer".
How can i backup another PostgreSql server database remotely  via an
internet connection .
I trid "add a connection to a server", but it did not work.
Does anyone can help me or give suggestions?
Thanks
Fanbin

Re: [GENERAL] Puzzling full database lock

[Merlin Moncure]

On Fri, Feb 3, 2012 at 2:55 PM, Christopher Opena  wrote:
> Merlin, thanks for the response.

no problem.  if you're open to architecture suggestions you might also
want to consider going with HS/SR and getting those large olap queries
off your main database.  you'll have to configure it to be very
forgiving of long running queries (which can pause replication) but
this should be a huge win in terms of total load on the main server.
also, you can skimp on hardware to a certain degree to cut costs.  we
still want to get to the bottom of this obviously but it can't hurt to
get a plan 'B' going...

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to create crosstab with 3 values in every crosstab column

[Andrus]

I'm looking for a way to generate cross tab with 3 columns for every store 
where 
number of stores in not hard coded.
Every store info should contain 3 columns:

turnover
budget
budget percent (=turnover/budget*100)

Result should look like:

Acc   st1turnover   st1budget st1percent  ...   stNturnover  st1budget 
stNpercent
311   100   20050 200  ...300   67
312   400   50080 600  ...700   86  
  
...

I tried crosstab from tablefunc but it allows only single value in every 
crosstabled column. 

How to show 3 values in every column: sales, budget and percent in this order?

Tables are:

create table sales (
  account char(10), 
  store char(10),
  sales  numeric(12,2) );
 
insert into sales values 
('311','ST1',100)... ('311','STN',200)
('312','ST1',400)... ('312','STN',600);

create table budget (
  account char(10), 
  store char(10),
  budget numeric(12,2) );
 
insert into budger values 
('311','ST1',200)... ('311','STN',300)
('312','ST1',500)... ('312','STN',700);
 

Some account and store values may be missing from tables.

Andrus.

Re: [GENERAL] How to create crosstab with 3 values in every crosstab column

[Misa Simic]

Hm...

I am not sure it is possible at all and with just 1 column for crosstab
(instead of 3) to return table with undefined No of columns (to number of
stores do not be hardcoded)...

At least you must define your return type...

Problem is known to me, and we have solved it on some way... which is not
acceptable as generic solution... I mean - works just with our app...


So, we are doing that in two steps...

first calculate how much columns we should return...No of stores in in your
Sales table (multiply 3 in your case)...

(and based on that - build the grid in UI)

then with knowing that - we select full "table"  with simple 3 functions:
CalcSales(coount, Store), getBudget(account, Store)

and then build dynamic query with those 3 functions many times as we have
stores as columns...

Kind Regards,

Misa

2012/2/6 Andrus 

>   I'm looking for a way to generate cross tab with 3 columns for every
> store where
> number of stores in not hard coded.
> Every store info should contain 3 columns:
>
> turnover
> budget
> budget percent (=turnover/budget*100)
>
> Result should look like:
>
> Acc   st1turnover   st1budget st1percent  ...   stNturnover  st1budget
> stNpercent
> 311   100   20050 200  ...
> 300   67
> 312   400   50080 600  ...
> 700   86
> ...
>
> I tried crosstab from tablefunc but it allows only single value in every
> crosstabled column.
>
> How to show 3 values in every column: sales, budget and percent in this
> order?
>
> Tables are:
>
> create table sales (
>   account char(10),
>   store char(10),
>   sales  numeric(12,2) );
>  insert into sales values
> ('311','ST1',100)... ('311','STN',200)
> ('312','ST1',400)... ('312','STN',600);
>
> create table budget (
>   account char(10),
>   store char(10),
>   budget numeric(12,2) );
>  insert into budger values
> ('311','ST1',200)... ('311','STN',300)
> ('312','ST1',500)... ('312','STN',700);
>
> Some account and store values may be missing from tables.
>
> Andrus.
>

[GENERAL] windows 2008 scheduled task problem

[Ralph Dell]

I am running postgresql 8.4.3/ postgis 1.5 on a windows server 2008 R2

I am unable to get the shp2pgsql command to run as scheduled tasks.
There is no problem running any of the commands from the command line or
a python script.

 

Some sample commands are 

 

shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy >
myshape.txt

or

%POSTGIS_PATH%\shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx
-p yyy > myshape.txt

or 

shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy  | psql
-d mydb -U xxx -q

 

I do not get any output from the scheduled task.

 

The history tab in the task scheduler will show Task triggered/ task
started / task completed, and report the task successfully completed.

I am running the tasks under a system administrator account.

 

Any suggestions from someone who has a similar environment.

 

This is my first post to the list and I hope it is an appropriate place
for the question I and I have provided enough detail.

 

Ralph Dell

 

Re: [GENERAL] How to create crosstab with 3 values in every crosstab column

[Andrus]

Thank you.


first calculate how much columns we should return...No of stores in in your
Sales table (multiply 3 in your case)...
(and based on that - build the grid in UI)
then with knowing that - we select full "table"  with simple 3 functions:
CalcSales(coount, Store), getBudget(account, Store)
and then build dynamic query with those 3 functions many times as we have
stores as columns...


My goal is to open result En excel.
Instead of building dynamic query isn't it reasonable to create csv file
directly from code without using crosstab
but creating it manually ?
It looks that crosstab does not have any advantages instead on manual
crosstab creation ?

Another possibility is to merge those 3 columns into single column and
crosstab it .
After that create csv file by splitting single column into 3 columns.

Can some postgres array function or something like used to split 1 column
into 3 columns in result without
building query string manually ?

I posted this also in

http://stackoverflow.com/questions/9162497/how-to-generate-crosstab-with-3-columns-for-every-store-in-postgresql

Andrus. 



--
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] windows 2008 scheduled task problem

[Susan Cassidy]

See my reply below:

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ralph Dell
Sent: Monday, February 06, 2012 8:26 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] windows 2008 scheduled task problem

I am running postgresql 8.4.3/ postgis 1.5 on a windows server 2008 R2
I am unable to get the shp2pgsql command to run as scheduled tasks. There is no 
problem running any of the commands from the command line or a python script.

Some sample commands are

shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy > myshape.txt
or
%POSTGIS_PATH%\shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy 
> myshape.txt
or
shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy  | psql -d mydb 
-U xxx -q

I do not get any output from the scheduled task.

The history tab in the task scheduler will show Task triggered/ task started / 
task completed, and report the task successfully completed.
I am running the tasks under a system administrator account.

Any suggestions from someone who has a similar environment.

This is my first post to the list and I hope it is an appropriate place for the 
question I and I have provided enough detail.

Ralph Dell

I don't know about Win 2008, but under XP, I have to use the full path of any 
programs I have in scheduled job.

Susan

Re: [GENERAL] vacuumlo fails pgsql ver 8.3

[Tom Lane]

Pat Heuvel  writes:
> On 6/02/2012 4:39 AM, Tom Lane wrote:
>> What exactly happens when you try to reindex pg_largeobject?

> ERROR:  could not create unique index "pg_largeobject_loid_pn_index"
> DETAIL:  Table contains duplicated values.

Could be worse.  What you'll need to do is look through the
pg_largeobject catalog for duplicated (loid, pageno) values, and
manually DELETE the redundant rows, or else reassign them new OIDs
if you want to keep the data.

A tip for issuing the removal commands is to use the CTID column to
distinguish otherwise-identical rows, ie you could do something
like
select ctid, loid, pageno from pg_largeobject
where (loid, pageno) in (select loid, pageno from pg_largeobject
group by 1, 2 having count(*) > 1);
... examine results ...
delete from pg_largeobject where ctid = '...';

I believe you'll need to do any direct DELETE or UPDATE on the
catalog as superuser.

regards, tom lane

-- 
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] windows 2008 scheduled task problem

[Richard Sickler]

Ralph,

It may help to break this into a couple of parts
Make a folder called c:\cronjobs
in c:\cronjobs create a bat file that contains the commands you want
executed.

Maybe something like

set logfile=shp2pgsql.log
echo Running shp2pgsql > %logfile%
date /t >> %logfile%
time /t >> %logfile%
shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy >
myshape.txt

Get everything working as expected when you invoke the bat file. Once you
have that working, then setup scheduled tasks to call the bat file.

Rich

On Mon, Feb 6, 2012 at 8:42 AM, Susan Cassidy  wrote:

> **
>
> See my reply below:
>
> * *
>
> *From:* pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org] *On Behalf Of *Ralph Dell
> *Sent:* Monday, February 06, 2012 8:26 AM
> *To:* **pgsql-general@postgresql.org**
> *Subject:* [GENERAL] windows 2008 scheduled task problem
>
> ** **
>
> I am running postgresql 8.4.3/ postgis 1.5 on a windows server 2008 R2
>
> I am unable to get the shp2pgsql command to run as scheduled tasks. There
> is no problem running any of the commands from the command line or a python
> script.
>
> ** **
>
> Some sample commands are 
>
> ** **
>
> shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy >
> myshape.txt
>
> or
>
> %POSTGIS_PATH%\shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p
> yyy > myshape.txt
>
> or 
>
> shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy  | psql –d
> mydb –U xxx –q
>
> ** **
>
> I do not get any output from the scheduled task.
>
> ** **
>
> The history tab in the task scheduler will show Task triggered/ task
> started / task completed, and report the task successfully completed.
>
> I am running the tasks under a system administrator account.
>
> ** **
>
> Any suggestions from someone who has a similar environment.
>
> ** **
>
> This is my first post to the list and I hope it is an appropriate place
> for the question I and I have provided enough detail.
>
> ** **
>
> Ralph Dell
>
> ** **
>
> I don’t know about Win 2008, but under XP, I have to use the full path of
> any programs I have in scheduled job.
>
> ** **
>
> Susan
>

Re: [GENERAL] Backup database remotely

[Andreas Kretschmer]

Fanbin Meng  wrote:

> I installed the PostgreSql9.0 in windows 7 with "one click installer".
> How can i backup another PostgreSql server database remotely  via an internet
> connection .
> I trid "add a connection to a server", but it did not work.

Don't know waht you mean with 'add a connection to a server'. You should
use something like:

pg_dump -h  (or pg_dumpall)


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
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] How to create crosstab with 3 values in every crosstab column

[Misa Simic]

Hi,

I think you dont understand me (or I do not understand you :) )

the point is - it is not possible to get unknown no of columns in 1 SQL
query...

i.e.

Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00

to get:

Acount,St1 ,  St2
100, 1000.00  2000.00

to get that in your query... St1 and St2 - must be hardcoded... (is there 1
column per Store, or 3 columns per store it is less important...)

if it St1 and St2 are hardcoded in query, even if in table is:

Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00
100, St3, 3000.00

We would get the same result...actually if we want St3 we need to change
our query and add St3 in it...

that is the reason why we use Dynamic SQL to build the query...

when you build your Dynamic SQL query... you could use COPY (dynamicQuery)
TO CSV file...

or instead of to build dynamic query, you can export directly to file...

So still we need our functions: CalcSales(acount, Store),
getBudget(account, Store): calcPercent(account, Store)

make export function in some procedural language you are familiar with...
(plpgql, python, perl... whatever - just pure SQL is not possible...)

wich will:


   1. loop trough distinict Accounts from Sales Table
   2. inside Accounts loop,
   3. WriteToFile(Account), then  loop trough distinct Stores from Sales
   Table
   4. inside Stores Loop
   5. WriteToFile(';'  + Store)
   6. WriteToFile(';'  + calcSales(acount, Store)
   7. WriteToFile(';'  + getBudget(acount, Store)
   8. WriteToFile(';'  + calcPercent(acount, Store)
   9. after Stores loop make new line in file
   10. and after Accounts loop close the file..

Optionally you can first loop trough Stores loop to create header line...

Kind Regards,

Misa






that is the reason why we use dynamic query...


2012/2/6 Andrus 

> Thank you.
>
>
>  first calculate how much columns we should return...No of stores in in
>> your
>> Sales table (multiply 3 in your case)...
>> (and based on that - build the grid in UI)
>> then with knowing that - we select full "table"  with simple 3 functions:
>> CalcSales(coount, Store), getBudget(account, Store)
>> and then build dynamic query with those 3 functions many times as we have
>> stores as columns...
>>
>
> My goal is to open result En excel.
> Instead of building dynamic query isn't it reasonable to create csv file
> directly from code without using crosstab
> but creating it manually ?
> It looks that crosstab does not have any advantages instead on manual
> crosstab creation ?
>
> Another possibility is to merge those 3 columns into single column and
> crosstab it .
> After that create csv file by splitting single column into 3 columns.
>
> Can some postgres array function or something like used to split 1 column
> into 3 columns in result without
> building query string manually ?
>
> I posted this also in
>
> http://stackoverflow.com/**questions/9162497/how-to-**
> generate-crosstab-with-3-**columns-for-every-store-in-**postgresql
>
> Andrus.
>

Re: [GENERAL] How to create crosstab with 3 values in every crosstab column

[Andrus]

Thank you.

the point is - it is not possible to get unknown no of columns in 1 SQL 
query...

i.e.
Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00
to get:
Acount,St1 ,  St2
100, 1000.00  2000.00
to get that in your query... St1 and St2 - must be hardcoded... (is there 1 
column per Store, or 3 columns per store it >is less important...)

if it St1 and St2 are hardcoded in query, even if in table is:
Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00
100, St3, 3000.00
We would get the same result...actually if we want St3 we need to change 
our query and add St3 in it...

that is the reason why we use Dynamic SQL to build the query...
when you build your Dynamic SQL query... you could use COPY (dynamicQuery) 
TO CSV file...

or instead of to build dynamic query, you can export directly to file...


I din't knwo this. This seems very serious limitation which makes crosstab 
useless .

I tried

create temp table sales (
 account char(10),
 store char(10),
 sales  numeric(12,2) ) on commit drop;

insert into sales values
('311','ST1',100), ('311','STN',200),
('312','ST1',400), ('312','STN',600);

select * from
crosstab('select * from sales', 'select distinct store from sales' ) x

and got error

ERROR:  a column definition list is required for functions returning 
"record"


Can we use something like

select * from
 dynamicwrapper( crosstab('select * from sales', 'select distinct store 
from sales' ))  x


Where to find generic dynamicwrapper stored procedure which fixes this by 
building dynamic query itself or other idea ?


Andrus.







--
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] How to create crosstab with 3 values in every crosstab column

[Misa Simic]

Hi,

Well, I think you will need to write your own function(s) which will solve
your particular case...

There are two ways explaind in last mails... Dynamic SQL or direct export
to file...

Kind Regards,

Misa

2012/2/6 Andrus 

> Thank you.
>
>
>  the point is - it is not possible to get unknown no of columns in 1 SQL
>> query...
>> i.e.
>> Account, Store, Amount
>> 100, St1, 1000.00
>> 100, St2, 2000.00
>> to get:
>> Acount,St1 ,  St2
>> 100, 1000.00  2000.00
>> to get that in your query... St1 and St2 - must be hardcoded... (is there
>> 1 column per Store, or 3 columns per store it >is less important...)
>> if it St1 and St2 are hardcoded in query, even if in table is:
>> Account, Store, Amount
>> 100, St1, 1000.00
>> 100, St2, 2000.00
>> 100, St3, 3000.00
>> We would get the same result...actually if we want St3 we need to change
>> our query and add St3 in it...
>> that is the reason why we use Dynamic SQL to build the query...
>> when you build your Dynamic SQL query... you could use COPY
>> (dynamicQuery) TO CSV file...
>> or instead of to build dynamic query, you can export directly to file...
>>
>
> I din't knwo this. This seems very serious limitation which makes crosstab
> useless .
> I tried
>
> create temp table sales (
>  account char(10),
>  store char(10),
>  sales  numeric(12,2) ) on commit drop;
>
> insert into sales values
> ('311','ST1',100), ('311','STN',200),
> ('312','ST1',400), ('312','STN',600);
>
> select * from
> crosstab('select * from sales', 'select distinct store from sales' ) x
>
> and got error
>
> ERROR:  a column definition list is required for functions returning
> "record"
>
> Can we use something like
>
> select * from
>  dynamicwrapper( crosstab('select * from sales', 'select distinct store
> from sales' ))  x
>
> Where to find generic dynamicwrapper stored procedure which fixes this by
> building dynamic query itself or other idea ?
>
> Andrus.
>
>
>
>
>
>
>

[GENERAL] SSL mode detection

[Bosco Rama]

Hi folks,

I'm using PG 8.4.10 on Ubuntu Server 10.04.3.LTS x86_64.

Is there any way to query the SSL mode for client connections?

E.g.  select backend_id from some_table_or_view where ssl_mode = false;

I have not been able to find it in pg_stat_activity or anywhere else in
the catalog.  Maybe I'm just blind. :-(

TIA.

Bosco.

-- 
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] How to create crosstab with 3 values in every crosstab column

[Marc Mamin]

Hello,

 

as you don't seems to need the returned column definition in Postgres, a 
solution may be to cast the result to text.

 

e.g.:

 

 

create or replace function get_record ()

returns setof text as

$$

  select (foo)::text from 

  (values(1,'a a'),(3,'b b'))foo

$$

language sql;

 

select trim(r,'\\(\\)') from get_record () r;

 

regards,

 

Marc Mamin

 

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Misa Simic
Sent: Montag, 6. Februar 2012 19:52
To: Andrus
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to create crosstab with 3 values in every crosstab 
column

 

Hi,

 

Well, I think you will need to write your own function(s) which will solve your 
particular case... 

 

There are two ways explaind in last mails... Dynamic SQL or direct export to 
file...

 

Kind Regards,

 

Misa

2012/2/6 Andrus 

Thank you.

 

the point is - it is not possible to get unknown no of columns in 1 SQL 
query...
i.e.
Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00
to get:
Acount,St1 ,  St2
100, 1000.00  2000.00
to get that in your query... St1 and St2 - must be hardcoded... (is 
there 1 column per Store, or 3 columns per store it >is less important...)
if it St1 and St2 are hardcoded in query, even if in table is:
Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00
100, St3, 3000.00
We would get the same result...actually if we want St3 we need to 
change our query and add St3 in it...
that is the reason why we use Dynamic SQL to build the query...
when you build your Dynamic SQL query... you could use COPY 
(dynamicQuery) TO CSV file...
or instead of to build dynamic query, you can export directly to file...

 

I din't knwo this. This seems very serious limitation which makes crosstab 
useless .
I tried

create temp table sales (
 account char(10),
 store char(10),
 sales  numeric(12,2) ) on commit drop;

insert into sales values
('311','ST1',100), ('311','STN',200),
('312','ST1',400), ('312','STN',600);

select * from
crosstab('select * from sales', 'select distinct store from sales' ) x

and got error

ERROR:  a column definition list is required for functions returning "record"

Can we use something like

select * from
 dynamicwrapper( crosstab('select * from sales', 'select distinct store from 
sales' ))  x

Where to find generic dynamicwrapper stored procedure which fixes this by 
building dynamic query itself or other idea ?

Andrus.







 

Re: [GENERAL] windows 2008 scheduled task problem

[Ralph Dell]

 

 

Ralph Dell, GISP

GIS Dept.

Catawba County, NC

 

From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Richard Sickler
Sent: Monday, February 06, 2012 12:04 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] windows 2008 scheduled task problem

 

Ralph,

It may help to break this into a couple of parts
Make a folder called c:\cronjobs
in c:\cronjobs create a bat file that contains the commands you want
executed.

Maybe something like 

set logfile=shp2pgsql.log
echo Running shp2pgsql > %logfile%
date /t >> %logfile%
time /t >> %logfile%
shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy >
myshape.txt

Get everything working as expected when you invoke the bat file. Once
you have that working, then setup scheduled tasks to call the bat file. 

Rich

On Mon, Feb 6, 2012 at 8:42 AM, Susan Cassidy 
wrote:

See my reply below:

 

From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ralph Dell
Sent: Monday, February 06, 2012 8:26 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] windows 2008 scheduled task problem

 

I am running postgresql 8.4.3/ postgis 1.5 on a windows server 2008 R2

I am unable to get the shp2pgsql command to run as scheduled tasks.
There is no problem running any of the commands from the command line or
a python script.

 

Some sample commands are 

 

shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy >
myshape.txt

or

%POSTGIS_PATH%\shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx
-p yyy > myshape.txt

or 

shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy  | psql
-d mydb -U xxx -q

 

I do not get any output from the scheduled task.

 

The history tab in the task scheduler will show Task triggered/ task
started / task completed, and report the task successfully completed.

I am running the tasks under a system administrator account.

 

Any suggestions from someone who has a similar environment.

 

This is my first post to the list and I hope it is an appropriate place
for the question I and I have provided enough detail.

 

Ralph Dell

 

I don't know about Win 2008, but under XP, I have to use the full path
of any programs I have in scheduled job.

 

Susan

 

Re: [GENERAL] windows 2008 scheduled task problem - Solved

[Ralph Dell]

I don't want to admit how many times I have revisited this but I have
some preliminary tests working.

 

My test server is XP and I just reconfirmed that there are no issues
getting the scheduled task to run on that box pretty easily.

 

On the windows server 2008 the following looks like it will work  as a
scheduled task

 

cd  %POSTGIS_PATH%

shp2pgsql -s 900913 -I -d c:\\PostgreSQL\\data\\test6.shp gis.test6 -u
xxx -p yyy | psql -d mydb -U xxx -q

 

where %POSTGIS_PATH% is the path to my postgreSQL /bin folder, and my
data is residing  on the local computer

When my data was on another server shp2pgsql did not give me any output,
and the task also failed if I moved my shp2pgsql and/or psql to another
folder.

 

>From the command line I did not have any issues. I did not have to cd to
the /bin folder and my data could reside remote server.

 

Ralph Dell

 

From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Richard Sickler
Sent: Monday, February 06, 2012 12:04 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] windows 2008 scheduled task problem

 

Ralph,

It may help to break this into a couple of parts
Make a folder called c:\cronjobs
in c:\cronjobs create a bat file that contains the commands you want
executed.

Maybe something like 

set logfile=shp2pgsql.log
echo Running shp2pgsql > %logfile%
date /t >> %logfile%
time /t >> %logfile%
shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy >
myshape.txt

Get everything working as expected when you invoke the bat file. Once
you have that working, then setup scheduled tasks to call the bat file. 

Rich

On Mon, Feb 6, 2012 at 8:42 AM, Susan Cassidy 
wrote:

See my reply below:

 

From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ralph Dell
Sent: Monday, February 06, 2012 8:26 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] windows 2008 scheduled task problem

 

I am running postgresql 8.4.3/ postgis 1.5 on a windows server 2008 R2

I am unable to get the shp2pgsql command to run as scheduled tasks.
There is no problem running any of the commands from the command line or
a python script.

 

Some sample commands are 

 

shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy >
myshape.txt

or

%POSTGIS_PATH%\shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx
-p yyy > myshape.txt

or 

shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy  | psql
-d mydb -U xxx -q

 

I do not get any output from the scheduled task.

 

The history tab in the task scheduler will show Task triggered/ task
started / task completed, and report the task successfully completed.

I am running the tasks under a system administrator account.

 

Any suggestions from someone who has a similar environment.

 

This is my first post to the list and I hope it is an appropriate place
for the question I and I have provided enough detail.

 

Ralph Dell

 

I don't know about Win 2008, but under XP, I have to use the full path
of any programs I have in scheduled job.

 

Susan

 

[GENERAL] pg_upgrade: out of memory

[deepak]

Hi!

While running pg_upgrade, on one instance, it ran out of memory during the
final stages of upgrade
(just before it starts to "link" old database files to new ones).


We are using Postgres 9.1.1, and I see that there were some fixes to
pg_upgrade in 9.1.2, though
it doesn't mention anything about memory issues as such.

Wondering if anyone has run into a similar type of situation, and if so,
how to solve it?

Also, is it possible to recover the database once pg_upgrade aborts in this
manner?


Here's an excerpt from the log while running pg_upgrade:
...
"bin/pg_ctl" -w  -D "data"  stop
waiting for server to shut downLOG:  received smart shutdown request
LOG:  shutting down
...LOG:  database system is shut down
. done
server stopped
Restoring user relation files
Restoring user relation files
pg_upgrade: out of memory

pg_upgrade: out of memory



Thanks for any pointers,

Deepak

[GENERAL] problems connecting to php via pg_connect and PGCLUSTER

[Dave Potts]

I have two versions of postgres installed, 8.4 and 9.1 installed on the
same machine

To connect to my 9.1 database, I defined the envromental variable

PGCLUSTER=9.1/main

and use psql to connect via php pg_connect
I have try saying

define("PG_OPTIONS"  , "--cluster=9.1/main");

and using the phrase

  $con = pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER."
options=".PG_OPTIONS);

When I attempt to connect to postgres,  I get the  error

[Mon Feb 06 22:37:40 2012] [error] [client 127.0.0.1] PHP Warning:
pg_connect(): Unable to connect to PostgreSQL server: FATAL:  unrecognised
configuration parameter "cluster" in /var/www/re/php/pgrouting.php on line
33, referer: http://127.0.0.1/re/routing-final.html

Any suggestions as to the correct way of connecting to postgres?

Dave.


-- 



-- 
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] problems connecting to php via pg_connect and PGCLUSTER

[Chris]

On 07/02/12 10:03, Dave Potts wrote:


I have two versions of postgres installed, 8.4 and 9.1 installed on the
same machine

To connect to my 9.1 database, I defined the envromental variable

PGCLUSTER=9.1/main

and use psql to connect via php pg_connect
I have try saying

define("PG_OPTIONS"  , "--cluster=9.1/main");

and using the phrase

   $con = pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER."
options=".PG_OPTIONS);

When I attempt to connect to postgres,  I get the  error

[Mon Feb 06 22:37:40 2012] [error] [client 127.0.0.1] PHP Warning:
pg_connect(): Unable to connect to PostgreSQL server: FATAL:  unrecognised
configuration parameter "cluster" in /var/www/re/php/pgrouting.php on line
33, referer: http://127.0.0.1/re/routing-final.html

Any suggestions as to the correct way of connecting to postgres?


Try putting quotes around the options, eg:
pg_connect("host=localhost options='".PG_OPTIONS."'");

Alternatively, set the port and the path to the socket directory (not 
the actual file), eg:


pg_connect('dbname=xxx host=/tmp user=xxx port=5433');

If the socket file is in /tmp/

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


--
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] \copy: unexpected response (4)

[Charlie]

I have encountered the symmetric error to this -> PGRES_COPY_OUT.

We are using a foreign data wrapper into a large (and unnamed) database
system which generates a substantial pipeline of rows for copyout to bring
back through libPQ to respond to the psql request.

If the back end is blown away the request hangs in the perl command line
script containing the psql command. That command is then terminated with an
alarm(60) call, which was unfortunately a too short timeout.

Nevertheless, the gigabyte of error messages (one per row I assume) that are
generated is probably too much error notification. One (or two) error
message(s) would do. And the fix should possibly look something like:

/*
 * Make sure we have pumped libpq dry of results; else it may still
be in
 * ASYNC_BUSY state, leading to false readings in, eg, get_prompt().
 */
while ((result = PQgetResult(pset.db)) != NULL)
{
success = false;
psql_error("\\copy: unexpected response (%d)\n",
   PQresultStatus(result));
 /* if still in COPY IN or COPY OUT state, try to get out of it */
  if (PQresultStatus(result) == PGRES_COPY_IN || PQresultStatus(result) ==
PGRES_COPY_OUT)
 PQputCopyEnd(conn, _("trying to exit copy mode"));
PQclear(result);
}

I think this becomes an issue only when foreign data wrappers are used,
because both ends of the conversation can die with a pipeline of data being
shoveled through a shared library call under the foreign data wrapper, while
postgres bravely and pointlessly carries on.

Charlie ...


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/copy-unexpected-response-4-tp1922077p5461710.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
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] Don't Thread On Me (PostgreSQL related)

[Rodrigo E . De León Plicet]

On Jan 26, 4:52 pm, Rodrigo E. De León Plicet 
wrote:
> Quote:
>
> ==
>
> This thread
>
> http://postgresql.1045698.n5.nabble.com/Multithread-Query-Planner-td5...
>
> was mentioned in a performance sub-group posting. Give it a read.
>
> Back? It means, so far as I can see, that PG is toast. It will fall
> down to being the cheap and dirty alternative to MySql, which even
> has, at least two, multi-threaded engines. DB2 switched it's *nix
> engine to threads from processes with release 9.5. Oracle claims it
> for releases going back to 7 (I haven't tried to determine which parts
> or applications; Larry has bought so many tchochtkes over the
> years...). SQL Server is threaded.
>
> Given that cpu's are breeding threads faster than cores,
> PG will fall into irrelevance.
>
> ==
>
> Source:http://drcoddwasright.blogspot.com/2012/01/dont-thread-on-me.html
>
> Comments?


Author's followup:

http://drcoddwasright.blogspot.com/2012/02/damn-you-damocles.html

-- 
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] Don't Thread On Me (PostgreSQL related)

[John R Pierce]

On 02/03/12 5:53 PM, Rodrigo E. De León Plicet wrote:

Author's followup:

http://drcoddwasright.blogspot.com/2012/02/damn-you-damocles.html


his links hardly seem related to his proclamations.



--
john r pierceN 37, W 122
santa cruz ca mid-left coast



--
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] Error while importing CSV file

[Lockas]

OK .. my offending line number is 4533
How can i remove it while copying ?

*COPY Table
FROM 'insert .csv dir here' 
USING DELIMITERS ';'  CSV HEADER*

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5462255.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
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] Don't Thread On Me (PostgreSQL related)

[Chris Travers]

My reply is at:
http://ledgersmbdev.blogspot.com/2012/02/robert-young-is-wrong-about-threads-and.html

On Mon, Feb 6, 2012 at 9:05 PM, John R Pierce  wrote:

> On 02/03/12 5:53 PM, Rodrigo E. De León Plicet wrote:
>
>> Author's followup:
>>
>> http://drcoddwasright.**blogspot.com/2012/02/damn-you-**damocles.html
>>
>
> his links hardly seem related to his proclamations.
>
>
>
> --
> john r pierceN 37, W 122
> santa cruz ca mid-left coast
>
>
>
>
> --
> 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] problems connecting to php via pg_connect and PGCLUSTER

[Dave Potts]

Hi Chris
Thanks for the suggestion,  I tried a making the changes are you suggest, 
I still getting an error from Postgres ie
 define("PG_OPTIONS"  , "--cluster=9.1/main");
 define("PG_DB"  , "tripe");
 define("PG_HOST", "localhost");
 define("PG_USER", "dp42");
 define("PG_PORT", "5432");
 define("TABLE",   "route_table");
$con = pg_connect("dbname='".PG_DB."' host='".PG_HOST."'
user='".PG_USER."' options='".PG_OPTIONS."'");

Error message
04:41 2012] [error] [client 127.0.0.1] PHP Warning:  pg_connect(): Unable
to connect to PostgreSQL server: FATAL:  unrecognised configuration
parameter "cluster" in /var/www/re/php/pgrouting.php on line 34, referer:
http://127.0.0.1/re/routing-final.html

The problem is with the database server it has issues with the string
--cluster=9.1/main"

The system is happy enough if I set PGCLUSTER as follows

export PGCLUSTER=9.1/main
psql -d tripe

I am justing trying to the same with pg_connect


 wrote:
> On 07/02/12 10:03, Dave Potts wrote:
>>
>> I have two versions of postgres installed, 8.4 and 9.1 installed on the
>> same machine
>>
>> To connect to my 9.1 database, I defined the envromental variable
>>
>> PGCLUSTER=9.1/main
>>
>> and use psql to connect via php pg_connect
>> I have try saying
>>
>> define("PG_OPTIONS"  , "--cluster=9.1/main");
>>
>> and using the phrase
>>
>>$con = pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER."
>> options=".PG_OPTIONS);
>>
>> When I attempt to connect to postgres,  I get the  error
>>
>> [Mon Feb 06 22:37:40 2012] [error] [client 127.0.0.1] PHP Warning:
>> pg_connect(): Unable to connect to PostgreSQL server: FATAL:
>> unrecognised
>> configuration parameter "cluster" in /var/www/re/php/pgrouting.php on
>> line
>> 33, referer: http://127.0.0.1/re/routing-final.html
>>
>> Any suggestions as to the correct way of connecting to postgres?
>
> Try putting quotes around the options, eg:
> pg_connect("host=localhost options='".PG_OPTIONS."'");
>
> Alternatively, set the port and the path to the socket directory (not
> the actual file), eg:
>
> pg_connect('dbname=xxx host=/tmp user=xxx port=5433');
>
> If the socket file is in /tmp/
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


-- 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] default database selector

[Dave Potts]

I am running Ubuntu 11.04, I have Postgres 8.4 and 9.1 installed.

My default when I say psql it connects to postgres 8.4

ie I set export PGCLUSTER=9.1/main

it connects to 9.1

Q.  How can I connect to 9.1 by default without having to set PGCLUSTER?

I looked in /etc/postgresql-common, there did not seem to be anything
obivious in there for which version to use.

Dave.
-- 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general