Postgres Inner join insert into and select from syntax

Question

I am trying to make a forgot password interface with node and typescript. I want to insert a forgot password token into a forgot password database and I am using the below query to get this done. Basically I am using guid's as the tokens and trying to reference my people, parties and contact_details tables

INSERT INTO 
dbo.forgot_password_tokens
    SELECT 
    'random_guid' as forgot_password_tokens_id, 
    current_timestamp as updated_at,
    cd.contact_details_id,
    u.users_id
        FROM
           dbo.users as u
           inner join dbo.people as pe on pe.people_id = u.people_id           
           inner join dbo.parties as pa on pa.parties_id = pa.parties_id  
           inner join dbo.contact_details as cd on
               cd.parties_id = pa.parties_id
               and cd.value = '[email protected]'
               and cd.contact_details_types_id in ('guid_for_email', 'guid_for_second_email')

This is the error that I get

ERROR:  column "updated_at" is of type timestamp with time zone but expression is of type uuid

LINE 6:   cd.contact_details_id,

I am unsure as to why it says that the updated_at is getting the wrong data, I thought that the current_timestamp would put it in the correct place

Answer 1

The order of columns in the target table probably doesn't match the order of columns in your select. This is a very good example on why you should always specify the column list in an insert statement:

INSERT INTO dbo.forgot_password_tokens
   (forgot_password_tokens_id, updated_at, contact_details_id, users_id)
SELECT ...
FROM ...