PostgreSQL import text file

Question

I create a PostgreSQL database and table and I want import data to database with text file.

Example of my data:

jameson aa
david bb
piter cc
mat dd
rob ee

This bash can import files but is very slow for long files:

#!/bin/sh
sed 's/ /\',\'/g' file > My_file
while read line; do
psql -d My_db -c "insert into My_table values('$line')"
done < My_file

also i read this resolve but this import just firstline in first field & not use delimiter

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

Pavel Stehule · Accepted Answer · 2015-11-04 17:00:43Z

1

use \copy command. This command is designed for import data from files.

\copy my_table from my_file.txt DELIMITER ' '

It is simple, but Postgres is pretty strict - it requires no empty line on the end and exactly one space between fields.

Note: your data are not consistent - first row contains double space between fields.

Back to your example. It must be slow. You do import per one line - for every line you start psql and there transaction. Using pipe and single transaction option should be much faster. With awk you can generate SQL script:

cat my_file.txt | \
awk -F" " -v  Q="'" '{print "INSERT INTO my_table VALUES(" Q$1Q " , " Q$2Q ");" }' | \
psql postgres -1

But there is risk of possible missing escaped apostrophes in data (it same safe as your slow solution). COPY based solution should be much better and 100% safe. It enforce all necessary escaping internally.

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

This should not work. SQL statements in psql doesn't support bulk operations over array (like ODBC). The result of your example is just invalid INSERT command. The session variables are evaluated as strings substitution. You send a statement to server like:

INSERT INTO my_table VALUES ('Jameson','aa',
'david','bb',
'pitter','cc'
... )

And this is not valid.

edited Nov 4, 2015 at 17:00

answered Nov 4, 2015 at 15:44

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

2 Comments

David Over a year ago

My_file not have any blank line and were arranged exactly like the sample duble space is edited Unfortunately I could I use your code That's a little more explanation?

David Over a year ago

Tnx pavel , Your guidance was a great help.

Collectives™ on Stack Overflow

PostgreSQL import text file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related