[GENERAL] Negating the list of selected rows of a join
Hello, I want to list the rows of a table with a text field whose values do not exist in a similar field of another table. Basically what I want to get is negated results of a join. Lets say the tables table_a and table_b have the field name. table_a table_b name age name - --- - Peter 27Paul Paul 42 Mary 20 If I asked for a join like this: SELECT table_a.name,table_a.age FROM table_a,table_b WHERE table_a.name=table_b.name I would get: name age - --- Paul 42 But I want the opposite. I tried a non-equi join like this: SELECT table_a.name,table_a.age FROM table_a,table_b WHERE table_a.nametable_b.name and I got: name age - --- Peter 27 Mary 20 It worked except for the case when table_b is empty. In this case the nothing was returned. Is this the expected behaviour or is it a bug in PostgreSQL? How can I make a query that works the way I want all the time, even for the case when table_b is empty? Regards, Manuel Lemos E-mail: [EMAIL PROTECTED] URL: http://www.e-na.net/the_author.html PGP key: finger:[EMAIL PROTECTED] --
Re: [GENERAL] PostgreSQL EndTransactionBlock and not inprogress/abort state
Manuel Lemos wrote: Hello, I am trying to use transactions with PostgreSQL with the normal sequence BEGIN one or more SELECT/INSERT/UPDATE/DELETE queries COMMIT END But I am getting the following message just like if I haven't started a transaction when I commit. EndTransactionBlock and not inprogress/abort state I could be wrong, but the END isn't necessary. BEGIN COMMIT; or BEGIN ROLLBACK; Hope this helps, Clark
Re: [GENERAL] PostgreSQL EndTransactionBlock and not inprogress/abort state
Clark Evans wrote: Manuel Lemos wrote: Hello, I am trying to use transactions with PostgreSQL with the normal sequence BEGIN one or more SELECT/INSERT/UPDATE/DELETE queries COMMIT END But I am getting the following message just like if I haven't started a transaction when I commit. EndTransactionBlock and not inprogress/abort state Oops! forgot the semicolon. *whap* BEGIN; COMMIT; or BEGIN; ROLLBACK; BTW, are you putting on the "END" beacuse you are in a PL/pgsql block? If so, I'm not sure if transactions can work inside this language. Hmm. Clark
Re: [GENERAL] Negating the list of selected rows of a join
Manuel Lemos [EMAIL PROTECTED] wrote:
I want to list the rows of a table with a text field whose values do not
exist in a similar field of another table. Basically what I want to get
is negated results of a join. [...]
It worked except for the case when table_b is empty. In this case the
nothing was returned. Is this the expected behaviour or is it a bug in
PostgreSQL?
If you list two (or more) tables in the 'from' clause of a select
(that is, if you do a 'join'), a result table is built, in which each
row of the first table is combined with each row from (all) the other
table(s). To clarify, do simply
SELECT table_a.name,table_b.name FROM table_a,table_b;
on your table. When one of the tables has no rows, all the rows from
the other(s) are combined with *nothing*; this gives nothing!
('combined' may be the wrong word; it's like a multiplication, and
people speak of a 'Cartesian product' of the tables)
The 'where' clause can restrict the rows of the result table to
something useful, e.g., you can restrict to 'table_a.name =
table_b.name'. A feature that probably will help you is the
construction of a so-called 'sub-select' in the where clause:
SELECT name FROM table_a
WHERE name NOT IN (SELECT name FROM table_b);
Hope it helps!
Ulf
--
==
Ulf Mehlig[EMAIL PROTECTED]
Center for Tropical Marine Ecology/ZMT, Bremen, Germany
--
Re: [GENERAL] Negating the list of selected rows of a join
Clark Evans [EMAIL PROTECTED]: Manuel Lemos wrote: How can I make a query that works the way I want all the time, even for the case when table_b is empty? SELECT table_a.name, table_a.age FROM table_a WHERE NOT EXISTS ( SELECT 'x' FROM table_b WHERE table_b.name = table_a.name ); Hope this will do the trick. Maybe not -- doesn't that mean, that the query won't return a single row in case there is *any* pair of equal names in both tables?! Have a nice (sun)day, Ulf -- == Ulf Mehlig[EMAIL PROTECTED] Center for Tropical Marine Ecology/ZMT, Bremen, Germany --
Re: [GENERAL] Negating the list of selected rows of a join
Manuel Lemos wrote: I want to list the rows of a table with a text field whose values do not exist in a similar field of another table. Basically what I want to get is negated results of a join. Ulf Mehlig wrote: SELECT name FROM table_a WHERE name NOT IN (SELECT name FROM table_b); Clark Evans wrote: SELECT table_a.name, table_a.age FROM table_a WHERE NOT EXISTS ( SELECT 'x' FROM table_b WHERE table_b.name = table_a.name ); I'm not sure about how well PostgreSQL handles these two. I'd try them both with your data set. If table_b is small (less than a few thousand rows) then Ulf's approach would work best. However, if table_b is large (more than a thousand) then I think the other approach may work better if table_b.name is indexed. Clark
Re: [GENERAL] Negating the list of selected rows of a join
Clark Evans [EMAIL PROTECTED]/Ulf Mehlig [EMAIL PROTECTED] wrote: Hope this will do the trick. Maybe not -- doesn't that mean, that the query won't return a single row in case there is *any* pair of equal names in both tables?! No. It's a correlated sub-query. It's actually much more efficient with large tables than the other approach (which has to put the entire result set of table_b on the heap before it can process table_a). Your approach is, however, much better (by a large factor) if table_a is very large and table_b is very small, since you don't have to hit the index for table_b on every row of table_a... Yes, it *will* do the trick!! Sorry, Clark, I misinterpreted your sub-query ... have to read more carefully ... Thanks for your correction! Ulf -- == Ulf Mehlig[EMAIL PROTECTED] Center for Tropical Marine Ecology/ZMT, Bremen, Germany --
[GENERAL] Error building pg 6.4 on RH Linux
LS, When I try to build PG 6.4 on Red Hat Linux 5.2 gmake cant't find the file y.tab.h in the backand/bootstrap directory. I seems that this file should be created during the build process. What can i do to get this file. Henk Jan
Re: [GENERAL] daily check for expired data ?
At 22:48 +0200 on 11/3/99, Ralf Weidemann wrote: how could I do an automatic daily check to delete some expired data ? I mean can I have a cron functionality in post- gresql ? You don't need to have cron functionality in postgresql when you have cron functionality in cron. :) What you have to do is make a simple script, more or less like this: #!/usr/bin/sh PGHOST=... PGPORT=... PGUSER=... psql my_database END_SQL DELETE FROM table_in_question WHERE datetime_field ( 'now'::datetime - '1 day'::timespan ); END_SQL And then run it with cron... Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
Re: [GENERAL] Foreign Keys: check_primary_function
Applied.
Hi All,
I've changed the check_primary_key() function code to allow for either
the "automatic insert key rule" or "dependent insert key rule".
Previously it restricted the addtion of a child entry if the
corresponding parent entry was not there. Now if the option is
"automatic" it will add an entry in the parent too ( it will be
successful if there are no no-null fields in the parent apart from the
primary key).
The way to use it now is:
/*
* check_primary_key () -- check that key in tuple being
inserted/updated
* references existing tuple in "primary" table.
* Though it's called without args You have to specify referenced
* table/keys while creating trigger: key field names in triggered
table,
* referenced table name, referenced key field names,type of action
[automatic|dependent]:
* EXECUTE PROCEDURE
* check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2',
'[automatic|dependent]').
*/
I am attaching the new ../contrib/spi/refint.c file which will do this.
I will be glad to help in case of any problems.
- Anand.
/*
* refint.c --set of functions to define referential integrity
*constraints using general triggers.
*/
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* -"- and triggers */
#include ctype.h/* tolower () */
HeapTuple check_primary_key(void);
HeapTuple check_foreign_key(void);
typedef struct
{
char *ident;
int nplans;
void **splan;
} EPlan;
static EPlan *FPlans = NULL;
static intnFPlans = 0;
static EPlan *PPlans = NULL;
static intnPPlans = 0;
static EPlan *find_plan(char *ident, EPlan ** eplan, int *nplans);
/*
* check_primary_key () -- check that key in tuple being inserted/updated
* references existing tuple in "primary" table.
* Though it's called without args You have to specify referenced
* table/keys while creating trigger: key field names in triggered table,
* referenced table name, referenced key field names,type of action
[automatic|dependent]:
* EXECUTE PROCEDURE
* check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1',
'Pkey2','[automatic|dependent]').
*/
HeapTuple /* have to return HeapTuple to
Executor */
check_primary_key()
{
Trigger*trigger;/* to get trigger name */
int nargs; /* # of args specified in
CREATE TRIGGER */
char **args; /* arguments: column names and table
name */
int nkeys; /* # of key columns (=
(nargs-1) / 2) */
Datum *kvals; /* key values */
char *relname;/* referenced relation name */
char *action; /* action on insert or update*/
Relationrel;/* triggered relation */
HeapTuple tuple = NULL; /* tuple to return */
TupleDesc tupdesc;/* tuple description */
EPlan *plan; /* prepared plan */
Oid*argtypes = NULL;/* key types to prepare execution plan */
boolisnull; /* to know is some column NULL or not
*/
charident[2 * NAMEDATALEN]; /* to identify myself */
int ret;
int i;
/*
* Some checks first...
*/
/* Called by trigger manager ? */
if (!CurrentTriggerData)
elog(ERROR, "check_primary_key: triggers are not initialized");
/* Should be called for ROW trigger */
if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData-tg_event))
elog(ERROR, "check_primary_key: can't process STATEMENT events");
/* If INSERTion then must check Tuple to being inserted */
if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData-tg_event))
tuple = CurrentTriggerData-tg_trigtuple;
/* Not should be called for DELETE */
else if (TRIGGER_FIRED_BY_DELETE(CurrentTriggerData-tg_event))
elog(ERROR, "check_primary_key: can't process DELETE events");
/* If UPDATion the must check new Tuple, not old one */
else
tuple = CurrentTriggerData-tg_newtuple;
trigger = CurrentTriggerData-tg_trigger;
nargs = trigger-tgnargs;
args = trigger-tgargs;
if ((nargs-1) % 2 != 1) /* odd number of arguments! */
elog(ERROR, "check_primary_key: even number of arguments should be
specified");
nkeys = (nargs-1) / 2;
action=args[nargs -1];
if (strcmp(action,"automatic") strcmp(action,"dependent"))
[GENERAL] The value returned by autoinc ?
Hi! When I insert data into a table with a sequence associated to a column (and the required trigger), how can I know the value the sequence has just generated ? (think in parallel accesses) Would this approach be the answer ? begin work insert... select max... commit , ` Silvio Emanuel Nunes Barbosa de Macedo mailto:[EMAIL PROTECTED] INESC - Porto - Grupo CAV Pc da Republica, 93 R/C Tel:351 2 209 42 21 4000 PORTO PORTUGAL Fax:351 2 208 41 72
