[GENERAL] Compiling trigger function with MinGW

Hello All. Now I try to link dll with MinGW from Example in Postgres Help. Linker show me this error: D:\users\anthony\kursor\abzcrm\c\foo>gcc -shared foo.o -o foo.dll -L "d:/files/local/PostgreSQL/8.3/lib" -l postgres Cannot export ⌂postgres_NULL_THUNK_DATA: symbol not found collect2: ld return

[GENERAL] change encoding

Hello, I have installed a new postgresql server and I need to recover an old database on it. The database I have to recover has LATIN2 as encoding and when i try to restore it into the new one I get error messages due to encoding. I have read that from pgsql8-3 is not possible to have differe

Re: [GENERAL] change encoding

On Tue, May 13, 2008 at 09:00:49AM +0200, J. Manuel Velasco - UBILIBET wrote: > Hello, > > I have installed a new postgresql server and I need to recover an old > database on it. > The database I have to recover has LATIN2 as encoding and when i try to > restore it into the new one I get error m

Re: [GENERAL] Compiling trigger function with MinGW

Hi. One of question... Was 8.3 of a main part built for yourself(MinGW)? When it is official pginstaller, you should use it by MS-VC+. Regards, Hiroshi Saito - Original Message - From: "Anton Burkun" <[EMAIL PROTECTED]> Hello All. Now I try to link dll with MinGW from Example in P

[GENERAL] Upgreade 8.3.0 to 8.3.1 on windows: insufficient privileges to install system service

Hello ! I obtain an error message when I launch the upgrade.bat on windows 2000 server using an administrator account (the one used to install postgresql 8.3.0): insufficient privileges to install a system service. I log in with VNC, and I have the same message using Terminal server. Is this a p

Re: [GENERAL] Server not listening

On Mon, May 12, 2008 at 6:53 PM, D Galen <[EMAIL PROTECTED]> wrote: > If this isn't the right place to post this, please advise. > > I've spent a week trying to get PostgreSQL 8.3 to install correctly on > WIN2K. Server will load & I see the server processes loaded but none of > them have any ope

[GENERAL] Stripping out slony after / before / during pg_restore?

Hi people, I'm setting us up a separate staging / test server and I want to read in a pg_dump of our current origin stripping out all the slony stuff. I was thinking this could serve two purposes a) test out backups restore properly and b) provide us with us with the staging / test server What

Re: [GENERAL] Stripping out slony after / before / during pg_restore?

Glyn Astill wrote: Hi people, I'm setting us up a separate staging / test server and I want to read in a pg_dump of our current origin stripping out all the slony stuff. I was thinking this could serve two purposes a) test out backups restore properly and b) provide us with us with the staging

Re: [GENERAL] Stripping out slony after / before / during pg_restore?

On Tue, May 13, 2008 at 5:42 PM, Glyn Astill <[EMAIL PROTECTED]> wrote: > Hi people, > > I'm setting us up a separate staging / test server and I want to read in a > pg_dump of our current origin stripping out all the slony stuff. > > I was thinking this could serve two purposes a) test out backup

Re: [GENERAL] pg_standby / WAL archive-restore through system restarts

David Wall a écrit : [...] During regular operations, Server A and Server B may independently need to be rebooted or postgresql restarted, etc. Is there any sync-ing between Server A and Server B I have to worry about when doing this? That is, will Server B need to know anything about the fact

Re: [GENERAL] Upgreade 8.3.0 to 8.3.1 on windows: insufficient privileges to install system service

Kev wrote: > I had a different problem (initdb errors) where the only difference > was whether I was on Terminal Services or not. Running TS using > "mstsc.exe /console" (even within a session) worked for me. This is why I also tried with VNC which doesn't face this problem. -- Sent via pgsq

Re: [GENERAL] Is this a bug? (changing sequences in default value)

En un mensaje anterior, Merlin Moncure escribió: [...] > >> > Am I doing something wrong? > >> > >> yes and no when you created the table initially you probably made it > >> a 'serial' column which set up the ownership that prevents the drop > >> operation. that ownership did not go away when you

Re: [GENERAL] Stripping out slony after / before / during pg_restore?

Thanks guys, that's exactly what I wanted. - Original Message > From: Richard Huxton <[EMAIL PROTECTED]> > To: Glyn Astill <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org > Sent: Tuesday, 13 May, 2008 1:34:18 PM > Subject: Re: [GENERAL] Stripping out slony after

[GENERAL] DB Connections in TIME_WAIT state

Hi, I'm using the Pg perl module to connect to Postgresql 8.1 via localhost (127.0.0.1) from webscripts. I'm noticing a lot of TIME_WAIT socket connections: tcp0 0 127.0.0.1:39291 127.0.0.1:5432 TIME_WAIT tcp0 0 127.0.0.1:60720 127.0.0.1:5432

[GENERAL] Substring Problem

Hi there, it seems to work with 8.1, but not anymore with 8.3. What is wrong with this substring request? Or is it some installation issue? Thanks for any suggestion! SELECT substring(date from 1 for 7) AS stryearmonth, COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP

Re: [GENERAL] Substring Problem

On Tue, May 13, 2008 at 03:26:57PM +0200, Stefan Schwarzer wrote: > it seems to work with 8.1, but not anymore with 8.3. What is wrong > with this substring request? Or is it some installation issue? Thanks > for any suggestion! > SELECT substring(date from 1 for 7) AS stryearmonth, COUNT(id) A

Re: [GENERAL] Substring Problem

On Tue, May 13, 2008 at 03:26:57PM +0200, Stefan Schwarzer wrote: > SELECT substring(date from 1 for 7) AS stryearmonth, COUNT(id) AS > countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY > stryearmonth ORDER BY stryearmonth ASC Another way of doing this is by using date_trunc, i.e.:

Re: [GENERAL] Substring Problem

Type casting is required since 8.3, trySELECT substring(date :: varchar from 1 for 7) AS stryearmonth, COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY stryearmonth ORDER BY stryearmonth ASCBye...LudwigHi there, it seems to work with 8.1, but not anymore with 8.3. What is

[GENERAL] PG -v- MySQL

Hi: I work for a large corporation that uses many different databases. I used to use Oracle but then moved away, mainly because of the expense and complexity. I wanted to stay on Linux, so I shy'ed away from SQL-Server. Many others around here use MySQL, but I decided to go with Postgres bec

Re: [GENERAL] no privileges were granted

Volkan YAZICI <[EMAIL PROTECTED]> writes: > test_1_5_0_0=> GRANT EXECUTE ON FUNCTION > emove.emove_outgoingmessages_delete_for_commsrv() TO emove; > WARNING: no privileges were granted for > "emove_outgoingmessages_delete_for_commsrv" > GRANT > Does anybody have an idea about what might be caus

Re: [GENERAL] DB Connections in TIME_WAIT state

John Gateley <[EMAIL PROTECTED]> writes: > Hi, I'm using the Pg perl module to connect to Postgresql 8.1 > via localhost (127.0.0.1) from webscripts. I'm noticing a lot > of TIME_WAIT socket connections: > tcp0 0 127.0.0.1:39291 127.0.0.1:5432 TIME_WAIT > > tcp

Re: [GENERAL] Making sure \timing is on

Alvaro Herrera <[EMAIL PROTECTED]> writes: > David Fetter escribió: >> Thanks for the heads-up :) >> >> Second patch attached, this time with some docs. > Added to July commitfest. Surely this is merely proof of concept and not a complete patch. regards, tom lane -- Se

Re: [GENERAL] PG -v- MySQL

Gauthier, Dave wrote: Hi: I’ve been invited to participate in a forum which discusses technical issues for relational DBs used in the corporation. The 3 DBs they are discussing are Oracle, SQL-Server and MySQL. I’d like to introduce PG, but want to be able to address the “Why not use MySQL

Re: [GENERAL] Substring Problem

[EMAIL PROTECTED] wrote: Type casting is required since 8.3, try SELECT substring(date :: varchar from 1 for 7) AS stryearmonth, ^^ sorry but this hurts and should not recommended. I think depesz approach with to_string() and the correct format string is the better so

[GENERAL] Affected # of Rows After TRIGGER/RULE Return

Hi, I'm trying to fake DELETEs to a table using below methods: CREATE OR REPLACE FUNCTION mobileunit_fake_delete() RETURNS trigger AS $$ BEGIN UPDATE mobileunit SET networkid = OLD.networkid + OLD.muid * 1000, groupid = 146688, plate = 'DELETED

Re: [GENERAL] Making sure \timing is on

On Tue, May 13, 2008 at 10:47:40AM -0400, Alvaro Herrera wrote: > Tom Lane escribió: > > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > > David Fetter escribi?: > > >> Thanks for the heads-up :) > > >> > > >> Second patch attached, this time with some docs. > > > > > Added to July commitfest. > >

Re: [GENERAL] Making sure \timing is on

On Tue, May 13, 2008 at 08:14:51AM -0700, David Fetter wrote: > On Tue, May 13, 2008 at 10:47:40AM -0400, Alvaro Herrera wrote: > > Tom Lane escribió: > > > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > > > David Fetter escribi?: > > > >> Thanks for the heads-up :) > > > >> > > > >> Second patch

Re: [GENERAL] Is this a bug? (changing sequences in default value)

On Tue, May 13, 2008 at 8:50 AM, Fernando Schapachnik <[EMAIL PROTECTED]> wrote: > > > ALTER SEQUENCE name [ INCREMENT [ BY ] increment ] > > >[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO > > > MAXVALUE ] > > >[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] >

Re: [GENERAL] Stripping out slony after / before / during pg_restore?

Here's how you do it on restore step from a pg_dump in -Fc format. pg_restore -l dumpfile > list edit the file "list" to remove references to slony objects pg_restore -L list dumpfile -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: ht

Re: [GENERAL] pg_standby / WAL archive-restore through system restarts

Thanks, Guillaume. Can anyone confirm his answer to the question below about restarting the backup postmaster? It seems counter-intuitive since the backup server is only waiting for new WAL files to restore, it would seem it could be killed and restarted at any time and it should just recover

Re: [GENERAL] rounding problems

Tomasz Ostrowski wrote: On 2008-05-12 20:49, Justin wrote: We take (List Price * discount Percent) * Number of Pieces = net price. This is wrong. You should do in Excel: ( price * amount ) * discount As otherwise any small error in representation of price*discount would be multiplied

Re: [GENERAL] pg_standby / WAL archive-restore through system restarts

someone wrote: >>> Can I shutdown Server B (backup/recovery postmaster) simply by >>> killing the postmaster and restart it back in recovery mode to >>> continue re-syncing where it left off? Or does stopping Server B >>> while in recovery mode require any manual re-sync steps before it can

Re: [GENERAL] Making sure \timing is on

David Fetter escribió: > Thanks for the heads-up :) > > Second patch attached, this time with some docs. Added to July commitfest. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via p

Re: [GENERAL] Making sure \timing is on

Tom Lane escribió: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > David Fetter escribi�: > >> Thanks for the heads-up :) > >> > >> Second patch attached, this time with some docs. > > > Added to July commitfest. > > Surely this is merely proof of concept and not a complete patch. David, ya he

Re: [GENERAL] Making sure \timing is on

David Fetter <[EMAIL PROTECTED]> writes: >>> Surely this is merely proof of concept and not a complete patch. >> >> Next patch attached :) Uh, my point was that the agreement was to do this to *all* of psql's toggling backslash commands, not only \timing. regards, tom lan

[GENERAL] multicolumn index join

I have to do a multicolumn self-join to find the median. I am using postgres 8.2. How do I force postgres to use an index scan? I have a multicolumn index but postgres is not using it. Query explain select e.time from page_view e, page_view d where e.test_

Re: [GENERAL] PG -v- MySQL

On Tue, May 13, 2008 at 10:39 AM, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > > I've been invited to participate in a forum which discusses technical > issues for relational DBs used in the corporation. The 3 DBs they are > discussing are Oracle, SQL-Server and MySQL. I'd like to introduce PG, b

Re: [GENERAL] PG -v- MySQL

Le mardi 13 mai 2008, Joshua D. Drake a écrit : > I suggest you take a look at: > > http://www.scribd.com/doc/2575733/The-future-of-MySQL-The-Project > http://sql-info.de/mysql/gotchas.html > http://www.bytebot.net/blog/archives/2008/04/17/what-mysql-can-learn-from-p >ostgresql http://www.commandpr

[GENERAL] Conditional on Select List

Is it possible to do this? SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table; What I want is to return a boolean, but when I tried SELECT COUNT(colname)::BOOLEAN FROM table; it says it cannot cast bigint to boolean. Is there such IF function or do I have to create my own. Tha

Re: [GENERAL] pg_standby / WAL archive-restore through system restarts

David Wall <[EMAIL PROTECTED]> writes: > Thanks, Guillaume. Can anyone confirm his answer to the question below > about restarting the backup postmaster? IIRC, it works conveniently in 8.2 and up. Just do pg_ctl stop -m fast. regards, tom lane -- Sent via pgsql-gener

Re: [GENERAL] Conditional on Select List

Fernando, the "IF" function is called CASE WHEN condition THEN result [WHEN ...] [ELSE result] END read about it at http://www.postgresql.org/docs/8.3/static/functions-conditional.html best wishes, Harald On Tue, May 13, 2008 at 5:52 PM, Fernando <[EMAIL PROTECTED]> wrote: > >

Re: [GENERAL] Conditional on Select List

Fernando wrote: Is it possible to do this? SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table; What I want is to return a boolean, but when I tried SELECT COUNT(colname)::BOOLEAN FROM table; it says it cannot cast bigint to boolean. Is there such IF function or do I have to cr

Re: [GENERAL] Conditional on Select List

On Tue, May 13, 2008 at 11:52:24AM -0400, Fernando wrote: > Is it possible to do this? > > SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table; > > What I want is to return a boolean, but when I tried SELECT > COUNT(colname)::BOOLEAN FROM table; it says it cannot cast bigint to > b

Re: [GENERAL] Conditional on Select List

> > Is it possible to do this? > > SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table; > > What I want is to return a boolean, but when I tried SELECT > COUNT(colname)::BOOLEAN FROM table; it says it cannot cast bigint to > boolean. > How about this? Logic al expresses are alrea

Re: [GENERAL] Conditional on Select List

Fernando <[EMAIL PROTECTED]> writes: > Is it possible to do this? > SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table; SELECT COUNT(colname) > 0 AS colname FROM table; If you really like to type, you could use a CASE expression. regards, tom lane -- Sent

Re: [GENERAL] PG -v- MySQL

At 10:39a -0400 on Tue, 13 May 2008, Dave Gauthier wrote: > I’d like to introduce PG, > but want to be able to address the "Why not use MySQL" questions when > they arise. I know in the past there have been many comparisons between > the 2 DBs but would like to know if there are any good, recent o

Re: [GENERAL] Conditional on Select List

On Tue, May 13, 2008 at 11:52 AM, Fernando <[EMAIL PROTECTED]> wrote: > > Is it possible to do this? > > SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table; You should be able to use CASE for this. -Doug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To ma

Re: [GENERAL] Conditional on Select List

On Tue, May 13, 2008 at 8:52 AM, Fernando <[EMAIL PROTECTED]> wrote: > SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table; A rewrite of this query might do what you want: SELECT colname_cnt > 0 AS Greaterthanzero FROM ( SELECT COUNT( colname ) AS colname_cnt FROM Table

[GENERAL] too many warnings

Hi everybody i get a lot of warnings when i try to execute a postgresql from a script the warrning is HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. WARNING: nonstandard use of escape in a string literal LINE 1: ...9.table.viewentry_general_old.txt' WITH DELIMITER '\ '; DROP...

Re: [GENERAL] rounding problems

2008/5/13 Justin <[EMAIL PROTECTED]>: > Your saying in Excel, Multiplication is not Commutativity??? that sends > shudders down my back The word you want in this case is "associative". Since floating point math is not exact, sometimes the associativity (and other) properties of some operati

Re: [GENERAL] pg_standby / WAL archive-restore through system restarts

Thanks, Guillaume. Can anyone confirm his answer to the question below about restarting the backup postmaster? IIRC, it works conveniently in 8.2 and up. Just do pg_ctl stop -m fast. Thanks, Tom. What about if the postmaster is just killed (kill pid, or kill -9 pid) or the serve

Re: [GENERAL] PG -v- MySQL

On Tue, May 13, 2008 at 9:42 AM, Merlin Moncure <[EMAIL PROTECTED]> wrote: > Here are some other things we have v. mysql: > *) Transactional DDL: much safer and easier to roll out changes to 24/7 > systems It's also useful for lots and lots of other things. For instance, let's say you had an ind

Re: [GENERAL] Is this a bug? (changing sequences in default value)

En un mensaje anterior, Merlin Moncure escribió: > On Tue, May 13, 2008 at 8:50 AM, Fernando Schapachnik > <[EMAIL PROTECTED]> wrote: > > > > ALTER SEQUENCE name [ INCREMENT [ BY ] increment ] > > > >[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO > > > > MAXVALUE ] > > > >

Re: [GENERAL] PG -v- MySQL

Scott Marlowe wrote: On Tue, May 13, 2008 at 9:42 AM, Merlin Moncure <[EMAIL PROTECTED]> wrote: Here are some other things we have v. mysql: *) Transactional DDL: much safer and easier to roll out changes to 24/7 systems And I totally agree with everything you said Merlin. Especially about t

Re: [GENERAL] pg_standby / WAL archive-restore through system restarts

David Wall <[EMAIL PROTECTED]> writes: >> IIRC, it works conveniently in 8.2 and up. Just do pg_ctl stop -m fast. > Thanks, Tom. What about if the postmaster is just killed (kill pid, or > kill -9 pid) or the server/OS crashes? Will PG 8.3 in recovery mode be > able to come back up okay and r

Re: [GENERAL] too many warnings

"Pau Marc Munoz Torres" <[EMAIL PROTECTED]> writes: > i get a lot of warnings when i try to execute a postgresql from a script > the warrning is > HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. set escape_string_warning = off; or possibly set standard_conforming_strings = on; de

Re: [GENERAL] change encoding

Hello, Thanks for your quick replay, but I had other thing to do and I haven't could check your hint before. I started a new postgresql-8-3 installation. Set it up, ok I have the idea to create the database and the role and then load the pg_dump result from the other database. The old databs

Re: [GENERAL] Conditional on Select List

Thanks this is exactly what I need it. Fernando Tom Lane wrote: Fernando <[EMAIL PROTECTED]> writes: Is it possible to do this? SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table; SELECT COUNT(colname) > 0 AS colname FROM table; If you really like to type, you could us

[GENERAL] how can i get initdb

Hello, I read that with initdb command I can specify the server encoding, but with my installation I don't hae initdb command. How can I install it? Please Thanks. PS: I installed postgresql-8.3 debian package and it's respectives dependencies. --

Re: [GENERAL] pg_standby / WAL archive-restore through system restarts

Alvaro Herrera a écrit : someone wrote: Can I shutdown Server B (backup/recovery postmaster) simply by killing the postmaster and restart it back in recovery mode to continue re-syncing where it left off? Or does stopping Server B while in recovery mode require any manual re-sync steps be

Re: [GENERAL] multicolumn index join

On Tue, May 13, 2008 at 9:27 AM, Stephen Ince <[EMAIL PROTECTED]> wrote: > I have to do a multicolumn self-join to find the median. I am using postgres > 8.2. How do I force postgres to use an index scan? I have a multicolumn > index but postgres is not using it. While it's quite acceptable to f

Re: [GENERAL] how can i get initdb

Is it possible you dont have it in your path? try /initdb on my system /usr/local/pgsql/bin/initdb- Original Message From: J. Manuel Velasco - UBILIBET <[EMAIL PROTECTED]>To: pgsql-general@postgresql.orgSent: Tuesday, 13 May, 2008 4:42:37 PMSubject: [GENERAL] how can i get initdb Hello,

Re: [GENERAL] how can i get initdb

J. Manuel Velasco - UBILIBET wrote: Hello, I read that with initdb command I can specify the server encoding, but with my installation I don't hae initdb command. How can I install it? Please Thanks. PS: I installed postgresql-8.3 debian package and it's respectives dependencies. -- initd

Re: [GENERAL] rounding problems

Thanks to Andy's C code here is the pl/pgSQL function which does the same thing. I added a tiny bit so instead of returning a numeric value with 20 trailing zeros it returns a value with the desired precision. -Begin Code - create or replace function ro

Re: [GENERAL] how can i get initdb

found it, thanks a lot tomorrow i will try set the encoding with initdb adn try to restore the database thanks a have a good end of the day ! salman escribió: J. Manuel Velasco - UBILIBET wrote: Hello, I read that with initdb command I can specify the server encoding, but with

Re: [GENERAL] rounding problems

Tomasz Ostrowski wrote: On 2008-05-13 17:29, Justin wrote: Tomasz Ostrowski wrote: On 2008-05-12 20:49, Justin wrote: We take (List Price * discount Percent) * Number of Pieces = net price. This is wrong. You should do in Excel: ( price * amount ) * discount

Re: [GENERAL] pg_standby / WAL archive-restore through system restarts

On Tuesday 13 May 2008 12:48:38 Guillaume Lelarge wrote: > Alvaro Herrera a écrit : > > someone wrote: > Can I shutdown Server B (backup/recovery postmaster) simply by > killing the postmaster and restart it back in recovery mode to > continue re-syncing where it left off? Or does s

[GENERAL] C function

I wrote a C function that will connect to oracle and do insert. then I created a trigger that calls this C function for any insets/updates on TABLE A when there is a insert in table A , the C function is not called at all, any reason why ? -- http://iavian.com -- Sent via pgsql-general maili

Re: [GENERAL] PG -v- MySQL

On Tuesday 13 May 2008 12:09:28 Kevin Hunter wrote: > * The documentation on the website for Postgres is by far the better of > the two DBs. MySQL's is decent and very useful, but Postgres' > documentation just plain rocks. In fact, the Postgres documentation is > one of the very best examples of

Re: [GENERAL] rounding problems

Double holds 15 places which is the highest value of precision it can maintain before rounding occurs. Is is limit less no, but what is? Practically speaking taking a vale 0.000,000,000,000,001 aka 1 trillionth of anything, i view the problem solved for 98% of problems. Does it hide? Yes,

Re: [GENERAL] change encoding

On Tue, May 13, 2008 at 05:37:27PM +0200, J. Manuel Velasco - UBILIBET wrote: > I have the idea to create the database and the role and then load the > pg_dump result from the other database. The old databse has encoding > LATIN2 so I try to create with this encoding and I get the message(*): > E

[GENERAL] Totally unremovable dead rows?

Hi, I'm running OpenNMS on a machine that was misconfigured and our event database climbed to 2.8 million rows. I've gone in and archived that vast majority out to files on disk and now the table has about 20-30k rows. When I do a "vacuum full", though, it says that the vast majority of th

Re: [GENERAL] how can i get initdb

On Tue, May 13, 2008 at 06:05:48PM +0200, J. Manuel Velasco - UBILIBET wrote: > found it, thanks a lot > > tomorrow i will try set the encoding with initdb adn try to restore the > database If you're using debian then check out pg_dropcluster and pg_createcluster commands. Have a nice day, --

Re: [GENERAL] Totally unremovable dead rows?

Derek Chen-Becker <[EMAIL PROTECTED]> writes: > I'm running OpenNMS on a machine that was misconfigured and our > event database climbed to 2.8 million rows. I've gone in and archived > that vast majority out to files on disk and now the table has about > 20-30k rows. When I do a "vacuum fu

Re: [GENERAL] Making sure \timing is on

On Tue, May 13, 2008 at 11:36:57AM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > >>> Surely this is merely proof of concept and not a complete patch. > >> > >> Next patch attached :) > > Uh, my point was that the agreement was to do this to *all* of > psql's toggling backsla

Re: [GENERAL] Making sure \timing is on

On Tue, May 13, 2008 at 01:53:33PM -0700, David Fetter wrote: > On Tue, May 13, 2008 at 11:36:57AM -0400, Tom Lane wrote: > > David Fetter <[EMAIL PROTECTED]> writes: > > >>> Surely this is merely proof of concept and not a complete patch. > > >> > > >> Next patch attached :) > > > > Uh, my point

Re: [GENERAL] PG -v- MySQL

[EMAIL PROTECTED] ("Joshua D. Drake") writes: > Gauthier, Dave wrote: >> I’ve been invited to participate in a forum which discusses >> technical issues for relational DBs used in the corporation. The 3 >> DBs they are discussing are Oracle, SQL-Server and MySQL. I’d like >> to introduce PG, but

[GENERAL] Alias in the HAVING clause

I am in the middle of switching a bunch of queries over from MySQL to PostgreSQL and have hit a little snag. The following query works fine in MySQL but raises an error in postgres: SELECT COUNT(*), id % 3 AS f1 FROM table GROUP BY f1 HAVING f1 <> 0; It seems that Postgres does not recognize the

Re: [GENERAL] Alias in the HAVING clause

Oops, that second query should be SELECT COUNT(*), id % 3 AS f1 FROM table GROUP BY f1 HAVING id % 3 <> 0; On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher <[EMAIL PROTECTED]> wrote: > I am in the middle of switching a bunch of queries over from MySQL to > PostgreSQL and have hit a little snag.

Re: [GENERAL] Alias in the HAVING clause

On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher <[EMAIL PROTECTED]> wrote: > I am in the middle of switching a bunch of queries over from MySQL to > PostgreSQL and have hit a little snag. The following query works fine > in MySQL but raises an error in postgres: > > SELECT COUNT(*), id % 3 AS f1 F

Re: [GENERAL] Alias in the HAVING clause

On Tue, May 13, 2008 at 4:18 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher <[EMAIL PROTECTED]> wrote: >> I am in the middle of switching a bunch of queries over from MySQL to >> PostgreSQL and have hit a little snag. The following query works fine >

[GENERAL] WKT Viewer

Hi I am seeking a simple viewer that will display WKT geometric information housed in a Postgris of Postgresql database without it needing extensions, srid, projectons or any other geographical attributes. A simple database geometry viewer. If anyone knows of such a device I would appreciat

Re: [GENERAL] Alias in the HAVING clause

"Scott Marlowe" <[EMAIL PROTECTED]> writes: > On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher <[EMAIL PROTECTED]> wrote: >> I am in the middle of switching a bunch of queries over from MySQL to >> PostgreSQL and have hit a little snag. The following query works fine >> in MySQL but raises an error

Re: [GENERAL] Alias in the HAVING clause

On Tue, May 13, 2008 at 5:43 PM, Nathan Thatcher <[EMAIL PROTECTED]> wrote: > Is this the correct way to do this, or is there a better way / a way > to get PostgreSQL to recognize an alias in the HAVING clause? As Tom pointed out, f1's not in scope for the HAVING clause. If you're that concerned a

Re: [GENERAL] Unloading a table consistently

On Sat, 2008-05-03 at 09:11 -0700, Christophe wrote: > I will have a log table which, once a day or so, is copied to a file > (for movement to a data warehouse), and the log table emptied. ... > Is there a better way to handle this kind of thing that I'm > missing? Anyone care to comment

Re: [GENERAL] Making sure \timing is on

David Fetter <[EMAIL PROTECTED]> writes: > On Tue, May 13, 2008 at 11:36:57AM -0400, Tom Lane wrote: >>> Uh, my point was that the agreement was to do this to *all* of >>> psql's toggling backslash commands, not only \timing. >> >> Done :) Hmm, I thought we had a lot more than three that were lik

Re: [GENERAL] Alias in the HAVING clause

Thank you all for the insight. PG is obviously my first choice (that is why I am switching)... the hope is to do so without having to change everything. Thanks for the solution David - it did the trick. Nate On Tue, May 13, 2008 at 5:01 PM, David Wilson <[EMAIL PROTECTED]> wrote: > On Tue, May 13

Re: [GENERAL] Making sure \timing is on

Tom Lane escribió: > Actually, \a and \H are fairly bogus anyway, because they are "toggling" > a setting that has more than two values. I wonder whether defining the > argument as a boolean is really very sane. Perhaps it would be better to > take the argument if given as just a regular format

[GENERAL] Couple of question on functions

Hello, Reading the manual recently I came across this: ( http://www.postgresql.org/docs/8.3/interactive/xfunc-volatility.html ) > Because of the snapshotting behavior of MVCC (see Chapter > 13) a function containing only SELECT commands ca

Re: [GENERAL] Couple of question on functions

"Vyacheslav Kalinin" <[EMAIL PROTECTED]> writes: > Reading the manual recently I came across this: ( > http://www.postgresql.org/docs/8.3/interactive/xfunc-volatility.html ) >> Because of the snapshotting behavior of MVCC (see Chapter >> 13

Re: [GENERAL] Strange behaviour - performance decreases after each TRUNCATE

IVO GELOV wrote: > Hallow. > My name is IVO GELOV. Please excuse my English, it is not my primary language. > I've started to learn PostgreSQL with version 8.1, doing 2 upgrades - to > 8.2.4 and now 8.3.1 > I consider myself still a novice. Currently I'm porting our small warehouse > application

Re: [GENERAL] Substring Problem

COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY stryearmonth ORDER BY stryearmonth ASC btw, whats the reason for the subselect? Oh, right, looks a bit stupid like this. It's eventually being filled with something more useful, upon the user's request. It's dynamica

Re: [GENERAL] Couple of question on functions

> You might think it's "likely", but in fact no inlining will happen > because you have a volatile argument that's used more than once inside > the function body. Inlining would change the results. Yeah, right, apparently I overestimated chances of inilining, thanks for the hint, Tom. In fact in

Re: [GENERAL] Affected # of Rows After TRIGGER/RULE Return

On Wednesday 14 May 2008 02:59:27 Volkan YAZICI wrote: > RETURN NULL; > queries return 0 as # of affacted rows. > And this causes JDBC > applications (specifically Hibernate) ROLLBACK query as some failure > occured. At least, shouldn't the latter one return 1 as # of affected > rows? Any