Re: [GENERAL] Using psql to feed a file line by line to a table column
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
Re: [GENERAL] Using psql to feed a file line by line to a table column
Thank you - On Thu, Mar 14, 2013 at 10:40 AM, Ian Lawrence Barwick barw...@gmail.com wrote: 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. 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 so the return value of an insert trigger is actually what get's inserted? And it has to be an BEFORE trigger? Regards Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using psql to feed a file line by line to a table column
2013/3/14 Alexander Farber alexander.far...@gmail.com: Thank you - On Thu, Mar 14, 2013 at 10:40 AM, Ian Lawrence Barwick barw...@gmail.com wrote: 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. 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 so the return value of an insert trigger is actually what get's inserted? Yup, normally that would be the contents of the NEW record. And it has to be an BEFORE trigger? Yes, because an AFTER trigger is fired after the row is updated, so the row can't be changed. Regards Ian Barwick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Do not understand why this happens
HI. I connect to the database using the PGDAC, and then send a NOTIFY to myself every minute. In this case, the logs get that. 2013-03-10 10:34:36 19797 LOG: process 19797 still waiting for AccessExclusiveLock on object 0 of class 1262 of database 0 after 3000.100 ms 2013-03-10 10:34:36 19797 STATEMENT: NOTIFY test105, '' 2013-03-10 10:34:37 19797 LOG: process 19797 acquired AccessExclusiveLock on object 0 of class 1262 of database 0 after 3315.206 ms 2013-03-10 10:34:37 19797 STATEMENT: NOTIFY test105, '' 2013-03-10 10:34:37 19797 LOG: duration: 3315.322 ms statement: NOTIFY test105, '' 2013-03-10 14:27:43 19797 LOG: could not receive data from client: Connection reset by peer 2013-03-10 14:27:43 19797 LOG: unexpected EOF on client connection Tell me what's the problem? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Do not understand why this happens
On Thu, 14 Mar 2013 15:24:45 +0400 Aln Kapa alnk...@gmail.com wrote: I connect to the database using the PGDAC, and then send a NOTIFY to myself every minute. In this case, the logs get that. 2013-03-10 10:34:36 19797 LOG: process 19797 still waiting for AccessExclusiveLock on object 0 of class 1262 of database 0 after 3000.100 ms 2013-03-10 10:34:36 19797 STATEMENT: NOTIFY test105, '' 2013-03-10 10:34:37 19797 LOG: process 19797 acquired AccessExclusiveLock on object 0 of class 1262 of database 0 after 3315.206 ms 2013-03-10 10:34:37 19797 STATEMENT: NOTIFY test105, '' 2013-03-10 10:34:37 19797 LOG: duration: 3315.322 ms statement: NOTIFY test105, '' 2013-03-10 14:27:43 19797 LOG: could not receive data from client: Connection reset by peer 2013-03-10 14:27:43 19797 LOG: unexpected EOF on client connection Tell me what's the problem? Are the last two lines your perceived problem? Is the NOTIFY working? There's really not enough information here to actually understand what you're asking. Based on the log information, it looks like a client program issues a notify, then drops the connection. Could be because in intervening network control device times out the TCP state, or could be because the client drops the conneciton, or because the client crashed, or is poorly implementd in that it looses its TCP socket. In any event, the 4 hour lag between the NOTIFY and the conneciton drop during which nothing happens seems to indicate that the two events are probably not related. Is any of that helpful? I feel like I don't understand your question and suspect that you didn't receive an answer to your first post because most people didn't understand it. If my comments don't address your question, perhaps try describing it differently. -- Bill Moran wmo...@potentialtech.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] big un stacking query - help save me from myself
Kirk Wythers kirk.wyth...@gmail.com wrote: I hace a fairly large table with two columns that I need to de-normalize (235 million rows) There has got to be a better (i.e. faster) approach than what I am doing. I am using a MAX CASE on each of the 24 variables (column names variable and value) that I want to unstack. Any suggestions would be most appreciated. I didn't understand your description of what you are trying to do, and the example has so many columns and cases that it would take a long time to understand it. Can you distill this down to just a few columns and cases so that it is easier to understand what you are trying to accomplish? Even better would be a self-contained test case with just a few rows so people can see before and after data. What you have already posted will help give context on how it needs to scale, which is important, too; but if you make the issue easier to understand, the odds improve that someone will volunteer the time needed to make a suggestion. -Kevin -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Do not understand why this happens
On Wed, Mar 13, 2013 at 8:08 AM, Aln Kapa alnk...@gmail.com wrote: 2013-03-10 14:27:43 19797 LOG: could not receive data from client: Connection reset by peer 2013-03-10 14:27:43 19797 LOG: unexpected EOF on client connection Tell me what's the problem? Your client closed the socket without cleanly closing the connection to postgres. did it crash?
[GENERAL] Fedora 18 x86_64 repodata/repomd.xml: [Errno 14] curl#22 - The requested URL returned error: 404 Not Found on PostgreSQL 9.2
Using the yum repository for PostgreSQL 9.2, does anybody know what happened to http://yum.postgresql.org/9.2/fedora/fedora-18-x86_64/repodata/repomd.xml? -- René Romero Benavides @iCodeiExist @PgsqlMx Postgresql Tips en español para la comunidad de México e Hispanoamérica. http://postgresql.org.mx
Re: [GENERAL] Fedora 18 x86_64 repodata/repomd.xml: [Errno 14] curl#22 - The requested URL returned error: 404 Not Found on PostgreSQL 9.2
Hi, No idea what happened, but fixed. Thanks for the report. Regards, Devrim René Romero Benavides ichbinr...@gmail.com wrote: Using the yum repository for PostgreSQL 9.2, does anybody know what happened to http://yum.postgresql.org/9.2/fedora/fedora-18-x86_64/repodata/repomd.xml? -- René Romero Benavides @iCodeiExist @PgsqlMx Postgresql Tips en español para la comunidad de México e Hispanoamérica. http://postgresql.org.mx -- Devrim Gündüz