{"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":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-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 v28.8 (Yoast SEO v28.8) - 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=\"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=\"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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/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":"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":"Open source Team","twitter_card":"summary_large_image","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\/external-tables-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/external-tables-in-postgresql\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/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\/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\/28"}],"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}]}}