PostgreSQL copy to a new table with one extra column. How?

Question

I am trying to make a forum database and in this database I have a trigger. After the Insert it should copy the values of the other table into the new one, but should also make a new column showing the actual registerdate of the user. Here is the trigger code:

Create OR REPLACE FUNCTION insertUser() RETURNS TRIGGER AS $insertUser$

BEGIN 

    INSERT INTO public.freshlyinserted(user_id, username, bday, gender, uemail, pasword)
    VALUES (NEW.user_id, NEW.username, NEW.bday, NEW.gender, NEW.uemail, NEW.pasword, NEW.registerdate);

RETURN NEW;
END;
$insertUser$ LANGUAGE plpgsql;

CREATE TRIGGER moveUserToInserted
AFTER INSERT ON forumusers
FOR EACH ROW EXECUTE PROCEDURE insertUser();

When i try this, it tells me this:

ERROR Record "new" has no field "registerdate" CONTEXT: SQL statement "INSERT INTO public.freshlyinserted(user_id, username, bday, gender, uemail, pasword) VALUES (NEW.user_id, NEW.username, NEW.bday, NEW.gender, NEW.uemail, NEW.pasword, NEW.registerdate)". PL/pgSQL function insertuser() row 2 for SQL statement

The forumuser table doesn't have the "registerdate" column...but I really would like it for the new Table withe the actual date(i dont know how to do it tho). I would be happy, if I get help with this. I will post any more needed code! Btw. Without the registerdate, it works...but then the table would make no sense for me XD

Thanks! :)

VBoka · Accepted Answer · 2020-01-26T16:23:20.833

Just use current_date like this:

Create OR REPLACE FUNCTION insertUser() RETURNS TRIGGER AS $insertUser$

BEGIN 

    INSERT INTO public.freshlyinserted(user_id, username, bday, gender, uemail, pasword)
    VALUES (NEW.user_id, NEW.username, NEW.bday, NEW.gender, NEW.uemail, NEW.pasword, current_date);

RETURN NEW;
END;
$insertUser$ LANGUAGE plpgsql;

CREATE TRIGGER moveUserToInserted
AFTER INSERT ON forumusers
FOR EACH ROW EXECUTE PROCEDURE insertUser();

You can also use current_timestamp

Here you can see what will they return or save when you use them: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=57bae2ba351bac477724aa5551c73ee2

thank you so much! Now it works :) I also figured out i forgot to put registerdate in the freshlyinserted table in INSERT INTO... XD Thanks again! — Raven sama, Jan 26 '20 at 16:27

score 0 · Answer 2 · answered Jan 26 '20 at 16:11

0

If I understand correctly, just replace NEW.registerdate with the current date for which you can use the function NOW()

answered Jan 26 '20 at 16:11

Rob Johansen

48
7

So you mean just like this: VALUES (NEW.user_id, NEW.username, NEW.bday, NEW.gender, NEW.uemail, NEW.pasword, GETDATE()); This is not working thi; i get an error again : ERROR: Function getdate() does not exist Maybe I am just stupid XD – Raven sama Jan 26 '20 at 16:15
I edited my answer after I posted it but guess you read it too soon. You are using postgres, so GETDATE() doesn't exist, but you can use NOW() instead. – Rob Johansen Jan 26 '20 at 22:57

score 0 · Answer 3 · answered Jan 26 '20 at 22:03

0

Doesn't need to change the trigger, you can use DEFAULT clause while creating/altering a table.

ALTER TABLE public.freshlyinserted
    ALTER COLUMN registerdate
        SET DEFAULT now();

answered Jan 26 '20 at 22:03

OO7

660
4
10

PostgreSQL copy to a new table with one extra column. How?

3 Answers3