2013/3/13 Alexander Farber <alexander.far...@gmail.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general