[SQL] Delete duplicates
Hi, I have a table with duplicates and trouble with my SQL. I'd like to keep a single record and remove older duplicates. For example below of the 6 recods I'd like to keep records 4 and 6. TABLE: aap id | keyword +- 1 | LEAGUE PANTHERS 2 | LEAGUE PANTHERS 3 | LEAGUE PANTHERS 4 | LEAGUE PANTHERS 5 | LEAGUE BRONCOS 6 | LEAGUE BRONCOS Here is my SQL so far, it will select records 1 to 5 instead of 1,2,3 and 5 only. Any help greatly appreciated. I think I need a Group By somewhere in there. select a1.id from aap a1 where id < ( SELECT max(id) FROM aap AS a2 ) AND EXISTS ( SELECT * FROM aap AS a2 WHERE a1.keyword = a2.keyword ) Regards Rudi. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Delete duplicates
On Sunday 22 June 2003 11:15, Rudi Starcevic wrote: > Hi, > > I have a table with duplicates and trouble with my SQL. (...) > select a1.id > from aap a1 > where id < ( SELECT max(id) FROM aap AS a2 ) > AND EXISTS > ( > SELECT * > FROM aap AS a2 > WHERE a1.keyword = a2.keyword > ) How about (untested): SELECT a1.id FROM aap a1 WHERE id = (SELECT MAX(id) FROM aap a2 WHERE a2.keyword = a1.keyword) Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Delete duplicates
On 22/06/2003 10:15 Rudi Starcevic wrote: Hi, I have a table with duplicates and trouble with my SQL. I'd like to keep a single record and remove older duplicates. For example below of the 6 recods I'd like to keep records 4 and 6. TABLE: aap id | keyword +- 1 | LEAGUE PANTHERS 2 | LEAGUE PANTHERS 3 | LEAGUE PANTHERS 4 | LEAGUE PANTHERS 5 | LEAGUE BRONCOS 6 | LEAGUE BRONCOS Here is my SQL so far, it will select records 1 to 5 instead of 1,2,3 and 5 only. Any help greatly appreciated. I think I need a Group By somewhere in there. select a1.id from aap a1 where id < ( SELECT max(id) FROM aap AS a2 ) AND EXISTS ( SELECT * FROM aap AS a2 WHERE a1.keyword = a2.keyword ) I just tries this with 7.3.3: select max(id), keyword from aap where keyword in (select distinct keyword from aap) group by keyword; max | keyword --- 6 | LEAGUE BRONCOS 4 | LEAGUE PANTHERS (2 rows) HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] date question
On Fri, Jun 20, 2003 at 19:33:35 +0200, javier garcia - CEBAS <[EMAIL PROTECTED]> wrote: > Hi all; > > Peter, thank you very much for your help. Just a little thing. I've done as > you say: > > CREATE TABLE rain_series_dated AS SELECT (year * interval '1 year' + month * > interval '1 month' + day * interval '1 day') AS > fecha,est7237,est7238,est7239,est7250 FROM rain_series ORDER by fecha; It would probably be better to use a view instead of copying the data to another table. That way you don't need to worry about the data getting out of sync. > And I've tried to use this result to be compared with my other table in which > "fecha" is "date" type. The result is that the query halts with no result; I > guess that it is because it tries to compare different data types. It wouldn't be because of trying to compare a date and an interval. You would get an error message instead. > Is it possible to cast the "interval" obtained type into a "date" one in the > creation of the mentioned table? (I've looked it in the documentation, but I > can't find the answer) Not really. You could add the interval values to a base date, but that may or may not make sense depending on your application. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Informing end-user of check constraint rules
I have not used column check constraints before, but I'd like to start using then and so would I'll like to know if there is a direct way to provide feedback to the end user about data validation rules expressed in column check constraints? For instance, say that I wanted to use a RE to check e-mail address format validity and then the data entry clerk typed in invalid data. My understanding is that when the check constraint returns FALSE, the row will not insert, and an ExecAppend: rejected due to CHECK constraint "table_column " exception is raised. That at least tells the column (albeit in language that would scare the computer-phobe), but I like the exception message to tell the end user what the format is supposed to be. Is my only option to have the end-user application (as opposed to the database) inform the end-user what the correct data format is? If THAT is so, then it seems I might as well also perform the data formatting validation in the application, too, so that at least they'ld both be in the same place and not two separate places. What I'd like is to be able to specify some kind of error message telling the user what the correct format should be, and since the proper format is specified in the database, i.e., in the check constraint, it seems that the proper place to raise an exception providing the remedial instructions would also be in the database. ~Berend Tober ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Delete duplicates
Hi, you need find duplicates and then you remove them delete from aap where id not in ( select max(id) from aap b where aap.keyword = b.keyword ); Germán Sorry about my english -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Rudi Starcevic Enviado el: Domingo, 22 de Junio de 2003 5:15 Para: [EMAIL PROTECTED] Asunto: [SQL] Delete duplicates Hi, I have a table with duplicates and trouble with my SQL. I'd like to keep a single record and remove older duplicates. For example below of the 6 recods I'd like to keep records 4 and 6. TABLE: aap id | keyword +- 1 | LEAGUE PANTHERS 2 | LEAGUE PANTHERS 3 | LEAGUE PANTHERS 4 | LEAGUE PANTHERS 5 | LEAGUE BRONCOS 6 | LEAGUE BRONCOS Here is my SQL so far, it will select records 1 to 5 instead of 1,2,3 and 5 only. Any help greatly appreciated. I think I need a Group By somewhere in there. select a1.id from aap a1 where id < ( SELECT max(id) FROM aap AS a2 ) AND EXISTS ( SELECT * FROM aap AS a2 WHERE a1.keyword = a2.keyword ) Regards Rudi. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] Informing end-user of check constraint rules
Am Sonntag, 22. Juni 2003 14:45 schrieb [EMAIL PROTECTED]:
> I have not used column check constraints before, but I'd like to start
> using then and so would I'll like to know if there is a direct way to
> provide feedback to the end user about data validation rules expressed in
> column check constraints?
>
> For instance, say that I wanted to use a RE to check e-mail address
> format validity and then the data entry clerk typed in invalid data. My
> understanding is that when the check constraint returns FALSE, the row
> will not insert, and an ExecAppend: rejected due to CHECK constraint
> "table_column " exception is raised. That at least tells the column
> (albeit in language that would scare the computer-phobe), but I like the
> exception message to tell the end user what the format is supposed to be.
> Is my only option to have the end-user application (as opposed to the
> database) inform the end-user what the correct data format is? If THAT is
> so, then it seems I might as well also perform the data formatting
> validation in the application, too, so that at least they'ld both be in
> the same place and not two separate places.
>
> What I'd like is to be able to specify some kind of error message telling
> the user what the correct format should be, and since the proper format
> is specified in the database, i.e., in the check constraint, it seems
> that the proper place to raise an exception providing the remedial
> instructions would also be in the database.
you can use a trigger on insert and write your own error handling function
like below. then you have everything at one place. I think postgres should
have better ways to report errors but i am not a database guru and dont know
how other databases do their error handling.
Maybe its better to have some kind of "middleware" to keep the business logic
and use the database just to store data... i thought about it a lot and tried
to find relevant informations about how to model data/businesslogic/frontend
in a convienient way...
here is an example to check different columns and return an explanation of on
or more errors. of course your frontend has to parse this errormsg for the
relevant part shown to the user. its just copied but a little bit modified
code from a working example. but this code below isn't tested and
errormessages are in german.
what i like most is not having good error message but you can show all errors
at once.
kind regards
janning
CREATE TRIGGER tg_user BEFORE INSERT OR UPDATE ON USER FOR EACH ROW EXECUTE
PROCEDURE tg_user_col_check();
CREATE FUNCTION tg_user_col_check () RETURNS TRIGGER AS '
DECLARE
var_errmsg text := ''TIPPER'';
var_errorboolean;
rec_any RECORD;
var_countint4;
var_maxmitgl int4 := 1000; ---
-- email --
---
NEW.email := btrim(NEW.email);
IF NEW.email !~ ''[EMAIL PROTECTED]'' THEN
var_error := ''true'';
var_errmsg := var_errmsg || ''#name:''
|| ''Die E-Mail Adresse darf nur aus Buchstaben, Zahlen und einigen
Sonderzeichen ("_", "-", "@", ".") bestehen. '';
END IF;
IF length(NEW.name) < 3 THEN
var_error := ''true'';
var_errmsg := var_errmsg || ''#name:''
|| ''Der Benutzername muss mindestens drei Zeichen lang sein";
END IF;
IF length(NEW.email) > 50 THEN
var_error := ''true'';
var_errmsg := var_errmsg || ''#email:''
|| ''Die E-Mail Adresse darf nicht länger als 50 Buchstaben sein'';
END IF;
IF var_error THEN
RAISE EXCEPTION ''%'', var_errmsg;
END IF;
RETURN NEW;
END;
' language 'plpgsql';
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Delete duplicates
try this DELETE FROM aap WHERE id NOT IN ( SELECT max(id) FROM aap GROUP BY keyword ); > > > Hi, > > I have a table with duplicates and trouble with my SQL. > I'd like to keep a single record and remove older duplicates. > For example below of the 6 recods I'd like to keep records > 4 and 6. > > TABLE: aap > id | keyword > +- > 1 | LEAGUE PANTHERS > 2 | LEAGUE PANTHERS > 3 | LEAGUE PANTHERS > 4 | LEAGUE PANTHERS > 5 | LEAGUE BRONCOS > 6 | LEAGUE BRONCOS > > Here is my SQL so far, it will select records 1 to 5 instead > of 1,2,3 and 5 only. > > Any help greatly appreciated. I think I need a Group By somewhere in > there. > > select a1.id > from aap a1 > where id < ( SELECT max(id) FROM aap AS a2 ) > AND EXISTS > ( > SELECT * > FROM aap AS a2 > WHERE a1.keyword = a2.keyword > ) > > Regards > Rudi. > > ---(end of > broadcast)--- TIP 1: subscribe and unsubscribe > commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] virtual table
Hi I have another virtual problem, currently without any examples ;-) Let's say we have some pl/pgsql function which puts result into table1. This flat table must be normalized and put into table2. Sometimes 1 row from table1 = 1 row from table2, but sometimes 1 row from table1= 3 rows from table2. Data from table1 are transferrend into table2 using triggers. I found, I don't to have any data in table1. The question is: Is it possible to create virtual table in Postgresql? Virtual - means it won't contain any columns nor data, but trigger doing all the job. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Delete duplicates
How to delete "real" duplicates? id | somthing --- 1 | aaa 1 | aaa 2 | bbb 2 | bbb (an accident with backup recovery...) Regards, Denis Arh - Original Message - From: "Franco Bruno Borghesi" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, June 22, 2003 11:17 PM Subject: Re: [SQL] Delete duplicates > try this > > DELETE FROM aap WHERE id NOT IN ( >SELECT max(id) >FROM aap >GROUP BY keyword > ); > > > > > > > Hi, > > > > I have a table with duplicates and trouble with my SQL. > > I'd like to keep a single record and remove older duplicates. > > For example below of the 6 recods I'd like to keep records > > 4 and 6. > > > > TABLE: aap > > id | keyword > > +- > > 1 | LEAGUE PANTHERS > > 2 | LEAGUE PANTHERS > > 3 | LEAGUE PANTHERS > > 4 | LEAGUE PANTHERS > > 5 | LEAGUE BRONCOS > > 6 | LEAGUE BRONCOS > > > > Here is my SQL so far, it will select records 1 to 5 instead > > of 1,2,3 and 5 only. > > > > Any help greatly appreciated. I think I need a Group By somewhere in > > there. > > > > select a1.id > > from aap a1 > > where id < ( SELECT max(id) FROM aap AS a2 ) > > AND EXISTS > > ( > > SELECT * > > FROM aap AS a2 > > WHERE a1.keyword = a2.keyword > > ) > > > > Regards > > Rudi. > > > > ---(end of > > broadcast)--- TIP 1: subscribe and unsubscribe > > commands go to [EMAIL PROTECTED] > > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Delete duplicates
> How to delete "real" duplicates? > > id | somthing > --- > 1 | aaa > 1 | aaa > 2 | bbb > 2 | bbb > > (an accident with backup recovery...) I'm not 100% on some of the syntax off the top of my head, but: BEGIN; ALTER TABLE orig_table RENAME TO backup_table; CREATE TABLE orig_table AS SELECT id,something FROM backup_table GROUP BY id, something; -- Create any indexes on orig_table that need to be recreated DROP TABLE orig_table; COMMIT; This isn't for the faint of heart: be sure to do this inside of a transaction or on a backup db until you're 100% good to go. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Delete duplicates
"Denis Arh" <[EMAIL PROTECTED]> writes: > How to delete "real" duplicates? Use the OID or CTID system columns. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Delete duplicates
Hi, Would this be OK or a little crude (untested) : INSERT INTO new_table ( id, something ) SELECT DISTINCT ON (id) id, something FROM old_table ORDER BY id Or something similar but create a new table ? Cheers Rudi. Denis Arh wrote: How to delete "real" duplicates? id | somthing --- 1 | aaa 1 | aaa 2 | bbb 2 | bbb (an accident with backup recovery...) Regards, Denis Arh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Urgent Help : Use of return from function/procedure.
Title: Urgent Help : Use of return from function/procedure. Hi, I'm new to postgres and using version 7.2.4 I've created a trigger and function which does the following: trigger 'T' fires after insert on a spcific table takes place and it executes function 'F' Function 'F' returns the new record inserted by 'return new' statement. Now my question is: How can I use this 'new' value in my client program? In this prgm., I want to know which all values are inserted into the table. Help is appreciated. Thx, Anagha
