To_tsvector() inside of bulk COPY FROM STDIN Postgres

I am bulk loading a CSV file into Postgres, and instead of using database triggers to update the ts_vector column every time I insert a record, I am trying to trying to insert with a COPY FROM STDIN with records that look like

"some text value", to_tsvector('English', 'some text value')

see if I get any performance increase. I can do this manually from Postgres shell with an INSERT INTO tablename VALUES () statement, but when I do it from the COPY FROM I get a ERROR: extra data after last expected column which I assume means my syntax is not correct.

Any help with this would be much appreciated!

Solution

You cannot use functions in COPY. It only allows actual values. You will need to use INSERT statement(s) or create a trigger to update your tsvector.

Alternatively, you could try to generate the appropriate tsvector string by hand, but it would not be worth the hassle (error prone and probably not any faster).

Here is what the format looks like:

postgres=> SELECT to_tsvector('The quick brown fox jumped over the lazy dog.');
                  to_tsvector                      
-------------------------------------------------------
 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
(1 row)

postgres=>