Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: Fill Null Values with Text Sequence

    Okay, I have a table :

    name | phone | email

    Some of the records contain emails and some do not. I am not allowed to have null values in the email column. For whatever reason, the client has chosen to use the email as a primary key, so, for those records that have no email, I need them to populate with noemail@noemail.com, but in sequence so that each email is unique (i.e. noemail1@noemail.com, noemail2@noemail.com, etc.). Is there a way to do this in Postgres ?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Code:
    update emails
       set email = 'noemail'||t.rn::text||'@noemail.com'
    from (select row_number() over () as rn, ctid
          from emails) t
    where t.ctid = emails.ctid;
    If the name was unique (or you have a another unique column) you can use that for joining instead of the (internal) ctid column

  3. #3
    Join Date
    Feb 2012
    Posts
    2

    Smile Awesome!

    This is exactly what I needed. Thank you so much!

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    And, if you would like to provide this default value when the data is inserted, rather than afterward:

    Add a new sequence
    set default value for the email field to be the concatenation of 'noemail', nextval from the sequence, cast as text, and '@noemail.com'

    For example:
    Code:
    CREATE SEQUENCE noemail
      INCREMENT 1
      MINVALUE 1
      MAXVALUE 999999999
      START 1
      CACHE 1;
    
    CREATE TABLE test_no_emails
    (
      id serial NOT NULL,
      email character varying DEFAULT (('NoEmail'::text || (nextval('noemail'::regclass))::text) || '@noemail.com'::text),
      CONSTRAINT pk_email PRIMARY KEY (email)
    )
    WITH (OIDS=FALSE);
    
    insert into test_no_emails values (default, 'test@test.com');
    insert into test_no_emails values (default, default);
    insert into test_no_emails values (default, default);
    The resulting data after the above inserts is then
    Code:
    1   test@test.com
    2   NoEmail1@noemail.com
    3   NoEmail2@noemail.com
    Last edited by loquin; 02-27-12 at 19:03.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Tags for this Thread

Posting Permissions

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