Обсуждение: Deleting obsolete values
This may look familiar to you - it was on the list last month.
Consider the following table
create table partitur(userid text, val integer, ts timestamp DEFAULT NOW() );
Do some inserts
insert into partitur values('Bart', 1440);
insert into partitur values('Lisa', 1024);
insert into partitur values('Bart', 7616);
insert into partitur values('Lisa', 3760);
insert into partitur values('Bart', 3760);
insert into partitur values('Lisa', 7616);
To retrieve the latest values (meaning the last ones inserted)
Tom Lane wrote
>This is what SELECT DISTINCT ON was invented for. I don't know any
>comparably easy way to do it in standard SQL, but with DISTINCT ON
>it's not hard:
>SELECT DISTINCT ON (userid) userid, val, ts FROM partitur
>ORDER BY userid, ts DESC;
My question now is
Is there a way to delete all rows the select statement did not
bring up?
After that *unknown* delete statement
select userid, val, ts from partitur ;
should show exactly the same as the SELECT DISTINCT ON (userid) ...
did before.
Regards, Christoph
DELETE FROM partitur WHERE EXISTS (SELECT * FROM partitur AS ss_partitur
WHERE ss_partitur.userid=partitur.userid AND ss_partitur.ts>partitur.ts);
Seems like it should seems like it should delete all old values (however I
have not tested it)
- Stuart
> -----Original Message-----
> From: Haller Christoph [SMTP:ch@rodos.fzk.de]
> Sent: Tuesday, October 16, 2001 5:45 PM
> To: pgsql-sql@postgresql.org
> Subject: Deleting obsolete values
>
> This may look familiar to you - it was on the list last month.
> Consider the following table
> create table partitur
> (userid text, val integer, ts timestamp DEFAULT NOW() );
> Do some inserts
> insert into partitur values('Bart', 1440);
> insert into partitur values('Lisa', 1024);
> insert into partitur values('Bart', 7616);
> insert into partitur values('Lisa', 3760);
> insert into partitur values('Bart', 3760);
> insert into partitur values('Lisa', 7616);
> To retrieve the latest values (meaning the last ones inserted)
> Tom Lane wrote
> >This is what SELECT DISTINCT ON was invented for. I don't know any
> >comparably easy way to do it in standard SQL, but with DISTINCT ON
> >it's not hard:
> >SELECT DISTINCT ON (userid) userid, val, ts FROM partitur
> >ORDER BY userid, ts DESC;
>
> My question now is
> Is there a way to delete all rows the select statement did not
> bring up?
> After that *unknown* delete statement
> select userid, val, ts from partitur ;
> should show exactly the same as the SELECT DISTINCT ON (userid) ...
> did before.
>
> Regards, Christoph
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello: I've got a table containing property_id's with values of the form ###-####. I would like to discard the slash onwards (and I can't use a substr() because I am not guaranteed if a) the -#### portion exists, b) what position it exists from. If this were a text file, I would use a sed expression such as: cat textfile | sed 's/-.*$//' I've been looking for a way to do this with PostgreSQL but so far haven't found a function that seems to be suitable. I thought maybe I could do it with translate, but translate doesn't appear to work with regular expressions. So far I've tried things like: select translate(property_id, '-.*', '') from mytable; I need to do this, because the -.* portion of my property_id was entered in error, and I would like to do an update on the entire table and just have the left-hand side of the property_id column remaining. Any ideas? Thank you in advance. - ---------------< LINUX: The choice of a GNU generation. >------------- Steve Frampton <frampton@LinuxNinja.com> http://www.LinuxNinja.com GNU Privacy Guard ID: D055EBC5 (see http://www.gnupg.org for details) GNU-PG Fingerprint: EEFB F03D 29B6 07E8 AF73 EF6A 9A72 F1F5 D055 EBC5 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.0 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7zv1TmnLx9dBV68URAisEAJ4nNYz4lxpgWojULE/Xo9SUb5IexgCfS9At J6kAVn/3vFHeJkl9bjr4AcQ= =W4xQ -----END PGP SIGNATURE-----
Well, the easiest general way is probably a plperl function, but I think
the following may work for your specific case:
update mytable setproperty_id=substr(property_id, 1, position('-' in property_id)-1) where position('-' in
property_id)!=0;
On Thu, 18 Oct 2001, Steve Frampton wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello:
>
> I've got a table containing property_id's with values of the form
> ###-####. I would like to discard the slash onwards (and I can't use a
> substr() because I am not guaranteed if a) the -#### portion exists, b)
> what position it exists from.
>
> If this were a text file, I would use a sed expression such as:
>
> cat textfile | sed 's/-.*$//'
>
> I've been looking for a way to do this with PostgreSQL but so far haven't
> found a function that seems to be suitable. I thought maybe I could do it
> with translate, but translate doesn't appear to work with regular
> expressions. So far I've tried things like:
>
> select translate(property_id, '-.*', '') from mytable;
>
> I need to do this, because the -.* portion of my property_id was entered
> in error, and I would like to do an update on the entire table and just
> have the left-hand side of the property_id column remaining.
You could write a Tcl (i.e. pltcl) function, and use that to do what you want:
CREATE FUNCTION remove(varchar) RETURNS varchar AS '
set input $1
regsub -- {-.*$} $input {} output
return $output
' language 'pltcl';
[NOTE: untested]
you may have to monkey with the regexp to get exactly what you want...
--brett
On Thu, 18 Oct 2001 12:03:28 -0400 (EDT)
Steve Frampton <frampton@LinuxNinja.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello:
>
> I've got a table containing property_id's with values of the form
> ###-####. I would like to discard the slash onwards (and I can't use a
> substr() because I am not guaranteed if a) the -#### portion exists, b)
> what position it exists from.
>
> If this were a text file, I would use a sed expression such as:
>
> cat textfile | sed 's/-.*$//'
>
> I've been looking for a way to do this with PostgreSQL but so far haven't
> found a function that seems to be suitable. I thought maybe I could do it
> with translate, but translate doesn't appear to work with regular
> expressions. So far I've tried things like:
>
> select translate(property_id, '-.*', '') from mytable;
>
> I need to do this, because the -.* portion of my property_id was entered
> in error, and I would like to do an update on the entire table and just
> have the left-hand side of the property_id column remaining.
>
> Any ideas? Thank you in advance.
>
> - ---------------< LINUX: The choice of a GNU generation. >-------------
> Steve Frampton <frampton@LinuxNinja.com> http://www.LinuxNinja.com
> GNU Privacy Guard ID: D055EBC5 (see http://www.gnupg.org for details)
> GNU-PG Fingerprint: EEFB F03D 29B6 07E8 AF73 EF6A 9A72 F1F5 D055 EBC5
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.0.0 (GNU/Linux)
> Comment: For info see http://www.gnupg.org
>
> iD8DBQE7zv1TmnLx9dBV68URAisEAJ4nNYz4lxpgWojULE/Xo9SUb5IexgCfS9At
> J6kAVn/3vFHeJkl9bjr4AcQ=
> =W4xQ
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Steve Frampton <frampton@LinuxNinja.com> writes:
> If this were a text file, I would use a sed expression such as:
> cat textfile | sed 's/-.*$//'
> I've been looking for a way to do this with PostgreSQL but so far haven't
> found a function that seems to be suitable.
Write a function in pltcl or plperl, either of which can mash text
strings with ease and abandon ...
regards, tom lane
Steve, > I've got a table containing property_id's with values of the form > ###-####. I would like to discard the slash onwards (and I can't use > a > substr() because I am not guaranteed if a) the -#### portion exists, > b) > what position it exists from. > > If this were a text file, I would use a sed expression such as: > > cat textfile | sed 's/-.*$//' In SQL/plpgsql, you can't do this with a single expression. However, you can do it with three expressions put together. CREATE FUNCTION remove_propid_tail (VARCHAR ) RETURNS VARCHAR AS' SELECT SUBSTR($1, 1, ((STRPOS($1, ''-'') - 1)); END;' LANGUAGE 'SQL'; Then run: UPDATE main_table SET property_id = remove_propid_tail(property_id) WHERE property_id ~ '-'; -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Delete from partitur where userid NOT IN (SELECT DISTINCT ON (userid)
userid, val, ts FROM partitur)
"Haller Christoph" <ch@rodos.fzk.de> wrote in message
news:200110161445.QAA11833@rodos...
> This may look familiar to you - it was on the list last month.
> Consider the following table
> create table partitur
> (userid text, val integer, ts timestamp DEFAULT NOW() );
> Do some inserts
> insert into partitur values('Bart', 1440);
> insert into partitur values('Lisa', 1024);
> insert into partitur values('Bart', 7616);
> insert into partitur values('Lisa', 3760);
> insert into partitur values('Bart', 3760);
> insert into partitur values('Lisa', 7616);
> To retrieve the latest values (meaning the last ones inserted)
> Tom Lane wrote
> >This is what SELECT DISTINCT ON was invented for. I don't know any
> >comparably easy way to do it in standard SQL, but with DISTINCT ON
> >it's not hard:
> >SELECT DISTINCT ON (userid) userid, val, ts FROM partitur
> >ORDER BY userid, ts DESC;
>
> My question now is
> Is there a way to delete all rows the select statement did not
> bring up?
> After that *unknown* delete statement
> select userid, val, ts from partitur ;
> should show exactly the same as the SELECT DISTINCT ON (userid) ...
> did before.
>
> Regards, Christoph
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)