{"id":5137,"date":"2014-01-14T13:11:16","date_gmt":"2014-01-14T21:11:16","guid":{"rendered":"http:\/\/www.chesnok.com\/daily\/?p=5137"},"modified":"2014-01-14T13:13:59","modified_gmt":"2014-01-14T21:13:59","slug":"everyday-postgres-specifying-all-your-insert-columns","status":"publish","type":"post","link":"https:\/\/www.chesnok.com\/daily\/2014\/01\/14\/everyday-postgres-specifying-all-your-insert-columns\/","title":{"rendered":"Everyday Postgres: Specifying all your INSERT columns"},"content":{"rendered":"<p>Postgres has so many convenient features, including the ability to <em>not<\/em> provide a list of columns to an <code>INSERT<\/code>.<\/p>\n<p>For example:<\/p>\n<pre><code>CREATE TABLE temp_product_versions ( LIKE product_versions );\nINSERT INTO temp_product_versions ( SELECT * from product_versions ); \n<\/code><\/pre>\n<p>That&#8217;s pretty badass.<\/p>\n<p>However, you may encounter trouble in paradise later if you use this kind of shortcut in production code.<\/p>\n<p>See if you can spot the error in this code sample below.<\/p>\n<p>Here&#8217;s the error message:<\/p>\n<pre><code>ERROR:  column \"is_rapid_beta\" is of type boolean but expression is of type citext\nLINE 10:     repository\n             ^\nHINT:  You will need to rewrite or cast the expression.\nQUERY:  INSERT INTO releases_recent\nSELECT 'MetroFirefox',\n    version,\n    beta_number,\n    build_id\n    update_channel,\n    platform,\n    is_rapid,\n    is_rapid_beta,\n    repository\nFROM releases_recent\n    JOIN products\n        ON products.product_name = 'MetroFirefox'\nWHERE releases_recent.product_name = 'Firefox'\n    AND major_version_sort(releases_recent.version)\n        &gt;= major_version_sort(products.rapid_release_version)\nCONTEXT:  PL\/pgSQL function update_product_versions(integer) line 102 at SQL statement\n<\/code><\/pre>\n<p>And here&#8217;s the code (long!)<\/p>\n<p><script src=\"https:\/\/gist.github.com\/selenamarie\/d215038bbd6539ce7b88.js\"><\/script><br \/>\nI&#8217;m sure quite a few of you found the problem right away. For the rest of us&#8230;<\/p>\n<p>Here&#8217;s the error message you get if you specify the columns for the <code>INSERT<\/code>:<\/p>\n<pre><code>ERROR:  INSERT has more target columns than expressions\nLINE 10:     repository\n             ^\nQUERY:  INSERT INTO releases_recent (\n    product_name,\n    version,\n    beta_number,\n    build_id,\n    update_channel,\n    platform,\n    is_rapid,\n    is_rapid_beta,\n    repository\n)\nSELECT 'MetroFirefox',\n    version,\n    beta_number,\n    build_id\n    update_channel,\n    platform,\n    is_rapid,\n    is_rapid_beta,\n    repository\nFROM releases_recent\n    JOIN products\n        ON products.product_name = 'MetroFirefox'\nWHERE releases_recent.product_name = 'Firefox'\n    AND major_version_sort(releases_recent.version)\n        &gt;= major_version_sort(products.rapid_release_version)\nCONTEXT:  PL\/pgSQL function update_product_versions(integer) line 112 at SQL statement\n<\/code><\/pre>\n<p>Now, it should be completely obvious. There&#8217;s a missing comma after <code>build_id<\/code>.<\/p>\n<p>Implicit columns for <code>INSERT<\/code> are a convenient feature when you&#8217;re getting work done quickly, they are definitely not a best practice when writing production code. If you know of a linting tool for plpgsql that calls this kind of thing out, I&#8217;d love to hear about it and use it.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Postgres has so many convenient features, including the ability to not provide a list of columns to an INSERT. For example: CREATE TABLE temp_product_versions ( LIKE product_versions ); INSERT INTO temp_product_versions ( SELECT * from product_versions ); That&#8217;s pretty badass. &hellip; <a href=\"https:\/\/www.chesnok.com\/daily\/2014\/01\/14\/everyday-postgres-specifying-all-your-insert-columns\/\">Continue reading &rarr;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[604],"class_list":["post-5137","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-everyday-postgres"],"_links":{"self":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5137","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/comments?post=5137"}],"version-history":[{"count":5,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5137\/revisions"}],"predecessor-version":[{"id":5143,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5137\/revisions\/5143"}],"wp:attachment":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/media?parent=5137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/categories?post=5137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/tags?post=5137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}