Re: [SQL] SQL (table transposition)
[EMAIL PROTECTED] wrote: > > Is there any way to get table T3 (below) from T1 and T2 using SQL (select, view, >etc)? > > T3 is basically all INDEX values from T1 matched to IND from T2 with the >corresponding KEY/VALUE pairs transposed from rows to columns. > > --- > |INDEX| (T1) > --- > | 1 | > | 2 | > | 3 | > --- > > - > |IND|KEY| VALUE | (T2) > - > | 1 | 1 | val_a | > | 1 | 2 | val_b | > | 1 | 3 | val_c | > | 2 | 1 | val_d | > | 2 | 2 | val_e | > | 3 | 1 | val_f | > | 3 | 3 | val_g | > - > > -- > |T1_INDEX|KEY1VAL|KEY2VAL|KEY3VAL| (T3) > -- > | 1| val_a | val_b | val_c | > | 2| val_d | val_e | | > | 3| val_f | | val_g | > -- > I think what you are looking for is cross tabulation, TRANSFORM statement, but I don't know if that is supported by PostgreSQL. Volker Paul
Re: [SQL] Extracting data by months
I do it like this:
SELECT datums FROM jaunumi
WHERE date_part('year',datetime(datums)) = '2000' AND
date_part('month',datetime(datums)) = '08';
Where datums is a timestamp field.
Regards,
Sandis Jerics
www.mediaparks.lv
> This might seem rather silly, but could you simply do something like:
>
> select * from database
>where date_field >= '01/01/2000'::date
>and date_field < '02/01/2000'::date;
>
> Of course, if date_field could contain many different years, then this
> would not get you the result you wanted.
>
> John
>
> On Thu, 3 Aug 2000, Antti Linno wrote:
>
> > Lo.
> >
> > I'm in dire need of knowledge, how to extract data by month. Monthday
> > and year arent' important, those I can't give from perl script, but what
> > I do give to postgres are the numbers of the months. Date field is in
> > timestamp. I thought about date_trunc, but I can't think of, how to get
> > data with it. In script will be 12 queries, for each month, to get the
> > news from that month.
> > Btw, is there somewhere a manual about date_trunc, the docs that come
with
> > RH6.1 distribution are somewhat short and lacking explanation of
> > date_trunc.
> > Antti
> >
> >
>
Re: [SQL] A question about indexes...
Hello, I have the following tables in my database Painter(id integer, uri varchar(256)) paints(id1 integer, id2 integer) in order to speed up the join (select * from painter, paints where painter.id= paints.id1) between these two tables I have created indexes on the field painter.id and/or paints.id1. But as I see from the query plan the indexes are not used, instead sequential search is done either I define indexes or not. As you can see below the query plan remains the same. Is that reasonable??? Shouldn't Postgresql use the indexes in order to optimize question???I can't see why is better to make sequential search since the size of tables is relatively big. A) No indexes are defined on the tables Hash Join (cost=12269.78 rows=60014 width=24) -> Seq Scan on painter1 (cost=4234.97 rows=9 width=16) -> Hash (cost=1931.92 rows=50331 width=8) -> Seq Scan on paints (cost=1931.92 rows=50331 width=8) B1) BTree index on painter.id Hash Join (cost=12269.78 rows=60014 width=24) -> Seq Scan on painter (cost=4234.97 rows=9 width=16) -> Hash (cost=1931.92 rows=50331 width=8) -> Seq Scan on paints (cost=1931.92 rows=50331 width=8) B2) Primary Key on painter.id Hash Join (cost=12269.78 rows=60014 width=24) -> Seq Scan on painter (cost=4234.97 rows=9 width=16) -> Hash (cost=1931.92 rows=50331 width=8) -> Seq Scan on paints (cost=1931.92 rows=50331 width=8) C1) BTree index on painter.id and Btree on paints.id1 Hash Join (cost=12269.78 rows=60014 width=24) -> Seq Scan on painter (cost=4234.97 rows=9 width=16) -> Hash (cost=1931.92 rows=50331 width=8) -> Seq Scan on paints (cost=1931.92 rows=50331 width=8) C2) Primary Key on painter.id and Btree on paints.id1 Hash Join (cost=12269.78 rows=60014 width=24) -> Seq Scan on painter (cost=4234.97 rows=9 width=16) -> Hash (cost=1931.92 rows=50331 width=8) -> Seq Scan on paints (cost=1931.92 rows=50331 width=8) Regards, Sofia Alexaki
RE: [SQL] SQL (table transposition)
Hi, When I saw cross tab, I realized that I'd done this before. If you know what your keys are ahead of time, you can write the query. Otherwise, you can write a program go generate the query by looking at the distinct list of keys and generating code as follows. The code generator would only have to replace the ?'s in "SUM(CASE KEY WHEN ? THEN VALUE ELSE 0 END) AS KEY?VAL". SELECT INDEX AS T1_INDEX, SUM(CASE KEY WHEN 1 THEN VALUE ELSE 0 END) AS KEY1VAL, SUM(CASE KEY WHEN 2 THEN VALUE ELSE 0 END) AS KEY2VAL, SUM(CASE KEY WHEN 3 THEN VALUE ELSE 0 END) AS KEY3VAL FROM T1, T2 WHERE T1.INDEX = T2.IND GROUP BY T1.INDEX ; Henry -Original Message- From: Volker Paul [mailto:[EMAIL PROTECTED]] Sent: Friday, August 04, 2000 2:30 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [SQL] SQL (table transposition) [EMAIL PROTECTED] wrote: > > Is there any way to get table T3 (below) from T1 and T2 using SQL (select, view, etc)? > > T3 is basically all INDEX values from T1 matched to IND from T2 with the corresponding KEY/VALUE pairs transposed from rows to columns. > > --- > |INDEX| (T1) > --- > | 1 | > | 2 | > | 3 | > --- > > - > |IND|KEY| VALUE | (T2) > - > | 1 | 1 | val_a | > | 1 | 2 | val_b | > | 1 | 3 | val_c | > | 2 | 1 | val_d | > | 2 | 2 | val_e | > | 3 | 1 | val_f | > | 3 | 3 | val_g | > - > > -- > |T1_INDEX|KEY1VAL|KEY2VAL|KEY3VAL| (T3) > -- > | 1| val_a | val_b | val_c | > | 2| val_d | val_e | | > | 3| val_f | | val_g | > -- > I think what you are looking for is cross tabulation, TRANSFORM statement, but I don't know if that is supported by PostgreSQL. Volker Paul
Re: [SQL] Extracting data by months
This is not exactly what the original question was about. You select the month
is one specific year only.
What you do it however easier achieved by:
SELECT datums FROM jaunumi WHERE date_trunc('month', datums) = '2000-08-01';
datetime is an type full of tricks. :-)
Daniel
>>>"Sandis" said:
> I do it like this:
>
> SELECT datums FROM jaunumi
> WHERE date_part('year',datetime(datums)) = '2000' AND
> date_part('month',datetime(datums)) = '08';
>
> Where datums is a timestamp field.
>
> Regards,
> Sandis Jerics
> www.mediaparks.lv
>
> > This might seem rather silly, but could you simply do something like:
> >
> > select * from database
> >where date_field >= '01/01/2000'::date
> >and date_field < '02/01/2000'::date;
> >
> > Of course, if date_field could contain many different years, then this
> > would not get you the result you wanted.
> >
> > John
> >
> > On Thu, 3 Aug 2000, Antti Linno wrote:
> >
> > > Lo.
> > >
> > > I'm in dire need of knowledge, how to extract data by month. Monthday
> > > and year arent' important, those I can't give from perl script, but what
> > > I do give to postgres are the numbers of the months. Date field is in
> > > timestamp. I thought about date_trunc, but I can't think of, how to get
> > > data with it. In script will be 12 queries, for each month, to get the
> > > news from that month.
> > > Btw, is there somewhere a manual about date_trunc, the docs that come
> with
> > > RH6.1 distribution are somewhat short and lacking explanation of
> > > date_trunc.
> > > Antti
> > >
> > >
> >
>
Re: [SQL] Extracting data by months
On Fri, 4 Aug 2000, Sandis wrote:
> I do it like this:
>
> SELECT datums FROM jaunumi
> WHERE date_part('year',datetime(datums)) = '2000' AND
> date_part('month',datetime(datums)) = '08';
>
> Where datums is a timestamp field.
Or (in 7.0):
SELECT datums FROM jaunum WHERE to_char('/MM', datums) = '2000/08';
becuase it more simple and it *will faster*.
Karel
[SQL] Database in recovery mode
Hi. I've got a postgres system that keeps going into recovery mode. I can't really find any docs on this. All of the postgres processes will be in the STOP state and when I try to connect it will say "The database is in recovery mode". I suspect there is a query that is causing it to crash in some specific way that causes it. This is happening about once per day. Any ideas? -Michael _ http://fastmail.ca/ - Fast Free Web Email for Canadians
Re: [SQL] What's ETA for read/write Views?
Tom Lane writes: > Some people seem to think that the system should try to intuit > those rules for them, but I don't believe that's either possible > or desirable. The entire point of a view is that it's not an > exact image of the underlying data, so how is a machine going to > figure out what you want an update on the view to do? The SQL standard has pretty precise rules for when views are updatable and how. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
[SQL] Strange strategy with COALESCE?
I get unexpected query strategy when using coalesce. Good: - explain update zzz set b = (select x.newVal from zzz_xref x where x.oldVal = zzz.b); NOTICE: QUERY PLAN: Seq Scan on zzz (cost=0.00..20.00 rows=1000 width=10) SubPlan -> Index Scan using zzz_xref_ix2 on zzz_xref x (cost=0.00..726.82 rows=983 width=4) Bad (just by adding a COALESCE call): - explain update zzz set b = coalesce((select x.newVal from zzz_xref x where x.oldVal = zzz.b),b); NOTICE: QUERY PLAN: Seq Scan on zzz (cost=0.00..20.00 rows=1000 width=10) SubPlan -> Index Scan using zzz_xref_ix2 on zzz_xref x (cost=0.00..726.82 rows=983 width=4) -> Seq Scan on zzz_xref x (cost=0.00..1757.80 rows=98304 width=4) Does this make sense? P.S. There are indexes on both oldVal and newVal in zzz_xref, and table zzz has one column (b). Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.C.N. 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
