Postgresql How to create a table with n columns

If you need to create a table with 99 columns, the column names are regular, such as S01,s02,s03 ... Perhaps you would think of the following statement

CREATE TABLE tb_sxx (s01 varchar), S02 varchar (TEN), S03 varchar, ... s99 varchar (10));

Is there a quicker way? Of course, let's take a look at the three powerful functions of PostgreSQL.
Generate_series (x, y)--generates a value between x and Y
Array_agg (x)--Convert the X value into a format like {1,2,3,4}, i.e., to a group
Array_to_string ({}, ', ') turns the array into a string, where ', ' is the delimiter of the array

For a more intuitive understanding of the following examples (generating a 5-column table), step-by-stage instructions, if you are familiar with the above three functions, can go directly to the 6th step:

1. Generate a number from 1 to 5
SELECT generate_series (1,5);

Hotel=> SELECT generate_series (1,5); Generate_series-----------------1 2 3 4 5 (5 rows)

2. Turn 1 to 5 of the number into the array
SELECT Array_agg (i)
From Generate_series (1,5) as I;

Hotel=> SELECT Array_agg (i) hotel-> from generate_series (1,5) as I; Array_agg-------------{1,2,3,4,5} (1 row)

3. Process the data generated by step 2nd to change the format to {S01,S02,S03,S04,S05}
SELECT Array_agg (' s ' | | | lpad (i::text,2, ' 0 '))
From Generate_series (1,5) as I;

hotel=> SELECT Array_agg (' s ' | | | lpad (i::text,2, ' 0 ')) hotel-> from Generate_series (1,5) as I; Array_agg-----------------------{S01,S02,S03,S04,S05} (1 row)

Explain:
I::text is the conversion of I into a character-like shape
Lpad is to convert I to 2 bits, and 0 on the left when the number of digits is insufficient.

4. Process the data generated by the 3rd step, convert it to {S01 varchar (ten), S02 varchar (TEN), S03 varchar (TEN), S04 varchar (TEN), S05 varchar (10)}
SELECT Array_agg (' s ' | | | lpad (i::text,2, ' 0 ') | | ' varchar (10) ')
From Generate_series (1,5) as I;

hotel=> SELECT Array_agg (' s ' | | | lpad (i::text,2, ' 0 ') | |                                          ' varchar ') hotel-> from generate_series (1,5) as I; Array_agg----------------------------------------------------------------------- ----------------------{"S01 varchar", "S02 varchar", "s03 varchar", "s04 varchar", "s05 varchar (10)"} (1 Row

5. Process the data generated by step 4th, convert it to a string
 SELECT  array_to_string (
               Array_agg (' s ' | | | lpad (i::text,2, ' 0 ') | | ' varchar ')
              , ', ')
from Generate_series (1,5) as I;

hotel=> select  array_to_string  ( hotel (>                array_agg (' s '  | |  lpad (i::text,2, ' 0 ')  | |   '  varchar (Ten) ')  hotel (>                 ,  ', ') hotel-> from generate_series (1,5)  AS i;                                   array_to_string                                   -------------------------- ------------------------------------------------------- s01 varchar, S02 varchar (Ten), S03  varchar (Ten), S04 varChar (Ten), S05 varchar (1 row) 

6. Finalize the Build statement
SELECT ' CREATE TABLE tb_s5 (' | |
Array_to_string (
Array_agg (' s ' | | | lpad (i::text,2, ' 0 ') | | ' varchar (10) ')
, ', ') | |
‘);‘
From Generate_series (1,5) as I;

hotel=>  select   ' create table tb_s5  (  '  | | Hotel->               array_to_ string  ( hotel (>                array_agg (' s '  | |  lpad (i::text,2, ' 0 ')  | |   '  varchar (Ten) ')  hotel (>                 ,  ', ')  | | hotel->                '); ' Hotel-> from generate_series (1,5)  AS i;                                                   ?column?                                                  -------------------------------------------- ------------------------------------------------------------ CREATE TABLE tb_s5  ( s01  varchar (S02 varchar), S03 varchar (Ten), S04 varchar (1), S05 varchar (Ten));(  row)

The build table statement is generated, copy and paste can be executed, if it is psql also use the following to save the following statement as
Build_s5.psql file, then execute directly with Psql
Psql-h localhost-p 5432-u username-f build_s5.psql-d dbname

The contents of the file are as follows:

\a \ t
SELECT ' CREATE TABLE tb_s5 (' | |
Array_to_string (
Array_agg (' s ' | | | lpad (i::text,2, ' 0 ') | | ' varchar (10) ')
, ', ') | |
‘);‘
From Generate_series (1,5) as I;
\g Create_s5.sql
\i Create_s5.sql

Explain:
\a Closing the Alignment mode
\ t turns off the marked line output
\G output the query results to the specified file
\i Executing files

This article is from the "Mirror" blog, please be sure to keep this source http://383133430.blog.51cto.com/454215/1739310

Postgresql How to create a table with n columns