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

2013/3/13 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:
> Thank you, this was indeed the
> (uneeded) semicolon at end of the COPY line.
>
> May I ask another question -
(...)
> 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?

Yes, if you test for the presence of the word you can return NULL
and the row will be discarded. See example below.

Regards

Ian Barwick

testdb=# CREATE TABLE foo (word TEXT NOT NULL PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
testdb=#
testdb=# INSERT INTO foo VALUES('bar'),('baz');
INSERT 0 2
testdb=# INSERT INTO foo VALUES('bar');
ERROR: duplicate key value violates unique constraint "foo_pkey"
DETAIL: Key (word)=(bar) already exists.

CREATE OR REPLACE FUNCTION foo_check()
RETURNS TRIGGER
LANGUAGE 'plpgsql'
AS
$$
BEGIN
PERFORM TRUE
FROM foo
WHERE word = NEW.word;
IF FOUND THEN
RETURN NULL;
END IF;
RETURN NEW;
END;
$$;

CREATE TRIGGER tr_foo_check
BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE foo_check();

testdb=# INSERT INTO foo VALUES('bar');
INSERT 0 0