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

score 0 · Answer 1 · answered Dec 07 '22 at 21:15

0

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:

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)
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).

answered Dec 07 '22 at 21:15

Bill Weiner

8,835
2
7
18

create table my_table (id int, created_at datetime default sysdate); https://stackoverflow.com/questions/34587051/redshift-creating-a-table-with-timestamp-column-defaulting-to-now – mellerbeck Dec 07 '22 at 23:03
Thank you. Question - do i remove getdate() from this part values_ = ', '.join([f"('{str(i.columnA)}','{str(i.columnB)}','{str(i.columnC)}','{str(i.columnD)}', 'GETDATE()')" for i in df2.itertuples()]) ? – kgk Dec 08 '22 at 00:08
Yes and add the key word default per - https://docs.aws.amazon.com/redshift/latest/dg/c_Examples_of_INSERT_30.html – Bill Weiner Dec 08 '22 at 00:29
like this? vals= ', '.join([f"('{str(i.columnA)}','{str(i.columnB)}','{str(i.columnC)}','{str(i.columnD)}', default 'GETDATE()')" for i in df2.itertuples()]) – kgk Dec 08 '22 at 01:03
No. Just default in the values string. The getdate() goes in the table definition of the table - in the CREATE TABLE statement. If you cannot redefined the table (only needed to be done once) then you need to go down option #2 in my answer. – Bill Weiner Dec 08 '22 at 04:31

score 0 · Answer 2 · edited Jan 13 '23 at 07:14

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_})  
    ;
    ''')

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

2 Answers2