PostgreSQL import file

Question

In PostgreSQL and bash(linux), are there ways to directly import a file from the filesystem like

[execute.sh]

pgsql .... -f insert.txt

[insert.txt]

insert into table(id,file) values(1,import('/path/to/file'))

There seems to be no import function, bytea_import as well, lo_import would save int and I don't know how to get the file back (these files are in small sizes so using lo_import seems not appropriate)

And can how do I move the insert.txt statement to PostgreSQL?

John P · Accepted Answer · 2010-06-27 15:51:34Z

8

I'm not sure what you're after, but if you have script with SQL-statements, for example the insert statements that you mention, you can run psql and then run the script from within psql. For example:

postgres@server:~$ psql dbname
psql (8.4.1)
Type "help" for help.

dbname=# \i /tmp/queries.sql

This will run the statements in /tmp/queries.sql.

Hope this was what you asked for.

answered Jun 27, 2010 at 15:51

John P

15.3k4 gold badges51 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

monmonja Over a year ago

yap saw this but i need to use expect in bash so that i could use the import statement, i was looking for something like input pipe in mysql mysql dbname -uuser -pmypwd < /tmp/queries.sql Thanks

John P Over a year ago

Then perhaps copy might help you. Citing the PostgreSQL documentation: COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already).

Tomas Votruba · Accepted Answer · 2014-06-09 11:26:53Z

1

In case of more detailed parameters:

$ psql -h [host] -p [port] -d [databaseName] -U [user] -f [/absolute/path/to/file]

answered Jun 9, 2014 at 11:26

Tomas Votruba

24.4k10 gold badges85 silver badges123 bronze badges

Comments

Frank Heikens · Accepted Answer · 2010-06-24 07:33:42Z

0

The manual has some examples:

testdb=> \set content '''' `cat my_file.txt` ''''
testdb=> INSERT INTO my_table VALUES (:content);

See http://www.postgresql.org/docs/8.4/interactive/app-psql.html

answered Jun 24, 2010 at 7:33

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

1 Comment

monmonja Over a year ago

ill be importing a whole lot of text and using cat is not idea. Thanks

Collectives™ on Stack Overflow

PostgreSQL import file

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related