[SQL] Database Link

[Gerhard Dieringer]

A question for the postgreSQL gurus:

Some RDBMSs, for example Oracle, have a feature named 'Database Link' -
at least in Oracle - It's something like a view, thats lets you access a table, 
that is located within another DB.

AFAIK, postgreSQL doesn't have this feature. Are there any plans to implement
it in the (near) future?


Gerhard

Antw: [SQL] Join

[Gerhard Dieringer]

>>> Craig May <[EMAIL PROTECTED]> 08.09.2000  02.59 Uhr >>>
> Hi,

> I'm having trouble with this statement, could someone help out please.

> Select g.ID From Grouping g join Entity_Grouping eg on g.ID=eg.Group_ID where
> eg.Entity_ID=1

> The error message is:

> ERROR: transfromExpr: does not know hot to transfrom node 501 (internal error)

> Craig May


> Enth Dimension
> http://www.enthdimension.com.au/ 

It's a bug in 7.0.0. You can update to 7.0.2 or use the non-ANSI-join-syntax:

Select g.ID From Grouping g, Entity_Grouping eg 
WHERE g.ID=eg.Group_ID AND eg.Entity_ID=1

Gerhard

Antw: [SQL] many-many mapping between unique tables

[Gerhard Dieringer]

>>> Indraneel Majumdar <[EMAIL PROTECTED]> 16.10.2000  20.11 Uhr >>>
> Hi,
>
> I am facing a problem in mapping between two tables containing unique
> entries
>
> T1T2
> _ 
> |  x1 |   |  y1   |
> |  x2 |   |  y2   |
> |  x3 |   |  y3   |
> - -
>
> x(i) points to 1 or more entries in T2. y(i) points to one or more entries
> in T1. How do I store this mapping? I'd cannot use an array due to size
> restrictions and inability to extract data easily for furthur processing.
> 
> I don't want to put everything into a single table and repeat values in
> the 2nd column since these are really huge tables and I cannot increase
> overhead by increasing data redundancy.
> 
> Is there some way to do something about this?
>
> \Indraneel
> 
> /.
> # Indraneel Majumdar  ¡  E-mail: [EMAIL PROTECTED]  #
> # Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
> # Centre for DNA Fingerprinting and Diagnostics, #
> # Hyderabad, India - 500076  #
> `/

I'm not sure if I understand your question. Is it right, that the x(i) are different 
values
of an column x in table T1 and the y(i) are different values of an column y in table 
T2?
And you want to have a n:m relation between the two tables?
Then you need a third table T12 with columns x and y, that contains one row for each
mapping of a x(i) with a y(j).

I hope this helps.


Gerhard

Re: Antw: [SQL] many-many mapping between unique tables

[Gerhard Dieringer]

>>> Indraneel Majumdar <[EMAIL PROTECTED]> 16.10.2000  22.13 Uhr >>>
> Yeah you understand the problem perfectly. The third table will definitely
> contain unique entries if you take both x and y simultaneously. I wanted
> some way of mapping where I would not need to repeat individual values of
> x and y. I am not from a maths background and have absolutely no idea of
> database design.
>
> Is it possible to map the columns without repeating values?
> 
> \Indraneel

I don't know what type of data your columns x and y contain. If they are,
for example, of type text and contain long strings, then it would be better
to add an integer id column in each tables T1 and T2: x_id and y_id and
use these ids in the table T12.

Then you only have to repeat the (short) numeric vales in T12. But I think there 
is no way to totaly avoid any repetition.

--
Gerhard

Antw: [SQL] Outer Joins

[Gerhard Dieringer]

>>> "Marc Rohloff" <[EMAIL PROTECTED]> 01.11.2000  09.02 Uhr >>>
>
> select a.col1, b.col2 from a,b 
> where a.col1 = b.col2
>   or  b.col2 is null
>

This query has nothing to do with an outer join. See the following example:

table a

c1
---
x
y 

and 

table b

c2
---
x

Then an outer join gives:

select a,c1, b.c2
from a left outer join b on a.c1 = b.c2

c1 | c2
x  |  x
y  | (null)

but your query gives:

select a.c1, b.c2
from a, b 
where a.c1 = b.c2
  or  b.c2 is null

c1 | c2
x  |  x

because there are no rows in table b with c2 is null

-
Gerhard

Re: [SQL] Requests for Development

[Gerhard Dieringer]

>>> Josh Berkus <[EMAIL PROTECTED]> 09.11.2000  17.40 Uhr >>>
> Tom, Bruce, Jan, etc.:
>
>   As a PGSQL developer and business customer, I wanted to make some
> public requests as to the development path of PGSQL.  While, obviously,
> you will develop the functionality *you* are interested in, I thought it
> might be valuable to you to know what things would be most appreciated
> (and please, list folks, speak up).

I'm still dreaming of 'database links', or how ever you want to call the possibility 
to access 
tables/views in different dbs, maybe on different hosts, in one sql statement.


>   Thanks so much for your ongoing hard work!

ditto


-
Gerhard

Antw: [SQL] Selecting empty columns

[Gerhard Dieringer]

>>> Hans-Jürgen Schönig <[EMAIL PROTECTED]> 28.11.2000  12.39 Uhr >>>
> How can I retrieve empty columns from a table?
> The affected column is int4, some row do have values, others are empty -
> how can those empty ones be retrieved and updated to 0
>
>Hans

try:
update tablename set columnname=0 where columnname is null;

Gerhard

Antw: [SQL] aliases break my query

[Gerhard Dieringer]

Joseph Shraibman wrote:

> These two queries are exactly alike. The first one uses aliases except
> for the order by. The second uses aliases also for the order by. The
> third uses whole names.  The third has the behavior I want.

> Someone please tell me what I am doing wrong. I don't want to have to
> use whole names for my query.

> playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> ta.a) from tablea ta, tableb tb order by tablea.a;

> playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> ta.a) from tablea ta, tableb tb order by ta.a;

>playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz)
> where tableb.yy = tablea.a) order by tablea.a;


I think what you actually want is an outer join:

SELECT  tablea.a, tablea.b, tablea.c, Count(tableb.zz) AS zzcount
FROM tablea LEFT JOIN tableb ON tablea.a = tableb.yy
GROUP BY tablea.a, tablea.b, tablea.c;

that is not supported in postgreSQL 7.0, but can be simulated with 

SELECT  tablea.a, tablea.b, tablea.c, Count(tableb.zz) AS zzcount
FROM tablea INNER JOIN tableb ON tablea.a = tableb.yy
GROUP BY tablea.a, tablea.b, tablea.c
UNION 
SELECT  tablea.a, tablea.b, tablea.c, 0 AS zzcount
FROM tablea
WHERE tablea.a NOT IN (SELECT yy FROM  tableb);

Gerhard

Antw: [SQL] Rollback & Nextval fails

[Gerhard Dieringer]

Fredrik Eriksson wrote:

> I have been trying the following SQL code :

> BEGIN;
> INSERT INTO table VALUES ( NEXTVAL('serial'), 'Data' );
> ROLLBACK;

> And the insert function is rolled back but the serial sequence isn't. Hav I
> misunderstood the functionality of rollback or is this a bug? Is there
> someway to get the functionality that rollsback everything?

It's not a bug but a feature. 
Sequence numbers exist to create unique entity-id-number with (usually) no external 
meaning, so 
1.) there is no need to roll them back
2.) the system would have to keep a list, what sequence number were rolled back.

Gerhard

Antw: [SQL] Insert with replace?

[Gerhard Dieringer]

Wampler, Steve wrote:

> ...
> I've got a database that (logically) represents a set of
> items, where each item has a primary key represented by two
> columns (id and name).  If that key isn't in the database,
> I want to insert the item.  If that key is in the database, I want
> to update the item.  I have no need to retain the previous
> values.
> 

You can solve your problem with a trigger and a view, that has the same attributes as 
your table.
You insert the data into the view, not the table. The trigger first looks, if a row 
with that primary key is allready in the table and decides, 
if to use update or insert into the table.

example:
create table test_table (
  id   int4 primary key,
  data text);

create view test_view as
  select id, data 
from test_table;

create function insert_or_update() returns opaque as '
  declare 
lid int4;
  begin
select t.id into lid
  from test_table t
  where t.id = new.id;
if found then
  update test_table
 set data = new.data
   where id = new.id;
else
  insert into test_table 
  values (new.id, new.data);
end if;
return null;
  end;
' language 'plpgsql';


create trigger insert_or_update_trigger 
before insert on test_view
   for each row execute procedure insert_or_update();

Of course this solution has some drawbacks. 
PostgreSQL always returns INSERT 0 0, because the insert into the view is canceled by 
returning null in the trigger.
But it works.

Test it with two inserts, that do insert

insert into test_view values (1,'one');
insert into test_view values (2,'two');

select * from test_view;

Now an insert that dose an update:

insert into test_view values (1,'ONE');

select * from test_view;

Gerhard

Antw: [SQL] using max() aggregate

[Gerhard Dieringer]

Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr wrote:

> Hello,
> 
> I am trying to return the most recently updated record from a table:
>
> SELECT max(stopdate) FROM auction;
>
> and this works but only returns the timestamp, however if I try to get
> another column with the aggregate it fails:
> 
> SELECT title,max(stopdate) FROM auction;
> ERROR:  Attribute auction.title must be GROUPed or used in an aggregate function
> 
> Ok, so I group it now:
>
> SELECT title,max(stopdate) FROM auction GROUP BY title;
>  title |  max   
> ---+
>  dfsdfsdf  | 2000-07-10 05:00:00+02
>  dssdfsdfsdfsf | 2000-07-09 16:00:00+02
>  sdfsdfsdfsdf  | 2001-04-10 15:00:00+02
> (3 rows)
> 
> But the problem is that I now get three rows when I only want the max()
> item.
> 
> How should I do it?
> 
> Thanks in advance,

I didn't test it, but something like

SELECT title,stopdate 
FROM auction
WHERE stopdate = (SELECT max(stopdate) FROM auction);

should work.


Gerhard

Re: Antw: [SQL] using max() aggregate

[Gerhard Dieringer]

Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr wrote:

> ...
> Yes this would work nicely but if I need to add more conditional clauses
> I have to duplicate them in the main SELECT and in the sub-SELECT:
>
> SELECT title,max(stopdate)
> FROM auction
> WHERE stopdate = (SELECT max(stopdate) FROM auction AND stopdate > now()) 
> AND stopdate > now();
>
> Or am I missing something?
>
> Tom Lane suggested using:
>
> SELECT title,stopdate FROM auction ORDER BY stopdate LIMIT 1;
>
> which seems the best solution (I was a bit concerned about performance,
> but then again the max() aggregate does a scan of all rows as well).
> ...

I don't see why you repeat your conditions in the outer select. The condition in the 
inner select drops all records that violate the conditions,
so the same conditions in the outer select have nothing to do and you can leave them 
away.

Tom's solution has the drawback, that if you have more than one record with the same 
max value you only get one of them, but may be that you want
to see all of them.

Gerhard

Antw: [SQL] LEFT JOIN

[Gerhard Dieringer]

Antti Linno wrote:
> Greetings.
>  I have a problem. I have 2 tables. E.g. work and workers. I want to
> select records, that 1st table has, and the second hasn't(both have id
> attribute). I mean I can't do it with is NULL, because those records don't
> exist. I was shown, how it is done with mysql

I think, the following select will solve your problem

select  first.id
from first
except
second.id_first 
from second;

>
> select first.id,second.id_first 
> from first left join second on
> id=id_first where id_first is NULL;
> 

This query should never give any result, because you require
id_first = id and id_first is null
but if id_first is null then it's  equal to nothing,
and BTW it's also unequal to nothing.


> but when I tried it in psql, it said, not implemented.

Outer joins (left or right joins) are not implemented in PostgreSQL 7.0, but will
come in 7.1.

> 
> Antti

Gerhard

Antw: [SQL] Problem with joins

[Gerhard Dieringer]

Jean-Marc Libs wrote:
>...
>I have also tried:
>select source_name,data_value from source,data where data_source_id=source_id union 
>select source_name,source_id,NULL from source,data

>This is a bit better, in the sense that I get back all I need, but there
>are too many lines: when there is data, I get the line with the data value
>and also with NULL.
>...

You are on the right way. Change your querry to 

select source_name,data_value 
from source,data 
where data_source_id=source_id 
union 
select source_name,source_id
from source
WHERE source_id NOT IN (SELECT source_id FROM data);

and you will get your expected result.

BTW this simulates an outer join.

Gerhard

Antw: [SQL] GROUP by

[Gerhard Dieringer]

Antti Linno <[EMAIL PROTECTED]> wrote:

> I have table job, and I want to select data from it, grouping by
> workgroups. When I use 
>  select id,name from job where workgroup='top leaders' AND ...  group by
> workgroup;
> When I want to group, I get 
> ERROR:  Illegal use of aggregates or non-group column in target list
> I'm confused, why does query work only with sum,min etc. alone in
> select.
> 
> Antti

You can use GROUP BY only in conjunction with aggregat functions. Then you have to 
gruop by all attributes in the target list, that are not aggregated.

Example:
select id,name, workgroup, count(*) 
from job 
where workgroup='top leaders' AND ...  
group by id,name,workgroup;

-
Gerhard

Antw: RE: [SQL] join if there, blank if not

[Gerhard Dieringer]

Henry Lafleur wrote:

> ...
> What I have always had trouble with, though, is if you have multiple fields
> for a primary key. For example, if a customer master table also had ship-to
> locations as the key and you wanted to get all customers and any orders for
> that customer, in rough ANSI SQL it would be:
> 
> SELECT c.cust_number, c.ship_to, o.item
> FROM cust c LEFT OUTER JOIN orders o ON c.cust_number = o.cust_number AND
> c.ship_to = o.ship_to
> 
> then, in the union, it is not clear how to do it:
> 
> SELECT c.cust_number, c.ship_to, o.item
> FROM cust c, orders o
> WHERE c.cust_number = o.cust_number AND c.ship_to = o.ship_to
> UNION
> SELECT cust_number, ship_to, NULL AS item
> FROM cust
> WHERE ???
> ...

I don't see any problems with multiple fields. See the following example:

Outer join:
SELECT  tab_a.k1, tab_a.k2, tab_a.a_txt, tab_b.b_txt
FROM tab_a LEFT JOIN tab_b ON (tab_a.k2 = tab_b.k2) AND (tab_a.k1 = tab_b.k1);

Simulated outer join:
SELECT tab_a.k1, tab_a.k2, tab_a.a_txt, tab_b.b_txt
FROM tab_a , tab_b  WHERE (tab_a.k2 = tab_b.k2) AND (tab_a.k1 = tab_b.k1)
UNION
SELECT tab_a.k1, tab_a.k2, tab_a.a_txt, NULL
FROM tab_a WHERE (tab_a.k1 NOT IN (SELECT tab_b.k1 FROM tab_b))
OR (tab_a.k2 NOT IN (SELECT tab_b.k2 FROM tab_b));

Gerhard

[SQL] strange error message

[Gerhard Dieringer]

Hi all,

I'm sure I found somewhere in the docs, that postgreSQL 7.0 accepts ANSI-SQL style 
join syntax, but when I try:

SELECT ta.a, tb.b FROM taba ta INNER JOIN tabb tb ON ta.c = tb.c;

I get the strange message:

ERROR: transformExpr: does not know how to transform node 501 (internal error)

Is my syntax wrong or is this a bug in the parser? I'm using 7.0.0

Gerhard

Antw: Re: [SQL] Large text insertion

[Gerhard Dieringer]

>>> [EMAIL PROTECTED] 27.07.2000  11.39 Uhr >>>
>7.1  will  be able to hold megabytes in the "text" data type.
>It's already in the CURRENT sources and works well.

Is there a release date visible at the horizon?

Gerhard

Antw: [SQL] SQL Question

[Gerhard Dieringer]

>>> Daryl Herzmann <[EMAIL PROTECTED]> 01.08.2000  22.38 Uhr >>>
> Hello,
>   I hope this question is not too novice for this group.
>
> I have a table
>
> portfolio=# \d questions
>   Table "questions"
>  Attribute |Type |  Modifier   
> ---+-+-
>  qid   | integer | not null default nextval('questions_qid_seq'::text)
>  optiona   | text| default 'Z'
>  optionb   | text| default 'Z'
>  optionc   | text| default 'Z'
>  optiond   | text| default 'Z'
>  optione   | text| default 'Z'
>  optionf   | text| default 'Z'
>  optiong   | text| default 'Z'
>  optionh   | text| default 'Z'
> 
>
> and other table named quizes, which contains references to the
> questions. Basically a quiz is composed of up to three questions.
> 
> portfolio=# \d quizes
>  Table "quizes"
>  Attribute |Type |   Modifier   
> ---+-+--
>  quiznum   | integer | not null default nextval('quizes_quiznum_seq'::text)
>  question1 | integer | 
>  question2 | integer | 
>  question3 | integer | 
> 
> 
> 
> So my question is if I want to querry out a particular quiz and all its
> questions and question options, how can I do that in one command. I know
> that I could do it with a couple of loops, but I think the SQL is much
> more eliquent.  Maybe I have my tables incorrectly set up?  And
> suggestions?
> 
> 
> Thanks all,
>   Daryl


I suggest to use the following structure:

create table quetions (
 qid  integer,
 optid   integer,
 option text default 'Z'
 primaray key (q_id,opt_id));

create table quizes (
 quiznum   integer,
 questioninteger,
 primary key (quiznum,question),
 foreign key (question) references questions(qid));

I hope the syntax is ok, because I didn't check it. 

Then your query is as simple as:

select i.quiznum, i.question, e.optid, e.option
  from questions e, quizes i
 where i.question = e.qid and i.quiznum = some_quiznum_value
 order by 1,2,3;

Of course this has the drawback, that you can't use serials, but you explicitely  have 
to use sequences and set the primary keys 'by hand'.


Gerhard