How to Load Text Data to Database in Postgresql

How to load data from a text file in a PostgreSQL database?

The slightly modified version of COPY below worked better for me, where I specify the CSV format. This format treats backslash characters in text without any fuss. The default format is the somewhat quirky TEXT.

COPY myTable FROM '/path/to/file/on/server' ( FORMAT CSV, DELIMITER('|') );

Is there a way to load text data to database in PostgreSQL?

An approach I use with my large XML files - 130GB or bigger - is to upload the whole file into a temporary unlogged table and from there I extract the content I want. Unlogged tables are not crash-safe, but are much faster than logged ones, which totally suits the purpose of a temporary table ;-)

Considering the following table ..

CREATE UNLOGGED TABLE tmp (raw TEXT);

.. you can import this 1GB file using a single psql line from your console (unix)..

$ cat 1gb_file.txt | psql -d db -c "COPY tmp FROM STDIN" 

After that all you need is to apply your logic to query and extract the information you want. Depending on the size of your table, you can create a second table from a SELECT, e.g.:

CREATE TABLE t AS
SELECT 
  trim((string_to_array(raw,','))[1]) AS operation,
  trim((string_to_array(raw,','))[2])::timestamp AS tmst,
  trim((string_to_array(raw,','))[3]) AS txt
FROM tmp
WHERE raw LIKE '%DEBUG%' AND
      raw LIKE '%ghtorrent-40%' AND 
      raw LIKE '%Repo EFForg/https-everywhere exists%'

Adjust the string_to_array function and the WHERE clause to your logic! Optionally you can replace these multiple LIKE operations to a single SIMILAR TO.

.. and your data would be ready to be played with:

SELECT * FROM t;

 operation |        tmst         |                               txt                                
-----------+---------------------+------------------------------------------------------------------
 DEBUG    | 2017-03-23 10:02:27 | ghtorrent-40 -- ghtorrent.rb:Repo EFForg/https-everywhere exists
(1 Zeile)

Once your data is extracted you can DROP TABLE tmp; to free some disk space ;)

Further reading: COPY, PostgreSQL array functions and pattern matching

Load .txt file into Postgres Database

Welcome to Stack Overflow.

In case your columns are split by tab, try this out:

COPY CRD_codes1 FROM '/home/jones/file.txt' CSV DELIMITER E'\t'

• The E'\t' states that the values are split by TAB

But in case your delimiter isn't properly set, as your screenshot suggests, you have to clean this data before inserting into your table. The following script imports data into a temporary table, replaces the redundant tab characters and populates the target table.

CREATE TEMPORARY TABLE t (c TEXT);
COPY t FROM '/home/jones/file.txt' CSV ESCAPE E'\t';

WITH j AS (
  SELECT 
    string_to_array(
      regexp_replace(c, '\t+', ',', 'g'),
    ',') AS val 
  FROM t
) INSERT INTO CRD_codes1
SELECT j.val[1],j.val[2],j.val[3],j.val[4],j.val[5] FROM j;

SELECT * FROM CRD_codes1;

 state | crd | county |       crd_name        | history_flag 
-------+-----+--------+-----------------------+--------------
 01    | 10  | 077    | Lauderdale            | 1
 01    | 10  | 888    | D10 Combined Counties | 1
(2 Zeilen)

How to import data from a text file into a specific PostgreSQL column?

There is a drop option in a table, there import the .txt file in the drop-down options with corresponding column name. Later refresh the table, then view it. Walah, you got table exactly as your .txt file. Thank You.

Load database into PostgreSQL from text file

PG_RESTORE will not work unless the source file is from a PG_DUMP.

Your best bet is to fire this off as an .SQL file on connection to the database.
eg.

psql -d [database] -U [user] -f [file from SQLite].sql

As long as the commands in the file are executable and the syntax will work with Postgres this will create your objects and populate them.

PostgreSQL import text file

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.

---

Related Topics