pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedappend fields for *where...*
3+ messages / 3 participants
[nested] [flat]
* append fields for *where...*
@ 2005-02-10 22:45 Johnny C <juandelacruz@gmail.com>
2005-02-10 23:12 ` Re: append fields for *where...* Bruno Wolff III <bruno@wolff.to>
2005-02-11 08:24 ` Re: append fields for *where...* Guillaume LELARGE <gleu@wanadoo.fr>
0 siblings, 2 replies; 3+ messages in thread
From: Johnny C @ 2005-02-10 22:45 UTC (permalink / raw)
To: pgsql-sql
I have the following tables:
TABLE A
month | year | item | num
1 2005 myitem 003
TABLE B
num | date | descr
003 02-01-2005 blahblah
003 01-01-2005 toratora
I am trying to come up with something like:
select date,item,descr from TABLEA a
LEFT OUTER JOIN TABLEB b ON b.num=a.num
WHERE date=month-01-year;
How can you make month (append) - 01 - (append) year? and
pass that as a condition to the field date? Is this possible?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: append fields for *where...*
2005-02-10 22:45 append fields for *where...* Johnny C <juandelacruz@gmail.com>
@ 2005-02-10 23:12 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2005-02-10 23:12 UTC (permalink / raw)
To: Johnny C <juandelacruz@gmail.com>; +Cc: pgsql-sql
On Thu, Feb 10, 2005 at 17:45:11 -0500,
Johnny C <juandelacruz@gmail.com> wrote:
>
> How can you make month (append) - 01 - (append) year? and
> pass that as a condition to the field date? Is this possible?
You can EXTRACT the day of the month from the date. If you need this
to use an index, you can create a functional index.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: append fields for *where...*
2005-02-10 22:45 append fields for *where...* Johnny C <juandelacruz@gmail.com>
@ 2005-02-11 08:24 ` Guillaume LELARGE <gleu@wanadoo.fr>
1 sibling, 0 replies; 3+ messages in thread
From: Guillaume LELARGE @ 2005-02-11 08:24 UTC (permalink / raw)
To: Johnny C <juandelacruz@gmail.com>; +Cc: pgsql-sql
Johnny C wrote:
> I have the following tables:
>
> TABLE A
> month | year | item | num
> 1 2005 myitem 003
>
> TABLE B
> num | date | descr
> 003 02-01-2005 blahblah
> 003 01-01-2005 toratora
>
> I am trying to come up with something like:
> select date,item,descr from TABLEA a
> LEFT OUTER JOIN TABLEB b ON b.num=a.num
> WHERE date=month-01-year;
>
> How can you make month (append) - 01 - (append) year? and
> pass that as a condition to the field date? Is this possible?
>
This works for me :
WHERE (month||'-01-'||year)::timestamp=date;
Here is my session :
galette=# create table a (month int4, year int4, item varchar(255), num
int4);
CREATE TABLE
galette=# insert into a values (1,2005,'myitem',3);
INSERT 17296 1
galette=# create table b (num int4, date timestamp, descr varchar(255));
CREATE TABLE
galette=# insert into b values (3,'02-01-2005','blahblah');
INSERT 17299 1
galette=# insert into b values (3,'01-01-2005','toratora');
INSERT 17300 1
galette=# select date,item,descr from a
LEFT OUTER JOIN b ON b.num=a.num
WHERE (month||'-01-'||year)::timestamp=date;
date | item | descr
---------------------+--------+----------
2005-01-01 00:00:00 | myitem | toratora
(1 ligne)
--
Guillaume.
<!-- http://abs.traduc.org/
http://lfs.traduc.org/
http://traduc.postgresqlfr.org/ -->
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-02-11 08:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-10 22:45 append fields for *where...* Johnny C <juandelacruz@gmail.com>
2005-02-10 23:12 ` Bruno Wolff III <bruno@wolff.to>
2005-02-11 08:24 ` Guillaume LELARGE <gleu@wanadoo.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox