Tally Table

[doug_harmon]

I needed to create a tally table. Here's how I did it.

Code: Select all

#!/bin/bash
# Create a Tally Table.

export SQLFile=/tmp/TallyLoad.sql
echo -n "" > $SQLFile
export DataFile=/tmp/Tally.dat
echo -n "" > $DataFile

export i
for i in {1..100000}
do
    echo  "${i}"   >> $DataFile
done

echo 'DROP TABLE Tally;' >> $SQLFile
echo 'CREATE TABLE Tally(N INT) UNSEGMENTED ALL NODES;' >> $SQLFile
echo 'COPY Tally FROM ' "'""${DataFile}""'"  ';' >> $SQLFile

vsql < $SQLFile

rm $SQLFile
rm $DataFile

[JimKnicely]

That's cool!

Another way to create that sort of table is with SQL...

Code: Select all

dbadmin=> create table tally as select n from (select row_number() over() n from columns cross join tables) as foo where n <= 100000;
CREATE TABLE
dbadmin=> \d tally;
                                List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
 public | tally | n      | int  |    8 |         | f        | f           |
(1 row)

dbadmin=> select count(*) from tally;
 count
--------
 100000
(1 row)

I think this is doing the same thing you were doing

[doug_harmon]

Thanks for sharing, that's an elegant solution.