[SQL] Determining Inheritance

[Gordon Ross]

Is there any way to determine which table a row belows to, if I do a
search on the top level table:

e.g. I have a table called "BASE". There are two tables which inherit
from "BASE" called "SUBTABLE1" and "SUBTABLE2"

If I do a SELECT * FROM BASE WHERE blah; how can I tell which table
(either SUBTABLE1 or SUBTABLE2) a row comes from ?

Do I need to add an extra column to BASE to specify which table a row
really belongs to ?

(If it helps or hinders, I am ultimately going to be doing this via
JDBC)

Thanks,

GTG

Gordon Ross,
Network Manager/Rheolwr Rhydwaith
Countryside Council for Wales/Cyngor Cefn Gwlad Cymru

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] Make a column case insensitive

[Gordon Ross]

Is it possible to make a column case insensitive, without having to pepper your 
SELECTs with lots of lower() function calls (and forgetting to do it at times !)

(I'm on 7.4.3)

Thanks,

GTG

Gordon Ross,
Network Manager/Rheolwr Rhydwaith
Countryside Council for Wales/Cyngor Cefn Gwlad Cymru


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Unable to get UPDATE ... FROM syntax correct

[Gordon Ross]

I have two tables:

   Table "public.audit"
   Column   |Type  | Modifiers
+--+---
 id | integer  | (serial)
 record_id  | integer  | not null
 key| character varying|
(...)


  Table "public.extension"
  Column |   Type |  Modifiers
-++
 id  | integer| (serial)
 number  | integer|
(...)


The column "key" was recently added to the "audit" table, and I wish to
populate it with the value form the "number" column in the extension table.
The join between the tables is audit.record_id = extension.id

I tried:

UPDATE audit SET key = extension.number FROM extension WHERE audit.record_id
= extension.number;

But that returns saying "UPDATE 0"

However, doing:

SELECT audit.record_id, extension.number FROM audit, extension WHERE
audit.record_id = extension.id;

Works fine.

Can someone tell me what I'm doing wrong ?

Thanks,

GTG


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Unable to get UPDATE ... FROM syntax correct

[Gordon Ross]

On 19/02/2010 10:28, "zkn"  wrote:
> update audit set key = (select extension.number from extension where
> audit.record_id = extension_id)

No joy, I still get "UPDATE 0"

:-(

GTG


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Unable to get UPDATE ... FROM syntax correct

[Gordon Ross]

On 19/02/2010 10:36, "Richard Huxton"  wrote:

> Works here - are you sure you don't have any triggers interfering?

Doh ! *blush*

Yes, I did, to stop anyone tampering with the audit table ;-)

Thanks for that.

GTG


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql