pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Query for block updates
2+ messages / 2 participants
[nested] [flat]

* Query for block updates
@ 2006-12-03 22:19  Amit Shah <ashah@opuspro.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Amit Shah @ 2006-12-03 22:19 UTC (permalink / raw)
  To: pgsql-sql

Hi,

If this question doesn't belong to this mailing list, please pardon.

I have a table with a few hundred million rows, and I need to write a query 
that does something like follows --

Change all the values of a column to null where the value cannot be casted to 
numeric(20,4)

I have it as varchar, and I need to convert it to numeric(20,4).

I am using this query - alter table optiondata ALTER volume TYPE numeric(20,4) 
using cast(volume as numeric(20,4)); then wait for DB to complain, and then 
update that value to null, and just keep doing this till all garbadge is 
removed :-(

Thanks,
Amit



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Query for block updates
@ 2006-12-03 22:22  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Amit Shah <ashah@opuspro.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2006-12-03 22:22 UTC (permalink / raw)
  To: Amit Shah <ashah@opuspro.com>; +Cc: pgsql-sql

Amit Shah <ashah@opuspro.com> writes:
> I am using this query - alter table optiondata ALTER volume TYPE numeric(20,4) 
> using cast(volume as numeric(20,4)); then wait for DB to complain, and then 
> update that value to null, and just keep doing this till all garbadge is 
> removed :-(

You could automate it in plpgsql ... something like

	for r in select id, val from table
	loop
		begin
			perform cast(r.val as numeric(20,4));
		exception
			when ... then
				update table set val = null where id = r.id;
		end;
	end loop;

and then do the ALTER TYPE after you've cleaned the data.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2006-12-03 22:22 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-03 22:19 Query for block updates Amit Shah <ashah@opuspro.com>
2006-12-03 22:22 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox