title: INSERT rows from multi-dimensional array in Postgres
body: <p>I have the following table:</p>
  <pre><code>CREATE TABLE &quot;public&quot;.&quot;users&quot; (
      &quot;roles&quot; _text CHECK ((array_ndims(roles) = 2) AND (array_length(roles, 2) = 3)),
      &quot;userId&quot; varchar(1000) NOT NULL
  );
  </code></pre>
  <p>Which yields the following:</p>
  <pre><code>userId     | roles
  ------------------
  foobarbaz  | {{user,owner,123},{organization,owner,456}}
  </code></pre>
  <p>I have a separate table with the following schema:</p>
  <pre><code>CREATE TABLE &quot;public&quot;.&quot;roles&quot; (
      &quot;domain&quot; varchar(1000) NOT NULL,
      &quot;role&quot; varchar(1000) NOT NULL,
      &quot;domainId&quot; varchar(1000) NOT NULL,
      &quot;userId&quot; varchar(1000) NOT NULL,
  );
  </code></pre>
  <p>I'm trying to write a query that takes the rows from the users table, and populates the roles table</p>
  <p>so the result in the roles table would be:</p>
  <pre><code>userId     | domain       | role  | domainId
  -------------------------------------------
  foobarbaz  | user         | owner | 123
  foobarbaz  | organization | owner | 456
  </code></pre>
  <p>I was looking at using the <code>unnest()</code> function, but that would split the user roles column into multiple rows. What are my best options here using a combo of <code>select</code> and <code>insert</code>?</p>