I have a syntax error in my bash command when I try to fill a Postgres table with data from Kafka using kafkacat:
[k ~]$ kafkacat -b XXX.XX.Y.Z:9092 -t test -o 20501 -f '%k|%s|%T|%p|%o|213n' -e | psql -c "copy raw_from_kafka(key, value, timestamp, partition, offset, load_cycle) from stdin delimiter '|'" -d dwh_dev -h XXX.ZZ.Y.ZZ -U konstantin
% Auto-selecting Consumer mode (use -P or -C to override)
ERROR: syntax error at or near "offset"
LINE 1: ... raw_from_kafka(key, value, timestamp, partition, offset, lo...
^
% ERROR: Write error for message of 34 bytes in test [0] at offset 20567: Broken pipe
The syntax is simple, so I can’t understand why the error appears.
Strangely, this error only appears when I specify a list of columns I want to fill. If I use the same command filling all columns in the table, everything goes nice and smooth, the data ends up in the table, just like I want:
[k ~]$ kafkacat -b XXX.XX.Y.Z:9092 -t test -o 20501 -f '%k|%s|%T|%p|%o|2020-08-05 22:33:44+03|213n' -e | psql -c "copy raw_from_kafka from stdin delimiter '|'" -d dwh_dev -h XXX.ZZ.Y.ZZ -U konstantin
% Auto-selecting Consumer mode (use -P or -C to override)
% Reached end of topic test [0] at offset 23437: exiting
But I want to be able to fill only specific columns with the data from Kafka, while the rest of columns are filled with default values. Therefore, I need the first version of the command working.
Does anybody have any idea, why the error appears?
Postgres version: 12
kafkacat version: 1.6.0
System: CentOS 7
2
Answers
Renaming offset to k_offset resolved the issue.
offset
is a reserved word in SQL. If you have a column namedoffset
you need to double-quote it, ie."offset"
.