Re: [GENERAL] Delete Question

Postgresql supports records in the where clause i.e. you can compare multiple columns simultaneously: > test=# delete from change where id || ':' || datum not in (select id || ':' || max(datum) from change group by id order by 1); could therefore be rewritten to: delete from change where (id, da

Re: [GENERAL] Delete Question

DELECT FROM table WHERE (ProdID,ChangeDate) not in (SELECT ProdID,MAX(ChangeDate) FROM table GROUP BY ProdID) I hope this works. On 12/7/05, Alex <[EMAIL PROTECTED]> wrote: > Hi, > > I have a table where I store changes made to an order. The looks like > ProdID, ChangeDate, Change1, Change2, ...

Re: [GENERAL] Delete Question

Thanks , this one works beautifully. Alex PS: also thanks to the other suggestions, have a look at this one hubert depesz lubaczewski wrote: On 12/7/05, *Alex* <[EMAIL PROTECTED] > wrote: I have a table where I store changes made to an order. The looks like

Re: [GENERAL] Delete Question

am 07.12.2005, um 18:21:25 +1100 mailte Alex folgendes: > Hi, > > I have a table where I store changes made to an order. The looks like > ProdID, ChangeDate, Change1, Change2, ... etc. > Some ProdIDs have multiple records. > > Is there an easy way to delete all records of a ProdID except the mos

[GENERAL] tables with lots of columns - what alternative from performance point of view?

hi jus recently there were some thread on postgresql list with people asying : i have 700 columns, i have 1000 columns and so on. some people, imediatelly responded: change your schema. this is what forced to me ask: i have a situation where i ahve to store a number of "objects" in database. all o

Re: [SQL] [GENERAL] lost in system tables

> Those parameters are specified when you declare the > foreign key. Look here, > in the section describing "references": > http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html > > Luca Thank you,Luca I know that these parameters are specified when you declare the foreign key, bu

Re: [GENERAL] Delete Question

On Dec 7, 2005, at 16:21 , Alex wrote: Is there an easy way to delete all records of a ProdID except the most recent (ChangeDate is timestamp) one? Preferably in one SQL statement? Here's one way to do it, though not it one SQL statement: create table copy_of_original_table as select dist

Re: [GENERAL] Delete Question

On 12/7/05, Alex <[EMAIL PROTECTED]> wrote: I have a table where I store changes made to an order. The looks likeProdID, ChangeDate, Change1, Change2, ... etc.Some ProdIDs have multiple records.Is there an easy way to delete all records of a ProdID except the most recent (ChangeDate is timestamp) o

Re: [GENERAL] [SQL] lost in system tables

Thank you,Tom, As for the description of 'nulls' I have taken it as it is from the Sybase help file :) __ Yahoo! DSL – Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broad

[GENERAL] Delete Question

Hi, I have a table where I store changes made to an order. The looks like ProdID, ChangeDate, Change1, Change2, ... etc. Some ProdIDs have multiple records. Is there an easy way to delete all records of a ProdID except the most recent (ChangeDate is timestamp) one? Preferably in one SQL stateme

Re: [GENERAL] Letting a function return multiple columns instead of a single complex one

> test=# select c,(ff).* from (select bar.*,get_a_foo(c) as ff from bar) b; > c | a | b > ---+---+--- > 1 | 1 | 2 > (1 row) > Tanks! that works great! It managed to get it even a bit more simplified: select bar.*, (get_a_foo(c)).* from bar; > Not amazingly elegant, but it works. Note that you

Re: [GENERAL] ltree patch is available

I assume Teodor just applied this patch to the proper branches. --- Oleg Bartunov wrote: > Hi there, > > Patch for ltree (all releases since 7.3) is available > http://www.sai.msu.su/~megera/postgres/gist/patches/_ltree.pa

Re: [GENERAL] Letting a function return multiple columns instead of a single complex one

"A.j. Langereis" <[EMAIL PROTECTED]> writes: > The problem I am facing is that I will execute this function as part of = > another query where the parameter will be one of the columns of another = > table. Something like: "select bar.*, get_a_foo(c) from bar". I need the = > result set to be like a

[GENERAL] Letting a function return multiple columns instead of a single complex one

Dear all,   I have two questions: fist of all, is there any function in pg like oracle's rownum?   secondly, I wonder how it is possible to let a function return a dataset with different columns instead of a single, complex, one.   create table foo (a int, b int);   insert into foo (a,b) valu

Re: [GENERAL] deadlock on the same relation

On Fri, Dec 02, 2005 at 10:15:04AM -0500, Tom Lane wrote: > "Francesco Formenti - TVBLOB S.r.l." <[EMAIL PROTECTED]> writes: > > I have a problem about deadlock. I have several stored procedures; only > > one of them uses ACCESS EXCLUSIVE LOCK on a table; however, all the > > stored procedures ca

Re: [GENERAL] Graphical modelling tool

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thomas Kellerer wrote: | Carlos Correia wrote on 06.12.2005 21:13: | |> -BEGIN PGP SIGNED MESSAGE- |> Hash: SHA1 |> |> Thomas Hallgren wrote: |> | Hi, |> | I'm about to start a new project where the first task is to design a |> | database. I'm

Re: [GENERAL] Graphical modelling tool

Carlos Correia wrote on 06.12.2005 21:13: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thomas Hallgren wrote: | Hi, | I'm about to start a new project where the first task is to design a | database. I'm looking for some tool that will allow me to model the | tables and relationships graphically

Re: [GENERAL] Graphical modelling tool

On Tuesday 06 December 2005 12:58, Thomas Hallgren wrote: > Hi, > I'm about to start a new project where the first task is to design a > database. I'm looking for some tool that will allow me to model the > tables and relationships graphically, UML or similar, and then let me > generate the SQL wit

Re: [GENERAL] Graphical modelling tool

I recommend Clay (http://www.azzurri.jp/en/software/clay/index.jsp). It requires a JRE + Eclipse (as it is an eclipse plugin), but it is really nice to work with and has built-in support for PostgreSQL DDL generation. /Mikael ---(end of broadcast)---

Re: [GENERAL] insert deadlock

Brian Cox <[EMAIL PROTECTED]> writes: > Thread 1 does 1+ inserts into Table A and 1 update of Table B in a single > transaction. > This thread runs on demand. > > Thread 2 does 1+ inserts into Table C in a separate transaction. This thread > runs once an hour. I suspect you'll have to include

Re: [GENERAL] Graphical modelling tool

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thomas Hallgren wrote: | Hi, | I'm about to start a new project where the first task is to design a | database. I'm looking for some tool that will allow me to model the | tables and relationships graphically, UML or similar, and then let me | generat

Re: [GENERAL] Graphical modelling tool

On Tue, 6 Dec 2005, Thomas Hallgren wrote: I'm about to start a new project where the first task is to design a database. I'm looking for some tool that will allow me to model the tables and relationships graphically, UML or similar, and then let me generate the SQL with PostgreSQL flavor. What'

Re: [GENERAL] insert deadlock

Brian Cox <[EMAIL PROTECTED]> writes: > There are no foreign key constraints from/to A or B to/from C. How sure are you of that? FK conflicts are much the most common reason for unexpected deadlocks in pre-8.1 PG releases. > 2005-12-02 15:04:46,773 [TP-Processor8] INFO > [com.timestock.tess.ser

Re: [GENERAL] Graphical modelling tool

I wrote a little app that does basically what you're looking for: http://www.hardgeus.com/projects/pgdesigner/ It's not professional quality, and is a little flaky, but it gets the job done. It has a wxGTK dependency, and of course postgres. Your best bet for running it is to pull it from my

[GENERAL] insert deadlock

Thread 1 does 1+ inserts into Table A and 1 update of Table B in a single transaction. This thread runs on demand. Thread 2 does 1+ inserts into Table C in a separate transaction. This thread runs once an hour. There are no foreign key constraints from/to A or B to/from C. The default isola

[GENERAL] Graphical modelling tool

Hi, I'm about to start a new project where the first task is to design a database. I'm looking for some tool that will allow me to model the tables and relationships graphically, UML or similar, and then let me generate the SQL with PostgreSQL flavor. What's the best tools out there? Are there

[GENERAL] ltree patch is available

Hi there, Patch for ltree (all releases since 7.3) is available http://www.sai.msu.su/~megera/postgres/gist/patches/_ltree.patch and from CVS. It fixes problem with unoptimal tree construction and big size of index. Thanks Lexa Tutubalin for test suite and persistency. Regards,

Re: [GENERAL] Should I fix something after disk full error

"Andrus" <[EMAIL PROTECTED]> writes: > I'm using 8.1 on XP. > Server disk becomes full during running ANALYSE command. > ... > Should I fix something or can I continue to use this cluster normally ? Everything should be OK once you clean off some disk space. Let us know if you see any evidence tha

[GENERAL] Should I fix something after disk full error

I'm using 8.1 on XP. Server disk becomes full during running ANALYSE command. The following lines are in log file 2005-12-06 18:29:10 ERROR: duplicate key violates unique constraint "pg_statistic_relid_att_index" 2005-12-06 18:30:23 LOG: autovacuum: processing database "postgres" 2005-12-06 18

Re: [GENERAL] [SQL] lost in system tables

Emil Rachovsky <[EMAIL PROTECTED]> writes: > I am trying to find the equivalent of these two > Sybase system columns : > check_on_commit (Y/N) - Indicates whether INSERT and > UPDATE commands should wait until the next COMMIT > command to check if foreign keys are valid. I think you are lookin

Re: [GENERAL] lost in system tables

On Tuesday 06 December 2005 08:47, Emil Rachovsky wrote: > I am trying to find the equivalent of these two > Sybase system columns : > > check_on_commit (Y/N) - Indicates whether INSERT and > UPDATE commands should wait until the next COMMIT > command to check if foreign keys are valid. A foreig

Re: [GENERAL] Temporary disable autovacuum in pgsql 8.1.0

Tom Lane <[EMAIL PROTECTED]> wrote on 06-12-2005 16:07:41: > That documentation is exactly correct.  Note that it doesn't say "can > only be set at server start" full stop. That's a very suttle difference, but you are right of couse. It's the word 'only' that threw me off. In my mind, it should

Re: [GENERAL] is there any way of specifying "i want x GB of space to be avaialble for my database"

[EMAIL PROTECTED] ("surabhi.ahuja") writes: > is there any way by which i can specify : to reserve x GB amount of > space to this database (this x includes all the space which the > database will require space for data files, indexes or any other > resources). I believe that is part of the intent

[GENERAL] lost in system tables

I am trying to find the equivalent of these two Sybase system columns : check_on_commit (Y/N) - Indicates whether INSERT and UPDATE commands should wait until the next COMMIT command to check if foreign keys are valid. A foreign key is valid if, for each row in the foreign table, the values in

Re: [GENERAL] error in creating database

Hi! > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Im Auftrag von Karthik.S > Gesendet: Dienstag, 6. Dezember 2005 13:26 > An: pgsql-general@postgresql.org > Betreff: [GENERAL] error in creating database > > Dear all, > > I am using postgres ve

Re: [GENERAL] error in creating database

am 06.12.2005, um 17:56:20 +0530 mailte Karthik.S folgendes: > Dear all, > > I am using postgres version: 7.1.3 in Red hat linux : 7.2. Uuhhh, 7.1 is out of lifetime. You should update! > > I am having a script (uses psql) which drops and creates a database by > logging > into 'temp

[GENERAL] error in creating database

Dear all, I am using postgres version: 7.1.3 in Red hat linux : 7.2. I am having a script (uses psql) which drops and creates a database by logging into 'template1'. This will be invoked from crontab. Sometimes (nearly 50% of the times) the database creation fails by saying "ERROR: CR

Re: [GENERAL] Temporary disable autovacuum in pgsql 8.1.0

Richard van den Berg <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> wrote on 06-12-2005 15:49:28: >> Couldn't you have just set autovacuum = off in postgresql.conf? (Unlike >> some other things, this setting can be changed after postmaster start.) > If that is true, the documentation

Re: [GENERAL] Temporary disable autovacuum in pgsql 8.1.0

Tom Lane <[EMAIL PROTECTED]> wrote on 06-12-2005 15:49:28: > Couldn't you have just set autovacuum = off in postgresql.conf?  (Unlike > some other things, this setting can be changed after postmaster start.) If that is true, the documentation at http://www.postgresql.org/docs/8.1/static/runtime-c

Re: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1

Hi! > -Ursprüngliche Nachricht- > Von: Howard Cole [mailto:[EMAIL PROTECTED] > Gesendet: Dienstag, 6. Dezember 2005 15:38 > An: Markus Wollny > Cc: PgSql General > Betreff: Re: [GENERAL] Unicode Corruption and upgrading to > 8.0.4. to 8.1 > I am avoiding this solution at the moment sin

Re: [GENERAL] Temporary disable autovacuum in pgsql 8.1.0

Richard van den Berg <[EMAIL PROTECTED]> writes: > We are in the process of upgrading our 400GB database from PostgreSQL > 7.4.7 to 8.1.0. During the pg_restore I noticed that the autovacuum > process was slowing things down significantly. I should have disabled it > in postgresql.conf before start

Re: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1

Thanks Markus, I am avoiding this solution at the moment since the database contains binary (ByteA) fields aswell as text fields and I am unsure what iconv would do to this data. If Zlatko's method does not work then I shall see if I can programmatically use libiconv for all the relevant data.

Re: [GENERAL] Inheritance Algebra

Trent, although I cannot contribute much of anything to your line of thought I'd encourage you to keep on with it as it'd be highly desirable (for GNUmed at least) to have a stronger/ more encompassing inheritance solution in PostgreSQL. Karsten, GNUmed developer -- GPG key ID E4071346 @ wwwkeys

Re: [GENERAL] massive performance hit when using "Limit 1"

Rich Doughty wrote: Richard Huxton wrote: Rich Doughty wrote: [snip] Try the same query but with a low retailer_id (100 or something) and see if it goes a lot quicker. If that is what the problem is, try changing the ORDER BY to something like "_s.retailer_id, _t.value, _t.number" and see

Re: [GENERAL] massive performance hit when using "Limit 1"

Richard Huxton wrote: Rich Doughty wrote: This one goes nuts and doesn't return. is there any way i can force a query plan similar to the one above? EXPLAIN SELECT _t.* FROM tokens.ta_tokens _t INNER JOIN tokens.ta_tokens_stock _s ON _t.token_id = _s.token_id WHERE

Re: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1

Hello! > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Im Auftrag von Howard Cole > Gesendet: Dienstag, 6. Dezember 2005 13:41 > An: 'PgSql General' > Betreff: Re: [GENERAL] Unicode Corruption and upgrading to > 8.0.4. to 8.1 > >> Hi everyone, I have a p

Re: [GENERAL] massive performance hit when using "Limit 1"

Rich Doughty wrote: This one goes nuts and doesn't return. is there any way i can force a query plan similar to the one above? EXPLAIN SELECT _t.* FROM tokens.ta_tokens _t INNER JOIN tokens.ta_tokens_stock _s ON _t.token_id = _s.token_id WHERE _s.retailer_id = '96

Re: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1

Hi Zlatko, I shall give this a try later and let you know how I get on. Thank you for responding. Howard. Zlatko Matic wrote: Have you tried to restore just schema first, then data? Greetings, Zlatko Hi everyone, I have a problem with corrupt UTF-8 sequences in my 8.0.4 dump which is pre

Re: [GENERAL] fts, compond words?

Oleg Bartunov wrote: On Mon, 5 Dec 2005, Marcus Engene wrote: I realized from the documentation that I'm not looking for compound words after all, I meant "exact phrase". I can't see how to make rank tell me which results has an exact phrase? Like "there must be a occurence of 'new' before 'yor

Re: [GENERAL] need help

Jenny wrote: I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been dealing with Psql for over than 2 years now, but I've never had this case before. Then I try to run the query from the psql shell. For example, the table has obat_id : A, B, C, D. db=# UPDATE s_apotik SET

[GENERAL] Temporary disable autovacuum in pgsql 8.1.0

We are in the process of upgrading our 400GB database from PostgreSQL 7.4.7 to 8.1.0. During the pg_restore I noticed that the autovacuum process was slowing things down significantly. I should have disabled it in postgresql.conf before starting the restore (duh). Not wanting to restart the restore

[GENERAL] UNSUBSCRIBE

Please Unsubscribe.

Re: [GENERAL] massive performance hit when using "Limit 1"

Rich Doughty wrote: can anyone explain the reason for the difference in the following 2 query plans, or offer any advice? the two queries are identical apart from the limit clause. [snip] fwiw, join order makes no difference here either. i get a slightly different plan, but with LIMIT 1 postgr

Re: [GENERAL] Using a 250GB RAID10 server for postgres

On Tue, Dec 06, 2005 at 09:41:55AM +, Rory Campbell-Lange <[EMAIL PROTECTED]> wrote: > Is it OK to use logical volume management to run an xfs partition > hosting postgres data? We use just that and it works splendid. It's very nice for adding space and all that. But I must admit th

Re: [GENERAL] Using a 250GB RAID10 server for postgres

In the absence of replies (and sorry to bombard the list), I should clarify my question: Is it OK to use logical volume management to run an xfs partition hosting postgres data? (The server specs are below.) Thanks for any replies. Rory On 05/12/05, Rory Campbell-Lange ([EMAIL PROTECTE

[GENERAL] need help (not anymore)

I run the VACUUM as you suggested, but still no response from the server. So, I decided to DROP the database. I got a message that the database is being used. I closed every application that accessing it. But, the message remains. I checked the server processes (ps -ax). There were lots of 'UPDAT

Re: [GENERAL] need help

Jenny schrieb: I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been dealing with Psql for over than 2 years now, but I've never had this case before. I have a table that has about 20 rows in it. Table "public.s_apotik" Column | Type

[GENERAL] need help

I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been dealing with Psql for over than 2 years now, but I've never had this case before. I have a table that has about 20 rows in it. Table "public.s_apotik" Column | Type| Modifi