Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2009
    Posts
    54

    Unanswered: multi-multi relation data input and query help

    hello folks,
    I need to make myself a small database for bibliography storage. I used to have one in access, but it broke, and since then I moved to unix and postgresql. But I'm not that expert and cannot figure out from the various tutorials and manpages I've read how I could achieve the following:
    I have table "A" for authors and table "B" for titles (as a simple example). As you all know an author may have many titles and a book may have more than one author. I created a third table referencing the primary keys in "A" and "B", but then I cannot understand how to insert data telling postgres which rows of "A" and "B" are linked together and then how to retreive it. Should I do it with a view (is it something comparable to access' forms?) or with a rule or with a trigger? And how?
    thank you in advance

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Table setup:
    Code:
    create table author 
    (
      author_id integer primary key, 
      name varchar(20)
    );
    
    create table book 
    (
      book_id integer primary key, 
      title varchar(20)
    );
    
    create table book_author 
    (
       author_id integer, 
       book_id integer, 
       primary key (author_id, book_id),
       foreign key (author_id) references author (author_id),
       foreign key (book_id) references book (book_id)
    );
    commit;
    Create books and authors:
    Code:
    insert into author (author_id, name) values (1, 'Author One');
    insert into author (author_id, name) values (2, 'Author Two');
    insert into author (author_id, name) values (3, 'Author Three');
    
    insert into book (book_id, title) values (1, 'Book 1');
    insert into book (book_id, title) values (2, 'Book 2');
    insert into book (book_id, title) values (3, 'Book 3');
    commit;
    Now create the relation between books and authors:

    Book 1 is written by Author 1
    Code:
    insert into book_author (book_id, author_id) values (1,1);
    commit;
    Book 2 is written by Author 2 and Author 3
    Code:
    insert into book_author (book_id, author_id) values (2,2);
    insert into book_author (book_id, author_id) values (2,3);
    commit;
    Book 3 is written by Author 1
    Code:
    insert into book_author (book_id, author_id) values (3,1);
    commit;
    Now you have one book ('Book 2') that is written by two authors (Author 2 and Author 3)
    And one author ('Author 1') that has written two books ('Book 1' and 'Book 3')

  3. #3
    Join Date
    Feb 2009
    Posts
    54
    thank you for you answer!
    Actually I did already that, but at this point I must think that my lack of knoledge is with the queries:
    If one author has no books how do I do a "left join" query? If I write
    Code:
    select * from author left join books using (author)
    it says
    Code:
    select name, title from author left join book using (author);
    ERROR:  column "author" specified in USING clause does not exist in left table
    Also, is there a way to make this linking author to books automatic? (I can live with it if it's not, really).

    Most important question:
    How do I concatenate the result:
    Author1, Author2, Author3 Book1 ...?


    by the way, what is the "commit" command? This is what I get from it:
    Code:
     commit;
    WARNING:  there is no transaction in progress
    COMMIT
    Last edited by shwe; 02-14-09 at 18:11.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shwe
    Actually I did already that, but at this point I must think that my lack of knoledge is with the queries:
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Joins Between Tables

    Also, is there a way to make this linking author to books automatic? (I can live with it if it's not, really).
    I have no idea what you mean with "automatic linking"

    by the way, what is the "commit" command?
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Transactions

    WARNING: there is no transaction in progress
    You are running with autocommit on, which is not a good idea.

  5. #5
    Join Date
    Feb 2009
    Posts
    54
    first of all, what I mean by "automatic linking". As I said, I used to have this little database in access. In access you can make a form (I guess something like an update view in postgresql) with a subform which in our case would relate to the "author" (form) and "book" (subform) tables. You can select or insert an author and in the same window insert the book, and they will be automatically linked in the third table. I hope I'm not being to confusing.
    But this is the least of the problems.

    I've read the join manpage many times, but still have some problems. As you see from the example from last time I cannot do a direct join like this:
    Code:
    select * from author left join books using (author)
    There is something I'm really not grasping.
    How do I achieve that?

    I until now managed to achieve something which resembles what I want by creating two views:
    Code:
    create view a as select name, author_id, book_id from author left join book_author using (author_id);
    Code:
    create view b as select title, book_id, author_id from book_author left join  book using (book_id);
    this way I can have this:
    Code:
    select distinct name,title from a left join b using (author_id);
    Code:
         name     | title
    --------------+--------
     Author Four  |
     Author One   | Book 1
     Author One   | Book 3
     Author Three | Book 2
     Author Two   | Book 2
    (5 rows)
    Is this the way to do it?
    Now, the most important thing for me to make this database useful is to be able to concatenate the authors if they are co-authors of a book, I managed to do it like this:
    Code:
    select distinct (select array_to_string(array(select name from a where book_id=2), ', ')), title from b where book_id=2;
             ?column?         | title
    --------------------------+--------
     Author Two, Author Three | Book 2
    (1 row)
    Now my questions are: where do I put the "as" to give a name to the "?column?"?, I've tried every position without success.
    Also, now there's only one record, but what if I have hundreds of titles and want to tell postrgresql to concatenate only those authors that have books in common to their very own book, and at the same time get the ones that only have one author also?
    I' quite stuck now.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by shwe
    first of all, what I mean by "automatic linking". As I said, I used to have this little database in access. In access you can make a form (I guess something like an update view in postgresql) with a subform which in our case would relate to the "author" (form) and "book" (subform) tables. You can select or insert an author and in the same window insert the book, and they will be automatically linked in the third table.
    Access is an application development tool whereas PostgreSQL is not. This isn't a database question because it's not the DBMS's job to "link" tables in forms.

    This may be the query you are looking for:

    SELECT author.name, book.title
    FROM author
    LEFT JOIN book_author
    ON author.author_id = book_author.author_id
    LEFT JOIN book
    ON book_author.book_id = book.book_id ;

  7. #7
    Join Date
    Feb 2009
    Posts
    54
    Thanks a lot, that is what I wanted. And I'll be fine "linking" my rows manually.

    What about the concatenate? I've made a view from the query you gave me, but I can't get authors to concatenate the right way.

  8. #8
    Join Date
    Feb 2009
    Posts
    54
    I admit it, I have been a bit lazy and did not look really hard for the answer by myself at the beginning.
    Now, I've made a view from the suggestion kindly given by dportas, and from that running an aggregate function i get this:
    Code:
     title  |             name
    --------+-------------------------------
            | {"Author Four"}
     Book 1 | {"Author One"}
     Book 2 | {"Author Two","Author Three"}
     Book 3 | {"Author One"}
    which basically is exactly what I want. But how do I get rid of curled brackets and quote marks? Is there a different function that doesn't use them and gives the same result?
    Code:
    CREATE AGGREGATE name_of_function(anyelement)
    (
    sfunc = array_append,
    stype = anyarray
    );
    also, where can I find an detailed explanation of what all the functions do?
    thank you

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shwe
    But how do I get rid of curled brackets and quote marks? Is there a different function that doesn't use them and gives the same result?
    That should be doable using replace() or if a more sophisticated method is necessary, regexp_replace()

    Check out:
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: String Functions and Operators

  10. #10
    Join Date
    Feb 2009
    Posts
    54
    thanks a lot, I'll try and work it out by myself, otherwise come back here

  11. #11
    Join Date
    Feb 2009
    Posts
    54
    I've tried the replace() function, but id does not seem to work :
    Code:
    select replace(name,'{"',''), title from my_view;  
    ERROR:  function replace(character varying[], "unknown", "unknown") does not exist
    LINE 1: select replace(name,'{"',''), title from my_view;
                   ^
    HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
    As you see the name column type has changed from varchar(20) to varchar[] (I don't know what it means).
    If I specify a string to change it changes all the rows to that string which is not what I need:
    Code:
    select replace('{"Author One"}','{"',''), title from my_view;
       replace    | title  
    --------------+--------
     Author One"} | 
     Author One"} | Book 1
     Author One"} | Book 2
     Author One"} | Book 3
    this is the result starting from this view:
    Code:
    select * from my_view;
                 name              | title  
    -------------------------------+--------
     {"Author Four"}               | 
     {"Author One"}                | Book 1
     {"Author Two","Author Three"} | Book 2
     {"Author One"}                | Book 3
    (4 rows)

    what am I missing?

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shwe
    As you see the name column type has changed from varchar(20) to varchar[] (I don't know what it means).
    You should have shown us the DDL for the CREATE VIEW...
    I don't think the column "changed", I guess the column evaluates to an array in your view definition.
    In that case, the curly braces are not part of the values but are the way psql is displaying an array in it's output (but that's just guessing as I don't know the real view definition).

    In your view definition you need to wrap the generation of the array (the expression that defines the name column) with the function array_to_string(anyarray, text) as you have already done in a previous post.

  13. #13
    Join Date
    Feb 2009
    Posts
    54

    solved!

    my goodness! looking once again on google to see different concatenate options I've found this that did not appear in my previous searches:
    Code:
    CREATE AGGREGATE textcat_all(
      basetype    = text,
      sfunc       = textcat,
      stype       = text,
      initcond    = ''
    );
    then I can do:
    Code:
    select textcat_all(name || ', '), title from joined_view group by title;
    and get this:
    Code:
            textcat_all         | title  
    ----------------------------+--------
     Author Four,               | 
     Author One,                | Book 1
     Author Two, Author Three,  | Book 2
     Author One,                | Book 3
    (4 rows)
    this has the problem that leaves a comma at the and, which I don't want. In the page I've found it says that:
    In order to get the ", " inserted in between them without having it at the end,
    you might want to make your own concatenation function and substitute it for the
    "textcat" above. Here is one I put together but haven't tested:
    Code:
    CREATE FUNCTION commacat(acc varchar, instr varchar) RETURNS varchar AS $$
      BEGIN
        IF acc IS NULL OR acc = '' THEN
          RETURN instr;
        ELSE
          RETURN acc || ', ' || instr;
        END IF;
      END;
    $$ LANGUAGE plpgsql;
    but I couldn't make it work.
    I hope you can give me some hints, otherwise with some more homework I might solve it.
    Thank you.

  14. #14
    Join Date
    Feb 2009
    Posts
    54
    ok guys, I've found yet another variant, which seems the more useful (I really should do some real study of sql...).
    First (mind the comma in between || '', '' ||:
    Code:
       create or replace function text_cat(text, text) returns text called on null
       input
       language sql immutable as 'select case when $1 is null then $2 when $2
       is null then $1 else $1 || '', '' || $2 end';
    then:
    Code:
       create aggregate textcat (basetype = text, sfunc = text_cat, stype = text);
       create table memos (memo_id int, sequence int, memo_text text);
    Create a view to make it easier afterwords, or directly run from the "select" part:
    Code:
    create view concat_view as select textcat(name) as author, title from (select * from joined_view order by title) as foo group by title;
    (I cannot understand the last "as", without it postgres gives error, but as you see it seems useless.
    run:
    Code:
    select * from concat_view;
              author          | title  
    --------------------------+--------
     Author One               | Book 1
     Author Two, Author Three | Book 2
     Author One               | Book 3
     Author Four              | 
    (4 rows)
    do some more concatenate:
    Code:
    select author || '. ' || title from concat_view as bibliography;
    Code:
                ?column?             
    ----------------------------------
     Author One. Book 1
     Author Two, Author Three. Book 2
     Author One. Book 3
     
    (4 rows)
    as you see, the "as" is usless here also, and the "Author Four" has disappeared although is being counted. Why?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •