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

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of PostgreSQL copy to a new table with one extra column. How? without wasting too much if your time.

The question is published on by Tutorial Guruji team.

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! 🙂

Answer

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

We are here to answer your question about PostgreSQL copy to a new table with one extra column. How? - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji