Re: Using psql to feed a file line by line to a table column - Mailing list pgsql-general

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@gmail.com> wrote:
>>> 2013/3/13 Alexander Farber <alexander.farber@gmail.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