Thread: How to create a specific table

Pierre Hsieh wrote
> Hi,
>
> Is there anyone who can help me to create a specific table as following?
> Thanks
>
> Pierre
>
> rule:
> 1. just one column which type is integer in table
> 2. this columns only has 1 and 2 for 50 times as following
>
> 1
> 2
> 1
> 2
> 1
> 2
> 1
> 2
> .....

use generate_series(...), the modulus operator (to determine even/odd via
%2), and +1

HTH

David J.




--
View this message in context: http://postgresql.nabble.com/How-to-create-a-specific-table-tp5835024p5835026.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 01/22/2015 06:54 AM, Pierre Hsieh wrote:
> Hi,
>
> Is there anyone who can help me to create a specific table as following?
> Thanks

The commands you will need are here:

http://www.postgresql.org/docs/9.3/interactive/sql-commands.html

In particular:

CREATE TABLE
http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html

INSERT
http://www.postgresql.org/docs/9.3/interactive/sql-insert.html

>
> Pierre
>
> rule:
> 1. just one column which type is integer in table
> 2. this columns only has 1 and 2 for 50 times as following
>
> 1
> 2
> 1
> 2
> 1
> 2
> 1
> 2
> .....


--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Jan 22, 2015 at 11:59 PM, David G Johnston
<david.g.johnston@gmail.com> wrote:
> Pierre Hsieh wrote
>> 1. just one column which type is integer in table
>> 2. this columns only has 1 and 2 for 50 times as following
> use generate_series(...), the modulus operator (to determine even/odd via
> %2), and +1
Yes, embedded with CREATE TABLE AS:
=# create table test as select a % 2 + 1 from generate_series(1,100) as a;
SELECT 100
--
Michael

On 1/22/2015 6:54 AM, Pierre Hsieh wrote:
> 1. just one column which type is integer in table
> 2. this columns only has 1 and 2 for 50 times as following

note that tables are unordered sets, the rows of a table have no implied
order.    1 1 1 1 1 2 2 2 2 2 is the same table as 1 2 1 2 1 2 1 2 1 2 ...





--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

> On 22 Jan 2015, at 15:54, Pierre Hsieh <pierre.hsieh@gmail.com> wrote:
> rule:
> 1. just one column which type is integer in table
> 2. this columns only has 1 and 2 for 50 times as following
>
> 1
> 2
> 1
> 2
> 1
> 2
> 1
> 2
> .....

create table test as select b from generate_series(1,50) s1(a), generate_series(1,2) s2(b);

But since tables are unordered sets, you'll need something to order your data by when querying, so I'd use this:

create table test as select a, b from generate_series(1,50) s1(a), generate_series(1,2) s2(b);
select b from test order by a,b;


I'm kind of curious what kind of problem you're trying to solve. First you ask how to calculate the standard deviation
ofblocks of 50 records and now this. Are you sure those blocks are ALWAYS 50 records large? Never, say 49 or 51 because
somerecords either weren't stored at all or were stored in the wrong block? If that occurs, you're dealing with an
increasingskew in your statistics. Perhaps the right thing to ask yourself is what groups those 50 records together,
whatdo they have in common? 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.