Re: [GENERAL] General Ledger db design

2007-02-25 Thread Karl O. Pinc
On 02/25/2007 06:21:45 PM, Kenneth Downs wrote: Martin Winsler wrote: This is a real world situation where referential integrity needs to be broken in theory, I believe. Does anybody have any experience or knowledge of building financial accounting databases? Am I wrong about this? Th

Re: [GENERAL] pulling hair out trying to force replan

2007-02-25 Thread Adam Rich
> I've got some pretty big tables with partial indexes > on very specific values. It seems as though no matter > what I try to force a replan it won't plan to use the > partial indexes because it seems to be caching a plan > valid for all potential parameters. I'm using hibernate > which uses

[GENERAL] pulling hair out trying to force replan

2007-02-25 Thread Gene
I've got some pretty big tables with partial indexes on very specific values. It seems as though no matter what I try to force a replan it won't plan to use the partial indexes because it seems to be caching a plan valid for all potential parameters. I'm using hibernate which uses prepared stateme

Re: [GENERAL] General Ledger db design

2007-02-25 Thread Kenneth Downs
Martin Winsler wrote: I hope this isn't too far off topic. I've noticed some discussion about referential integrity, the use of nulls, and database design recently here. This is a real world situation where referential integrity needs to be broken in theory, I believe. Does anybody have any

Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL

2007-02-25 Thread Leonel Nunez
> Hi, > > I would like to store picture in my DB and after to display them on my PHP > pages. > > What is the best solution for that ? > > thanks a lot > > -- > Alain > > Windows XP SP2 > PostgreSQL 8.1.4 > Apache 2.0.58 > PHP 5 > > I do with ByteA datatype

Re: [GENERAL] perfromance world records

2007-02-25 Thread Tomi N/A
2007/2/25, Tom Lane <[EMAIL PROTECTED]>: If the objective is to claim a world record, we'd look pretty silly trying to do so with a nonstandard, non-certified test. The point of certification in this context is that you have someone else attesting to the validity of your results. Without that,

Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?

2007-02-25 Thread Karl O. Pinc
On 02/24/2007 11:24:40 PM, Jaime Casanova wrote: On 2/24/07, Karl O. Pinc <[EMAIL PROTECTED]> wrote: http://www.postgresql.org/docs/current/static/rules-views.html Actually, i found it very clear: if you create a SELECT rule on a table it becomes a view, this is what postgres does every time

Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL

2007-02-25 Thread Steve Atkins
On Feb 25, 2007, at 9:26 AM, Tomas Vondra wrote: Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;-) Andreas Don't do that - the filesystems are not transactional (at least not the usual ones), so you'll lose the abi

Re: [GENERAL] perfromance world records

2007-02-25 Thread Tom Lane
"Tomi N/A" <[EMAIL PROTECTED]> writes: > 2007/2/24, Joshua D. Drake <[EMAIL PROTECTED]>: >> You can not publish TPC tests without a TPC fee :). However there are >> plenty of other tests such as dbt2 and odbcbench that can give you >> comparable and free results. > I mentioned a TPC test as an exa

Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL

2007-02-25 Thread Kenneth Downs
Tomas Vondra wrote: Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;-) Andreas Don't do that - the filesystems are not transactional (at least not the usual ones), so you'll lose the ability to use transactions. Imagi

Re: [GENERAL] perfromance world records

2007-02-25 Thread Tomi N/A
2007/2/24, Joshua D. Drake <[EMAIL PROTECTED]>: Ron Johnson wrote: > On 02/24/07 11:00, Tom Lane wrote: >>> "Tomi N/A" <[EMAIL PROTECTED]> writes: ...which made me think: postgresql aims at the same (or very similar) clients and use cases as Oracle, DB2 and MSSQL. I pose the question >>

Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL

2007-02-25 Thread Jean-Christophe Roux
Hello Tomas, Tomas Vondra wrote: Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;-) Andreas Anyway, I do recommend storing images in the database, using a 'bytea' column for the binary data (and load them only if reall

Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL

2007-02-25 Thread Alain Roger
This is what i did (to stored pictures in DB)... but i use the following process : 1.store picture on my localhost db 2. export as SQL statement all pictures from my table :-( ===> it was 7.4Mb 3. import to the remote db hosted by a company. is there an easy way to store image into a hosted DB

Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL

2007-02-25 Thread Andreas Kretschmer
Tomas Vondra <[EMAIL PROTECTED]> schrieb: > > >Store the pictures in the filesystem and only the path, description and > >other metadata in the database. My suggestion ;-) > >Andreas > > > Don't do that - the filesystems are not transactional (at least not the > usual ones), so you'll lose the

Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL

2007-02-25 Thread Andrej Ricnik-Bay
On 2/26/07, Joris Dobbelsteen <[EMAIL PROTECTED]> wrote: See the discussion "[GENERAL] Database versus filesystem for storing images" earlier on the List. And man, do I wish people used threading-capable mailers and didn't do tofu-posts. :/ - Joris Cheers, Andrej ---

Re: [GENERAL] SQL Question - Using Group By

2007-02-25 Thread Tomas Vondra
Hi, I have a question about using Group By. On a table like this: Type (varchar) | Active (boolean) Type One | False Type Two | True Type One | True Type Fifty | Flase

Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL

2007-02-25 Thread Tomas Vondra
Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;-) Andreas Don't do that - the filesystems are not transactional (at least not the usual ones), so you'll lose the ability to use transactions. Imagine what happens when

Re: [GENERAL] SQL Question - Using Group By

2007-02-25 Thread Mike
Thank you! Exactly what I needed. Mike On Feb 23, 4:42 pm, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > You could use COUNT() in conjunction with NULLIF: > > select "Type", > count(nullif("Active", false)) as "Active Count", > count(nullif("Active", true)) as "Inactive Count", > 100 * count(nu

Re: [GENERAL] schema design question

2007-02-25 Thread Didier Gasser-Morlay
On Sat, 24 Feb 2007 00:59:02 -0800 [EMAIL PROTECTED] (snacktime) wrote: > Say you have 8 different data models that are related enough to share > roughly 70% of the same fields, but the shared fields are not always > the same. And also within any given model, some fields can be empty. > The busin

Re: [GENERAL] Priorities for users or queries?

2007-02-25 Thread Benjamin Arai
My problem with [1] is that even for 10 users the percentage of time spent in locks is very high. Can priorities scale? Benjamin Ron Mayer wrote: Bruce Momjian wrote: Hard to argue with that. Is it a strong enough argument to add a TODO? I'm thinking some sort of TODO might be ca

[GENERAL] SQL Question - Using Group By

2007-02-25 Thread Mike
Hi, I have a question about using Group By. On a table like this: Type (varchar) | Active (boolean) Type One | False Type Two | True Type One | True Type Fifty | Flase Typ

Re: [GENERAL] complex referential integrity constraints

2007-02-25 Thread Robert Haas
I don't understand what a weighted constraint would mean. Either the attacker_id can be a wolf, or it can't. Knowing that it is only 1% likely over the long haul is insufficient to disallow any particular transaction. It's certainly true that the constraint as stated is insufficient to guarantee

Re: [GENERAL] select all matches for a regular expression ?

2007-02-25 Thread [EMAIL PROTECTED]
I'm going to disagree and say it can be done (maybe). Use regexp_replace() to convert non-numeric characters. Depending on your final needs, you could leave it as a comma-separated list or split it to an array. select string_to_array(regexp_replace(regexp_replace('hello4 is 4 very n1ce num8er',

Re: [GENERAL] SQL Question - Using Group By

2007-02-25 Thread [EMAIL PROTECTED]
You could use COUNT() in conjunction with NULLIF: select "Type", count(nullif("Active", false)) as "Active Count", count(nullif("Active", true)) as "Inactive Count", 100 * count(nullif("Active", false)) / count(*) as "Active Percent" from table_name group by "Type" On Feb 23, 2:50 pm, "Mike" <[E

Re: [GENERAL] complex referential integrity constraints

2007-02-25 Thread Robert Haas
Actually, what would be really nice is if there were just a button I could push that would make all of my data automatically correct. Can that go into 8.3? Thanks, ...Robert -Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: Friday, February 23, 2007 9:35 AM To: Alb

Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL

2007-02-25 Thread Tommy Gildseth
Andreas Kretschmer wrote: Alain Roger <[EMAIL PROTECTED]> schrieb: Hi, I would like to store picture in my DB and after to display them on my PHP pages. What is the best solution for that ? Store the pictures in the filesystem and only the path, description and other metadata in the

Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL

2007-02-25 Thread Joris Dobbelsteen
See the discussion "[GENERAL] Database versus filesystem for storing images" earlier on the List. It started at 31 december 2006 and ended 9 januari 2007. It goes trough all/most pro/con arguments for different options. - Joris From: [EMAIL PROTECTED]

Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL

2007-02-25 Thread Andreas Kretschmer
Alain Roger <[EMAIL PROTECTED]> schrieb: > Hi, > > I would like to store picture in my DB and after to display them on my PHP > pages. > > What is the best solution for that ? Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;

[GENERAL] Best way to store and retrieve photo from PostGreSQL

2007-02-25 Thread Alain Roger
Hi, I would like to store picture in my DB and after to display them on my PHP pages. What is the best solution for that ? thanks a lot -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

2007-02-25 Thread Chad Wagner
On 2/25/07, Magnus Hagander <[EMAIL PROTECTED]> wrote: > For the record, anyone using wikipgedia deserves the pain they > get: it is deprecated. The latest version of MediaWiki itself is what > should now be used: it will detect if you have Postgres upon > installation. :) Perhaps the project s

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

2007-02-25 Thread Chad Wagner
On 2/25/07, Greg Sabino Mullane <[EMAIL PROTECTED]> wrote: For the record, anyone using wikipgedia deserves the pain they get: it is deprecated. The latest version of MediaWiki itself is what should now be used: it will detect if you have Postgres upon installation. :) Some of us are still us

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

2007-02-25 Thread Magnus Hagander
> For the record, anyone using wikipgedia deserves the pain they > get: it is deprecated. The latest version of MediaWiki itself is what > should now be used: it will detect if you have Postgres upon > installation. :) Perhaps the project should be *gasp* deleted then? ;-) Or is there actual histo

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

2007-02-25 Thread Greg Sabino Mullane
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > How is the Postgres port of the Wikipedia doing this days anyway? > Is it in a shape where one would consider it "competitive"? The port of MediaWiki is going well: it is certainly usable, and is already being used by a number of sites. I w

Re: [GENERAL] Inserting a new column in between.

2007-02-25 Thread Noel Faux
Hi RPK My suggestion would be to create a view which presents the columns as you wish. eg CREATE OR REPLACE VIEW tableView AS SELECT a, b, c FROM table Then when you add add a new column, just adjust the view as required. Cheers Noel RPK wrote: Andreas, I am talking about inserting a f

Re: [GENERAL] Inserting a new column in between.

2007-02-25 Thread Andreas Kretschmer
RPK <[EMAIL PROTECTED]> schrieb: > > Andreas, > > I am talking about inserting a field or changing their order in the > structure itself. In MS Access and SQL Server we have this facility. Some > times I need to shift the less important field to the last so that when I > query using: > > Select

Re: [GENERAL] Inserting a new column in between.

2007-02-25 Thread Thomas Kellerer
RPK wrote on 25.02.2007 09:44: Select * from tablename; the un-necessary field gets displayed in the last. However, you I can ignore that field altogether using filtered fields only in Select statement, but still sometimes it is necessary. No it's not. SELECT * should be avoided by all means (

Re: [GENERAL] Inserting a new column in between.

2007-02-25 Thread RPK
Andreas, I am talking about inserting a field or changing their order in the structure itself. In MS Access and SQL Server we have this facility. Some times I need to shift the less important field to the last so that when I query using: Select * from tablename; the un-necessary field gets disp