Re: [GENERAL] looping through query to update column
On 2006-10-13, "Albe Laurenz" <[EMAIL PROTECTED]> wrote:
> You lock the table (with LOCK) or the row you're working on
> (with SELECT FOR UPDATE) so that nobody else can change it while
> you are working on it.
>
> You need something like ctid if your table has the fundamental flaw
> of lacking a primary key.
Looping over rows unnecessarily is a mistake.
You can add a SERIAL column to a table using ALTER TABLE, which will
automatically number the existing rows; this is a better way to fix a
lack of a primary key than messing around with ctids.
For a one-off update, use a temporary sequence:
create temporary sequence foo;
update table set recordid = nextval('foo');
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] looping through query to update column
On 10/13/06, Jean-Christophe Roux <[EMAIL PROTECTED]> wrote: Thanks for the "ctid" trick. The code below worked fine for rec in select * from fromemail_trades loop update fromemail_trades set recordid = row where ctid = rec.ctid; row := row -1; end loop; The first line is a little different from your's: FOR row IN SELECT ctid, * FROM table FOR UPDATE LOOP How important is it to specify ctid in the select and to add 'for update'? it's not. also, without a where clause you are better off just locking the table (lock table...). also, the above loop is better achieved via a single query. merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] looping through query to update column
Thanks for the "ctid" trick. The code below worked fine for rec in select * from fromemail_trades loop update fromemail_trades set recordid = row where ctid = rec.ctid; row := row -1; end loop;The first line is a little different from your's: FOR row IN SELECT ctid, * FROM table FOR UPDATE LOOPHow important is it to specify ctid in the select and to add 'for update'?Thanks againJCR - Original Message From: Albe Laurenz <[EMAIL PROTECTED]>To: pgsql-general@postgresql.orgSent: Friday, October 13, 2006 6:24:16 AMSubject: Re: [GENERAL] looping through query to update columnRafal Pietrak wrote:>> You might use 'ctid' to identify the row if you have no suitable> > How should I use 'ctid'? Like in the case, when I've selected > something by means of SELECT ... FOR UPDATE?You lock the table (with LOCK) or the row you're working on(with SELECT FOR UPDATE) so that nobody else can change it whileyou are working on it.You need something like ctid if your table has the fundamental flawof lacking a primary key.Sample:FOR row IN SELECT ctid, * FROM table FOR UPDATE LOOP UPDATE table SET column=value WHERE ctid=row.ctid; ...END LOOP;If your table has a primary key, use that instead and pleaseforget about the ctid.Yours,Laurenz Albe---(end of broadcast)---TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] looping through query to update column
Rafal Pietrak wrote: >> You might use 'ctid' to identify the row if you have no suitable > > How should I use 'ctid'? Like in the case, when I've selected > something by means of SELECT ... FOR UPDATE? You lock the table (with LOCK) or the row you're working on (with SELECT FOR UPDATE) so that nobody else can change it while you are working on it. You need something like ctid if your table has the fundamental flaw of lacking a primary key. Sample: FOR row IN SELECT ctid, * FROM table FOR UPDATE LOOP UPDATE table SET column=value WHERE ctid=row.ctid; ... END LOOP; If your table has a primary key, use that instead and please forget about the ctid. Yours, Laurenz Albe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] looping through query to update column
On Fri, 2006-10-13 at 09:23 +0200, Albe Laurenz wrote: > You might use 'ctid' to identify the row if you have no suitable How should I use 'ctid'? Like in the case, when I've selected something by means of SELECT ... FOR UPDATE? -- -R ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] looping through query to update column
Jean-Christophe Roux wrote: > I am trying to loop through a table to update one column > > create or replace function foo() returns integer as $$ > declare > rec RECORD; > row integer := 0; > begin > for rec in select * from table loop > update rec set recordid = row; > row++; > end loop; > return 0; > end; > $$ language plpgsql > > In pgadmin, I am getting the following error message, but > that does not help me much: > ERROR: syntax error at or near "$1" at character 9 > QUERY: update $1 set recordid = $2 > CONTEXT: SQL statement in PL/PgSQL function "foo" near line 6 You cannot UPDATE a record, you can only UPDATE a table. So it should be something like UPDATE table SET recordid = row WHERE primarykey = rec.primarykey You might use 'ctid' to identify the row if you have no suitable primary key (you do have one, don't you?), but beware that ctid can change suddenly and unexpectedly when somebody else modifies the row. To protect against that, you can either LOCK the table or SELECT ... FOR UPDATE. Yours, Laurenz Albe ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] looping through query to update column
Hello,I am trying to loop through a table to update one columncreate or replace function foo() returns integer as $$declare rec RECORD; row integer := 0;begin for rec in select * from table loop update rec set recordid = row; row++; end loop; return 0;end;$$ language plpgsqlIn pgadmin, I am getting the following error message, but that does not help me much:ERROR: syntax error at or near "$1" at character 9QUERY: update $1 set recordid = $2 CONTEXT: SQL statement in PL/PgSQL function "foo" near line 6Thanks for any helpJCR
