Postgres: Create Column and Update Column Values with Query Output

Question

I have a file that has no Primary Key. In order to load the file and perform analysis I want to concatenate 2 existing columns and send the output to a new column. I'm then going to do a hash of this resultant column and use that as a PK.

I haven't even got to the hash part as I can not for the life of me work out how to populate my concatenated column with data.

The query I'm trying to use is:

ALTER TABLE members_250815
ADD COLUMN email_id VARCHAR;
UPDATE members_250815
INSERT INTO members_250815(email_id)(
SELECT ARRAY_TO_STRING(ARRAY[emailaddress, id], ' ') AS email_id
FROM members_250815);

As seperate queries both

ALTER TABLE members_250815
ADD COLUMN email_id VARCHAR; 

and

SELECT ARRAY_TO_STRING(ARRAY[emailaddress, id], ' ') AS email_id
FROM members_250815;

seem to work as I want them to (ie - 1) create the new column and 2) concatenate the 2 columns) however my issue seems to be in joining it all together.

Am I doing something really stupid? I have tried to research this for hours but I am getting nowhere. Essentially the task I am trying to achieve is:

Create new column on existing table
Concatenate 2 existing columns

Take the result of the concatenation and update this new column with this data without affecting any of my other existing data.

Is this possible?

Many thanks in advance

---Update 260815

Many thanks for the quick advice guys, much appreciated! Using a combination of your advice I have gotten to here:

CREATE TABLE members_update AS 
SELECT * FROM members_250815;

ALTER TABLE members_update
   ADD COLUMN email_id VARCHAR;<br/>

UPDATE members_update
SET email_id = email || id;

ALTER TABLE members_update
   ADD COLUMN hashed_primary_key VARCHAR;

UPDATE members_update
SET hashed_primary_key = md5(email_id::VARCHAR);

ALTER TABLE members_update
   ADD CONSTRAINT hashed_primary_key_urn
   PRIMARY KEY (hashed_primary_key);

ANALYSE members_update;

I have checked and everything works as expected up until adding the primary key. This is because it turned out that my email field contains numerous NULL values which are then carried into to the email_id and hashed columns and stop the hashed version from being used as the PK.

As such I have been experimenting with IF THEN ELSE and WHERE ELSE statements like

UPDATE members_update(
   IF email IS NOT NULL
   THEN SET email_id = email || id
   ELSE SET email_id = id
   END IF);

I have tried numerous combinations, with and without brackets etc and I can never get it to work! I think I am close but just can't seem to make this final part work - has anyone got any ideas?

Many thanks,

Mark

Answer 1

The problem is your update statement is wrong

You need SET, and CASE Should be:

ALTER TABLE members_250815
    ADD COLUMN email_id VARCHAR;

UPDATE members_250815
SET email_id = CASE 
                   WHEN email IS NULL THEN id
                   ELSE email || id
               END;

ARRAY_TO_STRING(ARRAY[emailaddress, id], ' ') may also work, but a further research will be necesarry to know if is more eficient than just concatenate the string.

Better way to create an PK column: Just alter the table and add a serial column

SQL Fiddle Demo

CREATE TABLE members_250815
    ("DMDUNIT" varchar(5), 
     "IND" int)
;

INSERT INTO members_250815 VALUES ('TM001', 1);
INSERT INTO members_250815 VALUES ('TM002', 1);
INSERT INTO members_250815 VALUES ('TM003', 1);

ALTER TABLE members_250815
ADD COLUMN id SERIAL NOT NULL PRIMARY KEY;

Aditional Info

In postgres updates are very slow. So in some cases is better consider just create a new table:

CREATE new_table AS 
    SELECT *, CASE 
                   WHEN email IS NULL THEN id
                   ELSE email || id
              END as email_id
    FROM members_250815

and then

DROP TABLE IF EXITS members_250815;
ALTER TABLE new_table RENAME TO members_250815