Re: [SQL] Junk queries with variables?
> > In pgadmins SQL-window SQL is the 'language' of choice. Or it is > rather the > only language. Thus if you intend to program plTk or PL/pgSQL, there's no > way around defining a function. > > (At first you have to define a new language in your schema) I'm a little confused, is there no way around this, or are you saying I need to use CREATE LANGUAGE to define a new language to use? Currently the only language I have for the DB is plpgsql. Thanks, Steve ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] SQL error: function round(double precision, integer) does not exist
I received the following error when executing a SQL statement: SQL error: ERROR: function round(double precision, integer) does not exist In statement: select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count, round((parameter*oe_count_matches(smiles,smarts)),2) as psa,tpsa(smiles) as ctpsa,tpsa from structure,tpsa where id < 237610 and oe_count_matches(smiles,smarts) > 0 order by id; The functions described at: http://www.postgresql.org/docs/7.4/static/functions-math.html show that round(numeric,int) should work ok. If I use round() without a second argument, it works OK, but this gives a loss of precision which I do not want. Can anyone help me with this? Thanks, TJ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Postgres 8 - problem: invalid input syntax for integer
> What number does '' represent?
'No response' value...
> Does that mean a string of '/2' should equal your number divided by two?
right, but it is never required.
> If not, why not?
because I use it to GROUP BY values.
> Who is providing an empty string where you've asked for a number, and
> why not trap this error (or store a NULL)?
You are certainly right. My problem concerns the compatibility of code
among postgres 8 and 7.2 that I wanted to maintain. The existing code
(data analysis) exploits the particularity that the null ('') becomes
0 (ok, no comment :) ) logically wrong but practically perfect!
[...CAST CODE...]
Thank you for the explicit-cast code, but I want reproduce it in
'database level' so I don't use explicit cast but IMPLICIT; everytime
updating integer fields with '' values it cast to (0 or NULL).
Best regards, Mauro
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Advanced SELECT
Richard Huxton schrieb: > Search the mailing-list archives for "custom aggregate concat" and > you'll quickly find an example of how to write your own custom aggregate > (like SUM()). > > Warning - I don't think you can guarantee the order of elements in the > aggregated sectors. Thank you very much. This was the right hint where to search. For the order I'll try to find some possibilities to write a function, that does a sort on this ;) We'll see... -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Wer Unrecht tut ist ungluecklicher als wer unrecht leidet. (Demokrit, um 460 v. Chr.) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Software for database-visualisation
Sean Davis schrieb: > If you mean literally visualizing the ERD, you can look at > SQL::Translator (on cpan) which can draw fairly complex ERDs and output > as graphics (I forget the supported formats) I'll have a look at it. Thank you both! -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Wer Unrecht tut ist ungluecklicher als wer unrecht leidet. (Demokrit, um 460 v. Chr.) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Software for database-visualisation
Maybe would you like http://www.dbvis.com/products/dbvis/ it's can vizualize. But maybe you want some CASE tool like Sybase PowerDesigner?? - Original Message - From: "Sean Davis" <[EMAIL PROTECTED]> To: "Richard Huxton" Cc: ; "Kai Hessing" <[EMAIL PROTECTED]> Sent: Thursday, February 24, 2005 1:04 PM Subject: Re: [SQL] Software for database-visualisation If you mean literally visualizing the ERD, you can look at SQL::Translator (on cpan) which can draw fairly complex ERDs and output as graphics (I forget the supported formats) Sean On Feb 24, 2005, at 3:17 AM, Richard Huxton wrote: Kai Hessing wrote: Another question: Which software are you using to visualize your database-structur. We're doing it with Quark, but are not very happy with this. Well, AutoDoc can generate HTML/Dia/other outputs http://www.rbt.ca/autodoc/index.html Might be worth checking the (freely available) Red-Hat db tools. Can't remember if there's a schema visualiser in there, but there might well be. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL error: function round(double precision, integer) does not exist
On Sun, Feb 27, 2005 at 03:26:07PM -0800, TJ O'Donnell wrote: > ERROR: function round(double precision, integer) does not exist [snip] > The functions described at: > http://www.postgresql.org/docs/7.4/static/functions-math.html > show that round(numeric,int) should work ok. ^^^ The two-argument form of round() expects the first argument to be numeric, not double precision. There's no implicit cast from double precision to numeric, so you'll have to use an explicit cast: SELECT ... round((expression)::numeric, 2) ... -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] diference in dates in minutes
On Mon, Feb 28, 2005 at 09:09:09 -0500,
Joel Fradkin <[EMAIL PROTECTED]> wrote:
> Yea I probably forgot respond to all. I agree (specialy for this topic).
>
> In any case, I have dates not time (dates with times).
It really helps if you use precise language when discussing problems.
date, time with time zone, time without time zone, timestamp with time zone,
and timestamp without time zone are all different types.
> I did not use datevar::date - date2::date, I did datevar - datevar2 and it
> appeared to work.
That can not give you a result that is an interval if datevar and datevar2
are actually dates. They must be some other type, probably a timestamp
of some sort.
> Since the dates I was comparing were over a year apart the number in secs
> was hard to verify. Soon as I get to debuggin the actual app where the time
> dif will be a few minutes I will let you know if it worked to do the
> date_part('epoch',date-date) returns in secs so /60.
If the date variables are of type timestamp with time zone you should
be OK. You probably want to test comparing dates in different time zones
(if you have different time offsets from GMT at different times of the year
at your locale, e.g. daylight savings vs standard time) to make sure you get
the expected result.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] SQL error: function round(double precision, integer) does
Thanks everyone. Your tips about casting my arg to round() as ::numeric worked just fine. I guess I was surprised that plpgsql didn't that on it's own! I'm used to too many forgiving c compilers, and such. TJ Christoph Haller wrote: TJ O'Donnell wrote: I received the following error when executing a SQL statement: SQL error: ERROR: function round(double precision, integer) does not exist In statement: select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count, round((parameter*oe_count_matches(smiles,smarts)),2) as psa,tpsa(smiles) as ctpsa,tpsa from structure,tpsa where id < 237610 and oe_count_matches(smiles,smarts) > 0 order by id; The functions described at: http://www.postgresql.org/docs/7.4/static/functions-math.html show that round(numeric,int) should work ok. If I use round() without a second argument, it works OK, but this gives a loss of precision which I do not want. Can anyone help me with this? Thanks, TJ And round(numeric,int) does work ok. The error message is telling you there is double precision argument where a numeric is expected. And with 7.4.5 it says in addition HINT: No function matches the given name and argument types. You may need to add explicit type casts. Try round((parameter*oe_count_matches(smiles,smarts))::numeric,2) Works for me with double precision arguments. Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] diference in dates in minutes
Yea I probably forgot respond to all. I agree (specialy for this topic).
In any case, I have dates not time (dates with times).
I did not use datevar::date - date2::date, I did datevar - datevar2 and it
appeared to work.
Since the dates I was comparing were over a year apart the number in secs
was hard to verify. Soon as I get to debuggin the actual app where the time
dif will be a few minutes I will let you know if it worked to do the
date_part('epoch',date-date) returns in secs so /60.
I appreciate your help and concern, it will be very important to us to
ensure we can do the proper calculations.
Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305
[EMAIL PROTECTED]
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.
-Original Message-
From: Bruno Wolff III [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 27, 2005 12:54 AM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] diference in dates in minutes
Discussions along this line should stay on the list so that other people
can learn from and add comments to the discussion.
On Sat, Feb 26, 2005 at 16:57:15 -0500,
Joel Fradkin <[EMAIL PROTECTED]> wrote:
> You sure?
> I thought date1::date - date2::date returns an integer of day's diff, but
> date -date returns an interval (least I can do a to_char on it and see
day's
> hours etc that were correct.
Then your "date" column is most likely a timestamp, not a date. That is
what you want anyway if you are trying to get a time difference in minutes.
That wouldn't make much sense for dates.
> Why are they depreciating the ability to look at an interval as a string
> anyhow? Is there an approved method of looking at an interval as a string
> replacing it?
I think because the current version does some odd things and no one has put
together a spec to replace it. You can ge formatted output using EXTRACT
and suitable further manipulation.
>
> Joel Fradkin
>
> Wazagua, Inc.
> 2520 Trailmate Dr
> Sarasota, Florida 34243
> Tel. 941-753-7111 ext 305
>
> [EMAIL PROTECTED]
> www.wazagua.com
> Powered by Wazagua
> Providing you with the latest Web-based technology & advanced tools.
> C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
> This email message is for the use of the intended recipient(s) and may
> contain confidential and privileged information. Any unauthorized review,
> use, disclosure or distribution is prohibited. If you are not the
intended
> recipient, please contact the sender by reply email and delete and destroy
> all copies of the original message, including attachments.
>
>
>
>
> -Original Message-
> From: Bruno Wolff III [mailto:[EMAIL PROTECTED]
> Sent: Saturday, February 26, 2005 4:16 PM
> To: Joel Fradkin
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] diference in dates in minutes
>
> On Sat, Feb 26, 2005 at 15:14:02 -0500,
> Joel Fradkin <[EMAIL PROTECTED]> wrote:
> > You probably want to convert the dates to timestamps, subtract them to
> > get an interval, extract the epoch to get timme in seconds and then
divide
> > by 60 to get time in minutes.
> >
> > The converting date to timestamp part isn't trivial. You need to decide
> > on what you mean when you do this. If you really have timestamps in the
> > first place, then you can skip the covernsion step.
> >
> > They are dates and I did find I could do date - date to give me an
> interval
> > date_part('epoch',date-date) returns in secs so /60
>
> date - date won't give you an interval, it will give you an integer of
some
> sort.
>
> > This appeared to work ok without converting to time stamps, but maybe I
am
> > missing it if it is not correct as the example I looked at was a large
> > difference. The app is analyzing Tlogs and the difference should never
be
> > too large, so I will further analyze it with real data.
> > As always I appreciate the help.
> > My real question is this an interval then and will it be depreciated
soon?
>
> The Interval type won't be depreciated. Using to_char to convert intervals
> to strings is being depreciated. This won;t cause a problem for extract
> or similar functions.
>
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] SQL error: function round(double precision, integer) does not
TJ O'Donnell wrote: > > I received the following error when executing a SQL statement: > > SQL error: > ERROR: function round(double precision, integer) does not exist > > In statement: > > select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count, > round((parameter*oe_count_matches(smiles,smarts)),2) as psa,tpsa(smiles) as > ctpsa,tpsa > from structure,tpsa > where id < 237610 > and oe_count_matches(smiles,smarts) > 0 > > order by id; > > The functions described at: > http://www.postgresql.org/docs/7.4/static/functions-math.html > show that round(numeric,int) should work ok. > If I use round() without a second argument, it works OK, but > this gives a loss of precision which I do not want. > > Can anyone help me with this? > > Thanks, > TJ > And round(numeric,int) does work ok. The error message is telling you there is double precision argument where a numeric is expected. And with 7.4.5 it says in addition HINT: No function matches the given name and argument types. You may need to add explicit type casts. Try round((parameter*oe_count_matches(smiles,smarts))::numeric,2) Works for me with double precision arguments. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL error: function round(double precision, integer) does
I got round(numeric,int) working OK, but it's got me thinking (a dangerous thing!). Is there some fundamental reason for round(dp) but round(numeric,int)? Shouldn't they be, at least, consistent, having round(numeric) or round(dp,int)? Am I missing something? Thanks, TJ Michael Fuhr wrote: On Sun, Feb 27, 2005 at 03:26:07PM -0800, TJ O'Donnell wrote: ERROR: function round(double precision, integer) does not exist [snip] The functions described at: http://www.postgresql.org/docs/7.4/static/functions-math.html show that round(numeric,int) should work ok. ^^^ The two-argument form of round() expects the first argument to be numeric, not double precision. There's no implicit cast from double precision to numeric, so you'll have to use an explicit cast: SELECT ... round((expression)::numeric, 2) ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Read count ?
Hi, Thanks Ragnar. This, and an other GROUP BY + name query within output got me there. SELECT DISTINCT news_id, news_header, segment, segment_id, count(*) FROM news_table NATURAL JOIN segments_table NATURAL JOIN read_history WHERE account_id = #Url.account_id# GROUP BY news_id, news_header, segment, segment_id ORDER BY count DESC ... SELECT segment_name FROM segments_table WHERE segment_id = #segment# #news_header# - #get_seg.segment_name# - #count# On Saturday 26 February 2005 15:24, you wrote: > On Thu, 2005-02-24 at 17:17 +0200, Aarni Ruuhimäki wrote: > > Hi, > > > > Could someone please give a hint on how to query the following neatly ? > > > > Get news from a news table that belong to a particular account, get > > segment name from segments table for each news item and read count from > > read history table that gets a news_id and timestamp insert every time > > the news is read. Display everything by news count, most read news first > > ? > > > > news_id 4, news_header, segment_name x, read 10 times > > news_id 2, news_header, segment_name y, read 8 times > > news_id 1, news_header, segment_name x, read 7 times > > news_id 3, news_header, segment_name x, read 0 times > > > > news_table: > > news_id, account_id, segment, news_header, ... > > > > segments_table: > > segment_id, account_id, segment_name > > > > read_history_table: > > history_id, news_id, timestamp > > how about: > > select news_id,news_header,segment_name,count(*) > from news_table > natural join segments_table > natural join read_history_table > where account_id=? > group by news_id,news_header,segment_name; > > ? > > gnari > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings -- This is a bugfree broadcast to you from **Kmail** on **Fedora Core 2** linux system -- Linux is like a wigwam - no windows, no gates and an apache inside. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Junk queries with variables?
- Original Message - From: "Steve Valaitis" <[EMAIL PROTECTED]> To: "KÖPFERL Robert" <[EMAIL PROTECTED]>; Sent: Thursday, February 24, 2005 12:15 PM Subject: Re: [SQL] Junk queries with variables? > In pgadmins SQL-window SQL is the 'language' of choice. Or it is rather the only language. Thus if you intend to program plTk or PL/pgSQL, there's no way around defining a function. (At first you have to define a new language in your schema) I'm a little confused, is there no way around this, or are you saying I need to use CREATE LANGUAGE to define a new language to use? Currently the only language I have for the DB is plpgsql. Yes. You need to install the language into the database using CREATE LANGUAGE and then you can use the language to create functions. Sean ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SQL error: function round(double precision, integer) does not exist
the round sintax is round(numeric,int) not round (double,int) you must cast the value into numeric: ex: round (cast(doublecolumn as numeric),2) should work ok Adrian Din, Om Computer & SoftWare On Sun, 27 Feb 2005 15:26:07 -0800, TJ O'Donnell <[EMAIL PROTECTED]> wrote: I received the following error when executing a SQL statement: SQL error: ERROR: function round(double precision, integer) does not exist In statement: select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count, round((parameter*oe_count_matches(smiles,smarts)),2) as psa,tpsa(smiles) as ctpsa,tpsa from structure,tpsa where id < 237610 and oe_count_matches(smiles,smarts) > 0 order by id; The functions described at: http://www.postgresql.org/docs/7.4/static/functions-math.html show that round(numeric,int) should work ok. If I use round() without a second argument, it works OK, but this gives a loss of precision which I do not want. Can anyone help me with this? Thanks, TJ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SQL error: function round(double precision, integer) does not exist
TJ,
> SQL error:
> ERROR: function round(double precision, integer) does not exist
> http://www.postgresql.org/docs/7.4/static/functions-math.html
> show that round(numeric,int) should work ok.
> If I use round() without a second argument, it works OK, but
> this gives a loss of precision which I do not want.
NUMERIC and FLOAT are different data types. Do:
round({value}::NUMERIC, {places})
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Postgres 8 - problem: invalid input syntax for integer
mauro wrote:
What number does '' represent?
'No response' value...
Would've been better to have a genuine response_provided flag, but then
you obviously know that.
Who is providing an empty string where you've asked for a number, and
why not trap this error (or store a NULL)?
You are certainly right. My problem concerns the compatibility of code
among postgres 8 and 7.2 that I wanted to maintain. The existing code
(data analysis) exploits the particularity that the null ('') becomes
0 (ok, no comment :) ) logically wrong but practically perfect!
Your best choice is probably to tweak your application and translate ''
to NULL? Then you could add a before trigger to the table to replace
NULL with 0.
[...CAST CODE...]
Thank you for the explicit-cast code, but I want reproduce it in
'database level' so I don't use explicit cast but IMPLICIT; everytime
updating integer fields with '' values it cast to (0 or NULL).
If you can't do the above, you've got three options:
1. Stay with PG version 7.2
2. Write your own type, with in/out functions that map '' to 0
3. Hack the code to replace the ''=>0 conversion - you could probably
identify the old code from CVS.
Obviously the null+trigger option is better than these three. Of these
three though, number 2 is probably the cleanest solution.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] SQL error: function round(double precision, integer) does
TJ O'Donnell wrote: I received the following error when executing a SQL statement: SQL error: ERROR: function round(double precision, integer) does not exist In statement: select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count, round((parameter*oe_count_matches(smiles,smarts)),2) as round((...)::numeric, 2) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [despammed] [SQL] SQL error: function round(double precision, integer) does not exist
am 27.02.2005, um 15:26:07 -0800 mailte TJ O'Donnell folgendes: > I received the following error when executing a SQL statement: > > SQL error: > ERROR: function round(double precision, integer) does not exist > > In statement: > > select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as > count, > round((parameter*oe_count_matches(smiles,smarts)),2) as > psa,tpsa(smiles) as ctpsa,tpsa > from structure,tpsa > where id < 237610 > and oe_count_matches(smiles,smarts) > 0 try ... round((parameter*oe_count_matches(smiles,smarts))::numeric,2) Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] AutoCommit and DDL
On Sun, Feb 27, 2005 at 11:55:37AM -0600, Don Drake wrote:
> I know it's not failing, I have the server logging the commands and
> there are no errors.
>
> The only change made was turning AutoCommit on.
Have you used any of DBI's tracing capabilities? Could you post a
simple test case? The following works for me with Perl 5.8.6, DBI
1.47, DBD::Pg 1.32, and PostgreSQL 7.4.7 on FreeBSD 4.11-STABLE:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect("dbi:Pg:dbname=test", "mfuhr", "", {AutoCommit => 0});
$dbh->do("CREATE TABLE foo (x integer)");
$dbh->commit;
$dbh->disconnect;
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] AutoCommit and DDL
On Sun, Feb 27, 2005 at 07:55:35PM -0600, Don Drake wrote: > The problem has to do with multiple concurrent connections to the > server causing problems. I've removed the concurrent connections and > now this works. Strange. Can you elaborate? Can you describe the scenario with enough detail that somebody else could attempt to duplicate it? It's certainly possible to issue DDL statements concurrent with other connections, so we still haven't identified what's really causing the problem. Investigation shouldn't end until "strange" becomes "aha!" -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] diference in dates in minutes
Sorry you are correct again it is TimeStamp not date.
So maybe that is why it appeared to work ok.
I will do as you suggest and play around with it before I accept it is a
perfect solution, but it appeared to do what I was looking for (figure the
difference in minutes).
Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305
[EMAIL PROTECTED]
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Bruno Wolff III
Sent: Monday, February 28, 2005 10:25 AM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] diference in dates in minutes
On Mon, Feb 28, 2005 at 09:09:09 -0500,
Joel Fradkin <[EMAIL PROTECTED]> wrote:
> Yea I probably forgot respond to all. I agree (specialy for this topic).
>
> In any case, I have dates not time (dates with times).
It really helps if you use precise language when discussing problems.
date, time with time zone, time without time zone, timestamp with time zone,
and timestamp without time zone are all different types.
> I did not use datevar::date - date2::date, I did datevar - datevar2 and it
> appeared to work.
That can not give you a result that is an interval if datevar and datevar2
are actually dates. They must be some other type, probably a timestamp
of some sort.
> Since the dates I was comparing were over a year apart the number in secs
> was hard to verify. Soon as I get to debuggin the actual app where the
time
> dif will be a few minutes I will let you know if it worked to do the
> date_part('epoch',date-date) returns in secs so /60.
If the date variables are of type timestamp with time zone you should
be OK. You probably want to test comparing dates in different time zones
(if you have different time offsets from GMT at different times of the year
at your locale, e.g. daylight savings vs standard time) to make sure you get
the expected result.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] table constraints
Hi,
I am running PostgreSQL 7.4.7. I am having some issues with a
constraint for one of my database tables. The code snippet below
outlines the code and its output (output is commented).
In this case each bldg has an owner associated to it, and each animal
lives in some bldg. Each owner has exactly one own favorite animal out
of all the bldgs owned by him. So the constraint added to each zoo row
is that the boolean field 'favorite' is true for exactly once for each
group of animals in the zoo that have a common owner.
The unique_favorite(text) function is created to help with this
constraint. The function returns what it is supposed to (see select
statement in the code), but doesn't hold when used as a constraint.
There may be a better way to do this, but I'm not looking for that right
now. I would like to know if I am using this constraint wrong, or if
there is there something wrong with the table constraints in the
database system.
Thanks,
Casey
-- Code snippet ---
CREATE TABLE owner (owner varchar(50));
-- CREATE TABLE
INSERT INTO owner VALUES ('steve');
-- INSERT 13193166 1
CREATE TABLE bldg (bldg varchar(50), owner varchar(50));
-- CREATE TABLE
INSERT INTO bldg VALUES ('bldg1', 'steve');
-- INSERT 13193169 1
CREATE TABLE zoo (animal varchar(50), bldg varchar(50), favorite
boolean);
-- CREATE TABLE
CREATE OR REPLACE FUNCTION unique_favorite(text) RETURNS BOOLEAN AS '
DECLARE
temp RECORD;
BEGIN
SELECT into temp * FROM
(SELECT b.owner, sum(CASE WHEN favorite = TRUE THEN 1 ELSE 0 END) AS
num_favorites
FROM (SELECT * FROM zoo WHERE animal = $1) z INNER JOIN bldg b
USING(bldg) GROUP BY b.owner) sub
WHERE sub.num_favorites <> 1;
RETURN NOT FOUND;
END;
' LANGUAGE plpgsql;
-- CREATE FUNCTION
ALTER TABLE zoo ADD check(unique_favorite(animal));
-- ALTER TABLE
INSERT into zoo VALUES ('monkey', 'bldg1', false);
-- INSERT 13193173 1
-- (This shouldn't be allowed!!!)
SELECT *, unique_favorite(animal) FROM zoo;
-- animal | bldg | favorite | unique_favorite
-- +---+--+-
-- monkey | bldg1 | f| f
INSERT into zoo VALUES ('monkey', 'bldg1', false);
-- ERROR: new row for relation "zoo" violates check constraint "$1"
INSERT into zoo VALUES ('monkey', 'bldg1', true);
-- ERROR: new row for relation "zoo" violates check constraint "$1"
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] table constraints
On Mon, Feb 28, 2005 at 11:28:30 -0800, "Casey T. Deccio" <[EMAIL PROTECTED]> wrote: > > In this case each bldg has an owner associated to it, and each animal > lives in some bldg. Each owner has exactly one own favorite animal out > of all the bldgs owned by him. So the constraint added to each zoo row > is that the boolean field 'favorite' is true for exactly once for each > group of animals in the zoo that have a common owner. One way to do this kind of thing is to add a UNIQUE key to the table that holds the animal - table relation of the animal and table and add a UNIQUE key for the building table of the building and owner. Then in the owner table add favorite animal building and favorite animal columns with the property NOT NULL. Then add two deferable foreign keys (owner, favorite animal building) to the building table and (favorite animal building, favorite animal) to the animal - building table. When modifying data you want to change favorites before changing the underlying tables (where a building or animal change affects someones favorites). P.S. in your example you used varchar(50) for the text strings. Using 'text' is better unless there is a business rule limiting the name lengths to 50 or you expect to port the application to another rdbms. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] table constraints
On Mon, 2005-02-28 at 13:20 -0700, Bruno Wolff III wrote: > On Mon, Feb 28, 2005 at 11:28:30 -0800, > "Casey T. Deccio" <[EMAIL PROTECTED]> wrote: > > > > In this case each bldg has an owner associated to it, and each > animal > > lives in some bldg. Each owner has exactly one own favorite animal > out > > of all the bldgs owned by him. So the constraint added to each zoo > row > > is that the boolean field 'favorite' is true for exactly once for > each > > group of animals in the zoo that have a common owner. > > One way to do this kind of thing is to add a UNIQUE key to the table > that holds the animal - table relation of the animal and table and add > a UNIQUE key for the building table of the building and owner. > Then in the owner table add favorite animal building and favorite > animal columns with the property NOT NULL. Then add two deferable > foreign keys (owner, favorite animal building) to the building table > and (favorite animal building, favorite animal) to the animal - > building > table. When modifying data you want to change favorites before > changing > the underlying tables (where a building or animal change affects > someones > favorites). > Thanks for the input. This may work in the example I've given. However, the example I provided was contrived and was used merely to show the discrepancy that I'm finding with using the function as a constraint. In the larger example, things are a bit more complex, and I've found using such a constraint a better fit for now for the problem I'm working with. That said, I'd like to know why the constraint I provided isn't working with the corresponding example. > P.S. in your example you used varchar(50) for the text strings. Using > 'text' > is better unless there is a business rule limiting the name lengths to > 50 > or you expect to port the application to another rdbms. Thanks for the tip. Good point. I've never really known when to use text over varchar, so though I began using varchar for potential later porting to other rdbms, and since then it's just been tradition...:) Casey ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL error: function round(double precision, integer) does
"TJ O'Donnell" <[EMAIL PROTECTED]> writes: > Is there some fundamental reason for round(dp) but round(numeric,int)? I think the main argument against supporting round(dp,int) is that the result would be inherently inexact (at least for int>0). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] AutoCommit and DDL
I don't think it would be easy to duplicate since our code base is quite extensive. Basically, what was happening was a script would first open a database connection (AutoCommit turned off by default), create a few objects (that also opened independent db connections), the objects would run queries so they have data populated, an insert is done and committed, then we call a generic function that will create a new table (using inherits, part of our partitioning) as well as adding indexes and constraints to this new table. It would get to a point in the function where it was adding a FK constraint and every query against the table would "hang" which appeared to be some exclusive lock not being released. Activity on the DB would be 100% idle during this period, the alter table never came back so we killed it each time. I commented out the code doing the FK constraint add and everything worked just fine. As a test I moved the partition function call to the beginning of the script (before the objects were created) and it worked just fine. I then changed the object declarations passing in the single DB handle, and every now works just fine. I ran DBI traces and everything looked just fine. This was a strange problem, I'm just happy everything is working. -Don On Sun, 27 Feb 2005 20:33:55 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Sun, Feb 27, 2005 at 07:55:35PM -0600, Don Drake wrote: > > > The problem has to do with multiple concurrent connections to the > > server causing problems. I've removed the concurrent connections and > > now this works. Strange. > > Can you elaborate? Can you describe the scenario with enough detail > that somebody else could attempt to duplicate it? It's certainly > possible to issue DDL statements concurrent with other connections, > so we still haven't identified what's really causing the problem. > Investigation shouldn't end until "strange" becomes "aha!" > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ http://www.MailLaunder.com/ 312-560-1574 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] table constraints
On 2005-02-28, "Casey T. Deccio" <[EMAIL PROTECTED]> wrote: > However, the example I provided was contrived and was used merely to > show the discrepancy that I'm finding with using the function as a > constraint. In the larger example, things are a bit more complex, and > I've found using such a constraint a better fit for now for the problem > I'm working with. > > That said, I'd like to know why the constraint I provided isn't working > with the corresponding example. The CHECK is obviously being evaluated prior to the actual insertion of the record, whereas the logic of your function clearly expects to be evaluated after the insertion. Allowing non-immutable functions in CHECK is probably an error, since it can lead to tables which can not be dumped+restored (consider in your example what happens when the constraint becomes false as a result of deleting a row - at that point, a dump and restore of the table will fail, since the constraint can not be defined after loading the data if it is violated by that data). Using triggers is a more reliable way to do this sort of thing - at least then it is clear that you are checking the data only at the time of modification, whereas CHECK constraints are declarative constraints which are expected to be true at all times. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] AutoCommit and DDL
On 2005-03-01, Don Drake <[EMAIL PROTECTED]> wrote: > I don't think it would be easy to duplicate since our code base is > quite extensive. > > Basically, what was happening was a script would first open a database > connection (AutoCommit turned off by default), create a few objects > (that also opened independent db connections), the objects would run > queries so they have data populated, an insert is done and committed, > then we call a generic function that will create a new table (using > inherits, part of our partitioning) as well as adding indexes and > constraints to this new table. It would get to a point in the > function where it was adding a FK constraint and every query against > the table would "hang" which appeared to be some exclusive lock not > being released. Activity on the DB would be 100% idle during this > period, the alter table never came back so we killed it each time. I > commented out the code doing the FK constraint add and everything > worked just fine. This sounds as though your application deadlocked against itself - by using multiple connections without autocommit, you can easily get into situations where you are waiting for completion on one connection, which is blocked waiting for a lock held by another connection - the lock remains until the second connection commits, which never happens since the app is waiting on the first. The DB can't detect this as a deadlock because it does not know that one session is waiting on another on the client side; deadlock detection considers only sessions waiting _inside the server_. This situation isn't specific to DDL, but is easier to produce that way since most DDL operations acquire very high level locks (often AccessExclusive, which blocks queries). > As a test I moved the partition function call to the beginning of the > script (before the objects were created) and it worked just fine. I > then changed the object declarations passing in the single DB handle, > and every now works just fine. This is consistent with it being a client-side deadlock. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] table constraints
On Mon, 2005-02-28 at 18:01 -0700, Andrew - Supernews wrote: > The CHECK is obviously being evaluated prior to the actual insertion > of > the record, whereas the logic of your function clearly expects to be > evaluated after the insertion. > I finally came to that conclusion just a few minutes before I got this email. However, it was your email that made it quite clear why this was. > Allowing non-immutable functions in CHECK is probably an error, since > it > can lead to tables which can not be dumped+restored (consider in your > example what happens when the constraint becomes false as a result of > deleting a row - at that point, a dump and restore of the table will > fail, > since the constraint can not be defined after loading the data if it > is > violated by that data). > Using triggers is a more reliable way to do this sort of thing - at > least > then it is clear that you are checking the data only at the time of > modification, whereas CHECK constraints are declarative constraints > which > are expected to be true at all times. > This is a very good point. I've now implemented the check with a trigger, and it works very well. Thanks for the input and the logic. Casey ---(end of broadcast)--- TIP 8: explain analyze is your friend
