{"id":9429,"date":"2016-11-27T18:59:27","date_gmt":"2016-11-27T17:59:27","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-4-external-tables\/"},"modified":"2016-11-27T18:59:27","modified_gmt":"2016-11-27T17:59:27","slug":"can-i-do-it-with-postgresql-4-external-tables","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-4-external-tables\/","title":{"rendered":"Can I do it with PostgreSQL? \u2013 4 \u2013 External tables"},"content":{"rendered":"<p>In the last posts of this series we talked about <a href=\"http:\/\/dbi-services.com\/blog\/can-i-do-it-with-postgresql-1-restore-points\/\" target=\"_blank\" rel=\"noopener\">restore points<\/a>, how you could do things <a href=\"http:\/\/dbi-services.com\/blog\/can-i-do-it-with-postgresql-2-dual\/\" target=\"_blank\" rel=\"noopener\">that would require the dual table in Oracle<\/a> and <a href=\"http:\/\/dbi-services.com\/blog\/can-i-do-it-with-postgresql-3-tablespaces\/\" target=\"_blank\" rel=\"noopener\">how you can make use of tablespaces in PostgreSQL<\/a>. In this post we&#8217;ll look at what my colleague <a href=\"https:\/\/www.dbi-services.com\/on-the-company-and-its-associates\/our-associates-and-certified-experts\/clemens-bleile\/\" target=\"_blank\" rel=\"noopener\">Clemens<\/a> thinks is <a href=\"http:\/\/dbi-services.com\/blog\/otn-appreciation-day-external-tables\/\" target=\"_blank\" rel=\"noopener\">one of the greatest features in Oracle<\/a>. Can you do external external tables in PostgreSQL?<\/p>\n<p><!--more--><\/p>\n<p>The easy answers is: yes, of course you can. And you can do it in various ways. To start with we&#8217;ll need a sample file were we can load data from. For the test here we&#8217;ll use <a href=\"http:\/\/spatialkeydocs.s3.amazonaws.com\/FL_insurance_sample.csv.zip\" target=\"_blank\" rel=\"noopener\">this one<\/a>. Note that this file uses Windows line feeds which you&#8217;ll need to convert to unix style if you are working on Linux like me. You can <a href=\"http:\/\/stackoverflow.com\/questions\/811193\/how-to-convert-the-m-linebreak-to-normal-linebreak-in-a-file-opened-in-vim\" target=\"_blank\" rel=\"noopener\">use VI to do this<\/a>.<\/p>\n<p>Once you extracted the file the content looks like this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres@pgbox:\/home\/postgres\/ [PG961] head -2 FL_insurance_sample.csv\npolicyID,statecode,county,eq_site_limit,hu_site_limit,fl_site_limit,fr_site_limit,tiv_2011,tiv_2012,eq_site_deductible,hu_site_deductible,fl_site_deductible,fr_site_deductible,point_latitude,point_longitude,line,construction,point_granularity\n119736,FL,CLAY COUNTY,498960,498960,498960,498960,498960,792148.9,0,9979.2,0,0,30.102261,-81.711777,Residential,Masonry,1\n<\/pre>\n<p>So, we have a total of 18 columns and 36634 rows to test with. Should be fine \ud83d\ude42<\/p>\n<p>How can we bring that into PostgreSQL? Clemens talked about <a href=\"http:\/\/docs.oracle.com\/database\/122\/SUTIL\/oracle-sql-loader-concepts.htm#SUTIL003\" target=\"_blank\" rel=\"noopener\">SQL*Loader<\/a> in his post. There is a similar project for PostgreSQL called <a href=\"https:\/\/github.com\/ossc-db\/pg_bulkload\" target=\"_blank\" rel=\"noopener\">pg_bulkload<\/a> which we&#8217;ll not be talking about. We will look at two options you can use to load data from files into PostgreSQL which are available by default:<\/p>\n<ol>\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/9.6\/static\/sql-copy.html\" target=\"_blank\" rel=\"noopener\">copy<\/a><\/li>\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/9.6\/static\/file-fdw.html\" target=\"_blank\" rel=\"noopener\">file_fdw<\/a><\/li>\n<\/ol>\n<p>What we need no matter with which option we go first is the definition of the table. These are the columns we need:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres@pgbox:\/home\/postgres\/ [PG961] head -1 FL_insurance_sample.csv | sed 's\/,\/,n\/g'\npolicyID,\nstatecode,\ncounty,\neq_site_limit,\nhu_site_limit,\nfl_site_limit,\nfr_site_limit,\ntiv_2011,\ntiv_2012,\neq_site_deductible,\nhu_site_deductible,\nfl_site_deductible,\nfr_site_deductible,\npoint_latitude,\npoint_longitude,\nline,\nconstruction,\npoint_granularity\n<\/pre>\n<p>So the create table statement will look something like this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5439) [postgres] &gt; create table exttab ( policyID int,\n                                                           statecode varchar(2),\n                                                           county varchar(50),\n                                                           eq_site_limit numeric,\n                                                           hu_site_limit numeric,\n                                                           fl_site_limit numeric,\n                                                           fr_site_limit numeric,\n                                                           tiv_2011 numeric,\n                                                           tiv_2012 numeric,\n                                                           eq_site_deductible numeric,\n                                                           hu_site_deductible numeric,\n                                                           fl_site_deductible numeric,\n                                                           fr_site_deductible numeric,\n                                                           point_latitude numeric,\n                                                           point_longitude numeric,\n                                                           line varchar(50),\n                                                           construction varchar(50),\n                                                           point_granularity int);\nCREATE TABLE\n<\/pre>\n<p>Now that we have the table we can use copy to load the data:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5439) [postgres] &gt; copy exttab from '\/home\/postgres\/FL_insurance_sample.csv' with csv header;\nCOPY 36634\n(postgres@[local]:5439) [postgres] &gt; select count(*) from exttab;\n count \n-------\n 36634\n(1 row)\n<\/pre>\n<p>Quite fast. But there is a downside with this approach. As Clemens mentions in his posts one of the benefits of external tables in Oracle is that you can access the file via standard SQL and do transformations before the data arrives in the database. Can you do the same with PostgreSQL? Yes, if you use the <a href=\"https:\/\/www.postgresql.org\/docs\/9.6\/static\/file-fdw.html\" target=\"_blank\" rel=\"noopener\">file_fdw<\/a> <a href=\"https:\/\/www.postgresql.org\/docs\/9.6\/static\/ddl-foreign-data.html\" target=\"_blank\" rel=\"noopener\">foreign data<\/a> wrapper.<\/p>\n<p>The file_fdw is available by default:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5439) [postgres] &gt; create extension file_fdw;\nCREATE EXTENSION\nTime: 442.777 ms\n(postgres@[local]:5439) [postgres] &gt; dx\n                        List of installed extensions\n   Name   | Version |   Schema   |                Description                \n----------+---------+------------+-------------------------------------------\n file_fdw | 1.0     | public     | foreign-data wrapper for flat file access\n plpgsql  | 1.0     | pg_catalog | PL\/pgSQL procedural language\n\n(postgres@[local]:5439) [postgres] &gt; create server srv_file_fdw foreign data wrapper file_fdw;\nCREATE SERVER\n(postgres@[local]:5439) [postgres] &gt; create foreign table exttab2  ( policyID int,\n                                statecode varchar(2),\n                                county varchar(50),\n                                eq_site_limit numeric,     \n                                hu_site_limit numeric,     \n                                fl_site_limit numeric,     \n                                fr_site_limit numeric,     \n                                tiv_2011 numeric,          \n                                tiv_2012 numeric,          \n                                eq_site_deductible numeric,\n                                hu_site_deductible numeric,\n                                fl_site_deductible numeric,\n                                fr_site_deductible numeric,\n                                point_latitude numeric,    \n                                point_longitude numeric,   \n                                line varchar(50),          \n                                construction varchar(50),  \n                                point_granularity int)     \nserver srv_file_fdw options ( filename '\/home\/postgres\/FL_insurance_sample.csv', format 'csv', header 'true' );\nCREATE FOREIGN TABLE\n\n(postgres@[local]:5439) [postgres] &gt; select count(*) from exttab2;\n count \n-------\n 36634\n(1 row)\n\n<\/pre>\n<p>From now on you can work with the file by accessing it using standard SQL and all the options you have with SQL are available. Very much the same as Clemens states in his post: &#8220;Because external tables can be accessed through SQL. You have all possibilities SQL-queries offer. Prallelism, difficult joins with internal or other external tables and of course all complex operations SQL allows. ETL became much easier using external tables, because it allowed to process data through SQL joins and filters already before it was loaded in the database.&#8221;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the last posts of this series we talked about restore points, how you could do things that would require the dual table in Oracle and how you can make use of tablespaces in PostgreSQL. In this post we&#8217;ll look at what my colleague Clemens thinks is one of the greatest features in Oracle. Can [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-9429","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"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>Can I do it with PostgreSQL? \u2013 4 \u2013 External tables - 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\/can-i-do-it-with-postgresql-4-external-tables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Can I do it with PostgreSQL? \u2013 4 \u2013 External tables\" \/>\n<meta property=\"og:description\" content=\"In the last posts of this series we talked about restore points, how you could do things that would require the dual table in Oracle and how you can make use of tablespaces in PostgreSQL. In this post we&#8217;ll look at what my colleague Clemens thinks is one of the greatest features in Oracle. Can [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-4-external-tables\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-11-27T17:59:27+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\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=\"4 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\\\/can-i-do-it-with-postgresql-4-external-tables\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-4-external-tables\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"Can I do it with PostgreSQL? \u2013 4 \u2013 External tables\",\"datePublished\":\"2016-11-27T17:59:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-4-external-tables\\\/\"},\"wordCount\":433,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-4-external-tables\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-4-external-tables\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-4-external-tables\\\/\",\"name\":\"Can I do it with PostgreSQL? \u2013 4 \u2013 External tables - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2016-11-27T17:59:27+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-4-external-tables\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-4-external-tables\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-4-external-tables\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Can I do it with PostgreSQL? \u2013 4 \u2013 External tables\"}]},{\"@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\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Can I do it with PostgreSQL? \u2013 4 \u2013 External tables - 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\/can-i-do-it-with-postgresql-4-external-tables\/","og_locale":"en_US","og_type":"article","og_title":"Can I do it with PostgreSQL? \u2013 4 \u2013 External tables","og_description":"In the last posts of this series we talked about restore points, how you could do things that would require the dual table in Oracle and how you can make use of tablespaces in PostgreSQL. In this post we&#8217;ll look at what my colleague Clemens thinks is one of the greatest features in Oracle. Can [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-4-external-tables\/","og_site_name":"dbi Blog","article_published_time":"2016-11-27T17:59:27+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-4-external-tables\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-4-external-tables\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"Can I do it with PostgreSQL? \u2013 4 \u2013 External tables","datePublished":"2016-11-27T17:59:27+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-4-external-tables\/"},"wordCount":433,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-4-external-tables\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-4-external-tables\/","url":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-4-external-tables\/","name":"Can I do it with PostgreSQL? \u2013 4 \u2013 External tables - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-11-27T17:59:27+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-4-external-tables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-4-external-tables\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-4-external-tables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Can I do it with PostgreSQL? \u2013 4 \u2013 External tables"}]},{"@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\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"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\/9429","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=9429"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9429\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9429"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9429"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9429"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9429"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}