Re: [SQL] Question

[Christoph Haller]

> Can I get a table structure on a function using pgsql??

I think so. What are your intentions? You should be more specific.

Regards, Christoph


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

Re: [SQL] sql query

[Christoph Haller]

>
> Hi I have a problem forming a query
> I have 3 tables salary(hrs, clock_in , clock_out)
> Break ( date, employe_id, net_time, break_in, break_out)
> Employee(employee_id, pay_rate, name)
>
> I need to get an hourly report till the current time for that day
> stating name, hour, no of hours, salary
> Example
>
> Rob  3-448min   $6.56
> Min   2-323min   $2.34
> Min   3-454min   $7.67
>
> And so on..Would appreciate help on this.since I cant come up with the

> query
>
How do you intend to find out which entry in your table "salary" is
related
to which employee as there is no id to refer to.
Am I right in assuming your column "hour" with entries 3-4, 2-3, ...
means from 3 til 4 o'clock and so on?

Regards, Christoph


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

Re: [SQL] Question II

[Christoph Haller]

>
> I would like to get all field name of a table within a stored
procedure
> using pgsql. How to do it??
>
SELECT a.attname
FROM pg_class c, pg_attribute a
WHERE c.relname = ''
  AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum ;
gives you the field names of .

If you start a psql session with the -E option, you can see how
\d  is sql-generated.

If you are asking for support how to write this pgsql function
(table name parameter, query, etc.), then refer to the documentation
or send another request.

Regards, Christoph


---(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] Adding foreign key constraint post table creation

[Richard Huxton]

On Monday 09 Dec 2002 4:11 pm, Charles Hauser wrote:
> All,
>
> A couple of novice questions:
>
>
> I would like to modify an existing TABLE by addinga new column (FOREIGN
> KEY):
>
>type_id int not null,
>foreign key (type_id) references cvterm (cvterm_id),
>
>
> Will this work ( running PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled
> by GCC 2.96):

You'll need to check your manuals for 7.1.3 - look in the SQL command 
reference under ALTER TABLE. It should work with current versions but I don't 
have v7.1.3 to hand.

> I would like to load data into the table below from a file lacking the
> timestamp fields, where the file structure is:
>
> COPY table FROM STDIN;
> 1 feature_typetypes of features   \N
> 2 3'-exon \N  1
>
> This fails as the timestamp fields are 'not null'.  Othere than
> generating INSERT stmts for the data how else could I enter the data?

You could process the file with perl/awk etc. and add the required timestamps. 
I don't think COPY substitutes default values for you.

-- 
  Richard Huxton

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Problem with timestamp

[Beatrice Yueksel]

Hello,

I try to check the time needed by a function.
I would like to :
. select current_timestamp,
. execute 5000 times a function
. select the current_timestamp and return the interval.
Problem : the interval is always '00:00', the two timestamps have always 
the same value.
Could you help me ? have you any suggestions for testing sql functions 
speed?
Thank you in advance,
Béatrice


create function test_function() returns interval
as '
declare
   j integer;
   t1 timestamp;
   t2 timestamp;
   t3 interval;
   x integer;
begin
   select into t1 now();
   FOR j in 0..5000 loop
   select into x get_function_to_test();
   end loop;
   select into t2 now();
   t3 := t2 - t1;
   RAISE NOTICE '' from % to % = %'',t2,t1,t3;
   return t3;

end;
' language 'plpgsql';


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

Re: [SQL] Problem with timestamp

[Henshall, Stuart - Design & Print]

Title: RE: [SQL] Problem with timestamp 





Beatrice Yueksel wrote:
> Hello,
> 
> I try to check the time needed by a function.
> I would like to :
>  . select current_timestamp,
>  . execute 5000 times a function
>  . select the current_timestamp and return the interval.
> Problem : the interval is always '00:00', the two timestamps have
> always the same value.
> Could you help me ? have you any suggestions for testing sql functions
> speed?
> Thank you in advance,
> Béatrice
> 
> 
> create function test_function() returns interval
> as '
> declare
> j integer;
> t1 timestamp;
> t2 timestamp;
> t3 interval;
> x integer;
> begin
> select into t1 now();
> FOR j in 0..5000 loop
> select into x get_function_to_test();
> end loop;
> select into t2 now();
> t3 := t2 - t1;
> RAISE NOTICE '' from % to % = %'',t2,t1,t3;
> return t3;
> 
> end;
> ' language 'plpgsql';
> 
use timeofday(); as the others have the same value throughout the transaction
- Stuart

[SQL] Backup to data base how ?

[ksql]

This is vital for my, please send me an example
 about like I can make me to support my database with name WAREHOUSE
-- 
Saludos de Luis,
Mi correo:   mailto:[EMAIL PROTECTED]

_
Do You Yahoo!?
La emoción e intensidad del deporte en Yahoo! Deportes. http://deportes.yahoo.com.mx

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] Backup to data base how ?

[Frank Bax]

At 10:36 PM 12/11/02, ksql wrote:

This is vital for my, please send me an example
 about like I can make me to support my database with name WAREHOUSE



pg_dump -o -c warehouse  |  gzip  >  warehouse.gz

http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/backup.html

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] union query doubt:

[javier garcia]

Hi;
I've got a table with three fields: DATE, POINT, FLOW. The POINT field can 
have values among 1 and 6. So, for a same date I have six different points 
with the correspondings flows.
I would like to make a query to obtain something like:
DATE POINT1 POINT2 POINT3 POINT4 POINT5 POINT6

where for a date I have the flows data of the different points.

I think that I would need to establish different alias for the same field. Is 
this possible?

Perhaps something like:

SELECT date, flow AS POINT1 FROM samples WHERE POINT=1 
UNION ALL
SELECT date, flow AS POINT2 FROM samples WHERE POINT=2
UNION ALL
SELECT date, flow AS POINT3 FROM samples WHERE POINT=3
...;

and a SELECT over the result of this subselect, that groups by date, or so?

Thanks for you help.

Javier

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

Re: [SQL] union query doubt:

[Frank Bax]

At 11:21 AM 12/11/02, javier garcia wrote:

I've got a table with three fields: DATE, POINT, FLOW. The POINT field can
have values among 1 and 6. So, for a same date I have six different points
with the correspondings flows.
I would like to make a query to obtain something like:
DATE POINT1 POINT2 POINT3 POINT4 POINT5 POINT6

where for a date I have the flows data of the different points.



SELECT date,
case when point = 1 then flow else 0 end as flow1,
case when point = 2 then flow else 0 end as flow2,
case when point = 3 then flow else 0 end as flow3,
case when point = 4 then flow else 0 end as flow4,
case when point = 5 then flow else 0 end as flow5,
case when point = 6 then flow else 0 end as flow6
from samples


There have been several messages recently about this - search on crosstab 
or pivot - a couple of other options were presented.

Frank

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

Re: [SQL] union query doubt:

[Jean-Luc Lachance]

I think you meant:

SELECT date,
sum( case when point = 1 then flow else 0 end) as flow1,
sum( case when point = 2 then flow else 0 end) as flow2,
sum( case when point = 3 then flow else 0 end) as flow3,
sum( case when point = 4 then flow else 0 end) as flow4,
sum( case when point = 5 then flow else 0 end) as flow5,
sum( case when point = 6 then flow else 0 end) as flow6
from samples group by date;



Frank Bax wrote:
> 
> At 11:21 AM 12/11/02, javier garcia wrote:
> >I've got a table with three fields: DATE, POINT, FLOW. The POINT field can
> >have values among 1 and 6. So, for a same date I have six different points
> >with the correspondings flows.
> >I would like to make a query to obtain something like:
> >DATE POINT1 POINT2 POINT3 POINT4 POINT5 POINT6
> >
> >where for a date I have the flows data of the different points.
> 
> SELECT date,
> case when point = 1 then flow else 0 end as flow1,
> case when point = 2 then flow else 0 end as flow2,
> case when point = 3 then flow else 0 end as flow3,
> case when point = 4 then flow else 0 end as flow4,
> case when point = 5 then flow else 0 end as flow5,
> case when point = 6 then flow else 0 end as flow6
> from samples
> 
> There have been several messages recently about this - search on crosstab
> or pivot - a couple of other options were presented.
> 
> Frank
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [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

[SQL] trigger to maintain relationships

[David M]

I am maintaining a set of hierarchical data that looks a lot like a
tree.  (And my SQL is very rusty.  And I'm new to postgres.)

Questions:
-
1.)  Is the following a reasonable solution?  Is there a
postgres-specific way to handle this better?  Is there a good generic
SQL way to handle this?
2.)  Can I write pure "SQL" triggers to handle this?  Am I getting close
in my first cut (below)?
3.)  Any other ideas/suggestions?


I have one table with essentially the nodes of a tree:

nodes
--
node_id integer
parent_id   integer references nodes(node_id)
...and other descriptive columns...

I want an easy way to find all the elements of a subtree.  Not being
able to think of a good declarative solution, I was thinking about
cheating and maintaining an ancestors table:

ancestors
---
node_idinteger
ancestor_id   integer references nodes(node_id)

I figured I could populate the ancestors table via trigger(s) on the
nodes table.  Then I should be able to find a whole subtree of node X
with something like:

select *
from nodes
where node_id in (
select node_id
from ancestors
where ancestor_id = X)

Here's my best guess so far at the triggers (but, obviously, no luck so
far):

--insert trigger
create function pr_tr_i_nodes() returns opaque
as '
insert into ancestors
select NEW.node_id, ancestor_id
from ancestors
where node_id = NEW.parent_id;'
language sql;
create trigger tr_i_nodes after insert
on nodes for each row
execute procedure pr_tr_i_nodes();

--delete trigger
create function pr_tr_d_nodes() returns opaque
as '
delete from ancestors
where node_id = OLD.parent_id;'
language sql;
create trigger tr_d_nodes after insert
on nodes for each row
execute procedure pr_tr_d_nodes();

--update trigger
create function pr_tr_u_nodes() returns opaque
as '
delete from ancestors
where node_id = OLD.parent_id;

insert into ancestors
select NEW.node_id, ancestor_id
from ancestors
where node_id = NEW.parent_id;'
language sql;
create trigger tr_u_nodes after insert
on nodes for each row
execute procedure pr_tr_u_nodes();

I realize the update trigger could be handled a multitude of ways and
that my first guess may be pretty lousy.  But I figured the
insert/update triggers would be pretty straightforward.  Am I missing
something basic?  I also tried things like (following the one example in
the reference manual):

--insert trigger
create function pr_tr_i_nodes() returns opaque
as '
insert into ancestors
select NEW.node_id, ancestor_id
from ancestors
where node_id = NEW.parent_id;

return NEW;'
language 'plpgsql';
create trigger tr_i_nodes after insert
on nodes for each row
execute procedure pr_tr_i_nodes();



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

Re: [SQL] trigger to maintain relationships

[David M]

I think I figured out my join syntax error (sorry for confusing the issue
with noise like that).  I'd still be interested in general comments on
design.

FYI, join should've looked like:

create function pr_tr_i_nodes() returns opaque
as '
insert into ancestors
select NEW.node_id, ancestor_id
from NEW left outer join ancestors on (NEW.parent_id =
ancestors.node_id);

return NEW;'
language 'plpgsql';
create trigger tr_i_nodes after insert
on nodes for each row
execute procedure pr_tr_i_nodes();




David M wrote:

> I am maintaining a set of hierarchical data that looks a lot like a
> tree.  (And my SQL is very rusty.  And I'm new to postgres.)
>
> Questions:
> -
> 1.)  Is the following a reasonable solution?  Is there a
> postgres-specific way to handle this better?  Is there a good generic
> SQL way to handle this?
> 2.)  Can I write pure "SQL" triggers to handle this?  Am I getting close
> in my first cut (below)?
> 3.)  Any other ideas/suggestions?
>
> I have one table with essentially the nodes of a tree:
>
> nodes
> --
> node_id integer
> parent_id   integer references nodes(node_id)
> ...and other descriptive columns...
>
> I want an easy way to find all the elements of a subtree.  Not being
> able to think of a good declarative solution, I was thinking about
> cheating and maintaining an ancestors table:
>
> ancestors
> ---
> node_idinteger
> ancestor_id   integer references nodes(node_id)
>
> I figured I could populate the ancestors table via trigger(s) on the
> nodes table.  Then I should be able to find a whole subtree of node X
> with something like:
>
> select *
> from nodes
> where node_id in (
> select node_id
> from ancestors
> where ancestor_id = X)
>
> Here's my best guess so far at the triggers (but, obviously, no luck so
> far):
>
> --insert trigger
> create function pr_tr_i_nodes() returns opaque
> as '
> insert into ancestors
> select NEW.node_id, ancestor_id
> from ancestors
> where node_id = NEW.parent_id;'
> language sql;
> create trigger tr_i_nodes after insert
> on nodes for each row
> execute procedure pr_tr_i_nodes();
>
> --delete trigger
> create function pr_tr_d_nodes() returns opaque
> as '
> delete from ancestors
> where node_id = OLD.parent_id;'
> language sql;
> create trigger tr_d_nodes after insert
> on nodes for each row
> execute procedure pr_tr_d_nodes();
>
> --update trigger
> create function pr_tr_u_nodes() returns opaque
> as '
> delete from ancestors
> where node_id = OLD.parent_id;
>
> insert into ancestors
> select NEW.node_id, ancestor_id
> from ancestors
> where node_id = NEW.parent_id;'
> language sql;
> create trigger tr_u_nodes after insert
> on nodes for each row
> execute procedure pr_tr_u_nodes();
>
> I realize the update trigger could be handled a multitude of ways and
> that my first guess may be pretty lousy.  But I figured the
> insert/update triggers would be pretty straightforward.  Am I missing
> something basic?  I also tried things like (following the one example in
> the reference manual):
>
> --insert trigger
> create function pr_tr_i_nodes() returns opaque
> as '
> insert into ancestors
> select NEW.node_id, ancestor_id
> from ancestors
> where node_id = NEW.parent_id;
>
> return NEW;'
> language 'plpgsql';
> create trigger tr_i_nodes after insert
> on nodes for each row
> execute procedure pr_tr_i_nodes();
>
> ---(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


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

[SQL] error in copy table from file

[[EMAIL PROTECTED]]

hi
i'm using postgreSQL 7.3 b1.
when i try to populate my tables with the files that contain all the 
data, i have some troblues:
for example, my table has the following fields:
-date (date)
-id_street (int2)
-flux_h_0_1 (float8)
-flux_h_1_2 (float8)
.
.
.
-flux_h_23_0 (float8)

the input file has semicolon as delimiters
and it't like this
2000-01-01;25;325.236;-0.123; and so on ;1.2
2000-01-01;26;323.45;-1.23; 1.66

if i execute the command

copy table from 'path/file' using delimiters ';'
it returns the following:

'RROR:  copy: line 1, Bad float8 input format '-0.123

i can't understand why it's saying it's wrong
or another error in importing other tables can be:

": can't parse "ne 1, pg_atoi: error in "125

and 125 is the last number of a row.

what can i do to solve this problem?
thanx in advance for your help

massimo



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Getting the latest unique items

[A.M.]

I have a table as follows:
CREATE TABLE student_gradedmaterial(
	id SERIAL,
	studentid INT8 REFERENCES student,
	gradedmaterialid INT8 REFERENCES gradedmaterial,
	caid INT8 REFERENCES ca,
	...
	submittime TIMESTAMP,
   	gradedtime TIMESTAMP,
	score INT4
);

Every time a student submits a homework, one new entry in the table is 
created. I know how to grab the latest version based on the submittime 
but naturally, I'd like to be able to count how many homeworks are 
graded and ungraded (ungraded means score is NULL). This smells of a 
subselect:

graded (grab row count):
SELECT UNIQUE id FROM student_gradedmaterial WHERE EXISTS (SELECT the 
latest unique submissions);
or:
SELECT COUNT(score) FROM student_gradedmaterial WHERE 
gradedmaterialid=X AND submittime = MAX(SELECT submittime FROM 
student_gradedmaterial WHERE gradedmaterialid=X);

(Sub-selects just make my head explode.) Any hints for me? Thanks.
><><><><><><><><><
AgentM
[EMAIL PROTECTED]



---(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] error in copy table from file

[Stephan Szabo]

On Wed, 11 Dec 2002, [iso-8859-1] [EMAIL PROTECTED] wrote:

> 'RROR:  copy: line 1, Bad float8 input format '-0.123

Looks like you have end of line issues (given the fact that the
second quote is at the beginning of the line.  You
probably have carriage returns at the end of lines.
If you get rid of those, it probably will work.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] Getting the latest unique items

[Tomasz Myrta]

I'm not sure if I understood your problem,
but did you try with "distinct on"?
select distinct on (id)
from
...
order by submittime desc

Regards,
Tomasz Myrta


A.M. wrote:

> I have a table as follows:
> CREATE TABLE student_gradedmaterial(
> id SERIAL,
> studentid INT8 REFERENCES student,
> gradedmaterialid INT8 REFERENCES gradedmaterial,
> caid INT8 REFERENCES ca,
> ...
> submittime TIMESTAMP,
>gradedtime TIMESTAMP,
> score INT4
> );
>
> Every time a student submits a homework, one new entry in the table is
> created. I know how to grab the latest version based on the submittime
> but naturally, I'd like to be able to count how many homeworks are
> graded and ungraded (ungraded means score is NULL). This smells of a
> subselect:
>
> graded (grab row count):
> SELECT UNIQUE id FROM student_gradedmaterial WHERE EXISTS (SELECT the
> latest unique submissions);
> or:
> SELECT COUNT(score) FROM student_gradedmaterial WHERE gradedmaterialid=X
> AND submittime = MAX(SELECT submittime FROM student_gradedmaterial WHERE
> gradedmaterialid=X);
>
> (Sub-selects just make my head explode.) Any hints for me? Thanks.
>  ><><><><><><><><><
> AgentM
> [EMAIL PROTECTED]
>
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
>(send "unregister YourEmailAddressHere" to [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

Re: [SQL] error in copy table from file

[Manfred Koizar]

On Wed, 11 Dec 2002 18:40:48 +0100, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:
>copy table from 'path/file' using delimiters ';'
>
>it returns the following:
>
>'RROR:  copy: line 1, Bad float8 input format '-0.123
 ^
This belongs to the end of the error message.  Finding it here at the
beginning of the line makes me think your file has DOS style end of
lines (CR/LF).  Convert them to Unix style (LF only).

Servus
 Manfred

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] multi-user and multi-level database access

[Tomasz Myrta]

Hi
I want to protect my database against unauthorised data destruction 
(Postgresql 7.2) I have some applications accesing database, each one 
does other things and there is a lot of users using these applications.

Here is my solution:
- each application has one postgresql group (create group...)
- i have a lot of grants/revokes for these groups
- there is a table named "users" which contains logins, user names and 
other useful information.
- after inserting a row to table "users", inside plpgsql function
I "create user "
- for each application I "alter group application_group add user "

My questions are:
- how to check if some postgresql user exists? I found them in table 
pg_shadow, but selecting this table is legal?
- how to change postgresql user login? Do I have to drop/create user, or 
I can update table pg_shadow?
- is it possible to revoke some privileges to all tables without listing 
them? I want to revoke all trigger/references/rule to all users without 
database owner.

And one additional question - not exactly to this list:
- did anyone compile libpq under MsWindows with ssl?

Regards,
Tomasz Myrta


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] trigger to maintain relationships

[Josh Berkus]

David,

> FYI, join should've looked like:
> 
> create function pr_tr_i_nodes() returns opaque
> as '
> insert into ancestors
> select NEW.node_id, ancestor_id
> from NEW left outer join ancestors on (NEW.parent_id =
> ancestors.node_id);
> 
> return NEW;'
> language 'plpgsql';
> create trigger tr_i_nodes after insert
> on nodes for each row
> execute procedure pr_tr_i_nodes();

Ummm ... no.

Within the trigger produre, NEW is a record variable, and its fields
are values.  You cannot SELECT from NEW.  You're also missing the parts
of a PLPGSQL procedure.  What you want is:

create function pr_tr_i_nodes() returns opaque
> as '
DECLARE v_ancestor INT;
BEGIN
SELECT ancestor_id INTO v_ancestor
FROM ancestors WHERE ancestors.node_id = NEW.parent_id;
INSERT INTO ancestors
VALUES ( NEW.node_id, v_ancestor );
> return NEW;
END;'
> language 'plpgsql';

-Josh Berkus

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

[SQL] convert NULL into a value

[Jonathan Man]

Hi,
 
There is a function on the Oracle. That 
is NVL(field, 0) to convert null into a value (e.g.  
ZERO). 
 
Can I use this function on the 
PostgreSQL??
 
 
Thank you for your help!!
 
Regards,
 
JONATHAN MAN

Re: [SQL] convert NULL into a value

[Ludwig Lim]

--- Jonathan Man <[EMAIL PROTECTED]>
wrote:
> Hi,
> 
> There is a function on the Oracle. That is
> NVL(field, 0) to convert null into a value (e.g. 
> ZERO). 
> 
> Can I use this function on the PostgreSQL??
> 

 -- The equivalent function is PostgreSQL is 
SELECT COALESCE(field,0)


regards,

ludwig.

__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] convert NULL into a value

[Bruce Momjian]

No, the SQL standard command is COALESCE.

---

Jonathan Man wrote:
> Hi,
> 
> There is a function on the Oracle. That is NVL(field, 0) to convert null into a 
>value (e.g.  ZERO). 
> 
> Can I use this function on the PostgreSQL??
> 
> 
> Thank you for your help!!
> 
> Regards,
> 
> JONATHAN MAN

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [SQL] Getting the latest unique items

[A.M.]

When I try to run the following query:

select distinct on(student_gradedmaterial.id) student_gradedmaterial.id 
from coursesection_student,student_gradedmaterial WHERE 
gradedmaterialid=1 AND 
coursesection_student.studentid=student_gradedmaterial.studentid AND 
coursesectionid=1 and score is not null order by submittime desc;

I get the following error:

ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY 
expressions

Should I use GROUP BY somehow?
SELECT DISTINCT ON (student_gradedmaterial.id) 
student_gradedmaterial.id from 
coursesection_student,student_gradedmaterial where gradedmaterialid=1 
and coursesection_student.studentid=student_gradedmaterial.studentid 
and coursectionid=1 and score is not null having max(big subselect of 
max times);

?

The relevant schema follows:
CREATE TABLE coursesection_student (
   coursesectionid INT8 REFERENCES coursesection,
   studentid INT8 REFERENCES student,
   status INT4 DEFAULT 0 NOT NULL, --2 switched, 1 dropped, 0 
enrolled
   touch BOOL DEFAULT 'true',
   UNIQUE(coursesectionid,studentid)
);
CREATE TABLE coursesection_ca(
   coursesectionid INT8 REFERENCES coursesection,
   caid INT8 REFERENCES ca
);
CREATE TABLE gradedmaterial (
   id SERIAL PRIMARY KEY,
   name TEXT,
   visible BOOLEAN DEFAULT 'f',
   openforsubmission BOOLEAN DEFAULT 'f',
   description TEXT,
   webpage TEXT,
   predefcomments TEXT,
   weight INT4,
   restrictedfiletypes TEXT,
   duetime TIMESTAMP
);

CREATE TABLE coursesection_gradedmaterial(
   gradedmaterialid INT8 REFERENCES gradedmaterial,
   coursesectionid INT8 REFERENCES coursesection
);

CREATE TABLE student_gradedmaterial(
   id SERIAL,
   studentid INT8 REFERENCES student,
   gradedmaterialid INT8 REFERENCES gradedmaterial,
   caid INT8 REFERENCES ca,
   score INT4,
   comments TEXT,
   submittime TIMESTAMP,
   gradedtime TIMESTAMP,
   file OID,
   emailtostudent BOOLEAN DEFAULT 'f',
   suffix VARCHAR(6) DEFAULT '.zip'
);
On Wednesday, December 11, 2002, at 01:36  PM, Tomasz Myrta wrote:

I'm not sure if I understood your problem,
but did you try with "distinct on"?
select distinct on (id)
from
...
order by submittime desc

Regards,
Tomasz Myrta


A.M. wrote:

> I have a table as follows:
> CREATE TABLE student_gradedmaterial(
> id SERIAL,
> studentid INT8 REFERENCES student,
> gradedmaterialid INT8 REFERENCES gradedmaterial,
> caid INT8 REFERENCES ca,
> ...
> submittime TIMESTAMP,
>gradedtime TIMESTAMP,
> score INT4
> );
>
> Every time a student submits a homework, one new entry in the table 
is
> created. I know how to grab the latest version based on the 
submittime
> but naturally, I'd like to be able to count how many homeworks are
> graded and ungraded (ungraded means score is NULL). This smells of a
> subselect:
>
> graded (grab row count):
> SELECT UNIQUE id FROM student_gradedmaterial WHERE EXISTS (SELECT the
> latest unique submissions);
> or:
> SELECT COUNT(score) FROM student_gradedmaterial WHERE 
gradedmaterialid=X
> AND submittime = MAX(SELECT submittime FROM student_gradedmaterial 
WHERE
> gradedmaterialid=X);
>
> (Sub-selects just make my head explode.) Any hints for me? Thanks.
>  ><><><><><><><><><
> AgentM
> [EMAIL PROTECTED]
>
>
>
> ---(end of 
broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
>(send "unregister YourEmailAddressHere" to 
[EMAIL PROTECTED])
>





><><><><><><><><><
AgentM
[EMAIL PROTECTED]




---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] Primary Key Help !

[Waheed Rahuman]

Hi Greetings
Friends please help me in setting the primary 
key
in a table how many primary key i can 
create.
In MS access its only 10 column can be made into 
primary key !.
My question is How many primary key i can assign in 
a PostGresql Table
 
Rowid    
|  Parent1   
|  Parent2   
|  Parent3   
|  Parent4   
|  Parent5   
|  Parent6   
|  Parent7   
|  Parent8   
|  Parent9   
|  Parent10  
|
Here i want to make Parent1.Parent 10 
as Primary Key  and ...this parent field i want to make it more than 
10 fieldsif i try to make primary key more than 10 fields in ms access its 
say not possible so please let me know how i can do that in psql. or if there is 
an alternative way...i will be glad
Thank you for your reply
Regards
Waheed
 
 
 
 

Re: [SQL] Getting the latest unique items

[Tom Lane]

"A.M." <[EMAIL PROTECTED]> writes:
> When I try to run the following query:
> select distinct on(student_gradedmaterial.id) ...
 ^^
> ... order by submittime desc;
  ^^^

> I get the following error:

> ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY 
> expressions

Indeed.  You might benefit from reading the DISTINCT ON usage example
given in the SELECT reference page,
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/sql-select.html
(see under "Description").  Basically, DISTINCT ON is *only* useful when
designed hand-in-hand with an ORDER BY ...

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html