[SQL] case when... end in update clause?

[Emi Lu]

Hello,

May I know can "case when " used by update clause. If yes, how?

I use one small Example, table: test
=
id
==
5
6
8

try to update test.id


update test

case
 when id =5 then SET id = 6
end
;

Thanks!

--
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] case when... end in update clause?

[Emi Lu]

Aaron Bono wrote:

On Wed, Mar 12, 2008 at 10:47 AM, Emi Lu <[EMAIL PROTECTED]> wrote:


Hello,

May I know can "case when " used by update clause. If yes, how?

I use one small Example, table: test
=
id
==
5
6
8

try to update test.id


update test

case
 when id =5 then SET id = 6
end
;





Is this what you are looking for:


update test
set id = case when id = 5 then 6 else id end;


Exactly what I am looking for!

Thanks a lot!

--
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] case when... end in update clause?

[Scott Marlowe]

On Wed, Mar 12, 2008 at 8:47 AM, Emi Lu <[EMAIL PROTECTED]> wrote:
> Hello,
>
>  May I know can "case when " used by update clause. If yes, how?
>
>  I use one small Example, table: test
>  =
>  id
>  ==
>  5
>  6
>  8
>
>  try to update test.id
>
>
>  update test
>
>  case
>   when id =5 then SET id = 6
>  end
>  ;

would this work:

update test set id=5 where id=6;

???

-- 
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] case when... end in update clause?

[Emi Lu]

 I use one small Example, table: test
 =
 id
 ==
 5
 6
 8

 try to update test.id


 update test

 case
  when id =5 then SET id = 6
 end
 ;


would this work:

update test set id=5 where id=6;



No. I provide one small fake example.


I want to know how to use case when in update/set clause as the following:

update test
set id = case when id = 5 then 6 else id end;


Thanks.


--
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] case when... end in update clause?

[Scott Marlowe]

On Wed, Mar 12, 2008 at 9:11 AM, Emi Lu <[EMAIL PROTECTED]> wrote:
> >>
>  >>  I use one small Example, table: test
>  >>  =
>  >>  id
>  >>  ==
>  >>  5
>  >>  6
>  >>  8
>  >>
>  >>  try to update test.id
>  >>
>  >>
>  >>  update test
>  >>
>  >>  case
>  >>   when id =5 then SET id = 6
>  >>  end
>  >>  ;
>  >
>  > would this work:
>  >
>  > update test set id=5 where id=6;
>  >
>
>  No. I provide one small fake example.
>
>
>  I want to know how to use case when in update/set clause as the following:
>
>  update test
>  set id = case when id = 5 then 6 else id end;

Well, I think my point stands, that this stuff really belongs in a
where clause.  The way you're doing it it updates ALL the rows whether
it needs to or not, my way only updates the rows that need it.  How
about a REAL example of what you're trying to do.  There may well be a
more efficient way of doing this than using a case statement.  Or
not...

-- 
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] case when... end in update clause?

[Emi Lu]

Hi Scott ,

 No. I provide one small fake example.


 I want to know how to use case when in update/set clause as the following:

 update test
 set id = case when id = 5 then 6 else id end;


Well, I think my point stands, that this stuff really belongs in a
where clause.  The way you're doing it it updates ALL the rows whether
it needs to or not, my way only updates the rows that need it.  How
about a REAL example of what you're trying to do.  There may well be a
more efficient way of doing this than using a case statement.  Or
not...


Ok.


The situation would like this, in one query:


UPDATE tableName
SET
   col1 = val1 when col1 satisfy condition1
   col1 = val2 when col1 satisfy condition2

   ... ...

   col1 = valN when col1 satisfy conditionN

   ... ...

WHERE
   col3 satisfy conditionX;




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Timestamp, epoch and a bit confusion

[Valter Douglas Lisbôa Jr.]

Hello all.

I working with postgreSQL for a couple of months, and now I trying to convert 
timestamp columns in epoch and epoch to timestamp columns. So far I could 
reach, all SQL I find by 'googling' returns an error.

Where can I find some documentation with this convertions fo 8.2.x branch? I 
think the Manual on the main site is not very helpfull in this case!!! ;-(

Thanks in advance.

-- 
Valter Douglas Lisbôa Jr.
Sócio-Diretor
Trenix - IT Solutions
"Nossas Idéias, suas Soluções!"
www.trenix.com.br
[EMAIL PROTECTED]
Tel. +55 19 3402.2957
Cel. +55 19 9183.4244

-- 
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] Timestamp, epoch and a bit confusion

[Aurynn Shaw]

Valter Douglas Lisbôa Jr. wrote:

Hello all.

I working with postgreSQL for a couple of months, and now I trying to convert 
timestamp columns in epoch and epoch to timestamp columns. So far I could 
reach, all SQL I find by 'googling' returns an error.


SELECT extract (epoch from your_time_field) from your_table;
SELECT to_timestamp(your_epoch_field) from your_table;



Where can I find some documentation with this convertions fo 8.2.x branch? I 
think the Manual on the main site is not very helpfull in this case!!! ;-(




And you can check out the docs @
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT


Hope that helps,
--
Aurynn Shaw

The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

[EMAIL PROTECTED]

--
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] Timestamp, epoch and a bit confusion

[Valter Douglas Lisbôa Jr.]

Thank you, very much. I pass through extract(epoch from x).

On Wednesday 12 March 2008 19:58:07 Aurynn Shaw wrote:
> Valter Douglas Lisbôa Jr. wrote:
> > Hello all.
> >
> > I working with postgreSQL for a couple of months, and now I trying to
> > convert timestamp columns in epoch and epoch to timestamp columns. So far
> > I could reach, all SQL I find by 'googling' returns an error.
>
> SELECT extract (epoch from your_time_field) from your_table;
> SELECT to_timestamp(your_epoch_field) from your_table;
>
> > Where can I find some documentation with this convertions fo 8.2.x
> > branch? I think the Manual on the main site is not very helpfull in this
> > case!!! ;-(
>
> And you can check out the docs @
> http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCT
>IONS-DATETIME-EXTRACT
>
>
> Hope that helps,
> --
> Aurynn Shaw
>
> The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> [EMAIL PROTECTED]



-- 
Valter Douglas Lisbôa Jr.
Sócio-Diretor
Trenix - IT Solutions
"Nossas Idéias, suas Soluções!"
www.trenix.com.br
[EMAIL PROTECTED]
Tel. +55 19 3402.2957
Cel. +55 19 9183.4244

-- 
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] Composite UNIQUE across two tables?

[Jamie Tufnell]

Hi Dirk,

On 3/11/08, Dirk Jagdmann <[EMAIL PROTECTED]> wrote:
> I vote against duplicating site_group_id in the users table and the
> proposed unique constraint with a function. Because all those might
> fail, if you ever want to change the relationship between a site and a
> site group.

Good point!

> My advise would be to have two triggers for insert/update on the site
> and users table that check the uniqueness of the username with the
> site_group. A have made some tests with inserts and updates on the
> existing users and sites and these two functions seem to work.

I think this is the way that I'll go.  I'd hoped to somehow express this
solely in the design, if you know what i mean (e.g. without writing
SPs), but it looks like this is the best way to do it.

> One remark about your schema: If you use PostgreSQL, use the "text"
> datatype for strings, since you don't limit yourself with the string
> length.

[snip]

For some reason I assumed varchar had an advantage over text,
but a quick check of the docs suggests that's not the case. Thanks
for this tip! :-)

Thank you for your taking the time to write this up, it's very much
appreciated.

Cheers,
J.

-- 
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] Composite UNIQUE across two tables?

[Dirk Jagdmann]

>  > My advise would be to have two triggers for insert/update on the site
>  > and users table that check the uniqueness of the username with the
>  > site_group. A have made some tests with inserts and updates on the
>  > existing users and sites and these two functions seem to work.
>
>  I think this is the way that I'll go.  I'd hoped to somehow express this
>  solely in the design, if you know what i mean (e.g. without writing
>  SPs), but it looks like this is the best way to do it.

Well I thought about that, but finally came to the conclusion, that
standard SQL constraints can not express this inter-table
relationships. As I'm not a fan of (artificially) breaking up tables I
just wrote those two pl/pgsql functions, because I'd rather have a
simple table design and some complicated constraint checking functions
than the other way.

>  Thank you for your taking the time to write this up, it's very much
>  appreciated.

Most people reading this list like to think about/learn from other
people's problems.

-- 
---> Dirk Jagdmann
> http://cubic.org/~doj
-> http://llg.cubic.org

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql