Re: [GENERAL] Any good report/form generators for postgresql?

2004-11-24 Thread Chris Green
On Wed, Nov 24, 2004 at 08:55:35AM +0100, Alberto Cabello Sanchez wrote: On Sun, Nov 21, 2004 at 05:50:30PM +, Chris Green wrote: Has anyone got any recommendations for forms/reports generators for postgresql? I can probably get the forms I want from Rekall so the bigger requirement is

[GENERAL] Creating index on a view?

2004-11-24 Thread Net Virtual Mailing Lists
Hello, First, let me apologize for my flurry of emails as of late... I'm working on something which seems particularly difficult (at least to me)... My question is regarding creating an index on a view, or perhaps another way to accomplish this. For example: CREATE TABLE table1 ( table1_id

Re: [GENERAL] I need to add a column to a table

2004-11-24 Thread Richard Huxton
Krause, Lewis wrote: I know there is a command to add the column. I want to back up the table before I alter it. What is the best way. It has a couple indexes and a trigger. pg_dump -t table-name ... -- Richard Huxton Archonet Ltd ---(end of

Re: [GENERAL] VACUUM and ANALYZE With Empty Tables

2004-11-24 Thread Richard Huxton
Mark Dexter wrote: We use a development environment that works with Postgres via ODBC and uses cursors to insert and update rows in Postgres tables. I'm using Postgres version 7.4.5. A. If I TRUNCATE or DELETE all of the rows in the table and then run VACUUM or ANALYZE on the empty table, the

Re: [GENERAL] Upcoming Changes to News Server ...

2004-11-24 Thread Martijn van Oosterhout
On Tue, Nov 23, 2004 at 09:07:04PM -0500, Gary L. Burnore wrote: We've removed all of the comp.databases.postgres.* groups from our server and our feeds anyway. Do did google. So will anyone else who's still holding the bogus groups. Basically, the thing that Marc is doing that's 'bad',

[GENERAL] Row creation time

2004-11-24 Thread Najib Abi Fadel
Is there a to get the row creation time if we know it's object ID ?? Thx

Re: [GENERAL] Creating index on a view?

2004-11-24 Thread Peter Eisentraut
Net Virtual Mailing Lists wrote: My question is regarding creating an index on a view, or perhaps another way to accomplish this. Views are just macro expansions of queries (in a manner of speaking). To make queries on views use indexes, you create the indexes on the underlying tables in the

Re: [GENERAL] Copying into Unicode - Correcting Errors

2004-11-24 Thread Peter Eisentraut
Hunter Hillegas wrote: I need to import a file into a Unicode database. I am getting an error: ERROR: Unicode characters greater than or equal to 0x1 are not supported CONTEXT: COPY mailing_list_entry, line 30928, column first_last_name: Ver?nica If your file really does have

Re: [GENERAL] Creating index on a view?

2004-11-24 Thread Patrick Fiche
Some time ago, an excellent tutorial on materialized views with PostgreSQL was pointed at this address http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Patrick -- - Patrick Fiche

Re: [GENERAL] Row creation time

2004-11-24 Thread Michael Glaesemann
On Nov 24, 2004, at 7:07 PM, Najib Abi Fadel wrote: Is there a to get the row creation time if we know it's object ID ?? Only if you have a timestamp column on the table that records the creation time. For example, create table foo ( foo_id serial not null unique ,

Re: [GENERAL] Row creation time

2004-11-24 Thread Najib Abi Fadel
Actually i have an old table without any timestamp column. I want to know the creation time of one Row: One way is to look in the postgres dump files (which could take some time) in order to see the date the Row was inserted. I was hoping there is another way Thx anyways. - Original

[GENERAL] Best practice updating data in webapps?

2004-11-24 Thread Bjørn T Johansen
Maybe a bit outside the scope of PGSQL but when designing webapps where different users can edit the same record at the same time, what is the best way to solve this situation? One way is to use an update field, that gets selected with rest of the data and when updating the data, check this

[GENERAL] Insert may fail if i create a primary key on the oid column?

2004-11-24 Thread songsubo
I create a primary key on the oid column. The oid is generate by system itself. The oid may overlap, when this happen, this operation may fail? ---(end of broadcast)--- TIP 6: Have you searched our list archives?

Re: [GENERAL] Insert may fail if i create a primary key on the oid column?

2004-11-24 Thread Michael Glaesemann
On Nov 24, 2004, at 8:18 PM, songsubo wrote: I create a primary key on the oid column. The oid is generate by system itself. The oid may overlap, when this happen, this operation may fail? Yes. Check the mailing list archives, as this was just recently discussed. Michael Glaesemann grzm

Re: [GENERAL] Creating index on a view?

2004-11-24 Thread Net Virtual Mailing Lists
The problem in my case is that the view does a join between table2 and table3 and I want to do a select on a value from table2. So at the point the expansion happens, I am actually doing a query on a column that does not exist in table3 - it only exists in table2. Given what you said, perhaps a

Re: [GENERAL] Insert may fail if i create a primary key on the oid

2004-11-24 Thread Richard Huxton
songsubo wrote: I create a primary key on the oid column. The oid is generate by system itself. The oid may overlap, when this happen, this operation may fail? Yes. If you want a self-incrementing primary key, why not use a SERIAL type? -- Richard Huxton Archonet Ltd

[GENERAL] Moving/Using Postgres Binaries on multiple machines

2004-11-24 Thread Francis Reed
If I want to create a postgres database on multiple machines, is the practice of tarring or zipping up binaries compiled on one machine and untarring them on another, and using the binaries (initdb etc) acceptable?. This removes the need for having a compiler and environment on the target machine,

[GENERAL] Can't get planner to use multicolumn index on large table

2004-11-24 Thread Ulrich Meis
Hi! I am trying to get postgres to use my index but it just doesn't. Please Help! It follows the table definition and a series of commands I thought would ensure usage of my index. CREATE TABLE data.question_result ( id bigserial PRIMARY KEY, trial_idbigint

Re: [GENERAL] Can't get planner to use multicolumn index on large

2004-11-24 Thread Neil Conway
Ulrich Meis wrote: CREATE TABLE data.question_result ( id bigserial PRIMARY KEY, trial_idbigint NOT NULL REFERENCES data.trial(id), question_id bigint REFERENCES content.question(id), two more columns, ); mydb=# explain analyze select *

Re: [GENERAL] Creating index on a view?

2004-11-24 Thread Richard Huxton
Net Virtual Mailing Lists wrote: CREATE TABLE table2 ( table2_id INTEGER, table2_desc VARCHAR, table3_id INTEGER[] ); CREATE TABLE table3 ( table3_id INTEGER, table3_desc VARCHAR ); What I need is an indirect index (for lack of a better phrase) that allows me to do: SELECT b.table3_id,

Re: [GENERAL] Moving/Using Postgres Binaries on multiple machines

2004-11-24 Thread Richard Huxton
Francis Reed wrote: If I want to create a postgres database on multiple machines, is the practice of tarring or zipping up binaries compiled on one machine and untarring them on another, and using the binaries (initdb etc) acceptable?. This removes the need for having a compiler and environment on

Re: [GENERAL] Moving/Using Postgres Binaries on multiple machines

2004-11-24 Thread Martijn van Oosterhout
Sure, this is what Linux distributers do. They compile postgresql into a binary package which is installed on the user's machine. It works as long as the environments are reasonably compatable, all have readline, similar libc, etc. Hope this helps, On Wed, Nov 24, 2004 at 12:30:28PM -,

Re: [GENERAL] Moving/Using Postgres Binaries on multiple machines

2004-11-24 Thread Francis Reed
That's what we had hoped. We tested the principle with postgres 7.4.6 but found a what we believe is a compile time dependancy in create_conversion.sql where $libdir is not being resolved properly during the initdb process on the second machine. The usual environment variables don't seem to help

Re: [GENERAL] Moving/Using Postgres Binaries on multiple machines

2004-11-24 Thread Doug McNaught
Francis Reed [EMAIL PROTECTED] writes: If I want to create a postgres database on multiple machines, is the practice of tarring or zipping up binaries compiled on one machine and untarring them on another, and using the binaries (initdb etc) acceptable?. This removes the need for having a

[GENERAL] Performance

2004-11-24 Thread Werdin Jens
Title: Performance Hello, Ich have a big performance problem. I'm running postgres 7.4.2 on Suse Linux 9.0 on a dual Xeon 3.0 GHz with 3 Gbyte Ram. In postgres.conf I'm using the defaults. Filesystem is ext3 with writeback journaling I have 3 tables with ca 10 million entries with a gist

Re: [GENERAL] just a test

2004-11-24 Thread ohp
last one I hope -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED]

Re: [GENERAL] Moving/Using Postgres Binaries on multiple machines

2004-11-24 Thread Richard Huxton
Francis Reed wrote: Hi Richard, Sun/Sparc is the platform of choice in our case. We tested the principle with postgres 7.4.6 but found a what we believe is a compile time dependancy in create_conversion.sql where $libdir is not being resolved properly during the intidb process. The usual

[GENERAL] problem in modifing column datatype of a table

2004-11-24 Thread Durga Prasad Mohapatra
Hi, I am newbie in postgresql.I want to modify column datatype of a table how can i do it. for example there is a column with datatype varchar, i want to change it to text. How can i. Thanks Regards Durga ---(end of broadcast)--- TIP 6: Have

Re: [GENERAL] Performance

2004-11-24 Thread Richard Huxton
Werdin Jens wrote: Hello, Ich have a big performance problem. I'm running postgres 7.4.2 on Suse Linux 9.0 on a dual Xeon 3.0 GHz with 3 Gbyte Ram. In postgres.conf I'm using the defaults. That's the place to start. See the guide at: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

Re: [GENERAL] Moving/Using Postgres Binaries on multiple machines

2004-11-24 Thread Peter Eisentraut
Francis Reed wrote: We tested the principle with postgres 7.4.6 but found a what we believe is a compile time dependancy in create_conversion.sql where $libdir is not being resolved properly during the initdb process on the second machine. The usual environment variables don't seem to help

Re: [GENERAL] problem in modifing column datatype of a table

2004-11-24 Thread Peter Eisentraut
Durga Prasad Mohapatra wrote: I am newbie in postgresql.I want to modify column datatype of a table how can i do it. for example there is a column with datatype varchar, i want to change it to text. How can i. Before 8.0: Recreate the table and copy the data. After 8.0: ALTER TABLE --

[GENERAL] PostGreSQL upgrade failed (Debian Packages), need advice...

2004-11-24 Thread Eric D Nielsen
I recently tried to upgrade from the 7.2.1 PostGreSQL package on Debian Stable to the 7.4.6 PostGreSQL package on Debian Testing. The automatic update failed, message included below. The documentation for manual upgrades references a script which does not appear to exist (postgresql-dump) in the

Re: [GENERAL] PostGreSQL upgrade failed (Debian Packages), need advice...

2004-11-24 Thread Joshua D. Drake
Eric D Nielsen wrote: I recently tried to upgrade from the 7.2.1 PostGreSQL package on Debian Stable to the 7.4.6 PostGreSQL package on Debian Testing. The automatic update failed, message included below. The documentation for manual upgrades references a script which does not appear to exist

Re: [GENERAL] Moving/Using Postgres Binaries on multiple machines

2004-11-24 Thread Joshua D. Drake
Peter Eisentraut wrote: Francis Reed wrote: We tested the principle with postgres 7.4.6 but found a what we believe is a compile time dependancy in create_conversion.sql where $libdir is not being resolved properly during the initdb process on the second machine. The usual environment variables

Re: [GENERAL] pgdump of schema...

2004-11-24 Thread Tom Lane
Net Virtual Mailing Lists [EMAIL PROTECTED] writes: When I do a pgdump --schema=someschema somedatabase something.dump, the results of the dump file look like this: CREATE TABLE emailtemplates ( email_template_id integer DEFAULT nextval('emailtemplate_email_templat_seq'::text) NOT NULL,

Re: [GENERAL] PostGreSQL upgrade failed (Debian Packages), need advice...

2004-11-24 Thread Peter Eisentraut
Eric D Nielsen wrote: I recently tried to upgrade from the 7.2.1 PostGreSQL package on Debian Stable to the 7.4.6 PostGreSQL package on Debian Testing. The automatic update failed, message included below. The documentation for manual upgrades references a script which does not appear to

Re: [GENERAL] PostGreSQL upgrade failed (Debian Packages), need advice...

2004-11-24 Thread Peter Eisentraut
Joshua D. Drake wrote: Well you can't just upgrade 7.2.1 to 7.4.6. You have to dump and restore. The Debian package does that automatically. On some days... -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)---

[GENERAL] Nesting Stored Procedure Calls

2004-11-24 Thread Larry White
Is it possible for one stored procedure to call another? I would like certain procs to call a different proc to update an audit trail whenever they're executed. I thought about using triggers but want the trail to include info that's not in the updated table - specifically the application user

Re: [GENERAL] Copying into Unicode - Correcting Errors

2004-11-24 Thread Hunter Hillegas
Peter, Thanks for the reply. Perhaps I should go into some more detail about what is going on. Originally, the database was in SQL_ASCII and the data had been imported via COPY from a text file. The text file is no longer available. The data went into the table just fine. When selecting from

Re: [GENERAL] Nesting Stored Procedure Calls

2004-11-24 Thread Ben
Yes, it's no different than any other select statement. Just keep in mind that it's all one big happy transaction - if the inner stored proc aborts, so does the outer one. (Actually, that may have changed in version 8, but I don't know.) On Nov 24, 2004, at 8:17 AM, Larry White wrote: ne

Re: [GENERAL] PostGreSQL upgrade failed (Debian Packages), need advice...

2004-11-24 Thread Joshua D. Drake
Peter Eisentraut wrote: Joshua D. Drake wrote: Well you can't just upgrade 7.2.1 to 7.4.6. You have to dump and restore. The Debian package does that automatically. On some days... Really? WOW! I wonder if Gentoo does that. That is pretty remarkable. Sincerely, Joshua D. Drake --

[GENERAL] Are subselects treated as atomic single commands?

2004-11-24 Thread Thomas Chille
Hi, the docu about the Read Committed Transaction Isolation Level pointed out: ... The point at issue here is whether or not within a single command we see an absolutely consistent view of the database.. Because i dont want to use the Serializable Transaction Isolation Level or table locks if

Re: [GENERAL] Nesting Stored Procedure Calls

2004-11-24 Thread Joshua D. Drake
Ben wrote: Yes, it's no different than any other select statement. Just keep in mind that it's all one big happy transaction - if the inner stored proc aborts, so does the outer one. (Actually, that may have changed in version 8, but I don't know.) In 8 you could use an exception in plPgsql I

Re: [GENERAL] Nesting Stored Procedure Calls

2004-11-24 Thread Michael Fuhr
On Wed, Nov 24, 2004 at 11:17:26AM -0500, Larry White wrote: Is it possible for one stored procedure to call another? I would like certain procs to call a different proc to update an audit trail whenever they're executed. What happened when you tried it? If you're having trouble then it

Re: [GENERAL] Nesting Stored Procedure Calls

2004-11-24 Thread Tino Wildenhain
Hi, Am Mittwoch, den 24.11.2004, 11:17 -0500 schrieb Larry White: Is it possible for one stored procedure to call another? yes. More specifically these are stored functions rather then stored procedures in the M$-like way. Regards Tino ---(end of

Re: [GENERAL] PostGreSQL upgrade failed (Debian Packages), need

2004-11-24 Thread Robin Ericsson
On Wed, 2004-11-24 at 08:30 -0800, Joshua D. Drake wrote: Peter Eisentraut wrote: Joshua D. Drake wrote: Well you can't just upgrade 7.2.1 to 7.4.6. You have to dump and restore. The Debian package does that automatically. On some days... Really? WOW! I wonder if

Re: [GENERAL] Moving/Using Postgres Binaries on multiple machines

2004-11-24 Thread Tom Lane
Francis Reed [EMAIL PROTECTED] writes: We tested the principle with postgres 7.4.6 but found a what we believe is a compile time dependancy in create_conversion.sql where $libdir is not being resolved properly during the initdb process on the second machine. We only started supporting the idea

Re: [GENERAL] VACUUM and ANALYZE With Empty Tables

2004-11-24 Thread Mark Dexter
Thanks very much for the information. It would appear that our best option might be to vacuum analyze these tables in our application at a point in time when they contain rows instead of doing it at night. Needlesst to say, it would nice to have an option to analyze with a target number of

Re: [GENERAL] Regexp matching: bug or operator error?

2004-11-24 Thread Ken Tanzer
Thanks for the quick responses yesterday. At a minimum, it seems like this behavior does not match what is described in the Postgres documentation (more detail below). But I still have a hard time understanding the results of these two queries: select SUBSTRING(

Re: [GENERAL] Upcoming Changes to News Server ...

2004-11-24 Thread Andrew - Supernews
On 2004-11-23, Marc G. Fournier [EMAIL PROTECTED] wrote: On Wed, 24 Nov 2004, Peter Eisentraut wrote: Marc G. Fournier wrote: Due to recent action by Google concerning the comp.databases.postgresql.* hierarchy, we are going to make some changes that should satisfy just about everyone ... over

Re: [GENERAL] PostGreSQL upgrade failed (Debian Packages), need advice...

2004-11-24 Thread Eric D Nielsen
Quoting Peter Eisentraut [EMAIL PROTECTED]: Eric D Nielsen wrote: I recently tried to upgrade from the 7.2.1 PostGreSQL package on Debian Stable to the 7.4.6 PostGreSQL package on Debian Testing. The automatic update failed, message included below. The documentation for manual upgrades

Re: [GENERAL] PostGreSQL upgrade failed (Debian Packages), need advice...

2004-11-24 Thread Tom Lane
Eric D Nielsen [EMAIL PROTECTED] writes: There were two sets of errors. One set dealing with FATAL 1: unsupported frontend protocol during the data dumping stage of the automatic update script. It appears that the data was successfully dumped, however. Should I be worried? Is this FATAL

Re: [GENERAL] Upcoming Changes to News Server ...

2004-11-24 Thread Andrew - Supernews
On 2004-11-24, Marc G Fournier From : [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (Andrew - Supernews) writes: Any chance of there being regular (or even only occasional) signed checkgroups messages for the new hierarchy? Sure, but I've never done it before, so if you can help ... ? Sure. You

[GENERAL] Replication BLOBs

2004-11-24 Thread Mark Childerson
Hi, Does anyone know of an open source single master- multi (or single) slave replication system for Postgresql 7 or 8 which handles BLOBs? Thanks, Mark Childerson Mark Childerson espressoCode inc. Ph: 416-963-8793 Fax: 416-963-8643 www.exdocs.com www.espressocode.com

Re: [GENERAL] VACUUM and ANALYZE With Empty Tables

2004-11-24 Thread gnari
From: Mark Dexter [EMAIL PROTECTED] Thanks very much for the information. It would appear that our best option might be to vacuum analyze these tables in our application at a point in time when they contain rows instead of doing it at night. Needlesst to say, it would nice to have an option

[GENERAL] tableoid

2004-11-24 Thread Jamie Deppeler
Hi have a bit of a issue im planning on using tableoid to select the appropate table, but im not sure that you can in sql select statement? If not is there another approch i could be using? -- *Jamie Deppeler *Database Administrator ---(end of

Re: [GENERAL] Regexp matching: bug or operator error?

2004-11-24 Thread Tom Lane
Ken Tanzer [EMAIL PROTECTED] writes: Thanks for the quick responses yesterday. At a minimum, it seems like this behavior does not match what is described in the Postgres documentation (more detail below). After looking at this more, I think that it is actually behaving as Spencer designed

[GENERAL] Query for postmaster stats start time?

2004-11-24 Thread Ed L.
Is there a SQL query to retrieve the start time of a) when the postmaster was started, and/or b) when the stats were last reset? I'd like to calculate a few rates over time... TIA. Ed ---(end of broadcast)--- TIP 3: if posting/reading through

Re: [GENERAL] pgdump of schema...

2004-11-24 Thread Net Virtual Mailing Lists
Actually this database has been carried forward since the postgres95 days, so you are definitely right in your analysis.. Would another (perhaps safer?) way of doing this is to remove the CREATE SEQUENCE and SELECT pg_catalog.setval, and replace the DEFAULT nextval with SERIAL then restore

Re: [GENERAL] Replication BLOBs

2004-11-24 Thread Tatsuo Ishii
pgpool(http://www2b.biglobe.ne.jp/~caco/pgpool/index-e.html) surely can handle large objects. I don't know any other replication solutions can handle them. Be carefull, however, please make sure that the master and the slave database clusters are physically synced before starting replication.

Re: [GENERAL] Replication BLOBs

2004-11-24 Thread Joshua D. Drake
Tatsuo Ishii wrote: pgpool(http://www2b.biglobe.ne.jp/~caco/pgpool/index-e.html) surely can handle large objects. I don't know any other replication solutions can handle them. Mammoth Replicator can, but it is not open source. Sincerely, Joshua D. Drake Be carefull, however, please make sure

Re: [GENERAL] Any good report/form generators for postgresql?

2004-11-24 Thread Reid Thompson
Alberto Cabello Sanchez wrote: On Sun, Nov 21, 2004 at 05:50:30PM +, Chris Green wrote: I'm after a good report generator for a postgresql database. I am using Rekall at the moment which is quite good but I'm not quite able to do some of the things I want. Has anyone got any

[GENERAL] SELECT...VIEW...UNION...LIMIT

2004-11-24 Thread Ed L.
I have big_table (1M rows) and small_table (1K rows) with identical schemas and together in a view as follows: create view big_view as select *, 'big_table'::varchar as source from big_table union select *, 'small_table'::varchar as source

[GENERAL] why use SCHEMA? any real-world examples?

2004-11-24 Thread Miles Keaton
I just noticed PostgreSQL's schemas for my first time. (http://www.postgresql.org/docs/current/static/ddl-schemas.html) I Googled around, but couldn't find any articles describing WHY or WHEN to use schemas in database design. Since the manual says HOW, could anyone here who has used schemas

[GENERAL] Nesting Stored Procedure Calls

2004-11-24 Thread Larry White
Is it possible for one stored procedure to call another? I would like a number of procs to call another proc to update an audit trail whenever they're called. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL

Re: [GENERAL] Upcoming Changes to News Server ...

2004-11-24 Thread \Marc G. Fournier From\@svr1.postgresql.org
[EMAIL PROTECTED] (Gary L. Burnore) writes: It appears that his aliiasing hasn't actually taken effect yet. Once it does, apparently things will be slightly better because he's then sending posts to pgsql.* not comp.databases.postgres.* .As of a short while ago, we were still receiving

[GENERAL] Are subselects treated as atomic single commands?

2004-11-24 Thread Thomas Chille
Hi, the docu about the Read Committed Transaction Isolation Level pointed out: ... The point at issue here is whether or not within a single command we see an absolutely consistent view of the database.. Because i dont want to use the Serializable Transaction Isolation Level or table locks if it

[GENERAL] select into temp tables withough using EXECUTE in plpgsql

2004-11-24 Thread Edmund Kleiser
I have a query surrounding somthing taht seems to have been a relatively FAQ. It concerns the use of temporary tables in plpgsql. Which initially resulted in the good old 'oid not found' error. So I learnt from the maliing-list that I should be 'executing' (with EXECUTE Command) my queries

Re: [GENERAL] SELECT...VIEW...UNION...LIMIT

2004-11-24 Thread Greg Stark
Ed L. [EMAIL PROTECTED] writes: create view big_view as select *, 'big_table'::varchar as source from big_table union select *, 'small_table'::varchar as source from small_table; Try UNION ALL instead of just union The difference is that

[GENERAL] HELP speed up my Postgres

2004-11-24 Thread JM
Hi ALL, Ive been using postgres for 3 years and now we are having problems with its performance. Here are some givens.. We have 260 subscription tables per Database. We have 2 databases. Our main client has

Re: [GENERAL] [PERFORM] HELP speed up my Postgres

2004-11-24 Thread Vishal Kashyap @ [SaiHertz]
Dear JM , Ive been using postgres for 3 years and now we are having problems with its PostgrSQL version please -- With Best Regards, Vishal Kashyap. Lead Software Developer, http://saihertz.com, http://vishalkashyap.tk ---(end of

Re: [GENERAL] [PERFORM] HELP speed up my Postgres

2004-11-24 Thread JM
PG Version 7.3.4 On Thursday 25 November 2004 14:12, Vishal Kashyap @ [SaiHertz] wrote: Dear JM , Ive been using postgres for 3 years and now we are having problems with its PostgrSQL version please ---(end of broadcast)--- TIP 9:

Re: [GENERAL] [PERFORM] HELP speed up my Postgres

2004-11-24 Thread Iain
SQL: update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select mobile_num from LOADED_MOBILE_NUMBERS) Could you try using UPDATE ... FROM (SELECT ) AS .. style syntax? About 20 minutes ago, I changed a 8 minute update to an most instant by doing that. regards Iain

Re: [GENERAL] [PERFORM] HELP speed up my Postgres

2004-11-24 Thread Klint Gore
On Thu, 25 Nov 2004 14:00:32 +0800, JM [EMAIL PROTECTED] wrote: update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select mobile_num from LOADED_MOBILE_NUMBERS) does loaded_mobile_numbers have a primary key or index on mobile_num? same for subscriptiontable? have

Re: [GENERAL] [PERFORM] HELP speed up my Postgres

2004-11-24 Thread Christopher Kings-Lynne
update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select mobile_num from LOADED_MOBILE_NUMBERS) Change to: update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where exists (select 1 from LOADED_MOBILE_NUMBERS lmn where lmn.mobile_num=SUBSCRIPTIONTABLE.mobile_num); That should run a