Reputation: 22923

Copying a CSV file from stdin throws "missing data for column"

I have some data that has been exported from postgres, reworked a bit using a spreadsheet and I know want the data back into a table, but I keep failing on the import:

cat extract.csv  | psql -h 10.135.0.44 myapp myapp -f copy-user.sql`
psql:copy-user.sql:7: ERROR:  missing data for column "email"
CONTEXT:  COPY to_update, line 1: ""

The actual data is supplied below. I first converted the CSV file from DOS to Unix style line endings. It didn't seem to matter much.

copy-user.sql

COPY "to_update" 
FROM STDIN
WITH DELIMITER ';' CSV;

extract.csv

bfb92e29-1d2c-45c4-b9ab-357a3ac7ad13;[email protected];x
aeccc3ea-cc1f-43ef-99ff-e389d5d63b22;[email protected];x
9cec13ae-c880-4371-9b1c-dd201f5cf233;[email protected];x
aeada2bc-a362-4f3e-80f2-06a717206802;[email protected];x
fb85ddd8-7d17-4d41-8bc3-213b1e469506;[email protected];x
528e1f2e-1baa-483b-bc8c-85f993014696;[email protected];x
dbc8a9c1-56cf-4589-8b2c-cf1a2e0832ed;[email protected];x
fbf23553-baa2-410a-8f96-32b5c4deb0c7;[email protected];x
e22ec0de-06f9-428a-aa3e-171c38f9a1f7;[email protected];x
8e8d0f73-8eb7-43b4-8019-b79042731b97;[email protected];x

table definition for to_update

create table to_update(id text, email text, text char);

-- also tried this variant, but same error
-- create table to_update(id uuid, email text, text char);

EDIT: Additional info

It seems this exact same thing doesn't throw on my local machine:

$ cat extract.csv  | psql postgres -f copy-user.sql
Timing is on.
Line style is unicode.
Border style is 2.
Null display is "[NULL]".
Expanded display is used automatically.
COPY 0
Time: 0.430 ms

It still doesn't work (as it just copies 0 rows), but at least it doesn't throw an error. That points to it being related to the environment (versions, locale settings, etc).

Local machine (which doesn't throw error)

$ psql --version
psql (PostgreSQL) 10.6

$ psql postgres -c "SHOW server_version;"
Timing is on.
Line style is unicode.
Border style is 2.
Null display is "[NULL]".
Expanded display is used automatically.
┌────────────────┐
│ server_version │
├────────────────┤
│ 10.6           │
└────────────────┘
(1 row)

Time: 40.960 ms

$ printenv | grep LC
LC_CTYPE=UTF-8

Remote server(s) (which throws error)

$ psql --version  # this is the client, not the same physical server as the db
psql (PostgreSQL) 9.5.12

$ psql -h 10.135.0.44 myapp myapp -c "SHOW server_version;"
Password for user pete: 
 server_version 
----------------
 9.5.12
(1 row)

$ printenv | grep LC
LC_ALL=C.UTF-8
LC_CTYPE=UTF-8
LANG=C.UTF-8

Hex dump of extract.csv (all 7 lines)

$ wc -l extract.csv 
10 extract.csv

$ hexdump -C extract.csv 
00000000  62 66 62 39 32 65 32 39  2d 31 64 32 63 2d 34 35  |bfb92e29-1d2c-45|
00000010  63 34 2d 62 39 61 62 2d  33 35 37 61 33 61 63 37  |c4-b9ab-357a3ac7|
00000020  61 64 31 33 3b 74 65 73  74 40 74 65 73 74 39 30  |ad13;test@test90|
00000030  32 33 39 30 32 33 37 38  33 34 35 37 38 34 33 2e  |239023783457843.|
00000040  63 6f 6d 3b 78 0a 61 65  63 63 63 33 65 61 2d 63  |com;x.aeccc3ea-c|
00000050  63 31 66 2d 34 33 65 66  2d 39 39 66 66 2d 65 33  |c1f-43ef-99ff-e3|
00000060  38 39 64 35 64 36 33 62  32 32 3b 74 65 73 74 65  |89d5d63b22;teste|
00000070  72 40 74 65 73 74 65 72  6b 6a 6e 61 65 66 67 6a  |r@testerkjnaefgj|
00000080  6e 77 65 72 67 2e 6e 6f  3b 78 0a 39 63 65 63 31  |nwerg.no;x.9cec1|
00000090  33 61 65 2d 63 38 38 30  2d 34 33 37 31 2d 39 62  |3ae-c880-4371-9b|
000000a0  31 63 2d 64 64 32 30 31  66 35 63 66 32 33 33 3b  |1c-dd201f5cf233;|
000000b0  62 6c 6f 62 6c 6f 40 67  6d 61 69 6c 2e 63 6f 6d  |[email protected]|
000000c0  3b 78 0a 61 65 61 64 61  32 62 63 2d 61 33 36 32  |;x.aeada2bc-a362|
000000d0  2d 34 66 33 65 2d 38 30  66 32 2d 30 36 61 37 31  |-4f3e-80f2-06a71|
000000e0  37 32 30 36 38 30 32 3b  76 65 74 40 67 6d 61 69  |7206802;vet@gmai|
000000f0  6c 2e 63 6f 6d 3b 78 0a  66 62 38 35 64 64 64 38  |l.com;x.fb85ddd8|
00000100  2d 37 64 31 37 2d 34 64  34 31 2d 38 62 63 33 2d  |-7d17-4d41-8bc3-|
00000110  32 31 33 62 31 65 34 36  39 35 30 36 3b 6e 61 76  |213b1e469506;nav|
00000120  6e 6e 61 76 6e 65 73 65  6e 40 70 74 66 6c 6f 77  |nnavnesen@ptflow|
00000130  2e 63 6f 6d 3b 78 0a 35  32 38 65 31 66 32 65 2d  |.com;x.528e1f2e-|
00000140  31 62 61 61 2d 34 38 33  62 2d 62 63 38 63 2d 38  |1baa-483b-bc8c-8|
00000150  35 66 39 39 33 30 31 34  36 39 36 3b 6b 6b 6c 6b  |5f993014696;kklk|
00000160  40 68 6f 74 6d 61 69 6c  2e 63 6f 6d 3b 78 0a 64  |@hotmail.com;x.d|
00000170  62 63 38 61 39 63 31 2d  35 36 63 66 2d 34 35 38  |bc8a9c1-56cf-458|
00000180  39 2d 38 62 32 63 2d 63  66 31 61 32 65 30 38 33  |9-8b2c-cf1a2e083|
00000190  32 65 64 3b 67 68 69 69  69 69 40 68 6f 74 6d 61  |2ed;ghiiii@hotma|
000001a0  69 6c 2e 63 6f 6d 3b 78  0a 66 62 66 32 33 35 35  |il.com;x.fbf2355|
000001b0  33 2d 62 61 61 32 2d 34  31 30 61 2d 38 66 39 36  |3-baa2-410a-8f96|
000001c0  2d 33 32 62 35 63 34 64  65 62 30 63 37 3b 6c 61  |-32b5c4deb0c7;la|
000001d0  6c 61 40 6c 61 6c 61 2e  6e 6f 3b 78 0a 65 32 32  |[email protected];x.e22|
000001e0  65 63 30 64 65 2d 30 36  66 39 2d 34 32 38 61 2d  |ec0de-06f9-428a-|
000001f0  61 61 33 65 2d 31 37 31  63 33 38 66 39 61 31 66  |aa3e-171c38f9a1f|
00000200  37 3b 78 32 40 67 6d 61  69 6c 2e 63 6f 6d 3b 78  |7;[email protected];x|
00000210  0a 38 65 38 64 30 66 37  33 2d 38 65 62 37 2d 34  |.8e8d0f73-8eb7-4|
00000220  33 62 34 2d 38 30 31 39  2d 62 37 39 30 34 32 37  |3b4-8019-b790427|
00000230  33 31 62 39 37 3b 6d 61  69 6c 40 6d 61 69 6c 2e  |31b97;mail@mail.|
00000240  63 6f 6d 3b 78 0a                                 |com;x.|
00000246

Upvotes: 2

Answers (3)

Daniel Vérité

Reputation: 61506

I think you want \copy ... from pstdin... on a single line. Both the starting backslash and pstdin instead of stdin are on purpose.

This mailing-list thread: psql -f COPY from STDIN explains the problem and the solution.

COPY FROM STDIN expects data inline after the COPY command, as in a dump file, not from the standard input of the psql process.

Relevant snippet from the mailing list summing up the alternatives

I'd like the store the COPY command in a separate file without specifying an input file name. I want to feed it the data from the shell script that calls psql

"STDIN: All rows are read from the same source that issued the command" - As I understand now, this applies to both COPY and \COPY. In other words the input file must contain command and data.

I have found a few solutions to achieve my objective:

1) using COPY FROM STDIN cat event.csv | psql -c "$(cat event.sql)"

2) using COPY FROM STDIN psql -f <(cat event.sql event.csv)

3) using \COPY FROM PSTDIN cat event.csv | psql -f event.sql

4) using \COPY FROM STDIN psql -f <(cat event.sql event.csv <(echo "."))

What I don't like about \COPY is that it has to be on one line. Indeed it can't be split over multiple lines

Upvotes: 6

oligofren

Reputation: 22923

With regards to the actual thrown error, after some debugging, I found that this error only happens with Postgres 9.5.12, not my local database running 10.6. That's using the exact same script in the sql file.

Postgres 9.5.12 doesn't handle multi-line COPY FROM STDIN statements! Deleting the newlines so that the entire expression was on a single line made it run. It still didn't work, though, as it still showed 0 rows being copied, but that is really a different question ... Krishna was onto something though ... I'll post a separate question for that and link it up.

Upvotes: 0

jimmu

Reputation: 1056

following works in my setup:

cat extract.csv | psql -d db_name -U user_name -c "copy to_update from stdin with delimiter ';' csv"

psql -d db_name -U user_name -c "\copy public.to_update(id, email, text) from '/path_to/extract.csv' with delimiter ';' csv"

Upvotes: 1

Copying a CSV file from stdin throws &quot;missing data for column&quot;

EDIT: Additional info

Answers (3)

Related Questions

Copying a CSV file from stdin throws "missing data for column"