{"id":12271,"date":"2019-02-22T10:18:03","date_gmt":"2019-02-22T09:18:03","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/working-with-files-on-the-filesystem-in-postgresql\/"},"modified":"2019-02-22T10:18:03","modified_gmt":"2019-02-22T09:18:03","slug":"working-with-files-on-the-filesystem-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/working-with-files-on-the-filesystem-in-postgresql\/","title":{"rendered":"Working with files on the filesystem in PostgreSQL"},"content":{"rendered":"<p>PostgreSQL comes with various helper functions that support you with working with files on the filesystem on the host PostgreSQL is running on. You might ask yourself why that is important but there are use cases for that. Maybe you want to list the contents of a directory because new files that showed up since the last check do trigger something. Maybe you want to load a file into the database (which you also can (and event should) do using <a href=\"https:\/\/www.postgresql.org\/docs\/current\/sql-copy.html\" target=\"_blank\" rel=\"noopener noreferrer\">copy<\/a> if it is text based and somehow well formatted, but that is not the scope of this post). <\/p>\n<p><!--more--><\/p>\n<p>For listing files in a directory there is this one:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select * from pg_ls_dir('.');\n      pg_ls_dir       \n----------------------\n pg_wal\n global\n pg_commit_ts\n pg_dynshmem\n pg_notify\n pg_serial\n pg_snapshots\n pg_subtrans\n pg_twophase\n pg_multixact\n base\n pg_replslot\n pg_tblspc\n pg_stat\n pg_stat_tmp\n pg_xact\n pg_logical\n PG_VERSION\n postgresql.conf\n postgresql.auto.conf\n pg_hba.conf\n pg_ident.conf\n pg_log\n postmaster.opts\n autoprewarm.blocks\n postmaster.pid\n current_logfiles\n(27 rows)\n<\/pre>\n<p>By default the &#8216;.&#8217; listings are omitted by you can control this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select * from pg_ls_dir('.',true,true);\n      pg_ls_dir       \n----------------------\n .\n ..\n pg_wal\n global\n pg_commit_ts\n pg_dynshmem\n pg_notify\n pg_serial\n pg_snapshots\n pg_subtrans\n pg_twophase\n pg_multixact\n base\n pg_replslot\n pg_tblspc\n pg_stat\n pg_stat_tmp\n pg_xact\n pg_logical\n PG_VERSION\n postgresql.conf\n postgresql.auto.conf\n pg_hba.conf\n pg_ident.conf\n pg_log\n postmaster.opts\n autoprewarm.blocks\n postmaster.pid\n current_logfiles\n(29 rows)\n<\/pre>\n<p>There is no option to control sorting but of course you can add a where clause to do this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select * from pg_ls_dir('.',true,true) order by 1;\n      pg_ls_dir       \n----------------------\n .\n ..\n autoprewarm.blocks\n base\n current_logfiles\n global\n pg_commit_ts\n pg_dynshmem\n pg_hba.conf\n pg_ident.conf\n pg_log\n pg_logical\n pg_multixact\n pg_notify\n pg_replslot\n pg_serial\n pg_snapshots\n pg_stat\n pg_stat_tmp\n pg_subtrans\n pg_tblspc\n pg_twophase\n PG_VERSION\n pg_wal\n pg_xact\n postgresql.auto.conf\n postgresql.conf\n postmaster.opts\n postmaster.pid\n(29 rows)\n<\/pre>\n<p>You could load that into an array and then do whatever you want to do with it for further processing:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# x\nExpanded display is on.\npostgres=# with dirs as (select pg_ls_dir('.'::text,true,true) dir order by 1)\n                select array_agg(dir) from dirs;\n-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\narray_agg | {.,..,autoprewarm.blocks,base,current_logfiles,global,pg_commit_ts,pg_dynshmem,pg_hba.conf,pg_ident.conf,pg_log,pg_logical,pg_multixact,pg_notify,pg_replslot,pg_serial,pg_snapshots,pg_stat,pg_stat_tmp,pg_subtrans,pg_tblspc,pg_twophase,PG_VERSION,pg_wal,pg_xact,postgresql.auto.conf,postgresql.conf,postmaster.opts,postmaster.pid}\n\n<\/pre>\n<p>When you try to list the files of a directory you do not have the permissions to do so of course that fails:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select pg_ls_dir('\/root');\nERROR:  could not open directory \"\/root\": Permission denied\n<\/pre>\n<p>All other directories the PostgreSQL operating system user has access to can be listed:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# x\nExpanded display is off.\npostgres=# select pg_ls_dir('\/var\/tmp');\n                                pg_ls_dir                                \n-------------------------------------------------------------------------\n yum-postgres-uSpYMT\n systemd-private-f706224b798a404a8b1b7efbbb7137c9-chronyd.service-saK1Py\n systemd-private-bcd40d1946c94f1fbcb73d1047ee2fc2-chronyd.service-Fr7WgV\n systemd-private-798725e073664df6bbc5c6041151ef61-chronyd.service-kRvvJa\n(4 rows)\n<\/pre>\n<p>When you need to get some statistics about a file there is pg_stat_file:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select pg_stat_file('postgresql.conf');\n                                     pg_stat_file                                      \n---------------------------------------------------------------------------------------\n (26343,\"2019-02-21 17:35:22+01\",\"2019-02-05 15:41:11+01\",\"2019-02-05 15:41:11+01\",,f)\n(1 row)\npostgres=# select pg_size_pretty((pg_stat_file('postgresql.conf')).size);\n pg_size_pretty \n----------------\n 26 kB\n(1 row)\n<\/pre>\n<p>Loading a file into the database is possible as well:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create table t1 ( a text );\nCREATE TABLE\npostgres=# insert into t1 select pg_read_file('postgresql.conf');\nINSERT 0 1\npostgres=# select * from t1;\n                                                        a                                                        \n-----------------------------------------------------------------------------------------------------------------\n # -----------------------------                                                                                +\n # PostgreSQL configuration file                                                                                +\n # -----------------------------                                                                                +\n #                                                                                                              +\n # This file consists of lines of the form:                                                                     +\n #                                                                                                              +\n #   name = value                                                                                               +\n...\n<\/pre>\n<p>This works even with binary files (but do you really want to have binary files in the database?):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create table t2 ( a bytea );\nCREATE TABLE\npostgres=# insert into t2 select pg_read_binary_file('\/bin\/cp');\nINSERT 0 1\npostgres=# select * from t2;\n                                                                                                                                                                                                                   \n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n x7f454c4602010100000000000000000002003e0001000000293e4000000000004000000000000000c0560200000000000000000040003800090040001f001e000600000005000000400000000000000040004000000000004000400000000000f801000000000000\n(1 row)\npostgres=# drop table t1,t2;\nDROP TABLE\n<\/pre>\n<p>As usual this is all very well documented in the <a href=\"https:\/\/www.postgresql.org\/docs\/11\/functions-admin.html\" target=\"_blank\" rel=\"noopener noreferrer\">PostgreSQL documentation<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL comes with various helper functions that support you with working with files on the filesystem on the host PostgreSQL is running on. You might ask yourself why that is important but there are use cases for that. Maybe you want to list the contents of a directory because new files that showed up since [&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-12271","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>Working with files on the filesystem 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\/working-with-files-on-the-filesystem-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Working with files on the filesystem in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL comes with various helper functions that support you with working with files on the filesystem on the host PostgreSQL is running on. You might ask yourself why that is important but there are use cases for that. Maybe you want to list the contents of a directory because new files that showed up since [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/working-with-files-on-the-filesystem-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-02-22T09:18:03+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\\\/working-with-files-on-the-filesystem-in-postgresql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/working-with-files-on-the-filesystem-in-postgresql\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"Working with files on the filesystem in PostgreSQL\",\"datePublished\":\"2019-02-22T09:18:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/working-with-files-on-the-filesystem-in-postgresql\\\/\"},\"wordCount\":259,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/working-with-files-on-the-filesystem-in-postgresql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/working-with-files-on-the-filesystem-in-postgresql\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/working-with-files-on-the-filesystem-in-postgresql\\\/\",\"name\":\"Working with files on the filesystem in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2019-02-22T09:18:03+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/working-with-files-on-the-filesystem-in-postgresql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/working-with-files-on-the-filesystem-in-postgresql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/working-with-files-on-the-filesystem-in-postgresql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Working with files on the filesystem 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":"Working with files on the filesystem 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\/working-with-files-on-the-filesystem-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Working with files on the filesystem in PostgreSQL","og_description":"PostgreSQL comes with various helper functions that support you with working with files on the filesystem on the host PostgreSQL is running on. You might ask yourself why that is important but there are use cases for that. Maybe you want to list the contents of a directory because new files that showed up since [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/working-with-files-on-the-filesystem-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2019-02-22T09:18:03+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\/working-with-files-on-the-filesystem-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/working-with-files-on-the-filesystem-in-postgresql\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"Working with files on the filesystem in PostgreSQL","datePublished":"2019-02-22T09:18:03+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/working-with-files-on-the-filesystem-in-postgresql\/"},"wordCount":259,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/working-with-files-on-the-filesystem-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/working-with-files-on-the-filesystem-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/working-with-files-on-the-filesystem-in-postgresql\/","name":"Working with files on the filesystem in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-02-22T09:18:03+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/working-with-files-on-the-filesystem-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/working-with-files-on-the-filesystem-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/working-with-files-on-the-filesystem-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Working with files on the filesystem 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\/12271","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=12271"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12271\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12271"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12271"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12271"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12271"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}