use Perl

• Array types (Score:2)

  by djberg96 (2603) on 2006.04.21 9:32 (#47306) Journal

  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.

  Reply to This

  • Re:Array types (Score:2)

    by Theory (10)

    In other news, I'm surprised PostgreSQL doesn't have an "email_address" type. It seems to have everything else.

    You can create one easily enough with a CREATE DOMAIN statement. Here's a naive implementation:

    CREATE DOMAIN email_address AS TEXT
    CONSTRAINT ck_email_address CHECK (
       VALUE ~ '^[^@]+@[^@]+$'
    );

    Modify the regular expression as needed to increase its accuracy. Then you can use it like any other PostgreSQL data type:

    CREATE TABLE person (
      id SERIAL PRIMARY KEY,
      name text
  • Re:Array types (Score:2)

    by Ovid (2709)

    As Theory points out, you can create such a type. The problem is that if anything else needs to reference data within that type, Postgres does not allow any convenient way of doing so. This makes it pretty difficult to use Postgres for MV purposes.
    • Re:Array types (Score:1)

      by iburrell (4155)

      PostgreSQL actually supports composite types.

      CREATE TYPE inventory_item AS (
          name            text,
          supplier_id     integer,
          price           numeric
      );
      
      CREATE TABLE on_hand (
          item      inventory_item,
          count     integer
      );
      
      SELECT (item).supplier_id FROM on_hand;

      It sounds like PostgreSQL even automatically makes a composite type for each table
  • 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)

      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 oth