Re: [GENERAL] crosstab function - pivot - use column value as column description

pardon, correct link http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html pavel 2010/1/13 Pavel Stehule : > Hello > > 2010/1/12 Daniel Schuchardt : >> Hy, >> >> i'm looking for a solution to get this table dynamicaly in that resultset: >> >> SELECT r_dbrid, r_kategorie AS c

Re: [GENERAL] crosstab function - pivot - use column value as column description

Hello 2010/1/12 Daniel Schuchardt : > Hy, > > i'm looking for a solution to get this table dynamicaly in that resultset: > > SELECT r_dbrid, r_kategorie AS column_name, r_descr AS value FROM > recnokeyword WHERE r_dbrid=X >  r_dbrid  | r_kategorie |  r_descr > --+-+

Re: [GENERAL] Change Hard Disc Drive

On 13/01/2010 1:31 PM, Dave Page wrote: On Wed, Jan 13, 2010 at 10:57 AM, Craig Ringer wrote: Unfortunately, it doesn't. This means you need to alter the Windows service directly. What you need to do is stop the PostgreSQL service, then move your data directory (by default in C:\Program Files\

R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

The static binding worked fine in the second EXECUTE USING statement but not in the first one. I still think that it's weird more than wishful. I can work it around, though. Il giorno 12 gen, 2010 4:13 p., "Tom Lane" ha scritto: Vincenzo Romano writes: > I don't think so. Those variables shoul

Re: [GENERAL] Change Hard Disc Drive

On Wed, Jan 13, 2010 at 10:57 AM, Craig Ringer wrote: > Unfortunately, it doesn't. This means you need to alter the Windows service > directly. What you need to do is stop the PostgreSQL service, then move your > data directory (by default in C:\Program Files\PostgreSQL\version\data where > "versi

Re: [GENERAL] Change Hard Disc Drive

On 13/01/2010 11:03 AM, Yan Cheng Cheok wrote: Currently, I am installing PostgreSQL in C:\Program Files\PostgreSQL, with all default settings during installation. I realize my D drive has a much larger space and I would like PostgreSQL to store all the databases in D drive. How can I do so t

Re: [GENERAL] What is Cost 100 in stored procedure

On Tue, Jan 12, 2010 at 8:06 PM, Yan Cheng Cheok wrote: > May I know what is the meaning of Cost 100, at the end of stored procedure? > > > END;$BODY$ >  LANGUAGE 'plpgsql' VOLATILE >  COST 100; > ALTER FUNCTION create_tables() OWNER TO postgres; > > Thanks and Regards > Yan Cheng CHEOK > "

[GENERAL] What is Cost 100 in stored procedure

May I know what is the meaning of Cost 100, at the end of stored procedure? END;$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION create_tables() OWNER TO postgres; Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.o

[GENERAL] Change Hard Disc Drive

Currently, I am installing PostgreSQL in C:\Program Files\PostgreSQL, with all default settings during installation. I realize my D drive has a much larger space and I would like PostgreSQL to store all the databases in D drive. How can I do so through pgAdmin? Thanks and Regards Yan Cheng CHE

Re: [GENERAL] PSQL COMMAND LINE

On Tue, 2010-01-12 at 17:40 -0500, Amy Smith wrote: > when type \i c:\test.sqI - an error occurred - What is the *exact* error message? -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.or

[GENERAL] PSQL COMMAND LINE

all when type \i c:\test.sqI - an error occurred - no permission on file, but I have read/write access in c:\ any idea ? Amy

Re: [GENERAL] crosstab function - pivot - use column value as column description

See bottom. Am 12.01.2010 22:00, schrieb Merlin Moncure: On Tue, Jan 12, 2010 at 3:27 PM, Daniel Schuchardt wrote: Hy, i'm looking for a solution to get this table dynamicaly in that resultset: SELECT r_dbrid, r_kategorie AS column_name, r_descr AS value FROM recnokeyword WHERE r_dbrid=

Re: [GENERAL] WAL Log Shipping - Warm Standby not working under 8.3.7

On Jan 9, 2010, at 7:24 AM, Keaton Adams wrote: > Restored from last evening's backup from the master DB to make the rsync > across the network finish sooner. > >> > >> Shut down the PG instance on the slave machine > >> > >> Ran a script that does the following: > >> > >> select pg_start_backup

Re: [GENERAL] crosstab function - pivot - use column value as column description

On Tue, Jan 12, 2010 at 3:27 PM, Daniel Schuchardt wrote: > Hy, > > i'm looking for a solution to get this table dynamicaly in that resultset: > > SELECT r_dbrid, r_kategorie AS column_name, r_descr AS value FROM > recnokeyword WHERE r_dbrid=X >  r_dbrid  | r_kategorie |  r_descr > --+

[GENERAL] crosstab function - pivot - use column value as column description

Hy, i'm looking for a solution to get this table dynamicaly in that resultset: SELECT r_dbrid, r_kategorie AS column_name, r_descr AS value FROM recnokeyword WHERE r_dbrid=X r_dbrid | r_kategorie | r_descr --+-+ 14725737 |material | Alu Sorte1 14725737 |far

Re: [GENERAL] replication from multiple "master" servers to a single read-only slave

Thanks for note. I'm trying to avoid triggers based on past trauma from going into a client setup that uses a zillion of them and having to wait in order of seconds for a simple mod DML statement to execute :) I've been looking for an alternative that either sits above the database level (with

Re: [GENERAL] replication from multiple "master" servers to a single read-only slave

On Mon, Jan 11, 2010 at 04:18:30PM -0800, Omar Mehmood wrote: > (but I want to avoid using DB triggers). > I will check out Bucardo. Bucardo uses triggers just like Slony does. That said, it seems strange that you'd want to avoid them. Is there any particular reason you want to avoid them? Buc

Re: [GENERAL] annahensjerry has [OT]

2010/1/12 annahensjerry To: pgsql-general@postgresql.org > From: annahensjerry > > Hello, > (...) waiting for the love.I am Miss anna > This miss is going to love quite a bunch of fellows, isn't she? And this is good: > Weebly values your privacy. I guess they won't gain many fans of Weeb

[GENERAL] ChronicDB: Live database schema updates with zero downtime

Dear PostgreSQL Users, We would like to announce a new technology called ChronicDB[1] that aims to update databases (both their data and their schemas) while they are running live, without bringing the database down at all. The main focus of ChronicDB is to completely eliminate downtime in the pr

[GENERAL] Collate order on Mac OS X, text with diacritics in UTF-8

Hi, We are a software publisher searching for a new DBMS for our software. We have more than one hundred installed servers, running Mac OS and a Primebase database. We have spend some time evaluating PostgreSQL and we can't get correct outputs with the ORDER BY command. LC_COLLATE and LC_CTYPE

[GENERAL] annahensjerry has invited you to Weebly

Hello, I visited your profile and I find out that you'er the type my heart love, I believe we suit for real love, let walk that presious thing out for heart desire, I hope and believe we match for good. When contacting reply to my mail address (annhensje...@yahoo.co.uk) waiting for the love.I am Mi

Re: [GENERAL] Pre-calculate hash join

Dimitri Fontaine skrev 2010-01-12 12.01: war...@warrenandrachel.com writes: When joining two large tables [common in warehousing], a hash join is commonly selected. Calculating hash values for the merge phase is CPU intensive. Is there any way to pre-calculate value hashes to save that time?

Re: [GENERAL] Weird EXECUTE ... USING behaviour

Vincenzo Romano writes: > I don't think so. Those variables should be evaluated with the USING > *before* the actual execution. > Thus my statements only contain columns and constants. Unfortunately, that's just wishful thinking, not how EXECUTE USING actually works. rega

Re: [GENERAL] Weird EXECUTE ... USING behaviour

2010/1/12 Adrian Klaver : > On Tuesday 12 January 2010 6:24:13 am Vincenzo Romano wrote: >> 2010/1/12 Tom Lane : >> > Vincenzo Romano writes: >> >> In a PL/PgSQL function I have the following: >> >> >> >>             execute $l2$ >> >>               alter table $l2$||ct||$l2$ add check( >> >>

Re: [GENERAL] Weird EXECUTE ... USING behaviour

On Tuesday 12 January 2010 6:24:13 am Vincenzo Romano wrote: > 2010/1/12 Tom Lane : > > Vincenzo Romano writes: > >> In a PL/PgSQL function I have the following: > >> > >>             execute $l2$ > >>               alter table $l2$||ct||$l2$ add check( > >> data>=$1::timestamp and data<$2::t

Re: [GENERAL] need help with query, how to fold select result to array?

On Tue, Jan 12, 2010 at 04:06:20PM +0200, Sergey Levchenko wrote: > but I have to fold it in multi-dimensional, array like {{71629130, > 15518, 0}, {2668722, 616, 0}} ? > but it's not possible to pass more then one argument to the array_agg > function :/ Tuples work fine. Multidimensional arrays

Re: [GENERAL] How to find column type

take a look at information_schema.* table. -- 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 find column type

I'm looking for a way to create function which returns column type as single character: C = char/text/varchar, N=numeric, L=bool, others, U=unknown from expression in form 'tablename.columnname', where tablename if table from current search_path tables. search_path has two schemas. First

Re: [GENERAL] need help with query, how to fold select result to array?

CREATE TYPE varchararray AS (f varchar[]); SELECT office_id, serial, commit_date, service_id, array_agg(DISTINCT ROW(ARRAY(SELECT meter_id::varchar UNION ALL SELECT organization_reading::varchar UNION ALL SELECT reading::varchar))::varchararray) FROM meter_readings WHERE office_id

Re: [GENERAL] Weird EXECUTE ... USING behaviour

2010/1/12 Tom Lane : > Vincenzo Romano writes: >> In a PL/PgSQL function I have the following: >> >>             execute $l2$ >>               alter table $l2$||ct||$l2$ add check( >> data>=$1::timestamp and data<$2::timestamp and maga=$3 ) >>             $l2$ using rec.d0,rec.d1,rec.maga; >>

Re: [GENERAL] Weird EXECUTE ... USING behaviour

Vincenzo Romano writes: > In a PL/PgSQL function I have the following: > > execute $l2$ > alter table $l2$||ct||$l2$ add check( > data>=$1::timestamp and data<$2::timestamp and maga=$3 ) > $l2$ using rec.d0,rec.d1,rec.maga; > > which yields to this e

Re: [GENERAL] need help with query, how to fold select result to array?

but I have to fold it in multi-dimensional, array like {{71629130, 15518, 0}, {2668722, 616, 0}} ? but it's not possible to pass more then one argument to the array_agg function :/ 2010/1/12 A. Kretschmer : > In response to Sergey Levchenko : >> eps=# SELECT office_id, serial, commit_date, service

[GENERAL] Weird EXECUTE ... USING behaviour

In a PL/PgSQL function I have the following: execute $l2$ alter table $l2$||ct||$l2$ add check( data>=$1::timestamp and data<$2::timestamp and maga=$3 ) $l2$ using rec.d0,rec.d1,rec.maga; which yields to this error messsge: ERROR: there is no p

Re: [GENERAL] need help with query, how to fold select result to array?

In response to Sergey Levchenko : > eps=# SELECT office_id, serial, commit_date, service_id, meter_id, > organization_reading, reading FROM meter_readings WHERE office_id = > 134 AND serial = 27 AND commit_date = '2010-01-11' AND commit_time = > '13:44:37' AND person_id = 300871; > > office_id |

[GENERAL] need help with query, how to fold select result to array?

eps=# SELECT office_id, serial, commit_date, service_id, meter_id, organization_reading, reading FROM meter_readings WHERE office_id = 134 AND serial = 27 AND commit_date = '2010-01-11' AND commit_time = '13:44:37' AND person_id = 300871; office_id | serial | commit_date | service_id | meter_id |

Re: [GENERAL] properties/info on functions

In response to Huda Booley (h...@careerjunction.co.za) : > Hi > > > > We?re running a 8.3 pg database. Is there a pg_stat table I can query or some > way to find out what date / datetime a function was created or updated? No. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150,

[GENERAL] properties/info on functions

Hi We're running a 8.3 pg database. Is there a pg_stat table I can query or some way to find out what date / datetime a function was created or updated? Thank you Huda Booley DBA | CareerJunction | Better jobs. More often. Web: www.careerjunction.co.za |Email: h...@careerjunction.co.za

Re: [GENERAL] replication from multiple "master" servers to a single read-only slave

Omar Mehmood writes: > I really don't want to use separate schemas for each master to > logically partition the data. I ensure that the data on each master > will not clash with each other (in terms of any DB level contraints > such as PK), so I'd much prefer they all reside in a single schema. >

Re: [GENERAL] Pre-calculate hash join

war...@warrenandrachel.com writes: > When joining two large tables [common in warehousing], a hash join is > commonly selected. Calculating hash values for the merge phase is CPU > intensive. Is there any way to pre-calculate value hashes to save that > time? Would it even grant any performance to

Re: [GENERAL] Backup strategies with significant bytea data

Leigh Dyer wrote: Hi, For years now I've simply backed up my databases by doing a nightly pg_dump, but since we added the ability for users to import binary files in to our application, which are stored in a bytea fields, the dump sizes have gone through the roof — even with gzip compression, th