title: Split comma-delimited string to columns
body: <p>I have a table with 52 fields at the moment but it will grow, and I'm trying to write a dynamic INSERT statement that will fill the fields with 0 without having to specify 52 0's.</p>
  
  <p>This is a cut-down version of the table:</p>
  
  <pre><code>CREATE TABLE tbladmin_reportsettings
  (
    intreportsettingsid integer NOT NULL,
    strReportGroup character varying (20) NOT NULL,
    ysnExcludePax1 smallint DEFAULT NULL,
    ysnExcludePax3 smallint DEFAULT NULL,
    ysnExcludePax2 smallint DEFAULT NULL,
    ysnExcludePax4 smallint DEFAULT NULL,
    ysnExcludePax5 smallint DEFAULT NULL,
    ysnHideAgentABN smallint DEFAULT NULL,
  CONSTRAINT pk_tbladmin_reportsettings PRIMARY KEY (intreportsettingsid)
  )
  </code></pre>
  
  <p>The INSERT script would be:</p>
  
  <pre><code>INSERT INTO tblAdmin_ReportSettings
  VALUES
  ((select * from getautonumber('ReportSettingsID')),'Base',0,0,0,0,0);
  </code></pre>
  
  <p>The DEFAULT NULL on the columns is intentional so I can't simply change that to 0.</p>
  
  <p>I can get a string of enough '0,' repetitions to fill the rest of the insert:</p>
  
  <pre><code>select string_agg(0::text,','::text) from information_schema.columns
  WHERE table_name = 'tbladmin_reportsettings'
  AND column_name NOT IN('intreportsettingsid', 'strreportgroup')
  </code></pre>
  
  <p>but it takes the string_agg all as one field - I need to split those out into individual fields.</p>
  
  <p>This is my attempt:</p>
  
  <pre><code>INSERT INTO tblAdmin_ReportSettings
  VALUES
  (select (select * from getautonumber('ReportSettingsID')),'Base',
          trim(string_agg('0',','),',')
   from information_schema.columns
   WHERE table_name = 'tbladmin_reportsettings'
   AND column_name NOT IN('intreportsettingsid', 'strreportgroup')
  )
  </code></pre>
  
  <p>but the output is:</p>
  
  <pre><code>column1 | column2 | column3
  29 | Base | 0,0,0,0,0
  </code></pre>
  
  <p>instead of</p>
  
  <pre><code>column1 | column2 | column3 | column4 | column5 | column6 | column7
  29 | Base | 0 | 0 | 0 | 0 | 0
  </code></pre>
  
  <p>I did try to create an array and unnest() it, but the result is multiple rows not multiple columns.</p>
  
  <p>Could someone please suggest a syntax or function that might get this working?</p>