Skip site navigation (1) Skip section navigation (2)

Re: Transform table data

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Transform table data
Date: 2009-05-15 11:52:03
Message-ID: 20090515115203.GE20934@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Dante torioDate: 2009-05-15 12:33:48
Subject: Re: Primary/Foreign Keys
Previous:From: Jason Tan Boon TeckDate: 2009-05-15 11:37:26
Subject: Re: Transform table data

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group