Re: [SQL] COUNT
> "Brian" == Brian C Doyle <[EMAIL PROTECTED]> writes: Brian> Hello, You will need to do "SELECT count(attribute) FROM Brian> table;" or SELECT count(table.attribute);" You need to watch this: acspg=# create table tst ( acspg(# a integer acspg(# ); CREATE acspg=# insert into tst values (0); INSERT 333481 1 acspg=# insert into tst values (null); INSERT 333482 1 acspg=# insert into tst values (2); INSERT 333483 1 acspg=# select count(*) from tst; count --- 3 (1 row) acspg=# select count(a) from tst; count --- 2 (1 row) acspg=# select count(1) from tst; count --- 3 (1 row) acspg=# If you use the attribut name, null values won't be counted. -Dan
Re: [SQL] now() with microsecond granulity needed
> "Radoslaw" == Radoslaw Stachowiak <[EMAIL PROTECTED]> writes: Radoslaw> *** Tom Lane <[EMAIL PROTECTED]> [Tuesday, Radoslaw> 20.February.2001, 11:57 -0500]: >> > using now() to init TIMESTAMP fields I got resolution of one >> second. How > can I define DEFAULT in column (TIMESTAMP type) >> to get higher > time-resolution (TIMESTAMP supports >> microseconds). >> >> You could make a variant of now() that relies on gettimeofday() >> instead of time(). Note that you probably won't get >> microsecond precision in any case... Radoslaw> Do You mean changing sources and recompiling? So there Radoslaw> is no way of getting more accurate NOW time directly in Radoslaw> SQL ? /DEFAULT clause/ at this moment? the timeofday function seems to work: acspg=# select timeofday(); timeofday - Wed Feb 21 15:56:43.150389 2001 EST (1 row) acspg=#
[SQL] sum(bool)?
> "Olaf" == Olaf Zanger <[EMAIL PROTECTED]> writes: Olaf> hi there i'd like to add up the "true" values of a Olaf> comparison like Olaf> sum(a>b) Olaf> it just doesn't work like this Olaf> any workaround Try using a case statement: select sum(case when a > b then 1 else 0 end) -Dan
[SQL] Help with 'now', now(), timestamp 'now', ...
> "edipoelder" == edipoelder <[EMAIL PROTECTED]> writes:
edipoelder> times. Then I changed the function
and run, at id =
edipoelder> 14. Change again at id = 15.
Where is underlined
edipoelder> (), i tried to put, 'now', timestamp 'now', etc,
edipoelder> and always get the same time. What i'm doing wrong?
edipoelder>
obs.: -> TABLE TEST (ID SERIAL, ANUMBER INTEGER)
-> TABLE TIMES (ID SERIAL, START TIME, END TIME);
PostgreSQL
-> 7.0.2 under Conectiva Linux
now() returns the time at the start of a transaction. Since pl/psql
functions are implicit transactions, now() will not change inside a
function call.
I've used something like the following:
create function bm(integer) returns text as '
declare
cnt alias for $1;
startt text;
endt text;
begin
startt := timeofday();
for i in 1..cnt LOOP
-- insert statement you want to time here
end loop;
endt := timeofday();
return delta_time_msecs(startt,endt);
end;' language 'plpgsql';
create function delta_time_msecs(text,text) returns float8 as '
declare
startt alias for $1;
endtalias for $2;
spaninterval;
daysfloat8;
hours float8;
minutes float8;
msecondsfloat8;
begin
span := endt::timestamp - startt::timestamp;
mseconds := date_part(''milliseconds'',span)::float8;
minutes := date_part(''minutes'',span)::float8;
hours:= date_part(''hours'',span)::float8;
days := date_part(''days'',span)::float8;
return abs(mseconds + minutes*60.0*1000.0 + hours*3600.0*1000.0 +
days*24.0*3600.0*1000.0);
end;' language 'plpgsql';
select bm(1000)::float8/1000.0;
This will give you the average time, averaged over a thousand queries.
-Dan
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[SQL] Recursive select
> "Martin" == Martin Smetak <[EMAIL PROTECTED]> writes: Martin> Hi all! Anyone know if it's possible to make a recursive Martin> select from a table ? My problem: I got a table of "some Martin> categories" which all points to its parrent Martin> one(tree)...shown below. And I want to select all names of Martin> parrent categories of one child, lets say "fast[4]". Now Martin> I'm solving that with many SQL queries like : "SELECT Martin> main_id FROM cat WHERE id=4;"but I would like to Martin> optimize this. Martin> Anyone can help or point me to a way ?? The Openacs project has implemented a couple of different methods for handling tree queries. Check out these two threads: http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=eC&topic_id=11&topic=OpenACS http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=j6&topic_id=12&topic=OpenACS%204%2e0%20Design Regards, Dan Wickstrom ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
