PostgreSQL COPY command issues

Question

In my Postgresql database I have created a table called NAV_DATA with five columns:

 id (PKEY)
,name (VARCHAR)
,call_type (VARCHAR)
,date_created (date)

For these columns I have the csv file which the delimiter TAB \t and Quotechar ".
It also has some empty fields in middle column.

How to allow to accept null values in copy command?

I want to insert the columns (name, call_type, date_created). I don't have the field id in my csv.

How to copy specific columns into the DB?

The issue is also with delimiter TAB \t?

Please take a look at the [appropriate page](http://www.postgresql.org/docs/current/static/sql-copy.html) of the manual and modify your question if you see anything unclear. — Milen A. Radev, Nov 14 '11 at 16:24

score 2 · Answer 1 · answered Nov 14 '11 at 16:29

2

copy "NAV_DATA" (name,call_type,date_created) from 'c:/test.txt' with delimiter '\t' csv

answered Nov 14 '11 at 16:29

Tailor

31
5

I think the table name shouldn't be in quotes.The doubt is Is that primary key ID will be incremented automatically for every records? – Nava Nov 15 '11 at 11:58
Postgres is case sensitive for table and column names and unless surrounded by qoutes it will convert to lower case. this will mean that NAV_DATA wont match a table called NAV_DATA. You would have to write "NAV_DATA" in your query. – Tailor Nov 15 '11 at 15:20
You can use a sequence like has been suggested by Erwin. Personally when I import CSV's I import into a table that matched the CSV struture and then use SQL queries to copy from the imported dirty table in to my clean tables. This way you can TRIM spaces, set NULLs, create sequences and run the query DISTINCT to eliminate duplicates. – Tailor Nov 15 '11 at 15:21

Erwin Brandstetter · Answer 2 · 2011-11-14T18:52:47.073

If you wonder about how NULL values are represented in the CSV file have a look at the manual page @Milen is linking to in his comment. Or write some test data (including NULL values) into your table and export it with COPY TO. You get exactly the format that is expected for COPY FROM.

In the standard format NULL is represented by \N, in standard CSV format by nothing between the delimiters. And columns not mentioned in the COPY command will be filled in with their respective defaults.

A serial column for instance (like id probably) will get nextval() from the associated sequence for every row. If id doesn't have a default yet, create a sequence for it and make nextval('my_sequence_name') the column default.

PostgreSQL COPY command issues

2 Answers2