Re: [SQL] Date Index

[A. Kretschmer]

am  Thu, dem 30.10.2008, um 14:49:16 -0600 mailte Ryan Hansen folgendes:
> Hey all,
> 
>  
> 
> I?m apparently too lazy to figure this out on my own so maybe one of you can
> just make it easy on me. J 
> 
>  
> 
> I want to index a timestamp field but I only want the index to include the
> -mm-dd portion of the date, not the time.  I figure this would be where 
> the
> ?expression? portion of the CREATE INDEX syntax would come in, but I?m not 
> sure
> I understand what the syntax would be for this.
> 
>  
> 
> Any suggestions?

Sure.

You can create an index based on a function, but only if the function is
immutable:

test=# create table foo (ts timestamptz);
CREATE TABLE
test=*# create index idx_foo on foo(extract(date from ts));
ERROR:  functions in index expression must be marked IMMUTABLE


To solve this problem specify the timezone:

For the same table as above:
test=*# create index idx_foo on foo(extract(date from ts at time zone 'cet'));
CREATE INDEX


If you have a timestamp whitout timezone it is much easier:
test=# create table foo (ts timestamp);
CREATE TABLE
test=*# create index idx_foo on foo(extract(date from ts));
CREATE INDEX





Hope thats help, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-- 
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] Subqueries

[Gregory Stark]

"Pascal Tufenkji" <[EMAIL PROTECTED]> writes:

> I understand the fact that "the inner query is executed before the outer
> query and the inner query doesn't even know about the outer query."
>
> But why the following query can be executed, although the inner query is
> using the outer query.
>
> Aren't we here using the same concept ?

It's not that inner queries can't refer to outer queries. When they do it's
called a "correlated subquery" and it has to be executed once for every row of
the outer query.

It's that queries on one side of a join can't refer to tables on the other
side of the join.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

-- 
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] Subqueries

[Pascal Tufenkji]

Hi Helio,

 

I understand the fact that "the inner query is executed before the outer
query and the inner query doesn't even know about the outer query."

But why the following query can be executed, although the inner query is
using the outer query.

Aren't we here using the same concept ?

 

SELECT 

e1.empno,e1.ename,e1.job,e1.deptno,

(select count(e2.empno) from emp e2 where e2.deptno = e1.deptno) as "Total" 

from emp e1;

 

 empno | ename  |job| deptno | Total

---++---++---

  7839 | KING   | PRESIDENT | 10 | 3

  7698 | BLAKE  | MANAGER   | 30 | 6

  7782 | CLARK  | MANAGER   | 10 | 3

  7566 | JONES  | MANAGER   | 20 | 5

 

 

 

  _  

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Helio Campos Mello de Andrade
Sent: Thursday, October 30, 2008 2:53 PM
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Subqueries

 

The "v" reference need to exist in the inner query. You can't use an "outer
query reference" in the inner query.
This happens because the inner query is executed before the outer query and
the inner query doesn't even know about the outer query.

May be this helps you get what you want.



SELECT *, count(pl.id  ) * 1.5 AS nb_heures

FROM sip_vacations_v v

LEFT JOIN

  (

  mat_grp_v mg

  INNER JOIN planification_v pl ON pl.mat_grp_id   = mg.id
 

  INNER JOIN planification_ens_v pe ON pe.planification_id = pl.id
 

  WHERE mg.annee_univ = v.annee and mg.semestre = v.sem_civ

  GROUP BY pe.emp_id, mg.mat_id, mg.groupe

) p

ON p.emp_id = v.emp_id AND p.mat_id = v.mat_id AND p.groupe = v.groupe;

I not sure about the count(pl.id  ) * 1.5 is the same that
you are looking for because it will depend of what you ate looking for.

Regards

On Thu, Oct 30, 2008 at 10:17 AM, Pascal Tufenkji <[EMAIL PROTECTED]>
wrote:

Hello,

 

I don't understand the following error.

Can anyone help me plz

Thx

Pascal 

 

select *

from sip_vacations_v v

left join

(

  select pe.emp_id,mg.mat_id,mg.groupe,count(p.id) * 1.5 as nb_heures

  from mat_grp_v mg

  inner join planification_v p on p.mat_grp_id = mg.id

  inner join planification_ens_v pe on pe.planification_id = p.id

  where mg.annee_univ = v.annee and mg.semestre = v.sem_civ

  group by pe.emp_id,mg.mat_id,mg.groupe

) p on p.emp_id = v.emp_id

  and p.mat_id = v.mat_id

  and p.groupe = v.groupe

 

ERROR:  invalid reference to FROM-clause entry for table "v"

LINE 9: where mg.annee_univ = v.annee and mg.semestre = v.sem_civ

  ^

HINT:  There is an entry for table "v", but it cannot be referenced from
this part of the query.

 

 

 




-- 
Helio Campos Mello de Andrade