{"id":27935,"date":"2023-09-20T07:58:57","date_gmt":"2023-09-20T05:58:57","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=27935"},"modified":"2023-09-20T07:58:59","modified_gmt":"2023-09-20T05:58:59","slug":"things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/","title":{"rendered":"Things you can, but probably shouldn&#8217;t do in PostgreSQL: Tables in tables, types in types, and arrays on top"},"content":{"rendered":"\n<p>PostgreSQL comes with a lot of flexibility for defining new data types, defining tables and defining arrays over data types. You can make that as complex as you want, which does not mean that you should do it. Somehow you still need to manage and maintain what your settings up. The more complex your schema gets, the more time people will need to understand what is going on there, and why it is like it this.<\/p>\n\n\n\n<p>Let&#8217;s start simple by creating a very tiny and basic table:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# create table t1 (a int );\nCREATE TABLE\npostgres=# \\d t1\n                 Table &quot;public.t1&quot;\n Column |  Type   | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a      | integer |           |          | \n<\/pre><\/div>\n\n\n<p>What PostgreSQL did internally is, that it created two types you can see in the <a href=\"https:\/\/www.postgresql.org\/docs\/16\/catalog-pg-type.html\" target=\"_blank\" rel=\"noreferrer noopener\">pg_type<\/a> table:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# select oid,typname,typlen,typtype,typcategory,typarray from pg_type where typname like &#039;%t1%&#039;;\n  oid  | typname | typlen | typtype | typcategory | typarray \n-------+---------+--------+---------+-------------+----------\n 24578 | t1      |     -1 | c       | C           |    24577\n 24577 | _t1     |     -1 | b       | A           |        0\n(2 rows)\n\n<\/pre><\/div>\n\n\n<p>What that means:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The type &#8220;_t1&#8221; is a so called base type (typtype=b) while &#8220;t1&#8221; is a composite type (e.g., a table&#8217;s row type).<\/li>\n\n\n\n<li>&#8220;typlen=-1&#8221; means it is a variable length type.<\/li>\n\n\n\n<li>&#8220;typarray&#8221; means, that our table gets its definition from the other type (oid=24577)<\/li>\n\n\n\n<li>&#8220;typcategory&#8221; means: A is an array type, C again is a composite type.<\/li>\n<\/ul>\n\n\n\n<p>As everything here is a type, one type can reference another type, e.g.:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# create table t2 ( a int, b t1 );\nCREATE TABLE\npostgres=# \\d t2\n                 Table &quot;public.t2&quot;\n Column |  Type   | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a      | integer |           |          | \n b      | t1      |           |          | \n<\/pre><\/div>\n\n\n<p>What happened here is, that we used the &#8220;t1&#8221; table as a data type for the second column of the &#8220;t2&#8221; table. Adding a row to the t1 table is straight forward, nothing special here:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# insert into t1 values(1);\nINSERT 0 1\n<\/pre><\/div>\n\n\n<p>How does that work for the second table, which uses the first table\/type as a data type for the second column? For this you can use a <a href=\"https:\/\/www.postgresql.org\/docs\/current\/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS\">row constructor<\/a> and cast that to the appropriate data type:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# insert into t2 values ( 1, row(1)::t1 );\nINSERT 0 1\npostgres=# select * from t2;\n a |  b  \n---+-----\n 1 | (1)\n(1 row)\n<\/pre><\/div>\n\n\n<p>If you want, you can use both tables as data types for a third table:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# create table t3 ( a int, b t1, c t2 );\nCREATE TABLE\n<\/pre><\/div>\n\n\n<p>Once more use the row constructor to add data:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# insert into t3 values ( 1, row(1)::t1, row(1,row(1)::t1)::t2 );\nINSERT 0 1\npostgres=# select * from t3;\n a |  b  |     c     \n---+-----+-----------\n 1 | (1) | (1,&quot;(1)&quot;)\n(1 row)\n<\/pre><\/div>\n\n\n<p>As you can see, this can become quite complex very fast. I am not saying there is no use case for this, but you should think carefully about what you really need. Let&#8217;s make another example and add arrays to the game. We again start with two little tables:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# create table x (a int, b int, c int, d int, e int, f int, g int, h int, j int );\nCREATE TABLE\npostgres=# create table xx ( a int, b x );\nCREATE TABLE\n<\/pre><\/div>\n\n\n<p>In the same way as above we&#8217;ll add some data to the second table:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# insert into xx values ( 1, row(1,1,1,1,1,1,1,1,1)::x );\nINSERT 0 1\npostgres=# select * from xx;\n a |          b          \n---+---------------------\n 1 | (1,1,1,1,1,1,1,1,1)\n(1 row)\n<\/pre><\/div>\n\n\n<p>On top of that we&#8217;ll create another tables which contains an array over the xx table as a column definition:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# create table xxx ( a int, b x, c xx&#x5B;] );\nCREATE TABLE\n<\/pre><\/div>\n\n\n<p>For getting data into this table you need to use the array constructor:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# insert into xxx values ( 1, row(1,1,1,1,1,1,1,1,1)::x, array&#x5B;row(1,row(1,1,1,1,1,1,1,1,1)::x)::xx,row(1,row(1,1,1,1,1,1,1,1,1)::x)::xx] );\nINSERT 0 1\npostgres=# select * from xxx;\n a |          b          |                               c                               \n---+---------------------+---------------------------------------------------------------\n 1 | (1,1,1,1,1,1,1,1,1) | {&quot;(1,\\&quot;(1,1,1,1,1,1,1,1,1)\\&quot;)&quot;,&quot;(1,\\&quot;(1,1,1,1,1,1,1,1,1)\\&quot;)&quot;}\n(1 row)\n<\/pre><\/div>\n\n\n<p>This is even more complex than the example above. What about querying data from that table? This can be done by using the same features as before:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# select * from xx where b = row(1,1,1,1,1,1,1,1,1)::x;\n a |          b          \n---+---------------------\n 1 | (1,1,1,1,1,1,1,1,1)\n(1 row)\n\npostgres=# select * from xxx where b = row(1,1,1,1,1,1,1,1,1)::x;\n a |          b          |                               c                               \n---+---------------------+---------------------------------------------------------------\n 1 | (1,1,1,1,1,1,1,1,1) | {&quot;(1,\\&quot;(1,1,1,1,1,1,1,1,1)\\&quot;)&quot;,&quot;(1,\\&quot;(1,1,1,1,1,1,1,1,1)\\&quot;)&quot;}\n(1 row)\n\npostgres=# select * from xxx where c = array&#x5B;row(1,row(1,1,1,1,1,1,1,1,1)::x)::xx,row(1,row(1,1,1,1,1,1,1,1,1)::x)::xx];\n a |          b          |                               c                               \n---+---------------------+---------------------------------------------------------------\n 1 | (1,1,1,1,1,1,1,1,1) | {&quot;(1,\\&quot;(1,1,1,1,1,1,1,1,1)\\&quot;)&quot;,&quot;(1,\\&quot;(1,1,1,1,1,1,1,1,1)\\&quot;)&quot;}\n(1 row)\n<\/pre><\/div>\n\n\n<p>Conclusion: By combining types and arrays you can do almost anything, but keep in mind that this needs to be managed and understood by other people. And even yourself, are you sure you understand what you did when you look at that a year later?<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL comes with a lot of flexibility for defining new data types, defining tables and defining arrays over data types. You can make that as complex as you want, which does not mean that you should do it. Somehow you still need to manage and maintain what your settings up. The more complex your schema [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[77],"type_dbi":[],"class_list":["post-27935","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Things you can, but probably shouldn&#039;t do in PostgreSQL: Tables in tables, types in types, and arrays on top - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Things you can, but probably shouldn&#039;t do in PostgreSQL: Tables in tables, types in types, and arrays on top\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL comes with a lot of flexibility for defining new data types, defining tables and defining arrays over data types. You can make that as complex as you want, which does not mean that you should do it. Somehow you still need to manage and maintain what your settings up. The more complex your schema [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-09-20T05:58:57+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-09-20T05:58:59+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Things you can, but probably shouldn&#8217;t do in PostgreSQL: Tables in tables, types in types, and arrays on top\",\"datePublished\":\"2023-09-20T05:58:57+00:00\",\"dateModified\":\"2023-09-20T05:58:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\\\/\"},\"wordCount\":470,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\\\/\",\"name\":\"Things you can, but probably shouldn't do in PostgreSQL: Tables in tables, types in types, and arrays on top - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2023-09-20T05:58:57+00:00\",\"dateModified\":\"2023-09-20T05:58:59+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Things you can, but probably shouldn&#8217;t do in PostgreSQL: Tables in tables, types in types, and arrays on top\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Things you can, but probably shouldn't do in PostgreSQL: Tables in tables, types in types, and arrays on top - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/","og_locale":"en_US","og_type":"article","og_title":"Things you can, but probably shouldn't do in PostgreSQL: Tables in tables, types in types, and arrays on top","og_description":"PostgreSQL comes with a lot of flexibility for defining new data types, defining tables and defining arrays over data types. You can make that as complex as you want, which does not mean that you should do it. Somehow you still need to manage and maintain what your settings up. The more complex your schema [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/","og_site_name":"dbi Blog","article_published_time":"2023-09-20T05:58:57+00:00","article_modified_time":"2023-09-20T05:58:59+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Things you can, but probably shouldn&#8217;t do in PostgreSQL: Tables in tables, types in types, and arrays on top","datePublished":"2023-09-20T05:58:57+00:00","dateModified":"2023-09-20T05:58:59+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/"},"wordCount":470,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/","url":"https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/","name":"Things you can, but probably shouldn't do in PostgreSQL: Tables in tables, types in types, and arrays on top - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-09-20T05:58:57+00:00","dateModified":"2023-09-20T05:58:59+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/things-you-can-but-probably-shouldnt-do-in-postgresql-tables-in-tables-types-in-types-and-arrays-on-top\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Things you can, but probably shouldn&#8217;t do in PostgreSQL: Tables in tables, types in types, and arrays on top"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/27935","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=27935"}],"version-history":[{"count":9,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/27935\/revisions"}],"predecessor-version":[{"id":27950,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/27935\/revisions\/27950"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=27935"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=27935"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=27935"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=27935"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}