{"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:57","modified_gmt":"2023-09-20T05:58:57","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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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;&nbsp;<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133],"tags":[],"class_list":["post-27935","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - 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 services | 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;&nbsp;\" \/>\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 services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2023-09-20T05:58:57+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\"},\"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\",\"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,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"articleSection\":[\"Database\"],\"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 services | Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2023-09-20T05:58:57+00:00\",\"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 services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"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\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"caption\":\"Open source Team\"},\"description\":\"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/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 services | 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;&nbsp;","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 services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2023-09-20T05:58:57+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a"},"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","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,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"articleSection":["Database"],"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 services | Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-09-20T05:58:57+00:00","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 services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","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":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","caption":"Open source Team"},"description":"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.","url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_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\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=27935"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/27935\/revisions"}],"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"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}