Re: [SQL] question about partitioning
There is no partitioning by size that I know of but at: http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html there is very good documentation on the topic. As of this last weekend I had myself to do some testing with partitioning in Postgres 8.4. I had 7000 items. For each of them I stored 6 variables (smallint) for each hour quarter. Test data was generated for 5 years. That gave me 210336 records per id per 5 years period and a total of 1,472,352,000 records. This is taking about 33MB per partition table and a total of 231GB. Doing something like a grouping by the id and summing up the values of 10 ids takes about 2.5 seconds, which looks to me like a quite a nice performance. Petru Ghita On 24/06/2010 15:05, Joshua Gooding wrote: > rules for 0x7CF29D04.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature
Re: [SQL] string functions and operators
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
For the record if you'd like to use regexp:
select substring('201.123' from $$[0-9]*$$);
and
select substring('201.1232' from $$\.([0-9]*)$$);
On 23/03/2010 4:42, Petru Ghita wrote:
>
> select 0.341*pow(10,length(0.341::text)-2);
>
> 2 is a constat that stands for the '0.' part of the string
> representing the decimal part of the number.
>
>
> Petru Ghita
>
> On 23/03/2010 3:16, Neil Stlyz wrote:
>> This is good, however, I need
> only the numbers to the right of the
>
>> decimal point
>
>
>
>> so if my number if 17.2
>
>
>
>> I would need one query that would return 17 (your function
> will
>
>> do that)
>
>
>
>> and the second query would return: 2
>
>
>
>> not 0.2
>
>
>
>> just 2
>
>
>
>> Does that make sense?
>
>
>
>
> --
>
>
>
>
>
>
>
>
*From:* Petru Ghita
>> *To:* Neil Stlyz
> ; pgsql-sql mailing list
>
>> *Sent:* Mon, March 22, 2010
> 8:08:30 PM
>
>> *Subject:* Re: [SQL] string functions and operators
>
>
>
>> For numeric data types use:
>
>
>
>> http://www.postgresql.org/docs/8.4/static/functions-math.html
>
>
>
>> You could then use|floor|(dp or numeric)|| for example:
>
>
>
>> postgres=# select floor(71.912); floor --- 71 (1 row)
>
>
>
>> postgres=# select 71.912-floor(71.912); ?column? --
>> 0.912
>
>
>
>
>
>> But as you might have negative numbers in there I guess you
> should
>
>> abs() the values like in:
>
>
>
>> postgres=# select abs(71.912)-floor(abs(71.912)); ?column?
>
>> -- 0.912
>
>
>
>
>
>> postgres=# select abs(-171.912)-floor(abs(-171.912)); ?column?
>
>> -- 0.912 (1 row)
>
>
>
>
>
>
>
>
>
>
>
>
>
>> On 23/03/2010 2:50, Petru Ghita wrote:
>
>>> That field of yours... what type is it? Is it TEXT? is it a
>
>>> numeric type? If it's TEXT, why don't you make it say...
>
>>> NUMERIC(/10/, /6///)?
>
>
>
>>>
>
>
> http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
On 23/03/2010 2:20, Neil Stlyz wrote:
>>>> Hello,
>
>
>
>
>
>
>
>>>> I have a dilema and I was hoping someone here may offer
>
>>>> guidance
>
>
>
>>>> or assistance. I bet this is a very simple question for
>
>> someone
>
>
>
>>>> out there but I am having problems coming up with a
> solution.
>
>>> Here
>
>
>
>>>> it is...
>
>
>
>
>
>
>
>>>> suppose I have a field with the following values:
>
>
>
>
>
>
>
>>>> 77.1 77.2 134.1 134.2 134.3 5.1 5.2
>
>
>
>
>
>
>
>>>> I need two seperate SELECT queries. One would return the
>
>>> following
>
>
>
>>>> values (everything left of the decimal point)
>
>
>
>
>
>
>
>>>> 77 77 134 134 5 5
>
>
>
>
>
>
>
>>>> The second query would return all of the values to the
> right
>
>>>> of
>
>
>
>>>> the decimal point:
>
>
>
>
>
>
>
>>>> 1 2 1 2 3 1 2
>
>
>
>
>
>
>
>
>
>
>
>>>> Now, I have been using the following information
> (although
>
>>>> very
>
>
>
>>>> Greek) to try to solve this problem:
>
>
>
>
>
>
>
>
>
>
>
>
> http://www.postgresql.org/docs/current/static/functions-string.html
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
And I have been playing around with the syntax of the following:
>
>
>
>
>
>
>>>> substring('112.5' from '%#"___.#"_' for '#')
>
>
>
>
>
>
>
>>>> but the aforementioned is not quite working out... can
> someone
>
>
>
>>>> please show me a string function that will produce the
> desired
>
>
>
>>>> results?
>
>
>
>
>
>
>
>>>> Thanks! ~n
>
>
>
>
>
>
>
>
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkuoOw4ACgkQt6IL6XzynQTnowCgyBRLh7iaJR4sC5Rs2zGgXxXh
vC8An1Yvruvz0IdFF86dN5bQUIESmv8m
=TUxh
-END PGP SIGNATURE-
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] string functions and operators
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
select 0.341*pow(10,length(0.341::text)-2);
2 is a constat that stands for the '0.' part of the string
representing the decimal part of the number.
Petru Ghita
On 23/03/2010 3:16, Neil Stlyz wrote:
> This is good, however, I need only the numbers to the right of the
> decimal point
>
> so if my number if 17.2
>
> I would need one query that would return 17 (your function will
> do that)
>
> and the second query would return: 2
>
> not 0.2
>
> just 2
>
> Does that make sense?
>
> ------
>
>
>
*From:* Petru Ghita
> *To:* Neil Stlyz ; pgsql-sql mailing list
> *Sent:* Mon, March 22, 2010 8:08:30 PM
> *Subject:* Re: [SQL] string functions and operators
>
> For numeric data types use:
>
> http://www.postgresql.org/docs/8.4/static/functions-math.html
>
> You could then use|floor|(dp or numeric)|| for example:
>
> postgres=# select floor(71.912); floor --- 71 (1 row)
>
> postgres=# select 71.912-floor(71.912); ?column? -- 0.912
>
>
> But as you might have negative numbers in there I guess you should
> abs() the values like in:
>
> postgres=# select abs(71.912)-floor(abs(71.912)); ?column?
> -- 0.912
>
>
> postgres=# select abs(-171.912)-floor(abs(-171.912)); ?column?
> -- 0.912 (1 row)
>
>
>
>
>
>
> On 23/03/2010 2:50, Petru Ghita wrote:
>> That field of yours... what type is it? Is it TEXT? is it a
>> numeric type? If it's TEXT, why don't you make it say...
>> NUMERIC(/10/, /6///)?
>
>>
> http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
>
>
>
>
>
>
>
On 23/03/2010 2:20, Neil Stlyz wrote:
>>> Hello,
>
>
>
>>> I have a dilema and I was hoping someone here may offer
>>> guidance
>
>>> or assistance. I bet this is a very simple question for
> someone
>
>>> out there but I am having problems coming up with a solution.
>> Here
>
>>> it is...
>
>
>
>>> suppose I have a field with the following values:
>
>
>
>>> 77.1 77.2 134.1 134.2 134.3 5.1 5.2
>
>
>
>>> I need two seperate SELECT queries. One would return the
>> following
>
>>> values (everything left of the decimal point)
>
>
>
>>> 77 77 134 134 5 5
>
>
>
>>> The second query would return all of the values to the right
>>> of
>
>>> the decimal point:
>
>
>
>>> 1 2 1 2 3 1 2
>
>
>
>
>
>>> Now, I have been using the following information (although
>>> very
>
>>> Greek) to try to solve this problem:
>
>
>
>
>
> http://www.postgresql.org/docs/current/static/functions-string.html
>
>
>
>
>
>
>
And I have been playing around with the syntax of the following:
>
>
>
>>> substring('112.5' from '%#"___.#"_' for '#')
>
>
>
>>> but the aforementioned is not quite working out... can someone
>
>>> please show me a string function that will produce the desired
>
>>> results?
>
>
>
>>> Thanks! ~n
>
>
>
>
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkuoOKoACgkQt6IL6XzynQSVFgCgvUGRoBgCwj2UDa3M9sfF6U3s
Jm8AoMTL7Vb9ehj31y3Lv0PaNYV5tJhX
=vITl
-END PGP SIGNATURE-
Re: [SQL] string functions and operators
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
For numeric data types use:
http://www.postgresql.org/docs/8.4/static/functions-math.html
You could then use|floor|(dp or numeric)|| for example:
postgres=# select floor(71.912);
floor
- ---
71
(1 row)
postgres=# select 71.912-floor(71.912);
?column?
- --
0.912
But as you might have negative numbers in there I guess you should
abs() the values like in:
postgres=# select abs(71.912)-floor(abs(71.912));
?column?
- --
0.912
postgres=# select abs(-171.912)-floor(abs(-171.912));
?column?
- --
0.912
(1 row)
On 23/03/2010 2:50, Petru Ghita wrote:
> That field of yours... what type is it? Is it TEXT? is it a numeric
> type? If it's TEXT, why don't you make it say... NUMERIC(/10/,
> /6///)?
>
> http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
>
>
>
>
On 23/03/2010 2:20, Neil Stlyz wrote:
>> Hello,
>
>
>
>> I have a dilema and I was hoping someone here may offer guidance
>
>> or assistance. I bet this is a very simple question for someone
>
>> out there but I am having problems coming up with a solution.
> Here
>
>> it is...
>
>
>
>> suppose I have a field with the following values:
>
>
>
>> 77.1 77.2 134.1 134.2 134.3 5.1 5.2
>
>
>
>> I need two seperate SELECT queries. One would return the
> following
>
>> values (everything left of the decimal point)
>
>
>
>> 77 77 134 134 5 5
>
>
>
>> The second query would return all of the values to the right of
>
>> the decimal point:
>
>
>
>> 1 2 1 2 3 1 2
>
>
>
>
>
>> Now, I have been using the following information (although very
>
>> Greek) to try to solve this problem:
>
>
>
>
> http://www.postgresql.org/docs/current/static/functions-string.html
>
>
>
>
> And I have been playing around with the syntax of the following:
>
>
>
>> substring('112.5' from '%#"___.#"_' for '#')
>
>
>
>> but the aforementioned is not quite working out... can someone
>
>> please show me a string function that will produce the desired
>
>> results?
>
>
>
>> Thanks! ~n
>
>
>
>
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkuoIp4ACgkQt6IL6XzynQQ9igCfRjfOhKXjYZ4gaP3b/4qYqswb
qXMAoJcXbdB3BvCSJ7QH2PwAPMZpAdib
=OY7b
-END PGP SIGNATURE-
Re: [SQL] string functions and operators
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
That field of yours... what type is it?
Is it TEXT? is it a numeric type?
If it's TEXT, why don't you make it say... NUMERIC(/10/, /6///)?
http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
On 23/03/2010 2:20, Neil Stlyz wrote:
> Hello,
>
> I have a dilema and I was hoping someone here may offer guidance
> or assistance. I bet this is a very simple question for someone
> out there but I am having problems coming up with a solution. Here
> it is...
>
> suppose I have a field with the following values:
>
> 77.1 77.2 134.1 134.2 134.3 5.1 5.2
>
> I need two seperate SELECT queries. One would return the following
> values (everything left of the decimal point)
>
> 77 77 134 134 5 5
>
> The second query would return all of the values to the right of
> the decimal point:
>
> 1 2 1 2 3 1 2
>
>
> Now, I have been using the following information (although very
> Greek) to try to solve this problem:
>
> http://www.postgresql.org/docs/current/static/functions-string.html
>
> And I have been playing around with the syntax of the following:
>
> substring('112.5' from '%#"___.#"_' for '#')
>
> but the aforementioned is not quite working out... can someone
> please show me a string function that will produce the desired
> results?
>
> Thanks! ~n
>
>
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkuoHlIACgkQt6IL6XzynQTJ/ACfX4mSteAz9CmZLnPCayz+jXQI
IhoAnA7qrFHNmRVObfSvE+YXZ0OKr3MS
=wvB9
-END PGP SIGNATURE-
Re: [SQL] list of all months
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 One approach could be: You build a table with month information over which you are willing to show data from another table. Then you just cross join your data table and the data in your month table. Here is some code I use for generating the table that holds all the month I care about. DROP TABLE IF EXISTS tmp_lookup_months; CREATE TABLE tmp_lookup_months (month_year_str varchar(7) NOT NULL, first_day_of_month DATE NOT NULL, month INTEGER NOT NULL, year INTEGER NOT NULL, PRIMARY KEY (first_day_of_month, year), CONSTRAINT valid_date CHECK ( (EXTRACT (YEAR FROM first_day_of_month)::integer = year) AND (EXTRACT (MONTH FROM first_day_of_month)::integer = month) AND (EXTRACT (MONTH FROM first_day_of_month) > 0) AND (EXTRACT (MONTH FROM first_day_of_month) < 13) AND (EXTRACT (DAY FROM first_day_of_month) = 01) AND (month_year_str) like (CASE WHEN month <= 9 then cast (year::text ||'-0'|| month::text as char(7)) else cast (year::text||'-'|| month::text as char(7)) end) ) ); INSERT INTO tmp_lookup_months select month_year_str, first_day_of_month, month, year from ( select month, year, CASE WHEN month <= 9 then cast (year::text ||'-0'|| month::text as char(7)) else cast (year::text||'-'|| month::text as char(7)) end as month_year_str, cast (year::text||'-'||month||'-1' as date) as first_day_of_month from generate_series(1990, 2090) as year cross join generate_series(1,12) as month order by year, month ) as t1; What is nice about this approach is that you can easily change the granularity of the time over which you are willing to show the info so you can create a second table with a trimester list for example. Then say you have your data in a table called mydata. select ... from mydata CROSS JOIN tmp_lookup_months as ym where( ym.year >= $1 and ym.year <= $2 and my_intersection_function(start_date, end_date, ym.month, ym.year)>0 ) order by ... So I'm assuming here that in the mydata table you have at least 4 columns: id, start_date, end_date, some_data. some_data field probably only makes sense over the start_date to end_date interval. So in your select query you'll most likely need an aggregate function. If you could provided a more complete description of what you are trying to achive I might be able to further help. Petru Ghita On 08/03/2010 13:25, query wrote: > Hi, > > I want to display data for all days in a month even if no data > exists for that month. Some of the days in a month might not have > any data at all. With normal query, we can display days only if > data exists.But I want to display rows for all days in a month with > blank data for non-existing day in database. > > How can this be achieved ? > > <http://sigads.rediff.com/RealMedia/ads/click_nx.ads/www.rediffmail.com/signatureline@middle?> -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkueqZsACgkQt6IL6XzynQT+rgCguhFx6qzH3sgiti3O5zaqVQYS ra4Anjz1C8hS5YC6jRVD9coV6j1AxpPv =OoAd -END PGP SIGNATURE-
Re: [SQL] Does IMMUTABLE property propagate?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Documentation states: IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value. "..immediately replaced with the function value" doesn't mean that the results of a previously evaluated function for the same parameters are stored and reused? The problem here is exactly about evaluating the expression several times as the result is exactly the same for all the columns in the query. Greg Stark wrote: > > The immutable property had nothing to do with caching results. > Postgres never caches the results of functions. The immutable > property is used top determine if it's safe to use indexes or other > plans that avoid evaluating an expression repeatedly. > >> On 6 Mar 2010 02:45, "Petru Ghita" > <mailto:petr...@venaver.info>> wrote: >> > Given f1(x) as IMMUTABLE and f2(x) as IMMUTABLE, and f3(f1,f2) as > IMMUTABLE, does the query planner cache the result of f3 and reuse > it or if you want to get a little more speed you better explicitly > define yourself f3 as IMMUTABLE? > > I had an aggregate query like: > > select id, sum(p1*f1(a)/f2(b) as r1, sum(p2*f1(a)/f2(b) as r2, ... > sum(pn*f1(a)/f2(b) as rn > > ... group by id; > > Where f1(x) and f2(x) were defined as IMMUTABLE. > > By the experiments I ran looks like after defining a new function > f3(a,b):= f1(a)/f2(b) and rewriting the query as: > > select id, sum(p1*f3(a,b) as r1, sum(p2*f3(a,b) as r2, ... > sum(pn*f3(a,b) as rn > > ... group by id; > > *Looks like* I got a little (5%) improvement in performance of the > query. Is there a way to find out if the function is re-evaluated > each time? Is this the recommended way to proceed? > > Thank you! > > Petru Ghita >> >> - -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org <mailto:pgsql-sql@postgresql.org>) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkuSf+0ACgkQt6IL6XzynQSREQCfQsZpH/cWzMTqVBv4/2D4X+Ib uBYAniJwbox3bPA4dG/x4vmr0FY+icO9 =8Rvn -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Does IMMUTABLE property propagate?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Given f1(x) as IMMUTABLE and f2(x) as IMMUTABLE, and f3(f1,f2) as IMMUTABLE, does the query planner cache the result of f3 and reuse it or if you want to get a little more speed you better explicitly define yourself f3 as IMMUTABLE? I had an aggregate query like: select id, sum(p1*f1(a)/f2(b) as r1, sum(p2*f1(a)/f2(b) as r2, ... sum(pn*f1(a)/f2(b) as rn ... group by id; Where f1(x) and f2(x) were defined as IMMUTABLE. By the experiments I ran looks like after defining a new function f3(a,b):= f1(a)/f2(b) and rewriting the query as: select id, sum(p1*f3(a,b) as r1, sum(p2*f3(a,b) as r2, ... sum(pn*f3(a,b) as rn ... group by id; *Looks like* I got a little (5%) improvement in performance of the query. Is there a way to find out if the function is re-evaluated each time? Is this the recommended way to proceed? Thank you! Petru Ghita -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkuRwYQACgkQt6IL6XzynQTHEgCffi2QMWkkvTIsuglsanvcUyRB I+wAoKr22B7FJJVDCssGKGwB8zr4NjQG =V/BS -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create functions using a function
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Yes, it's possible. Use EXECUTE. There is one example under "Example 38-9. Porting a Procedure from PL/SQL to PL/pgSQL" http://www.postgresql.org/docs/8.4/static/plpgsql-porting.html Also have look in: http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html under "37.6.5. Executing Dynamic Commands" Petru Ghita Gianvito Pio wrote: > Hi all, is there a way to define functions and/or triggers in a > function? > > For example, can I create a function that takes an argument and > defines a function that has the name passed as argument to the > first function? > > Something like this . > > CREATE FUNCTION test (name varchar) RETURNS VOID AS $$ BEGIN CREATE > FUNCTION name ( ) (here "name" should be expanded to the variable > content) RETURNS ... > > END $$ > > Is there a way to do this? Thanks > -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkuMQdYACgkQt6IL6XzynQSjoQCffcPZ2W69uy3wpGlkhkraQm2a p0IAnA+8njdC6nAdpdhaTg/M9QEohYVb =/tvu -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Date comparison, user defined operators and magic
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello everybody, I was missing a comparison operator for DATE so I
wrote one after a really fast look into the documentation. Working
with version 8.4.
create or replace function vav_date_posterior(date, date) RETURNS
boolean AS $$
- -- return TRUE if $1 < $2, FALSE otherway
DECLARE
d2 ALIAS FOR $1;
d1 ALIAS FOR $2;
result BOOLEAN;
delta1 interval;
delta2 interval;
ini_date date;
BEGIN
ini_date := cast ('101-01-01' as date);
delta1 := d1 - ini_date;
delta2 := d2 - ini_date;
result := false;
if (delta1 > delta2) then
result := true;
end if;
return result;
END;
$$
LANGUAGE plpgsql;
CREATE OPERATOR < (
leftarg = date,
rightarg = date,
procedure = vav_date_posterior,
commutator = <
);
Then I tested it:
select vav_date_posterior(date '2001-01-2', date '2001-03-20'),
vav_date_posterior(date '2002-01-3', date '2001-03-20'),
vav_date_posterior(date '2001-01-4', date '2001-01-4'),
date '2001-01-5' <> date '2001-01-5',
date '2001-01-5' > date '2001-01-5',
date '2001-01-5' < date '2001-01-5',
date '2001-01-5' = date '2001-01-5',
date '2001-01-6' > date '2001-01-5',
date '2001-01-6' < date '2001-01-5',
date '2001-01-5' >= date '2001-01-5',
date '2001-01-5' <= date '2001-01-5',
date '2001-01-6' >= date '2001-01-5',
date '2001-01-5' <= date '2001-01-15';
And EVERYTHING was working!
So I started to wonder how is this possible because after doing the
first comparison using the < operator I really wasn't expecting any of
the other operators to work at all. But they did!
I thought ok, the > operator was inferred as it's the inverse function
for the operator I have just defined. As this one was magically
inferred, probably the equal operator was also automagically created
as the exclusion of the other two, so if A > B is FALSE, and B > A is
FALSE, we can assume that A = B. As the for the <>, >=, <=, the logic
from this point on would be quite straight forward.
The problem is that I then went back to the documentation and I red
the next page:
http://www.postgresql.org/docs/8.4/interactive/xoper-optimization.html
After reading that I understand that I'd actually have to go remove
the COMMUTATOR keyword from there as the function is not commutative
one, add a NEGATOR, define the > operator and do the same, and then go
for the = operator and so on.
But the thing is it's working...
So question is:
Is it this normal behavior?
Could someone please give a working example or a pointer to an
implemented comparison function?
Could someone please point me to the fastest way to do DATE comparison?
What would it be the fastest way of correctly implementing comparison
operators for the DATE type?
Thank you very much in advance,
Petru Ghita
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkuIjK8ACgkQt6IL6XzynQSDRQCfVLY8XEUZXUyMFWTiCzbG6PqE
828An3v47bGjM9p4oXltivmZZ+UFe6kr
=761N
-END PGP SIGNATURE-
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
