How do I add timestamp (using GETDATE()) to my insert statement?

Question

I'm trying to figure out how to add a timestamp to my database table. df2 doesn't include any column for time so i'm trying to create the value either in values_ or when I execute to sql. I want to use the GETDATE() redshift function

  values_ = ', '.join([f"('{str(i.columnA)}','{str(i.columnB)}','{str(i.columnC)}','{str(i.columnD)}', 'GETDATE()')" for i in df2.itertuples()])
        
        sqlexecute(f'''insert into table.table2 (columnA, columnB, columnC, columnD, time_) 
                values
        ({values_})
        ;
        ''')

This is one of several errors I get depending on where I put GETDATE()

FeatureNotSupported: ROW expression, implicit or explicit, is not supported in target list

Answer 1

The "INSERT ... VALUES (...)" construct is for inserting literals into a table and getdate() is not a literal. However, there are a number of ways to get this to work. A couple of easy ways are:

1. You can make the default value of the column 'time_' be getdate() and then just use the key work default in the insert values statement. This will tell Redshift to use the default for the column (getdate())

   insert into values ('A', 'B', 3, default)

2. You could switch to a "INSERT ... SELECT ..." construct which will allow you to have a mix of literals and function calls.

   insert into table (select 'A', 'B', 3, getdate())

NOTE: inserting row by row into a table in Redshift can slow and make a mess of the table if the number of rows being inserted is large. This can be compounded if auto-commit is on as each insert will be committed which will need to work its way through the commit queue. If you are inserting a large amount of data you should do this through writing an S3 object and COPYing it to Redshift. Or at least bundling up 100+ rows of data into a single insert statement (with auto-commit off and explicitly commit the changes at the end).

Answer 2

When I created the table I added a time_log column using timestamp.

drop table if exists table1;
create table table1(
column1 varchar (255),
column2 varchar(255),
time_log timestamp
);

The issue was I had parentheses around the values in my insert statement. remove those and it will work.{values_}

sqlexecute(f'''insert into table.table2 (columnA, columnB, time_log) 
            values
    ({values_})  
    ;
    ''')