Re: [SQL] Question on partitioning
Hi Christina, Quoting Oliveiros Cristina <[EMAIL PROTECTED]>: > Hello , All. > > I am not sure if this is the right mailing list to place this question. > If it doesn't, please kindly redirect me to the right list. > > I have a giant table with about 10,6 million records. > > Queries on it are usually slow, and if I try to do something more elaborate > like an INNER JOIN with itself it becomes unnacceptably slow. > > I am looking for a way to improve performance. > One of the columns is of type date. Each "day" includes about a few tens of > thousands records > And the older a date is the less likely I am to do queries on it. > > The objective of the "self join" is to compare data from two different days, > looking for diferences. > > Ive read that one of the benefits of partitioning is to speed up queries by > separating less used records. > > My question is if partitioning can be a good way to make the queries faster > (specially the self joins) or if it isn't worth trying because it doesn't > help on my particular situation. > > Please kindly advice me on this > > Many thanks in advance for your kind help > > Best, > Oliveiros > I'm sure partitioning is a good choice to improve your performance. But, as Scott noticed, be sure first to have done all possible improvements on your table such as indexes, of course (only if they don't damage your insert performances). I use partitioning with check constraints on a timestamp field and it works well without any optimization of the conf files of postgresql. It's ok for massive inserts (csv records to copy up to 6 times per second) without indexes (they cost a lot for insert statements), as to select many datas on the same tables. Best regards, Sylvain Caillet IT Performance Software Project Manager -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Concat field result in select query
Hi all, I was wondering if there is a way to concatenate the results of a field in a select to return it as a single string. Example : my_field -- 1 2 3 select concat_something(my_field) from my_table group by something; the result expected would be someting like that : 1/2/3 (with a separator it would be really nice) I hope that I am not asking for too much ;) Thanks to all
Re: [SQL] Concat field result in select query
any explanations ? On Fri, Aug 22, 2008 at 6:07 PM, Julien Cigar <[EMAIL PROTECTED]> wrote: > array_accum ? > > On Fri, 2008-08-22 at 15:40 +0200, Nacef LABIDI wrote: > > Hi all, > > > > I was wondering if there is a way to concatenate the results of a > > field in a select to return it as a single string. > > > > Example : > > > > my_field > > -- > > 1 > > 2 > > 3 > > > > select concat_something(my_field) from my_table group by something; > > the result expected would be someting like that : 1/2/3 (with a > > separator it would be really nice) > > > > I hope that I am not asking for too much ;) > > > > Thanks to all > > > -- > Julien Cigar > Belgian Biodiversity Platform > http://www.biodiversity.be > Université Libre de Bruxelles (ULB) > Campus de la Plaine CP 257 > Bâtiment NO, Bureau 4 N4 115C (Niveau 4) > Boulevard du Triomphe, entrée ULB 2 > B-1050 Bruxelles > Mail: [EMAIL PROTECTED] > @biobel: http://biobel.biodiversity.be/person/show/471 > Tel : 02 650 57 52 > >
Re: [SQL] Concat field result in select query
array_accum ? On Fri, 2008-08-22 at 15:40 +0200, Nacef LABIDI wrote: > Hi all, > > I was wondering if there is a way to concatenate the results of a > field in a select to return it as a single string. > > Example : > > my_field > -- > 1 > 2 > 3 > > select concat_something(my_field) from my_table group by something; > the result expected would be someting like that : 1/2/3 (with a > separator it would be really nice) > > I hope that I am not asking for too much ;) > > Thanks to all > -- Julien Cigar Belgian Biodiversity Platform http://www.biodiversity.be Université Libre de Bruxelles (ULB) Campus de la Plaine CP 257 Bâtiment NO, Bureau 4 N4 115C (Niveau 4) Boulevard du Triomphe, entrée ULB 2 B-1050 Bruxelles Mail: [EMAIL PROTECTED] @biobel: http://biobel.biodiversity.be/person/show/471 Tel : 02 650 57 52 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Concat field result in select query
Nacef LABIDI a écrit : > Hi all, > > I was wondering if there is a way to concatenate the results of a field > in a select to return it as a single string. > > Example : > > my_field > -- > 1 > 2 > 3 > > select concat_something(my_field) from my_table group by something; > the result expected would be someting like that : 1/2/3 (with a > separator it would be really nice) > > I hope that I am not asking for too much ;) > Something like: SELECT array_to_string(array(select myfield from mytable), ',')); For example: sherkin=# select * from t1; c1 | c2 + 1 | 2 | 3 2 | 4 2 | 5 (4 lignes) sherkin=# SELECT array_to_string(array(select c1 from t1), ','); array_to_string - 1,2,2,2 (1 ligne) -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Concat field result in select query
Thank you, this is all what I need On Fri, Aug 22, 2008 at 4:10 PM, Guillaume Lelarge <[EMAIL PROTECTED]>wrote: > Nacef LABIDI a écrit : > > Hi all, > > > > I was wondering if there is a way to concatenate the results of a field > > in a select to return it as a single string. > > > > Example : > > > > my_field > > -- > > 1 > > 2 > > 3 > > > > select concat_something(my_field) from my_table group by something; > > the result expected would be someting like that : 1/2/3 (with a > > separator it would be really nice) > > > > I hope that I am not asking for too much ;) > > > > Something like: > > SELECT array_to_string(array(select myfield from mytable), ',')); > > For example: > > sherkin=# select * from t1; > c1 | c2 > + > 1 | > 2 | 3 > 2 | 4 > 2 | 5 > (4 lignes) > > sherkin=# SELECT array_to_string(array(select c1 from t1), ','); > array_to_string > - > 1,2,2,2 > (1 ligne) > > > -- > Guillaume. > http://www.postgresqlfr.org > http://dalibo.com >
Re: [SQL] Concat field result in select query
Also, the array_accum aggregate isn't present by default, you need to
create it with :
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
On Fri, 2008-08-22 at 18:24 +0200, Julien Cigar wrote:
> of course: http://rafb.net/p/EOaYfO59.html
>
> Julien
>
> On Fri, 2008-08-22 at 15:58 +0200, Nacef LABIDI wrote:
> > any explanations ?
> >
> > On Fri, Aug 22, 2008 at 6:07 PM, Julien Cigar <[EMAIL PROTECTED]>
> > wrote:
> > array_accum ?
> >
> >
> > On Fri, 2008-08-22 at 15:40 +0200, Nacef LABIDI wrote:
> > > Hi all,
> > >
> > > I was wondering if there is a way to concatenate the results
> > of a
> > > field in a select to return it as a single string.
> > >
> > > Example :
> > >
> > > my_field
> > > --
> > > 1
> > > 2
> > > 3
> > >
> > > select concat_something(my_field) from my_table group by
> > something;
> > > the result expected would be someting like that : 1/2/3
> > (with a
> > > separator it would be really nice)
> > >
> > > I hope that I am not asking for too much ;)
> > >
> > > Thanks to all
> > >
> >
> > --
> > Julien Cigar
> > Belgian Biodiversity Platform
> > http://www.biodiversity.be
> > Université Libre de Bruxelles (ULB)
> > Campus de la Plaine CP 257
> > Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
> > Boulevard du Triomphe, entrée ULB 2
> > B-1050 Bruxelles
> > Mail: [EMAIL PROTECTED]
> > @biobel: http://biobel.biodiversity.be/person/show/471
> > Tel : 02 650 57 52
> >
> >
> >
--
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: [EMAIL PROTECTED]
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Concat field result in select query
of course: http://rafb.net/p/EOaYfO59.html Julien On Fri, 2008-08-22 at 15:58 +0200, Nacef LABIDI wrote: > any explanations ? > > On Fri, Aug 22, 2008 at 6:07 PM, Julien Cigar <[EMAIL PROTECTED]> > wrote: > array_accum ? > > > On Fri, 2008-08-22 at 15:40 +0200, Nacef LABIDI wrote: > > Hi all, > > > > I was wondering if there is a way to concatenate the results > of a > > field in a select to return it as a single string. > > > > Example : > > > > my_field > > -- > > 1 > > 2 > > 3 > > > > select concat_something(my_field) from my_table group by > something; > > the result expected would be someting like that : 1/2/3 > (with a > > separator it would be really nice) > > > > I hope that I am not asking for too much ;) > > > > Thanks to all > > > > -- > Julien Cigar > Belgian Biodiversity Platform > http://www.biodiversity.be > Université Libre de Bruxelles (ULB) > Campus de la Plaine CP 257 > Bâtiment NO, Bureau 4 N4 115C (Niveau 4) > Boulevard du Triomphe, entrée ULB 2 > B-1050 Bruxelles > Mail: [EMAIL PROTECTED] > @biobel: http://biobel.biodiversity.be/person/show/471 > Tel : 02 650 57 52 > > > -- Julien Cigar Belgian Biodiversity Platform http://www.biodiversity.be Université Libre de Bruxelles (ULB) Campus de la Plaine CP 257 Bâtiment NO, Bureau 4 N4 115C (Niveau 4) Boulevard du Triomphe, entrée ULB 2 B-1050 Bruxelles Mail: [EMAIL PROTECTED] @biobel: http://biobel.biodiversity.be/person/show/471 Tel : 02 650 57 52 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Concat field result in select query
> Hi all, > > I was wondering if there is a way to concatenate the > results of a field in a > select to return it as a single string. > > Example : > > my_field > -- > 1 > 2 > 3 > > select concat_something(my_field) from my_table group by > something; > the result expected would be someting like that : 1/2/3 > (with a separator it > would be really nice) > > I hope that I am not asking for too much ;) > > Thanks to all you can use aggregate function, check for previous threads for this solution http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Re: Efficiently determining the number of bits set in the contents of, a VARBIT field
All was well with the code below, apologies to all who read my previous
email. The error (an oversight) was on my part. In the "CREATE FUNCTION
..." statement I had FLOAT as the return type instead of INTEGER.
Now the function runs smoothly. Preliminary results show it is orders of
magnitude faster than the LENGTH(REGEXP(CAST(myVarBit AS
TEXT),'0','','g')) solution.
Thanks again TJ and the rest of the team.
Allan
Allan Kamau wrote:
Thank you TJ and everyone else for the advise and the c code. Today I
did finally return to the 'number of bits set challenge' and managed
to compile and link the nbits c function which went smoothly. However
the function does crash my postgres server installation (8.3.3) with a
segmentation fault each time I call it for example SELECT
nbits_set(B'1101');
My C skills are very sparse and am unable to debug the function, I
have included the C code of this function. Is there something I may
have left out?
#include "postgres.h"
#include "utils/varbit.h"
#include "fmgr.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(nbits_set);
Datum
nbits_set(PG_FUNCTION_ARGS)
{
/* how many bits are set in a bitstring? */
VarBit *a = PG_GETARG_VARBIT_P(0);
int n=0;
int i;
unsigned char *ap = VARBITS(a);
unsigned char aval;
for (i=0; i < VARBITBYTES(a); ++i) {
aval = *ap; ++ap;
if (aval == 0) continue;
if (aval & 1) ++n;
if (aval & 2) ++n;
if (aval & 4) ++n;
if (aval & 8) ++n;
if (aval & 16) ++n;
if (aval & 32) ++n;
if (aval & 64) ++n;
if (aval & 128) ++n;
}
PG_RETURN_INT32(n);
}
Allan
Bruce Momjian wrote:
Jean-David Beyer wrote:
TJ O'Donnell wrote:
I use a c function, nbits_set that will do what you need.
I've posted the code in this email.
TJ O'Donnell
http://www.gnova.com
#include "postgres.h"
#include "utils/varbit.h"
Datum nbits_set(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(nbits_set);
Datum
nbits_set(PG_FUNCTION_ARGS)
{
/* how many bits are set in a bitstring? */
VarBit *a = PG_GETARG_VARBIT_P(0);
int n=0;
int i;
unsigned char *ap = VARBITS(a);
unsigned char aval;
for (i=0; i < VARBITBYTES(a); ++i) {
aval = *ap; ++ap;
if (aval == 0) continue;
if (aval & 1) ++n;
if (aval & 2) ++n;
if (aval & 4) ++n;
if (aval & 8) ++n;
if (aval & 16) ++n;
if (aval & 32) ++n;
if (aval & 64) ++n;
if (aval & 128) ++n;
}
PG_RETURN_INT32(n);
}
Hi all,
Am looking for a fast and efficient way to count the number of
bits set (to 1) in a VARBIT field. I am currently using
"LENGTH(REGEXP_REPLACE(CAST(a.somefield_bit_code AS
TEXT),'0','','g'))".
Allan.
When I had to do that, in days with smaller amounts of RAM, but very
long
bit-vectors, I used a faster function sort-of like this:
static char table[256] = {
0,1,1,2,1,2,2,3,1,.
};
Then like above, but instead of the loop,
n+= table[aval];
You get the idea.
Uh, I was kind of confused by this, even when I saw a full
implementation:
http://graphics.stanford.edu/~seander/bithacks.html#CountBitsSetTable
Actually, this looks even better:
http://graphics.stanford.edu/~seander/bithacks.html#CountBitsSetKernighan
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Why *no* ambig·uous complain in select part?
Good morning, Just notice one small thing, and need your information about select select col1, col2 from table1 left join table2 using (col1, col2) ; This query never complain about ambiguous columns of col1 and col2 in the select part. My guess is: (1) col1, col2 always return table1.col1, table1.col2 (2) because using (col1, col2) that's why, table name is not necessary in select part Am I wrong? Please advise? Thank you! -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] RE: [SQL] Why *no* ambig.uous complain in select part?
Just a guess, but it seems to me that since the join is using col1 and col2 there is no ambiguity. They should be the same no matter which table it comes from. Edward W. Rouse -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Emi Lu Sent: Friday, August 22, 2008 4:12 PM To: [email protected] Subject: [SQL] Why *no* ambig.uous complain in select part? Good morning, Just notice one small thing, and need your information about select select col1, col2 from table1 left join table2 using (col1, col2) ; This query never complain about ambiguous columns of col1 and col2 in the select part. My guess is: (1) col1, col2 always return table1.col1, table1.col2 (2) because using (col1, col2) that's why, table name is not necessary in select part Am I wrong? Please advise? Thank you! -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] RE: [SQL] Why *no* ambig.uous complain in select part?
Hi Edward, Just a guess, but it seems to me that since the join is using col1 and col2 there is no ambiguity. They should be the same no matter which table it comes from. Not always the same; "Left join" may return: table2.col1,col2 = null, while table1.col1,col2 is not null -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Emi Lu Sent: Friday, August 22, 2008 4:12 PM To: [email protected] Subject: [SQL] Why *no* ambig.uous complain in select part? Good morning, Just notice one small thing, and need your information about select select col1, col2 from table1 left join table2 using (col1, col2) ; This query never complain about ambiguous columns of col1 and col2 in the select part. My guess is: (1) col1, col2 always return table1.col1, table1.col2 (2) because using (col1, col2) that's why, table name is not necessary in select part Am I wrong? Please advise? Thank you! -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] RE: [SQL] Why *no* ambig.uous complain in select part?
Hi Luigi, you will have problems if table 1 and table 2 have the same names to col1 e col2. For example, table1 has col1=parcel and col2=area and table 2 has col1=country and col2=area then, in that case you will have ambiguity. Would you please give me an example? I have two tables like the following: T1 (col1 varchar, col2 varchar, primary key (col1, col2)) T2 (col1 varchar, col2 varchar, primary key (col1, col2)) Query I have is: === select col1, col2 from T1 left join T2 using (T1, T2); Thanks a lot! 2008/8/22 Emi Lu <[EMAIL PROTECTED]> Hi Edward, Just a guess, but it seems to me that since the join is using col1 and col2 there is no ambiguity. They should be the same no matter which table it comes from. Not always the same; "Left join" may return: table2.col1,col2 = null, while table1.col1,col2 is not null -Original Message- From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] On Behalf Of Emi Lu Sent: Friday, August 22, 2008 4:12 PM To: [email protected] Subject: [SQL] Why *no* ambig.uous complain in select part? Good morning, Just notice one small thing, and need your information about select select col1, col2 from table1 left join table2 using (col1, col2) ; This query never complain about ambiguous columns of col1 and col2 in the select part. My guess is: (1) col1, col2 always return table1.col1, table1.col2 (2) because using (col1, col2) that's why, table name is not necessary in select part Am I wrong? Please advise? Thank you! -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] RE: [SQL] Why *no* ambig.uous complain in select part?
On Fri, 2008-08-22 at 17:10 -0400, Emi Lu wrote: > > Would you please give me an example? > > I have two tables like the following: > T1 (col1 varchar, col2 varchar, primary key (col1, col2)) > T2 (col1 varchar, col2 varchar, primary key (col1, col2)) > > > Query I have is: > === > select col1, col2 > from T1 > left join T2 using (T1, T2); > > Thanks a lot! If (T1.col1, T1.col2) != (T2.col1, T2.col2) then the join is unsuccessful and T2.col1 and T2.col2 will be null. If you're wondreing if the join was successful: select col1, col2 from T1 left outer join T2 using (col1, col2) where T2.col1 is not null -Mark -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Why *no* ambig·uous complain in select part?
Emi Lu <[EMAIL PROTECTED]> writes: > Just notice one small thing, and need your information about select > select col1, col2 > from table1 > left join table2 > using (col1, col2) > ; > This query never complain about ambiguous columns of col1 and col2 in > the select part. That's because you used USING, which merges the similarly-named columns into just one output column. RTFM, or any SQL book. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] What is wrong with this PostgreSQL UPDATE statement??
Sorry for the fairly long post.
I'm having a big problem trying to update one table from another in
PostgreSQL 8.3.1.
I have a lookup table called termgroup:
# select * from termgroup;
termgroupname | mindays | maxdays
---+-+-
1-30 days | 1 | 30
31-59 days| 31 | 59
60-89 days| 60 | 89
90-119 days | 90 | 119
120-179 days | 120 | 179
180-364 days | 180 | 364
1-2 years | 365 | 729
2-3 years | 730 |1094
3-4 years |1095 |1459
4-5 years |1460 |1824
5+ years |1825 | 99
(11 rows)
And also a data table with data that needs to be catagorized using the
above lookup table:
# select * from certgroups;
days | number | termgroupname
--++---
58 | 66 |
303 | 11 |
732 | 1056 |
1096 | 66 |
25 |123 |
(5 rows)
As you can see from the detailed session below, the update statement
that works perfectly in MS-SQL 2005 fails miserably in PG.
ANY SUGGESTIONS WOULD BE GREATLY APPRECIATED!!
Thanks,
S.
--- START of SQL session:
create table termgroup (
termgroupname varchar(20) not null,
mindays int not null,
maxdays int not null,
CONSTRAINT "PKtermgroup_termgroupname" PRIMARY KEY (termgroupname)
);
--
insert into termgroup (termgroupname,mindays,maxdays) values ('1-30 days',1,30);
insert into termgroup (termgroupname,mindays,maxdays) values ('31-59
days',31,59);
insert into termgroup (termgroupname,mindays,maxdays) values ('60-89
days',60,89);
insert into termgroup (termgroupname,mindays,maxdays) values ('90-119
days',90,119);
insert into termgroup (termgroupname,mindays,maxdays) values ('120-179
days',120,179);
insert into termgroup (termgroupname,mindays,maxdays) values ('180-364
days',180,364);
insert into termgroup (termgroupname,mindays,maxdays) values ('1-2
years',365,729);
insert into termgroup (termgroupname,mindays,maxdays) values ('2-3
years',730,1094);
insert into termgroup (termgroupname,mindays,maxdays) values ('3-4
years',1095,1459);
insert into termgroup (termgroupname,mindays,maxdays) values ('4-5
years',1460,1824);
insert into termgroup (termgroupname,mindays,maxdays) values ('5+
years',1825,99);
--
select * from termgroup order by mindays;
--
create table certgroups (
days int not null primary key,
number int not null,
termgroupname varchar(20) null);
--
insert into certgroups(days,number) values (25,123);
insert into certgroups(days,number) values (58,66);
insert into certgroups(days,number) values (303,11);
insert into certgroups(days,number) values (732,1056);
insert into certgroups(days,number) values (1096,66);
--
select * from certgroups order by days;
--
update certgroups
set termgroupname = tg.termgroupname
from certgroups c, termgroup tg
where (c.days >= tg.mindays) and (c.days <= tg.maxdays);
--
select * from certgroups order by days;
--
/*
-- MS-SQL output (CORRECT):
daysnumber termgroupname
--- ---
58 66 31-59 days
303 11 180-364 days
732 10562-3 years
109666 3-4 years
(4 row(s) affected)
-- PostgreSQL output (WRONG!!):
days | number | termgroupname
--++---
58 | 66 | 31-59 days OK
303 | 11 | 31-59 days WRONG
732 | 1056 | 31-59 days WRONG
1096 | 66 | 31-59 days WRONG
(4 rows)
*/
insert into certgroups(days,number) values (25,123);
--
update certgroups
set termgroupname = tg.termgroupname
from certgroups c, termgroup tg
where (c.days >= tg.mindays) and (c.days <= tg.maxdays);
--
select * from certgroups order by days;
--
/*
-- MS-SQL output (CORRECT):
daysnumber termgroupname
--- ---
25 123 1-30 days
58 66 31-59 days
303 11 180-364 days
732 10562-3 years
109666 3-4 years
(5 row(s) affected)
-- PostgreSQL output (WRONG!!):
days | number | termgroupname
--++---
25 |123 | 1-30 daysRIGHT
58 | 66 | 1-30 daysWRONG
303 | 11 | 1-30 daysWRONG
732 | 1056 | 1-30 daysWRONG
1096 | 66 | 1-30 daysWRONG
(5 rows)
*/
--- END of SQL session
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] What is wrong with this PostgreSQL UPDATE statement??
On Fri, 22 Aug 2008, Steve Johnson wrote: > update certgroups > set termgroupname = tg.termgroupname > from certgroups c, termgroup tg > where (c.days >= tg.mindays) and (c.days <= tg.maxdays); In recent PostgreSQL versions I believe this is properly written: update certgroups c set termgroupname = tg.termgroupname from termgroup tg where (c.days >= tg.mindays) and (c.days <= tg.maxdays); At least as of SQL2003, I think both of the above use extensions, so there's no guarantee to the behavior on different systems and to do it with a standard query, you'd need to use a subselect, something like: update certgroups c set termgroupname = (select termgroupname from termgroup tg where (c.days >= tg.mindays) and (c.days <=tg.maxdays)); -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] What is wrong with this PostgreSQL UPDATE statement??
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Fri, 22 Aug 2008, Steve Johnson wrote: >> update certgroups >> set termgroupname = tg.termgroupname >> from certgroups c, termgroup tg >> where (c.days >= tg.mindays) and (c.days <= tg.maxdays); > In recent PostgreSQL versions I believe this is properly written: > update certgroups c > set termgroupname = tg.termgroupname > from termgroup tg > where (c.days >= tg.mindays) and (c.days <= tg.maxdays); Yeah, in PG's eyes the former is creating a cartesian join between two versions of certgroups. I think MSSQL interprets the FROM reference as being the same as the update target, but we don't. > At least as of SQL2003, I think both of the above use extensions, Correct, the standard disallows a FROM clause altogether; and I'm not sure that they weren't right. No matter which way you resolve the above ambiguity, you've still got the problem that the update behavior is ill-defined if a given target row joins to more than one set of rows from the other table(s). regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
