<p>Hi <a href="https://github.com/pnorman" class="user-mention">@pnorman</a> let's start some design and drafts!... Do you have a <strong>UML class diagram</strong> of tables generated by <code>osm2pgsql</code>?</p>
<p>My suggestion is a option that produce another table structure... General ideia:</p>
<p><strong>WITHOUT the option</strong></p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">CREATE</span> <span class="pl-k">TABLE</span> <span class="pl-en">sometable1</span> (
id <span class="pl-k">serial</span> <span class="pl-k">NOT NULL</span> <span class="pl-k">PRIMARY KEY</span>,
mainfield11 <span class="pl-k">int</span>,
mainfield12 geometry(LINESTRING,<span class="pl-c1">4326</span>),
otherfield11 <span class="pl-k">int</span>,
otherfield12 <span class="pl-k">text</span>,
otherfield13 <span class="pl-k">int</span>,
UNIQUE(mainfield11)
);
<span class="pl-k">CREATE</span> <span class="pl-k">TABLE</span> <span class="pl-en">sometable2</span> (
id <span class="pl-k">serial</span> <span class="pl-k">NOT NULL</span> <span class="pl-k">PRIMARY KEY</span>,
fk <span class="pl-k">bigint</span> <span class="pl-k">REFERENCES</span> sometable1(id),
mainfield21 <span class="pl-k">int</span>,
mainfield22 <span class="pl-k">text</span>,
otherfield21 <span class="pl-k">int</span>,
otherfield22 <span class="pl-k">text</span>,
otherfield23 <span class="pl-k">int</span>,
UNIQUE(mainfield21,otherfield21)
);</pre></div>
<p><strong>WITH the JSONb option</strong></p>
<p>More compact and stable structure (not changes basic structure with future field changes),</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">CREATE</span> <span class="pl-k">TABLE</span> <span class="pl-en">sometable1</span> (
id <span class="pl-k">serial</span> <span class="pl-k">NOT NULL</span> <span class="pl-k">PRIMARY KEY</span>,
mainfield11 <span class="pl-k">int</span>,
mainfield12 geometry(LINESTRING,<span class="pl-c1">4326</span>),
info JSONb
UNIQUE(mainfield1)
);
<span class="pl-k">CREATE</span> <span class="pl-k">TABLE</span> <span class="pl-en">sometable2</span> (
id <span class="pl-k">serial</span> <span class="pl-k">NOT NULL</span> <span class="pl-k">PRIMARY KEY</span>,
fk <span class="pl-k">bigint</span> <span class="pl-k">REFERENCES</span> sometable1(id),
mainfield21 <span class="pl-k">int</span>,
otherfield21 <span class="pl-k">int</span>,
info JSONb,
UNIQUE(mainfield21,otherfield21)
);</pre></div>
<p>The only rule, to not move a field to JSONb, is that is "the field is in structural use": non-trivial datatypes (as <em>geometry</em> and <em>geography</em>), PRIMARY KEY, REFERENCES (FOREIGN KEY), UNIQUE, CHECK, etc. Some (subjective election) fields are "main" so we can preserve as SQL datatype.</p>
<p>We can also offer SQL VIEWs to obtain the old tables again,</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">CREATE</span> <span class="pl-k">VIEW</span> <span class="pl-en">old_sometable1</span> <span class="pl-k">AS</span>
<span class="pl-k">SELECT</span> <span class="pl-k">*</span>,
info<span class="pl-k">-</span><span class="pl-k">>></span><span class="pl-s"><span class="pl-pds">'</span>otherfield11<span class="pl-pds">'</span></span>::<span class="pl-k">int</span> <span class="pl-k">as</span> otherfield11, info<span class="pl-k">-</span><span class="pl-k">>></span><span class="pl-s"><span class="pl-pds">'</span>otherfield12<span class="pl-pds">'</span></span> <span class="pl-k">as</span> otherfield12, info<span class="pl-k">-</span><span class="pl-k">>></span><span class="pl-s"><span class="pl-pds">'</span>otherfield13<span class="pl-pds">'</span></span>::<span class="pl-k">int</span> <span class="pl-k">as</span> otherfield13
<span class="pl-k">FROM</span> sometable1;
<span class="pl-k">CREATE</span> <span class="pl-k">VIEW</span> <span class="pl-en">old_sometable2</span> <span class="pl-k">AS</span>
<span class="pl-k">SELECT</span> <span class="pl-k">*</span>, info<span class="pl-k">-</span><span class="pl-k">>></span><span class="pl-s"><span class="pl-pds">'</span>mainfield22<span class="pl-pds">'</span></span> <span class="pl-k">as</span> mainfield22,
info<span class="pl-k">-</span><span class="pl-k">>></span><span class="pl-s"><span class="pl-pds">'</span>otherfield22<span class="pl-pds">'</span></span>::<span class="pl-k">int</span> <span class="pl-k">as</span> otherfield22, info<span class="pl-k">-</span><span class="pl-k">>></span><span class="pl-s"><span class="pl-pds">'</span>otherfield23<span class="pl-pds">'</span></span> <span class="pl-k">as</span> otherfield23
<span class="pl-k">FROM</span> sometable2;</pre></div>
<p>The internal representation of <code>otherfield11</code>, <code>otherfield22</code>, etc. is in fact <em>integer</em>, but is "JSONb number", so it need a CAST to "SQL integer" in PostgreSQL 9.6 to express non-<em>text</em> fields.</p>
<hr>
<blockquote>
<p>JSONB is actually possible right now, declare a column as it, and use a lua transform which produces a string which casts cleanly to jsonb.</p>
</blockquote>
<p>Perfect! Can you show an little draft with this lua script? In principle the transform can be also after usual tables build, a <code>psql</code> script... But a previous transform save some memory and CPU time.</p>
<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">—<br />You are receiving this because you are subscribed to this thread.<br />Reply to this email directly, <a href="https://github.com/openstreetmap/osm2pgsql/issues/533#issuecomment-271532787">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/AD2-7tf0F5hHL381WX7MtinfVEbdiusrks5rQ1VlgaJpZM4HiUpY">mute the thread</a>.<img alt="" height="1" src="https://github.com/notifications/beacon/AD2-7s0689rGbwk5alvKnmil7Et5w2WYks5rQ1VlgaJpZM4HiUpY.gif" width="1" /></p>
<div itemscope itemtype="http://schema.org/EmailMessage">
<div itemprop="action" itemscope itemtype="http://schema.org/ViewAction">
<link itemprop="url" href="https://github.com/openstreetmap/osm2pgsql/issues/533#issuecomment-271532787"></link>
<meta itemprop="name" content="View Issue"></meta>
</div>
<meta itemprop="description" content="View this Issue on GitHub"></meta>
</div>
<script type="application/json" data-scope="inboxmarkup">{"api_version":"1.0","publisher":{"api_key":"05dde50f1d1a384dd78767c55493e4bb","name":"GitHub"},"entity":{"external_key":"github/openstreetmap/osm2pgsql","title":"openstreetmap/osm2pgsql","subtitle":"GitHub repository","main_image_url":"https://cloud.githubusercontent.com/assets/143418/17495839/a5054eac-5d88-11e6-95fc-7290892c7bb5.png","avatar_image_url":"https://cloud.githubusercontent.com/assets/143418/15842166/7c72db34-2c0b-11e6-9aed-b52498112777.png","action":{"name":"Open in GitHub","url":"https://github.com/openstreetmap/osm2pgsql"}},"updates":{"snippets":[{"icon":"PERSON","message":"@ppKrauss in #533: Hi @pnorman let's start some design and drafts!... Do you have a **UML class diagram** of tables generated by `osm2pgsql`? \r\n\r\nMy suggestion is a option that produce another table structure... General ideia:\r\n\r\n**WITHOUT the option**\r\n\r\n```sql\r\nCREATE TABLE sometable1 (\r\n id serial NOT NULL PRIMARY KEY,\r\n mainfield11 int,\r\n mainfield12 geometry(LINESTRING,4326),\r\n otherfield11 int,\r\n otherfield12 text,\r\n otherfield13 int,\r\n UNIQUE(mainfield11)\r\n);\r\n\r\nCREATE TABLE sometable2 (\r\n id serial NOT NULL PRIMARY KEY,\r\n fk bigint REFERENCES sometable1(id),\r\n mainfield21 int,\r\n mainfield22 text,\r\n otherfield21 int,\r\n otherfield22 text,\r\n otherfield23 int,\r\n UNIQUE(mainfield21,otherfield21)\r\n);\r\n```\r\n**WITH the JSONb option**\r\n\r\nMore compact and stable structure (not changes basic structure with future field changes),\r\n \r\n```sql\r\nCREATE TABLE sometable1 (\r\n id serial NOT NULL PRIMARY KEY,\r\n mainfield11 int,\r\n mainfield12 geometry(LINESTRING,4326),\r\n info JSONb\r\n UNIQUE(mainfield1)\r\n);\r\n\r\nCREATE TABLE sometable2 (\r\n id serial NOT NULL PRIMARY KEY,\r\n fk bigint REFERENCES sometable1(id),\r\n mainfield21 int,\r\n otherfield21 int,\r\n info JSONb,\r\n UNIQUE(mainfield21,otherfield21)\r\n);\r\n```\r\n\r\nThe only rule, to not move a field to JSONb, is that is \"the field is in structural use\": non-trivial datatypes (as *geometry* and *geography*), PRIMARY KEY, REFERENCES (FOREIGN KEY), UNIQUE, CHECK, etc. Some (subjective election) fields are \"main\" so we can preserve as SQL datatype. \r\n\r\nWe can also offer SQL VIEWs to obtain the old tables again,\r\n\r\n```sql\r\nCREATE VIEW old_sometable1 AS\r\n SELECT *, \r\n info-\u003e\u003e'otherfield11'::int as otherfield11, info-\u003e\u003e'otherfield12' as otherfield12, info-\u003e\u003e'otherfield13'::int as otherfield13 \r\n FROM sometable1;\r\n\r\nCREATE VIEW old_sometable2 AS\r\n SELECT *, info-\u003e\u003e'mainfield22' as mainfield22,\r\n info-\u003e\u003e'otherfield22'::int as otherfield22, info-\u003e\u003e'otherfield23' as otherfield23 \r\n FROM sometable2;\r\n``` \r\nThe internal representation of `otherfield11`, `otherfield22`, etc. is in fact *integer*, but is \"JSONb number\", so it need a CAST to \"SQL integer\" in PostgreSQL 9.6 to express non-*text* fields.\r\n \r\n\r\n----\r\n\r\n\u003e JSONB is actually possible right now, declare a column as it, and use a lua transform which produces a string which casts cleanly to jsonb.\r\n\r\nPerfect! Can you show an little draft with this lua script? In principle the transform can be also after usual tables build, a `psql` script... But a previous transform save some memory and CPU time.\r\n\r\n\r\n"}],"action":{"name":"View Issue","url":"https://github.com/openstreetmap/osm2pgsql/issues/533#issuecomment-271532787"}}}</script>