Re: [GENERAL] Code tables, conditional foreign keys?

[Lew]

Conrad Lender wrote:

I didn't intend any disrespect to Joe Celko. I have read a number of his
articles, which tend to be well written and informative. Last year, when
I posted to comp.databases asking for advice on whether to refactor that
table, he wrote "You will have to throw it all out and start over with a
relational design", "Throw away the idiot who did the EAV. This is not a
good design -- in fact, it is not a design at all", and "This is basic
stuff!!" Then he copied the same EAV example that was linked earlier by
Rodrigo, claiming that "someone like me" had suggested it. With all the
respect I have for Mr. Celko, that was hardly helpful, as that example
and the situation I had described were quite different. It also did not
encourage me to follow his advice and start from scratch (and fire my
boss, who was the mentioned "idiot").


If we fired every boss who actually is an idiot there would be about half the 
number of bosses.


All kidding aside, why is the boss specifying a database architecture?  That 
is not the boss's job.



I understand the problems that can arise from bad design choices, and I
know that Celko is vehemently opposed to anything that resembles EAV,


For good reasons.


but I felt that in our case "throwing it all away" would be excessive.


Perhaps not.  I had a situation some years ago where a supervisor would not 
let me normalize a database and consequently the project nearly failed. 
Fortunately, the company assigned a new team lead/project manager who did the 
normalization or it would have been a disaster.  Trying to make a bad approach 
work is often, if not always, more expensive than replacing it with a good 
approach.



We had safeguards to ensure referential integrity, and keeping the
values in the same table allowed us to let users manage them all with
the same form. So I guess it's like Stefan Keller said in a different
thread today: "Know when to break the rules."


Managing all the values in the same form is not intrinsically connected to 
whether one stores the values in an EAV layout.


Telling oneself that one should know when to break the rules is not the same 
as knowing when to break the rules.  They are the rules for good reason.


All I'm saying is that EAV is a very problematic approach.  I've been on 
projects that tried to use it, and while that didn't make me an expert on the 
matter by any means, it gave me some cause to trust Mr. Celko's opinion on the 
matter.


--
Lew

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] do postgresql this job for me ? (firebird user)

[mnavahan]

Hi 

 i am firebird user But now have some problem with it and chk postgresql to
migrate to it ...

1.have postgresql support online change of tables ?
 for example i have over 400 table in db with many relation :
  A: if i dont any user connect to table RR (but many user connect to other
tables) can in online Db i change   table RR ?
  B: if table A have FK to B can edit table  B for example Add field ?

have any documented information for change table when online
(book,note,.) of up ?

2. postgresql do any index corruption when memory low or same problem ?

3. postgresql support recursive call in stored procedure and in depth
recursive have good performance?

4.have any way port firebird stored procedure to postgresql ?

5.postgresql support Sql in stored procedure via sting (i have one string
contain sql can execute it and get result ?)

6.how performance of temp table (is in memory and then ultra fast ?) 

many thanx ...

-- 
View this message in context: 
http://www.nabble.com/do-postgresql-this-job-for-me---%28firebird-user%29-tp23690611p23690611.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Can't start service but works from command line

[Dejan]

Computer which hosted a database crashed, but I managed to save "data" 
folder. I copied it to another computer and pointed postgres to that folder 
(stopping the service first). But, the service cannot start. Progress bar 
just goes for awhile, and then a pop-up tells me that the service stopped.

I found in Services that command for starting the service is this

"C:\Program Files\PostgreSQL\8.2\bin\pg_ctl.exe" runservice -w -N 
"pgsql-8.2" -D "f:\PostgreSQL\data\"

I tried to start it from command line with

"C:\Program Files\PostgreSQL\8.2\bin\pg_ctl.exe" start -w -D 
"f:\PostgreSQL\data\"

and it works. The server starts and I can connect to it and access all 
tables. But I need it to start as a service. Any help? I'm using version 
8.2.5 on WinXP Pro SP2. 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Code tables, conditional foreign keys?

[Conrad Lender]

On 23/05/09 09:34, Scott Marlowe wrote:
> I have a great deal of respect for Celko.  I don't always agree with 
> everything he says, but most of it makes a lot of sense to me.

I didn't intend any disrespect to Joe Celko. I have read a number of his
articles, which tend to be well written and informative. Last year, when
I posted to comp.databases asking for advice on whether to refactor that
table, he wrote "You will have to throw it all out and start over with a
relational design", "Throw away the idiot who did the EAV. This is not a
good design -- in fact, it is not a design at all", and "This is basic
stuff!!" Then he copied the same EAV example that was linked earlier by
Rodrigo, claiming that "someone like me" had suggested it. With all the
respect I have for Mr. Celko, that was hardly helpful, as that example
and the situation I had described were quite different. It also did not
encourage me to follow his advice and start from scratch (and fire my
boss, who was the mentioned "idiot").

I understand the problems that can arise from bad design choices, and I
know that Celko is vehemently opposed to anything that resembles EAV,
but I felt that in our case "throwing it all away" would be excessive.
We had safeguards to ensure referential integrity, and keeping the
values in the same table allowed us to let users manage them all with
the same form. So I guess it's like Stefan Keller said in a different
thread today: "Know when to break the rules."


  - Conrad

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.3: timestamp subtraction

[Tom Lane]

=?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?=  writes:
> Thanks, It's off in both 8.2 and 8.3.

As was already stated, that depends on which build you're using.
(And no, "the Win32 distribution on the PgSql site" is not a unique
description, not even for a single PG version.)

> What will be the default in 8.4?

The same comment will apply to 8.4.  There is a general trend away from
floating point timestamps, but there will probably be some builds
continuing to use them for a long time to come, because of compatibility
considerations.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Excessive (?) Pg file descriptor use when partitioning

[Tom Lane]

Henry  writes:
> However,  once I start real processing and there's lots of updates and  
> selects occurring, file descriptor usage (by many Pg procs) will  
> gleefully claw it's way up to 400k (on the busy cluster master node)  
> and eventually bludgeon my arbitrary 500k limit.  Each Pg process eats  
> about 991 fd's.

You can adjust that with max_files_per_process.  I wouldn't make it
too small though.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.3: timestamp subtraction

[Havasvölgyi Ottó]

Thanks, It's off in both 8.2 and 8.3.
What will be the default in 8.4?

Best regards,
Otto

2009/5/23 Alvaro Herrera 

> Havasvölgyi Ottó escribió:
> > I mean the Win32 distribution on the PgSql site. I always used that.
>
> If you want to find out whether a particular build used floating point or
> integer datetimes, issue "SHOW integer_datetimes".
>
> If it says "off", then it's floating point.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/ 
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

Re: [GENERAL] Daylight saving time question

[Lew]

Bayless Kirtley wrote:
Thanks Tom and Scott. You got me looking in the right direction. In this 
case
the client and server are on the same machine (testing/development) and 
psql
does return the right result. I tried all the possibilities from the 
java program,
"show timezone", "select current_time" and "select current_timestamp". 
These

were all JDBC queries. When I used result.getString(), the values looked
right. When I used result.getTime(), they were off by one hour as if 
daylight saving were not in effect.


If 'result' is a Java 'java.util.Date' type (or one of its java.sql subtypes), 
then it only holds milliseconds since epoch as if in GMT; 'Date' holds no 
timezone information as such.  In that situation, 'result.getTime()' returns a 
'long' value.


How exactly are you displaying 'result.getTime()'?  How exactly are you 
determining that its value is "off" by one hour?  Can you show us Java code?


Is this a flaw in the JDBC driver or is that the expected behavior? In 
either case I do now have a workaround but would like to know.


It is not a flaw in the JDBC driver.

--
Lew

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.3: timestamp subtraction

[Alvaro Herrera]

Havasvölgyi Ottó escribió:
> I mean the Win32 distribution on the PgSql site. I always used that.

If you want to find out whether a particular build used floating point or
integer datetimes, issue "SHOW integer_datetimes".

If it says "off", then it's floating point.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Sql Update Sentence

[Martijn van Oosterhout]

On Sat, May 23, 2009 at 03:13:58PM -0400, mariolos wrote:
> hello to all!!! i have a quiestion and problem, i need replace a value from
> a field with other value, i try using this sql sentence:
> 
> update packing_acum set corr=corr + 200) BETWEEN 26821 AND 27340 and
> extract(year from fec_prod) = 2009 AND cod_packing  between 2321 and 2327
> 
> but this error appear..
> 
> ERROR:  column "corr" is of type numeric but expression is of type boolean
> HINT:  You will need to rewrite or cast the expression.
> ** Error **

There's a syntax error in your statement, so please send what the
actual query is. But if I imagine an extra brace then it's true that
you're trying to assign a boolean to a numeric:

a BETWEEN b AND c

is a boolean expression.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

Re: [GENERAL] Sql Update Sentence

[Adrian Klaver]

On Saturday 23 May 2009 12:13:58 pm mariolos wrote:
> hello to all!!! i have a quiestion and problem, i need replace a value from
> a field with other value, i try using this sql sentence:

Is this the entire actual SQL statement? If so see below.

>
> update packing_acum set corr=corr + 200) BETWEEN 26821 AND 27340 and
  
First no left parentheses. Second the BETWEEN clause is operating on corr + 200 
and resolving to true or false which is the being used to update corr. Since 
corr is numeric the update fails.
 
> extract(year from fec_prod) = 2009 AND cod_packing  between 2321 and 2327
>
> but this error appear..
>
> ERROR:  column "corr" is of type numeric but expression is of type boolean
> HINT:  You will need to rewrite or cast the expression.
> ** Error **
>
> ERROR: column "corr" is of type numeric but expression is of type boolean
> Estado SQL:42804
> Sugerencias:You will need to rewrite or cast the expression.
>
>
> where is the problem??
>
> thx in adv.



-- 
Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.3: timestamp subtraction

[Havasvölgyi Ottó]

I mean the Win32 distribution on the PgSql site. I always used that.
It would be very good if these data types were exact by default, even if
that's a bit slower.

Otto

2009/5/23 Christophe 

>
> On May 23, 2009, at 10:44 AM, Havasvölgyi Ottó wrote:
>
> Thanks.
>> I tested the standard Win32 distribution of 8.3.6.
>> The same happens on 8.2. But on 8.0 it works.
>>
>> When I don't use milliseconds, then it works.
>>
>> Will 8.4 work fine on Win32 again?
>>
>
> If the issue is using floating point timestamps, then the particular
> version of PostgreSQL isn't the issue; it's whether the distribution you
> were using was built with integer or floating point timestamps.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] Sql Update Sentence

[mariolos]

hello to all!!! i have a quiestion and problem, i need replace a value from
a field with other value, i try using this sql sentence:

update packing_acum set corr=corr + 200) BETWEEN 26821 AND 27340 and
extract(year from fec_prod) = 2009 AND cod_packing  between 2321 and 2327

but this error appear..

ERROR:  column "corr" is of type numeric but expression is of type boolean
HINT:  You will need to rewrite or cast the expression.
** Error **

ERROR: column "corr" is of type numeric but expression is of type boolean
Estado SQL:42804
Sugerencias:You will need to rewrite or cast the expression.


where is the problem??

thx in adv.

Re: [GENERAL] 8.3: timestamp subtraction

[Christophe]

On May 23, 2009, at 10:44 AM, Havasvölgyi Ottó wrote:


Thanks.
I tested the standard Win32 distribution of 8.3.6.
The same happens on 8.2. But on 8.0 it works.

When I don't use milliseconds, then it works.

Will 8.4 work fine on Win32 again?


If the issue is using floating point timestamps, then the particular  
version of PostgreSQL isn't the issue; it's whether the distribution  
you were using was built with integer or floating point timestamps.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Asssociative Arrays: Best practices / snippets?

[Stefan Keller]

Steve, Filip: Many thanks for your patient answers and concerns. Did'nt know
hstore!

=> Is it enough to run the hstore.sql (found in ..\8.3\share\contrib\ plus
.dll) in order to install this contrib type under Windows?

=> What's the status of this contribution? Is it going to be still in 8.4?
(else I would still consider Arrays).

As for the background: I'm importing data from OpenStreetMap - "the
Wikipedia of Maps" - actuall by applying "good" database design principles.
This entity-attribute-value model get's mapped to a relational one. Now I
won't and can't map all attributes (called tags). That's where the idea
about associative arrays came in. The KVPs would be an ANDed in a search
with "regular" columns.

So, my answer to Leif's hot-blooded judgment about KVPs could be: "Know when
to break the rules" ;->
-S.

2009/5/23 Steve Atkins 

>
> On May 23, 2009, at 2:25 AM, Stefan Keller wrote:
>
> I have a use case where the I want to put an unforeseable number of
>> key/value pairs in a column.
>> Now, PostgreSQL has arrays as first class types.
>> Are there any best practices and snippets (preferrably in plpgsql) for
>> handling key/value pairs?
>>
>
> Take a look at "hstore". It's a data type in contrib that's intended to
> store a set of key, value
> pairs in a single column.
>
> It's well suited for the case where you're not searching on the keys and
> values. If you do
> need to search on them then you can either index the hstore field with a
> gin or gist index,
> or use a separate EAV table for the keys and values.
>
> Cheers,
>  Steve
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] 8.3: timestamp subtraction

[Havasvölgyi Ottó]

Hi,

On 8.2 this comparision is also not true:

select '240:0:0.3'::interval = '10 0:0:0.3'::interval;

But without milliseconds it's true.

Is this also because interval is represented internally as a floating point
value?

On 8.3 this test does not fail.

Best regards,
Otto

2009/5/23 Havasvölgyi Ottó 

> Thanks.
> I tested the standard Win32 distribution of 8.3.6.
> The same happens on 8.2. But on 8.0 it works.
>
> When I don't use milliseconds, then it works.
>
> Will 8.4 work fine on Win32 again?
>
> Thanks,
> Otto
>
>
>
> 2009/5/23 Ludwig Kniprath 
>
> Scott Marlowe schrieb:
>>
>>> On Sat, May 23, 2009 at 7:18 AM, Christophe  wrote:
>>>
>>>
 On May 23, 2009, at 9:13 AM, Daniel Verite wrote:


> I don't know why this query returns false:
>> SELECT '20040506 070809.01'::timestamp(6) - '20010203
>> 040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
>> millisecond'::interval;
>> If I just subtract the two timestamps, its result is the interval I
>> specified.
>> What may cause this?
>>
>>
> It works for me:
>
> test=> SELECT '20040506 070809.01'::timestamp(6) -
> '20010203 040506.007000'::timestamp(6)=
> '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval;
> ?column? --
> t
> (1 row)
>
>
 Could this be due to the OP's build of PG using floating point
 timestamps?


>>>
>>> That's what I'm thinking
>>>
>> Me too, a testquery-result on a Windows-System with version "PostgreSQL
>> 8.3.0, compiled by Visual C++ build 1400":
>>
>> SELECT ('20040506 070809.01'::timestamp(6) - '20010203
>> 040506.007000'::timestamp(6) - '1188 day 3 hour 3 minute 3 second 3
>> millisecond'::interval) * 1e10;
>>
>> => -00:01:28.220986
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>

Re: [GENERAL] 8.3: timestamp subtraction

[Havasvölgyi Ottó]

Thanks.
I tested the standard Win32 distribution of 8.3.6.
The same happens on 8.2. But on 8.0 it works.

When I don't use milliseconds, then it works.

Will 8.4 work fine on Win32 again?

Thanks,
Otto



2009/5/23 Ludwig Kniprath 

> Scott Marlowe schrieb:
>
>> On Sat, May 23, 2009 at 7:18 AM, Christophe  wrote:
>>
>>
>>> On May 23, 2009, at 9:13 AM, Daniel Verite wrote:
>>>
>>>
 I don't know why this query returns false:
> SELECT '20040506 070809.01'::timestamp(6) - '20010203
> 040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
> millisecond'::interval;
> If I just subtract the two timestamps, its result is the interval I
> specified.
> What may cause this?
>
>
 It works for me:

 test=> SELECT '20040506 070809.01'::timestamp(6) -
 '20010203 040506.007000'::timestamp(6)=
 '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval;
 ?column? --
 t
 (1 row)


>>> Could this be due to the OP's build of PG using floating point
>>> timestamps?
>>>
>>>
>>
>> That's what I'm thinking
>>
> Me too, a testquery-result on a Windows-System with version "PostgreSQL
> 8.3.0, compiled by Visual C++ build 1400":
>
> SELECT ('20040506 070809.01'::timestamp(6) - '20010203
> 040506.007000'::timestamp(6) - '1188 day 3 hour 3 minute 3 second 3
> millisecond'::interval) * 1e10;
>
> => -00:01:28.220986
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] psql Windows and variables

[Ivan Sergio Borgonovo]

I need to loop from DOS [sic] on a set of files to get them imported
into a table.

I noticed that psql is wrapped up in a import.bat

I wrote another bat that substantially do

rem import.bat
FOR %%f in (pattern) do "longpathtopsql.bat" --variable csvfile=%%f
-f some.sql


-- some.sql
\copy import.transaction from :csvfile


but I get 
some.sql:3: :csvfile: No such file or directory

I tried -v --set etc...

Same result.

How can I pass variables to a sql script?

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Asssociative Arrays: Best practices / snippets?

[Steve Atkins]

On May 23, 2009, at 2:25 AM, Stefan Keller wrote:

I have a use case where the I want to put an unforeseable number of  
key/value pairs in a column.

Now, PostgreSQL has arrays as first class types.
Are there any best practices and snippets (preferrably in plpgsql)  
for handling key/value pairs?


Take a look at "hstore". It's a data type in contrib that's intended  
to store a set of key, value

pairs in a single column.

It's well suited for the case where you're not searching on the keys  
and values. If you do
need to search on them then you can either index the hstore field with  
a gin or gist index,

or use a separate EAV table for the keys and values.

Cheers,
  Steve


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.3: timestamp subtraction

[Ludwig Kniprath]

Scott Marlowe schrieb:

On Sat, May 23, 2009 at 7:18 AM, Christophe  wrote:
  

On May 23, 2009, at 9:13 AM, Daniel Verite wrote:


I don't know why this query returns false:
SELECT '20040506 070809.01'::timestamp(6) - '20010203
040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
millisecond'::interval;
If I just subtract the two timestamps, its result is the interval I
specified.
What may cause this?


It works for me:

test=> SELECT '20040506 070809.01'::timestamp(6) -
'20010203 040506.007000'::timestamp(6)=
'1188 day 3 hour 3 minute 3 second 3 millisecond'::interval;
?column? --
t
(1 row)
  

Could this be due to the OP's build of PG using floating point timestamps?



That's what I'm thinking
Me too, a testquery-result on a Windows-System with version "PostgreSQL 
8.3.0, compiled by Visual C++ build 1400":


SELECT ('20040506 070809.01'::timestamp(6) - '20010203 
040506.007000'::timestamp(6) - '1188 day 3 hour 3 minute 3 second 3 
millisecond'::interval) * 1e10;


=> -00:01:28.220986

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.3: timestamp subtraction

[Scott Marlowe]

On Sat, May 23, 2009 at 7:18 AM, Christophe  wrote:
>
> On May 23, 2009, at 9:13 AM, Daniel Verite wrote:
>>>
>>> I don't know why this query returns false:
>>> SELECT '20040506 070809.01'::timestamp(6) - '20010203
>>> 040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
>>> millisecond'::interval;
>>> If I just subtract the two timestamps, its result is the interval I
>>> specified.
>>> What may cause this?
>>
>> It works for me:
>>
>> test=> SELECT '20040506 070809.01'::timestamp(6) -
>> '20010203 040506.007000'::timestamp(6)=
>> '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval;
>> ?column? --
>> t
>> (1 row)
>
> Could this be due to the OP's build of PG using floating point timestamps?

That's what I'm thinking.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.3: timestamp subtraction

[Christophe]

On May 23, 2009, at 9:13 AM, Daniel Verite wrote:

I don't know why this query returns false:
SELECT '20040506 070809.01'::timestamp(6) - '20010203
040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
millisecond'::interval;
If I just subtract the two timestamps, its result is the interval I
specified.
What may cause this?


It works for me:

test=> SELECT '20040506 070809.01'::timestamp(6) -
'20010203 040506.007000'::timestamp(6)=
'1188 day 3 hour 3 minute 3 second 3 millisecond'::interval;
?column? --
t
(1 row)


Could this be due to the OP's build of PG using floating point  
timestamps?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.3: timestamp subtraction

[Daniel Verite]

Havasvölgyi Ottó wrote:


I don't know why this query returns false:

SELECT '20040506 070809.01'::timestamp(6) - '20010203
040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
millisecond'::interval;
If I just subtract the two timestamps, its result is the interval I
specified.

What may cause this?


It works for me:

test=> SELECT '20040506 070809.01'::timestamp(6) -
 '20010203 040506.007000'::timestamp(6)=
 '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval;
?column? 
--

t
(1 row)

test=> select version();
	  version		
			
--- 
-
PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real 
(Ubuntu 4.3.2-1ubuntu11) 4.3.2

(1 row)

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Aggregate Function to return most common value for a column

[Benjamin Smith]

I've used this same concept in subqueries for a very long time. Doing this 
allows me to "dive in" and get other values from the joined table, rather than 
just the thing that we're getting the most of. 



- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
"I kept looking for somebody to solve the problem. 
Then I realized... I am somebody!" 

-- Author Unknown

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

[GENERAL] 8.3: timestamp subtraction

[Havasvölgyi Ottó]

Hi,

I don't know why this query returns false:

SELECT '20040506 070809.01'::timestamp(6) - '20010203
040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
millisecond'::interval;
If I just subtract the two timestamps, its result is the interval I
specified.

What may cause this?

Thanks,
Otto

Re: [GENERAL] Asssociative Arrays: Best practices / snippets?

[Filip Rembiałkowski]

2009/5/23 Stefan Keller 

> I have a use case where the I want to put an unforeseable number of
> key/value pairs in a column.
> Now, PostgreSQL has arrays as first class types.
> Are there any best practices and snippets (preferrably in plpgsql) for
> handling key/value pairs?
> -- S.
>


common relational practice is to use entity-attribute-value model to attach
any number of key/value pairs to any object, for example:
object( object_id, ... )
attribute ( attr_id, attr_name )
object_attribute ( object_id, attr_id, attr_value )


if your a lazy DBA and text key/values are all you need, did you look at
hstore? it might be enough.

my personal hstore experience is:
1) keep your key/values reasonably small; if you need structured data - do
not put it into hstore.
2) keep in mind that hstore indexing is limited, many search operations
require full table scan




-- 
Filip Rembiałkowski
JID,mailto:filip.rembialkow...@gmail.com
http://filip.rembialkowski.net/

Re: [GENERAL] Asssociative Arrays: Best practices / snippets?

[Leif B. Kristensen]

On Saturday 23. May 2009, Stefan Keller wrote:
>I have a use case where the I want to put an unforeseable number of
>key/value pairs in a column.
>Now, PostgreSQL has arrays as first class types.
>Are there any best practices and snippets (preferrably in plpgsql) for
>handling key/value pairs?
>-- S.

The «best practice» here is probably rethinking your design. Storing 
an «unforeseeable number of key/value pairs in a column» is a blatant 
violation of good database design principles.

Rodrigo posted this link a few hours ago:

http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-table-vs-many.html
-- 
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Code tables, conditional foreign keys?

[Alban Hertroys]

Here's an example of the value groups that were contained in the  
table:


fax status:
 pending, active, sent, error
department:
 office, accounting, it, legal, experts
deadline type:
 official, unofficial
...



Is it really advisable to put all these values into 70 separate tables
with the exact same layout? I don't quite see the benefit.


You could use the ENUM type for that (http://www.postgresql.org/docs/current/static/datatype-enum.html 
), although that works best if these values are really static. If  
users should be able to edit them they're probably not the best choice.


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a17ca8210091499713462!



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] performance tuning on Vista problem

[Alban Hertroys]

On May 23, 2009, at 3:55 AM, Murray Richardson wrote:


Hello postgres community,

I am running postgresql 8.3 on Vista 64 and trying to do some  
performance tuning to make better use of my system resources.   
Anytime I make any changes to the postgresql.conf file, I cannot  
connect to the server - I get a "Server Doesn't Listen" error.  I  
also tried via the Enterprise Tuning Wizard and I get the same  
problem.  I have to reinstall postgres to get things working again.   
I suspect this is a permission issue in Vista but I am not sure how  
to proceed.  I would appreciate any advice or insight into this.



Yes, that's probably a permission issue.

I recall someone with the same issue recently and I believe this was  
caused because that person didn't edit the postgresql files as the  
postgres user or didn't restart the service as that user. This caused  
files needed by postgres not to be accessible by the postgres user,  
which displayed the behaviour you describe above.


I don't know the details, I rarely even use Windows, but this is what  
I remember.


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a17c59d10091254110353!



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] duplicate rows in query

[Alban Hertroys]

On May 22, 2009, at 9:41 PM, Mark Watson wrote:


Hello all,
I have a perplexing problem which I cannot figure out. I have a  
somewhat

complex query that is returning two identical rows, where only one row
exists in the table. If I run a simpler query, I receive the one row  
as

desired.


...


Explain analyze:
"Nested Loop Left Join  (cost=0.00..256.51 rows=1 width=307) (actual
time=0.470..13.167 rows=2 loops=1)"
"  Join Filter: ((f_dossier.do_quote_client)::text =
(public.f_quote_client.qc_code)::text)"
"  ->  Nested Loop Left Join  (cost=0.00..255.38 rows=1 width=305)  
(actual

time=0.386..13.059 rows=1 loops=1)"
"Join Filter: ((f_dossier.do_categ)::text =
(f_code_cour.co_code)::text)"
"->  Nested Loop Left Join  (cost=0.00..254.13 rows=1  
width=296)

(actual time=0.333..13.002 rows=1 loops=1)"
"  Join Filter: ((f_dossier.do_avoc1)::text =
(f_avocat.av_code)::text)"
"  ->  Nested Loop Left Join  (cost=0.00..250.05 rows=1
width=281) (actual time=0.306..12.796 rows=1 loops=1)"
"Join Filter: ((f_dossier.doclno)::text =
(f_client.cl_no)::text)"
"->  Nested Loop Left Join  (cost=0.00..22.61  
rows=1

width=206) (actual time=0.149..0.164 rows=1 loops=1)"
"  Join Filter:  
((f_dossier.do_code_info)::text =

(public.f_quote_client.qc_code)::text)"
"  ->  Nested Loop Left Join   
(cost=0.00..21.56

rows=1 width=204) (actual time=0.124..0.135 rows=1 loops=1)"
"Join Filter:
((f_dossier.do_type_reclamation)::text =
(public.f_quote_client.qc_code)::text)"
"->  Nested Loop Left Join
(cost=0.00..20.52 rows=1 width=202) (actual time=0.092..0.100 rows=1
loops=1)"
"  Join Filter:
((f_dossier.do_site)::text = (f_site.si_id)::text)"
"  ->  Index Scan using dono_idx  
on
f_dossier  (cost=0.00..8.27 rows=1 width=134) (actual  
time=0.063..0..069

rows=1 loops=1)"
"Index Cond:  
((dono)::text =

'NT003-011'::text)"
"  ->  Seq Scan on f_site
(cost=0.00..11.00 rows=100 width=74) (actual time=0.003..0.003 rows=0
loops=1)"
"->  Seq Scan on f_quote_client
(cost=0.00..1.02 rows=2 width=4) (actual time=0.006..0.010 rows=2  
loops=1)"


There you go, there are two matching rows in f_quote_client and since  
you join on that you get two rows in your result set.




"  ->  Seq Scan on f_quote_client   
(cost=0.00..1.02

rows=2 width=4) (actual time=0.003..0.007 rows=2 loops=1)"
"->  Seq Scan on f_client  (cost=0.00..175.22  
rows=2321

width=61) (actual time=0.027..9.045 rows=2321 loops=1)"
"  ->  Seq Scan on f_avocat  (cost=0.00..3.48 rows=48  
width=18)

(actual time=0.008..0.096 rows=48 loops=1)"
"->  Seq Scan on f_code_cour  (cost=0.00..1.11 rows=11  
width=11)

(actual time=0.006..0.023 rows=11 loops=1)"
"  ->  Seq Scan on f_quote_client  (cost=0.00..1.02 rows=2 width=4)  
(actual

time=0.003..0.006 rows=2 loops=1)"
"Total runtime: 13.738 ms"

I have rebuilt all indexes to no avail and would love to know how to  
solve
this. I’ll be happy to provide any additional information. I’m  
currently on
the digest version and also will be unavailable until Tuesday, so  
please do

not consider this an emergency.

Thanks for your time,

Mark Watson


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general






Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a17c3ee10091470919307!



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Asssociative Arrays: Best practices / snippets?

[Stefan Keller]

I have a use case where the I want to put an unforeseable number of
key/value pairs in a column.
Now, PostgreSQL has arrays as first class types.
Are there any best practices and snippets (preferrably in plpgsql) for
handling key/value pairs?
-- S.

Re: [GENERAL] Code tables, conditional foreign keys?

[Scott Marlowe]

On Sat, May 23, 2009 at 12:27 AM, Conrad Lender  wrote:
> On 23/05/09 06:05, Rodrigo E. De León Plicet wrote:
 Is there a better way?
>>>
>>> Yeah, natural keys.
>>
>> +1.
>>
>> Also, what Ben described reeks of EAV.
>>
>> Ben, please read:
>>
>> http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-table-vs-many.html
>
> Ah yes, the great Celko. I had the honor of being "lectured" by him on a
> similar topic, and to tell the truth, I'm still unconvinced. I had
> inherited a generally well-designed database with a clear relational
> structure, apart from one table about which I was uncertain. This table
> collected several groups of attributes/values that were not necessarily
> related to each other, and could (should?) be split into separate
> tables. It was nowhere near as bad as the example in the linked article,
> and we did have check constraints in place. The values were used to
> classify and sometimes "tag" rows in other tables. I hesitated to break
> this table up, because that would have meant
>
>  - doubling the number of existing tables (70 to 140)
>  - significant code changes all over the place
>  - having a lot of very small tables with identical layout
>     "id" (surrogate key)
>     "entry" (string; used to refer to this row by the application)
>     "label" (string; visible on the front end)
>
> Here's an example of the value groups that were contained in the table:
>
> fax status:
>  pending, active, sent, error
> department:
>  office, accounting, it, legal, experts
> deadline type:
>  official, unofficial
> ...
>
> Using the "entry" field as natural keys would have been possible in some
> places, but not everywhere, and the labels still had to be editable by
> the users. Some of the "entry" strings were rather verbose (>40
> characters), which would have made the other tables a lot larger. It
> also didn't "feel right" to have so many tiny tables with only 2-5 rows.
>
> Is it really advisable to put all these values into 70 separate tables
> with the exact same layout? I don't quite see the benefit.

I've used EAV for some stuff, and it definitely has its place, but if
the data set in an EAV tableset is large enough, it becomes unweildy
quickly.  For large amounts of data, it's usually better to bite the
bullet and make a table for it.  Since the data in EAV tables have no
strong meaning in and of themself, it's often hard for another
developer to grok what someone was doing with those data.

However, the whole point of using natural keys is that if you need the
attributes from the master table that's FK'ed to a natural key lookup
table, then you don't have to actually hit any other table, just the
main one.  So, small to medium finite and well defined sets like
states etc. are coherent, but cost little retrieve.

Sure there's bound to be large less well defined sets that you don't
always want, and for those a surrogate key works just fine.

PostgreSQL doesn't do as well as some dbs with huge star schemas,
where everything is in its own table and you always join for
everything, once the number of tables gets really large.

I have a great deal of respect for Celko.  I don't always agree with
everything he says, but most of it makes a lot of sense to me.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] changing datatype from int to bigint quickly

[Ow Mun Heng]

Is there a method to do this without transversing the whole 20GB table?

What about manipulating the pg_attribute table and changing atttypid just
like we can manipulate atttypmod to change from varchar(4) to varchar(8)?

Thanks

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general