{"id":6862,"date":"2016-01-16T03:42:22","date_gmt":"2016-01-16T02:42:22","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/"},"modified":"2016-01-16T03:42:22","modified_gmt":"2016-01-16T02:42:22","slug":"external-tables-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/","title":{"rendered":"External tables in PostgreSQL?"},"content":{"rendered":"<p>In Oracle you can use the <a href=\"http:\/\/docs.oracle.com\/database\/121\/ADMIN\/tables.htm#ADMIN01507\" target=\"_blank\" rel=\"noopener\">external table feature<\/a> to load data from files into the database. The traditional way to do this in PostgreSQL is to use the <a href=\"http:\/\/www.postgresql.org\/docs\/9.5\/static\/sql-copy.html\" target=\"_blank\" rel=\"noopener\">copy<\/a> command. But there is another option which makes use of <a href=\"http:\/\/www.postgresql.org\/docs\/9.5\/static\/ddl-foreign-data.html\" target=\"_blank\" rel=\"noopener\">foreign data wrappers<\/a>. The foreign data wrapper for doing this is <a href=\"http:\/\/www.postgresql.org\/docs\/9.5\/static\/file-fdw.html\" target=\"_blank\" rel=\"noopener\">file_fdw<\/a>.<\/p>\n<p>For creating some data here is a little script which generates 1&#8217;000 lines of the form: ID, TEXT:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n#!\/bin\/bash\n\nOUTPUT=\"\/var\/tmp\/data.csv\"\n`rm -rf ${OUTPUT}`\n\nfor i in {1..1000}\ndo\n  STRING=$(cat \/dev\/urandom | tr -dc 'a-zA-Z0-9' | fold -w 32 | head -n 1)\n  echo \"${i} , ${STRING}\" &gt;&gt; \/var\/tmp\/data.csv\ndone\n<\/pre>\n<p>For loading that into PostgreSQL the procedure is simple. First we need to create the extension and then define a server and a foreign table:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5000) [postgres] &gt; create extension file_fdw;\nCREATE EXTENSION\nTime: 752.715 ms\n(postgres@[local]:5000) [postgres] &gt; create server srv_file_fdw foreign data wrapper file_fdw;\nCREATE SERVER\nTime: 23.317 ms\n(postgres@[local]:5000) [postgres] &gt; create foreign table t_csv ( a int, b varchar(50) )\n                                            server srv_file_fdw \n                                            options ( filename '\/var\/tmp\/data.csv', format 'csv' );\nCREATE FOREIGN TABLE\nTime: 74.843 ms\n<\/pre>\n<p>From now we can access the table and load the data:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5000) [postgres] &gt; select count(*) from t_csv;\n count \n-------\n  1000\n(1 row)\n\nTime: 0.707 ms\n(postgres@[local]:5000) [postgres] &gt; create table t_csv2 as select * from t_csv;\nSELECT 1000\nTime: 147.859 ms\n<\/pre>\n<p>Currently, as outlined in the <a href=\"http:\/\/www.postgresql.org\/docs\/9.5\/static\/file-fdw.html\" target=\"_blank\" rel=\"noopener\">documentation<\/a>, you can not write to a foreign table defined with file_fdw:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5000) [postgres] &gt; insert into t_csv values (-1,'a');\nERROR:  cannot insert into foreign table \"t_csv\"\nTime: 18.113 ms\n<\/pre>\n<p>Using explain you can get some details of the file:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5000) [postgres] &gt; explain select count(*) from t_csv;\n                             QUERY PLAN                             \n--------------------------------------------------------------------\n Aggregate  (cost=171.15..171.16 rows=1 width=0)\n   -&gt;  Foreign Scan on t_csv  (cost=0.00..167.10 rows=1621 width=0)\n         Foreign File: \/var\/tmp\/data.csv\n         Foreign File Size: 38893\n(4 rows)\n\nTime: 0.521 ms\n<\/pre>\n<p>If you wonder why the estimate for the rows is wrong:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5000) [postgres] &gt; analyze t_csv;\nANALYZE\nTime: 79.925 ms\n(postgres@[local]:5000) [postgres] &gt; explain select count(*) from t_csv;\n                             QUERY PLAN                             \n--------------------------------------------------------------------\n Aggregate  (cost=107.50..107.51 rows=1 width=0)\n   -&gt;  Foreign Scan on t_csv  (cost=0.00..105.00 rows=1000 width=0)\n         Foreign File: \/var\/tmp\/data.csv\n         Foreign File Size: 38893\n(4 rows)\n\nTime: 0.296 ms\n<\/pre>\n<p>here you go &#8230;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Oracle you can use the external table feature to load data from files into the database. The traditional way to do this in PostgreSQL is to use the copy command. But there is another option which makes use of foreign data wrappers. The foreign data wrapper for doing this is file_fdw. For creating some [&hellip;]<\/p>\n","protected":false},"author":29,"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-6862","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 v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>External tables in PostgreSQL? - 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\/external-tables-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"External tables in PostgreSQL?\" \/>\n<meta property=\"og:description\" content=\"In Oracle you can use the external table feature to load data from files into the database. The traditional way to do this in PostgreSQL is to use the copy command. But there is another option which makes use of foreign data wrappers. The foreign data wrapper for doing this is file_fdw. For creating some [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-16T02:42:22+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\/external-tables-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"External tables in PostgreSQL?\",\"datePublished\":\"2016-01-16T02:42:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/\"},\"wordCount\":152,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/\",\"name\":\"External tables in PostgreSQL? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-01-16T02:42:22+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"External tables in PostgreSQL?\"}]},{\"@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":"External tables in PostgreSQL? - 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\/external-tables-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"External tables in PostgreSQL?","og_description":"In Oracle you can use the external table feature to load data from files into the database. The traditional way to do this in PostgreSQL is to use the copy command. But there is another option which makes use of foreign data wrappers. The foreign data wrapper for doing this is file_fdw. For creating some [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2016-01-16T02:42:22+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\/external-tables-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"External tables in PostgreSQL?","datePublished":"2016-01-16T02:42:22+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/"},"wordCount":152,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/","name":"External tables in PostgreSQL? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-01-16T02:42:22+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"External tables in PostgreSQL?"}]},{"@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\/6862","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=6862"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6862\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=6862"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=6862"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=6862"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=6862"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}