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.

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

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

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

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

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

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

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

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 of blocks of 50 records and now this. Are you sure those blocks are ALWAYS 50 records large? Never, say 49 or 51 because some records either weren't stored at all or were stored in the wrong block? If that occurs, you're dealing with an increasing skew in your statistics. Perhaps the right thing to ask yourself is what groups those 50 records together, what do 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.

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