Forum OpenACS Q&A: Postgre porting questions

Posted by Vinod Kurup on 12/25/00 04:41 AM

Merry Christmas everyone!

I'm in the process of porting some ACS code (photodb module) to openACS and I had a couple questions (mostly due to the fact that I'm not too handy with this SQL stuff)

First: If table2 has a column which references (foreign key) table1, then is there any reason why you'd want to do an outer join? (where table2.column=table1.column(+)) I've seen this in the ACS and I think a regular join is all that you need, because due to the foreign key, table1 will always have a row that matches table2. Right? So, I can just delete the little '(+)'...

Also: I've noted a couple of gotcha's not mentioned in the porting guide...

Changing nvl(a,b) to coalesce(a,b) only works if both a & b are the same type. If a is integer and b is char, then you need to convert a to a char via to_char.
You can't use subqueries in the FROM clause. You need to create a view instead.

Again, I'm not too experienced with this stuff, so I'd appreciate being corrected if I'm wrong about any of the above.

Thanks! If all goes well, I should be done with photodb sometime next week.

2: Response to Postgre porting questions (response to 1)

Posted by Don Baccus on 12/25/00 03:32 PM

Glad you're working on this. I was unaware of the nvl "gotcha",
because in our previous porting work the aD folk hadn't used it in
this way.

Subqueries in from will be included in PG 7.1, which is in "quiet
beta" at the moment, with a more visible public beta following soon.

As far as outer joins and foreign key references go, "NULL" is allowed
unless "not null" is specified. So the outer join may be necessary.
PG 7.1 will have outer joins ... ORDER BY on UNION queries don't work
correctly in PG 7.0 so when you expand the outer join query you need
to be aware of this.

It may be that photodb never inserts a row with a NULL value rather
than a key in the referenced table in the particular case you're
looking at, if true you can then drop the outer join entirely, as you
suggest doing.

3: Response to Postgre porting questions (response to 1)

Posted by Vinod Kurup on 12/26/00 07:06 AM

As far as outer joins and foreign key references go, "NULL" is allowed unless "not null" is specified.

Thanks Don! I didn't know about that. Fortunately most of the instances in photodb are outer joins on foreign key references to 'not null' columns.

4: Response to Postgre porting questions (response to 1)

Posted by Cynthia Kiser on 12/27/00 07:14 PM

About the nvl gottcha. Oracle's nvl surprised me because it will cast types - but only in one direction. If your column is a varchar, you can use nvl to get numbers or characters out: nvl(char_col, 'junk') and nvl(char_col, 33) both work. But if the column is a number, nvl(num_col, 'junk') does not work; you can only pull out numbers (e.g. nvl(num_col, 0)). In Oracle I replaced the NVL with a decode testing for NULL; casting using to_char did not throw an error but order of operations meant that I did not get what I wanted:

select nvl(to_char(sum(user_amount), '999,999,999.00'), 'None') as pending_gifts 
from gifts where gift_status in ('in progress', 'receivable', 'received')
group by daf_id;

Gives me ".00" rather than "None"

select decode(sum(user_amount),0, 'None', to_char(sum(user_amount), '999,999,999.00')) as pending_gifts 
from gifts where gift_status in ('in progress', 'receivable', 'received')
group by daf_id;

Gives me "None" when there are no pending gifts.

5: Response to Postgre porting questions (response to 1)

Posted by Vinod Kurup on 12/29/00 04:45 AM

I've found another difference between Postgre (7.03) and Oracle. Postgre doesn't seem to count rows in a view properly. If I define the following (assuming the users table is already defined):


openacs=# create table photos (
       photo_id         integer not null primary key,
       user_id          integer references users,
       creation_date    datetime
);

openacs=# create view new_users as
 select user_id, min(creation_date) first_photo_date from photos 
group by user_id;

openacs=# select * from photos;
 photo_id | user_id |     creation_date
----------+---------+------------------------
        1 |       3 | 2000-12-05 00:00:00-05
        2 |       3 | 2000-12-28 22:20:36-05
        3 |       4 | 2000-12-27 00:00:00-05
        4 |       4 | 2000-12-26 00:00:00-05
        5 |       4 | 2000-12-26 00:00:00-05
        6 |       4 | 2000-12-28 22:21:22-05
(6 rows)

openacs=# select * from new_users;
 user_id |          min
---------+------------------------
       3 | 2000-12-05 00:00:00-05
       4 | 2000-12-26 00:00:00-05
(2 rows)

Now, if i do select count(*) from new_users, I would expect to get the answer 2 (and that's what Oracle does). Postgre responds:


openacs=# select count(*) from new_users;
 count
-------
     2
     4
(2 rows)

So Postgre seems to count the rows inside the view instead of the rows that the view returns. I'm not sure if this is a bug, or simply a different way of doing things...