Re: Using psql to feed a file line by line to a table column

Thank you, this was indeed the
(uneeded) semicolon at end of the COPY line.

May I ask another question -

On Tue, Mar 12, 2013 at 6:24 PM, Ian Lawrence Barwick <barwick(at)gmail(dot)com> wrote:
>>> 2013/3/13 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:
>>>>
>>>> I have a list of 400000 non-english words,
>>>> each on a separate line and in UTF8 format,
>>>> which I'd like to put in the "word" column
>>>> of the following table (also in UTF8 and 8.4.13):
>>>>
>>>> create table good_words (
>>>> word varchar(64) primary key,
>>>> verified boolean not null default false,
>>>> stamp timestamp default current_timestamp
>>>> );

>> bukvy=> \copy good_words(word) from '/home/afarber/WORDS' ;
>> \copy: parse error at ";"

When I add few more words to my text file
and then try to load it into my table again,
then the COPY command will fail,
because of the already stored words:

bukvy=> \copy good_words(word) from WORDS
ERROR: duplicate key value violates unique constraint "good_words_pkey"
CONTEXT: COPY good_words, line 1: "абажур"

Can't I change the behaviour to silently
ignore inserting such words?

I also have an INSERT trigger on my table,
can I return a NULL from it or something similar?

Below is my complete code:

create table good_words (
word varchar(64) primary key,
letters integer[33],
verified boolean not null default false,
stamp timestamp default current_timestamp
);

create or replace function count_letters() returns trigger as $body$
declare
alphabet varchar[];
i integer;
begin
alphabet :=
'{А,Б,В,Г,Д,Е,Ё,Ж,З,И,Й,К,Л,М,Н,О,П,Р,С,Т,У,Ф,Х,Ц,Ч,Ш,Щ,Ъ,Ы,Ь,Э,Ю,Я}';

for i in 1 .. 33 loop
-- raise notice '%: %', i, alphabet[i];
new.letters[i] := length(new.word) -
length(replace(new.word, alphabet[i], ''));
end loop;
return new;
end;
$body$ language plpgsql;

create trigger count_letters
before insert on good_words
for each row execute procedure count_letters();

Regards
Alex