7

I wanted to how to read a file in my desk top using pg_read_file in PostgreSQL

pg_read_file(filename text [, offset bigint, length bigint])

my query 

select pg_read_file('/root/desktop/new.txt' , 0 , 1000000);

error 

ERROR:  absolute path not allowed

UPDATE

Community
  • 1
  • 1
Arjun Raj
  • 984
  • 2
  • 12
  • 32

4 Answers4

12

pg_read_file can read the files only from the data directory path, if you would like to know your data directory path use:

SHOW  data_directory;

I think that you can resolve you problem by looking to this post

Community
  • 1
  • 1
Houari
  • 5,326
  • 3
  • 31
  • 54
  • **No**, I am using to read `/tmp` and other directories. Use something like `chmod 777` to use any other directory. – Peter Krauss Apr 02 '21 at 09:47
8

If you're using psql you can use \lo_import to create a large object from a local file.

The pg_read_file tool only allows reads from server-side files.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 2
    PostgreSQL *server*. Not the `psql` or PgAdmin-III client. It's the PostgreSQL back-end you're connecting to. To see the current directory of the server you can use `SHOW data_directory`. – Craig Ringer Feb 28 '14 at 22:06
  • @ArjunRaj Please post a new question, rather than tacking on largely unrelated questions to the original. – Craig Ringer Mar 05 '14 at 09:10
  • http://stackoverflow.com/questions/22193303/postgresql-save-contents-in-lo-import-to-table – Arjun Raj Mar 05 '14 at 09:17
1

To read the content of a file from PostgreSQL you can use this.

CREATE TABLE demo(t text);
COPY demo from '[FILENAME]';
SELECT * FROM demo;

Each text-line in a SQL-ROW. Useful for temporary transfers.

Peter Krauss
  • 13,174
  • 24
  • 167
  • 304
Wh1t3 P4wn48
  • 11
  • 1
0

lo_import(file path) will generate an oid.This may solve your problem. you can import any type of file using this (even image)

chetan chadha
  • 1
  • 1