Re: Transform table data

In response to Jason Tan Boon Teck :
> Hi Andreas,
>
>
> Thank you for the prompt reply. I'm a real novice here, so here's some
> questions:
>
> 1. The values for 'cc' are not supposed to be in any order (could even
> be strings), then said statement would not work, right?

It works only with numerical data, not with strings, because of the
sum()-function.


>
> 2. What if i would like to use timestamp as the primary key.
> Therefore, the key would not be in sequential order. Is this possible?

I think no. Not in plain SQL. Maybe with a function, plpgsql.

>
> 3. Does this select statement just display the output as shown? Is
> there any method that I can use to transfer this output into another
> table, performed by postgresql instead of by programming in another
> language? My concern is time outs if the dataset is too large, bearing
> in mind that I'm developing a PHP web based app.

Sure, you can use CREATE TABLE new_table AS SELECT ...

>
> 4. My output from your SQL statement gives a different answer. Am i
> doing it wrong?

Yes:

>
> kguan=# select case
> kguan-#  when id = 0 then id/3 else (id/3)+1 end as key,

           when id%3 = 0 then id/3 else (id/3)+1 end as key,

Do you see the difference?


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