Re: [SQL] unique rows
HI, 1. You can try to create a trigger before insert to check if the new row exists in the table and if it does to cancel the insert. 2. Or create a UNIQUE INDEX on all the columns which will throw error if you try to insert duplicate rows in the table. Then in the apllication software just catch the error and move on with the next insert. I hope I helped. Regards, Kaloyan Iliev TJ O'Donnell wrote: I want to make a table that has unique rows - no problem. Create Table x (smarts Varchar Unique); I have input data that has many duplicates and until now I was creating a temp table allowing duplicates, and then Insert into x (smarts) select distinct smarts from tmpx; This is now failing when I have hundreds of millions of rows, running out of temp space (while trying to sort to accomplish the distinct?) So, is there a way (a constraint, a check?) that will simply REJECT a duplicate when I insert the raw data directly into x rather than FAILING on an error as it does with the unique constraint above? Thanks, TJ O'Donnell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] unique rows
Hi, TJ, TJ O'Donnell wrote: > So, is there a way (a constraint, a check?) that will simply > REJECT a duplicate when I insert the raw data directly into x > rather than FAILING on an error as it does > with the unique constraint above? Failing on an error is exactly the way PostgreSQL (and the SQL standard) uses to REJECT duplicates. :-) You seem to think about silently dropping the duplicates. That could be achieved with an BEFORE INSERT trigger, or with a rule on a view, as both can silently drop the inserted rule. The trigger body could even be in language SQL, along the lines of: SELECT CASE WHEN EXISTS (SELECT keycol FROM table WHERE table.keycol=NEW.keycol) THEN NULL ELSE NEW; Nevertheless, expect the insert performance to drop a little, due to the trigger overhead. The alternative approaches (SELECT'ing from the application, using a stored procedure that checks and then inserts the data, and using subtransactions to roll back the failing inserts) all seem worse (uglier and slower) to me, but don't hesitate to ask if you're interested. HTH, Markus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Help with optional parameters
I noticed this one by searching in the archives, as I am working with some “optional” parameters myself and noticed your solution. I just wanted to make one improvement suggestion which is instead of checking whether or not a parameter has been used simply start your query like so Query_base := ‘SELECT * FROM my_table WHERE 1 =1; If you do that then you can just add on any parameters you need or not add any at all. I think that seems to be a bit simpler than having a “has_param” Boolean variable. Thanks, Curtis From: Rob Tester [mailto:[EMAIL PROTECTED] Sent: Thursday, August 17, 2006 8:58 PM To: MaXX Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Help with optional parameters BEGIN query_base := 'SELECT * FROM my_table '; has_param := FALSE; query_where := ''; IF (a IS NOT NULL) THEN IF (has_param IS FALSE)THEN -- there is no param yet add WHERE to the query query_where := ' WHERE '; ELSE -- there is already something in the WHERE clause, we need to add AND query_where := query_where || ' AND '; END IF; query_where := query_where || 'parama='||a; --beware if param quoting is required has_param := TRUE; -- now there is at least 1 param END IF;
Re: [SQL] Help with optional parameters
Curtis Scheer wrote: > I noticed this one by searching in the archives, as I am working with some > "optional" parameters myself and noticed your solution. I just wanted to > make one improvement suggestion which is instead of checking whether or not > a parameter has been used simply start your query like so > Query_base := 'SELECT * FROM my_table WHERE 1 =1; > If you do that then you can just add on any parameters you need or not add > any at all. I think that seems to be a bit simpler than having a has_param" > Boolean variable. True, I think I was too focused on the "clean" output, your solution wastes less cycles and is more readable... Snipet updated, Thanks, -- MaXX ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
