shell脚本，向postgresql任意表中插入大量数据

由于业务需要，需要测试操作大量数据的时间，但是对于表字段比较多的表，并且表很多时，经常手动插入就很累，所以写了个自动根据系统表获取表字段，并且根据参数向表中插入大量数据的shell脚本

使用时需要的必要参数：

$host ，$port ：postgresql端实例连接用的端口号

$username，$password ：登录数据库时使用的用户名和密码

$dghome：postgresql的bin目录，目录下有psql工具，用来连接数据库

$dbname：表所在的数据库名称

$relation：需要插入数据的表名

 

获取表的主键名称 / 所有字段名称

colslist中存储表的所有字段名，用来后续生成sql语句

prikeylist中存储表的所有主键字段名，可以用可以不用，根据需求自行调整

function get_cols()
{
        # 得到表的所有字段名称
        colslist=(`PGPASSWORD=$password $dghome/psql -t -X -A -h $host -p $port -U $username -d $dbname \
                -c "SELECT a.attname \
                        FROM pg_catalog.pg_attribute a \
                        WHERE a.attrelid = '$relation'::regclass AND a.attnum > 0 AND NOT a.attisdropped \
                        ORDER BY a.attnum;"`)
        let colsnum=${#colslist[*]}

        # 得到表的主键列名称，可以查询多，列主键
        prikeylist=(`PGPASSWORD=$password $dghome/psql -t -X -A -h $host -p $port -U $username -d $dbname \
                -c "SELECT A.column_name \
                        FROM information_schema.columns A \
                        LEFT JOIN ( \
                                SELECT pg_attribute.attname \
                                FROM pg_index,pg_class,pg_attribute \
                                WHERE pg_class.oid = '$relation' :: regclass \
                                AND pg_index.indrelid = pg_class.oid \
                                AND pg_attribute.attrelid = pg_class.oid \
                                AND pg_attribute.attnum = ANY (pg_index.indkey) \
                        ) B ON A.column_name = b.attname \
                        WHERE length(B.attname) > 0 \
                        AND A.table_name = '$relation' \
                        ORDER BY ordinal_position ASC;"`)
        let prikeynum=${#prikeylist[*]}

        echo "======table '$relation' cols:======"
        echo ${colslist[*]}
        echo "======table '$relation' primary keys======"
        echo ${prikeylist[*]}
}

生成插入数据的sql

不推荐使用for循环进行插入，连接次数多，比如百万条后会很慢，这里直接使用的generate_series函数

这里支持的数据类型有int型，numeric型，text型，carchar型，char[x,y]型和timestramp型，

有其他需求可以加if语句自己扩展

此函数和上面的函数同时使用，有两个参数，第一个参数是各个字段插入的最小值数组，第二个参数是各个字段插入的最大值数组，调用时使用  mak_sql "${lelist}" "${rilist}"  这种形式进行调用，其中 lelist 为最小值数组，rilist为最大值数组

生成的sql语句会保存在变量sql中，由于shell默认全局变量，外部可以直接使用 $sql

function mak_sql()
{
        lelist=$1
        rilist=$2
        #echo ${lelist[*]}
        #echo ${rilist[*]}
        sql="insert into $relation select "

        for i in $(seq 0 $(($colsnum-1)))
        do
                col=${colslist[$i]}
                le=${lelist[$i]}
                ri=${rilist[$i]}

                # Get column type
                typ=(`PGPASSWORD=$password $dghome/psql -t -X -A -h $host -p $port -U $username -d $dbname \
                        -c "SELECT pg_catalog.format_type(a.atttypid, a.atttypmod) \
                                FROM pg_catalog.pg_attribute a \
                                WHERE a.attrelid = '$relation'::regclass \
                                AND a.attnum > 0 AND NOT a.attisdropped \
                                AND a.attname = '$col';"`)
                #echo ${typ[*]}
                typ=${typ[*]}
                #echo ">>$col<< : $typ"
                if [ $i -ne 0 ] ; then
                        sql=$sql","
                fi

                #character varying
                if [[ $typ =~ char[.]*varying[.]* ]] ; then
                        sql=$sql" generate_series($le,$ri)::text "
                #char()
                elif [[ $typ =~ [.]*char[.]* ]] ; then
                        sql=$sql" generate_series($le,$ri)::text "
                #text
                elif [ "$typ" == "text" ] ; then
                        sql=$sql" generate_series($le,$ri)::text "
                #int
                elif [[ $typ =~ [.]*int[.]* ]] ; then
                        sql=$sql" generate_series($le,$ri) "
                #numeric
                elif [[ $typ =~ [.]*numeric[.]* ]] ; then
                        sql=$sql" generate_series($le,$ri) "
                #timestamp
                elif [[ $typ =~ [.]*timestamp[.]* ]] ; then
                        sql=$sql" now() - ('1 day'::interval*floor(generate_series($le,$ri)*3650)) "
                fi
        done

        sql=$sql" ;"
}

调用及结果测试

需要进行插入数据的表的信息如下：

下面进行测试，先使用get_cols函数得到表的基础信息

使用如下代码生成插入值的上下限数组，并获取sql：

for i in $(seq 0 $(($colsnum-1)))
do
    lelist[$i]="1"
    rilist[$i]="1000000"
done

mak_sql "${lelist}" "${rilist}"

使用生成的sql，可以直接使用

PGPASSWORD=$password $dghome/psql -h ${host} -p ${port} -U $username -d $dbname -c "$sql"

 连接数据库进行查看：