[SQL] copy old column's values to new column

2004-09-02 Thread ogjunk-pgjedan 
Hello,

I need some basic SQL help.  I added a new column to an existing table,
and now I need to copy values from one of the old columns to this new
columns.

I need something like this:

FOR pvId IN SELECT id FROM preference_value LOOP
update preference_value SET display_value = (select value from
preference_value where id=pvId) where id=pvId;
END LOOP;

I tried running this from psql, but it didn't work (I suspect FOR can
be used in functions, which I don't know how to write in PG, yet).

Is there a simple way to do this?

Thanks,
Otis


---(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] copy old column's values to new column

2004-09-02 Thread Bruno Wolff III 
On Thu, Sep 02, 2004 at 06:16:47 -0700,
  [EMAIL PROTECTED] wrote:
> Hello,
> 
> I need some basic SQL help.  I added a new column to an existing table,
> and now I need to copy values from one of the old columns to this new
> columns.
> 
> I need something like this:
> 
> FOR pvId IN SELECT id FROM preference_value LOOP
> update preference_value SET display_value = (select value from
> preference_value where id=pvId) where id=pvId;
> END LOOP;
> 
> I tried running this from psql, but it didn't work (I suspect FOR can
> be used in functions, which I don't know how to write in PG, yet).
> 
> Is there a simple way to do this?

Assuming you are just copying "value" to "display_value", can't you just do:


UPDATE preference_value SET display_value = value;

---(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

[SQL] PRIMARY KEY and INDEX

2004-09-02 Thread Nosyman 
It is necessary to define an index for a primary key column?

Let's have an example
CREATE TABLE users(
id_user INTEGER PRIMARY KEY,
user_name VARCHAR(25)
);

Does PgSQL automatically create an index for id_user (primary key) or it
must be created by hand?

Thanks


_
Message
sent using ITCNet free webmailer (http://www.easymail.ro)

---(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] PRIMARY KEY and INDEX

2004-09-02 Thread Achilleus Mantzios 
O kyrios Nosyman egrapse stis Sep 2, 2004 :

> It is necessary to define an index for a primary key column?
> 
> Let's have an example
> CREATE TABLE users(
> id_user INTEGER PRIMARY KEY,
> user_name VARCHAR(25)
> );
> 
> Does PgSQL automatically create an index for id_user (primary key) or it
> must be created by hand?

Yes a unique index is created automatically so as to enforce the 
PK constraint.

> 
> Thanks
> 
> 
> _
> Message
> sent using ITCNet free webmailer (http://www.easymail.ro)
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 

-- 
-Achilleus


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] PRIMARY KEY and INDEX

2004-09-02 Thread Devrim GUNDUZ 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
On Thu, 2 Sep 2004, Nosyman wrote:
It is necessary to define an index for a primary key column?
Let's have an example
CREATE TABLE users(
id_user INTEGER PRIMARY KEY,
user_name VARCHAR(25)
);
Does PgSQL automatically create an index for id_user (primary key) or it
must be created by hand?
You could give it a try before asking:
test=# CREATE TABLE users(
test(# id_user INTEGER PRIMARY KEY,
test(# user_name VARCHAR(25)
test(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"users_pkey" for table "users"
CREATE TABLE

Regards,
- --
Devrim GUNDUZ 
devrim~gunduz.orgdevrim.gunduz~linux.org.tr
			http://www.tdmsoft.com
			http://www.gunduz.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFBNzLFtl86P3SPfQ4RAuOZAKDoE5NFtu7B3Dg356+CH5P9CGp7cgCfaBQm
33nie6C/x2PIfA0D1zqWgLE=
=QlUA
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Complicated "group by" question

2004-09-02 Thread Jeff Boes 
Andrew Perrin wrote:
I have a table of people ("reviewers"), a table of review assignments
("assign"), and a table of review acceptances ("accept"). I would like to
be able to write a query to return the latest (e.g., max(assign_date))
assignment for each reviewer, plus the acc_id field from "accept".  I
think I should be able to do this with a GROUP BY clause, but am having no
luck.
Table structure:
reviewers   assign  accept
-
reviewer_id assign_id   accept_id
reviewer_id assign_id
... assign_date
... ...

I think you want to write a non-GROUPed query using "DISTINCT ON". 
Something like this:

SELECT DISTINCT ON (reviewer_id,assign_id)
 reviewer_id,
 assign_id,
 assign_date,
 accept_id
FROM reviewers
JOIN assign USING (reviewer_id)
JOIN accept USING (accept_id)
ORDER BY reviewer_id, assign_id, assign_date DESC;
--
(Posted from an account used as a SPAM dump. If you really want to get
in touch with me, dump the 'jboes' and substitute 'mur'.)

Jeffery Boes <>< [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: 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