skip to Main Content

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


  1. Chosen as BEST ANSWER

    Renaming offset to k_offset resolved the issue.


  2. offset is a reserved word in SQL. If you have a column named offset you need to double-quote it, ie. "offset".

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search