use Perl

• Array types (Score:2)

  by djberg96 (2603)

  Oh, good point. Is something like PostgreSQL's array type the proper solution here then?

  create table sometable (
     id serial,
     name varchar(50),
     email text[]
  );

  This would store a list of email addresses as a single object. Or have I missed the point again?

  In other news, I'm surprised PostgreSQL doesn't have an "email_address" type. It seems to have everything else.
  • Re:Array types (Score:1)

    by runrig (3385)

    How do you then update, insert, delete, or search for a single email address? If it makes things more complicated to deal with the data, I think I'd rather normalize. Hmm, looking at the postgres docs, searching for a value in an array:

    SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                                pay_by_quarter[2] = 10000 OR
                             
    • Re:Array types (Score:2)

      by Ovid (2709) on 2006.04.21 15:09 (#47322) Homepage Journal

      The problem with CRUD on a single email address goes away if you have a language which supports that. SQL does not. While I confess that I don't remember my UniQuery (the query language for the MV database I used), imagine something like this:

      DELETE email
      FROM   customer
      WHERE  name  = ?
        AND  email = ?

      In other words, DELETE would operate on more than just tuples.

      How about searching for a particular email? Well, you show some sample syntax above.

      There are plenty of other pseudo-examples I could toss out to show how things work, but basically, SQL (as far as I know) deliberately doesn't address these issues because SQL isn't designed for MV databases.

      Reply to This

      Parent