Re: [SQL] the best way to get some records not in another table

[Christoph Haller]

>
> Try to get some records not in another table. As the following, please

> advise which one will be the best way to do. Or is there any other way
to do
> better?
>
> SELECT DISTINCT a.c1
> FROM test_j2 a
> WHERE a.c1 NOT IN (SELECT DISTINCT b.c1 FROM test_j1 b);
>
> SELECT a.c1 FROM test_j2 a
> EXCEPT
> SELECT b.c1 FROM test_j1 b;
>
IN resp. NOT IN clauses are known to be slow.

SELECT DISTINCT a.c1
FROM test_j2 a
WHERE NOT EXISTS
(SELECT b.c1 FROM test_j1 b WHERE b.c1 = a.c1) ;

Can't tell if EXISTS performs better than EXCEPT,
have a look at the EXPLAIN output.

Regards, Christoph



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

[SQL] showing records from the last 20 min

[Matthew Nuzum]

I know this is such a simple question, but I can't find the answer in
the manual and I've tried very hard to find it.

I want to show all the records in a table that occurred in the last 20
min.

So, maybe something like:
select * from sys_logins WHERE tstamp >= now() - '00:20';

on pg 7.3.2 this produces the error:
ERROR:  Bad timestamp external representation '00:20'
 
Thanks for any help,
-- 
Matthew Nuzum
[EMAIL PROTECTED]
www.bearfruit.org


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

http://archives.postgresql.org

Re: [SQL] showing records from the last 20 min

[Tomasz Myrta]

Użytkownik Matthew Nuzum napisał:
I know this is such a simple question, but I can't find the answer in
the manual and I've tried very hard to find it.
I want to show all the records in a table that occurred in the last 20
min.
So, maybe something like:
select * from sys_logins WHERE tstamp >= now() - '00:20';
on pg 7.3.2 this produces the error:
ERROR:  Bad timestamp external representation '00:20'
 
Thanks for any help,
Did you mean:
select * from sys_logins
WHERE tstamp >= now()-cast('20:00' as interval);
?
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org

[SQL] Casting with character and character varying

[David Loh]

Hi all.
Recently I face some problem with casting character type variable and
varchar variable.
The situation was like: I had 2 table, on table A, the user_name is defined
as character(32), and table B uses varchar(32). I have 1 function and a
trigger to manipulate with these data.

Here's the function: (NEW = tableB)
--
create or replace function prepaid () returns trigger as '
  declare Rec tableA%ROWTYPE;

begin
  if NEW.status != 2 then
   return NEW;
  else
  select into Rec * from tableA where user_name = trim(trailing '' '' from
cast(NEW.user_name as varchar)) and user_type = ''T'';
   if not found then
return NEW;
   end if;

   insert into temptable values (tableA.FieldA);
  end if;
  return NEW;
end;
' language 'plpgsql';
-
supposingly the insert will insert the value of field A in table into
temptable (declare as varchar(100)), instead of inserting single row, the
insert actually insert all data from tableA to temptable (if there's 10 row
in tableA, the insert statement will insert all to temptable), that's weird.

Then i tried with cast(trim(trailing '' '' from NEW.user_name)::varchar as
text), and it's returns me with nothing (suppose there'll be 1 record
matched).

If any of you guys willing to help me out, I'll apprepriate it. Or you may
point me to some postgresql casting tutorial.

Thanks.


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