CODEWITHSUNDEEP
amazon-redshift
Day Lee
Day Lee

Reputation: 11

Redshift insert a date value into a table

insert into table1 (ID,date)
select
ID,sysdate 
from table2

assume i insert a record into table2 with value ID:1,date:2023-1-1 the expected result is update the ID of table1 base on the ID from table2 and update the value of date of table1 base on the sysdate from table2.

select * 
from table1;

the expected result after running the insert statement will be

ID date
1 2023-1-6

but what i get is:

ID date
1 2023-1-1

Upvotes: 0

Views: 671

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11102

I see a few possibilities based on the information given:

  1. You say "the expected result is update the ID of table1 base on the ID from table2" and this begs the question - did ID = 1 exist in table1 BEFORE you ran the INSERT statement? If so are you expecting that the INSERT will update the value for ID #1? Redshift doesn't enforce or check uniqueness of primary keys and you would get 2 rows in the table1 in this case. Is this what is happening?
  2. SYSDATE on Redshift provides the start timestamp of the current transaction, NOT the current statement. Have you had the current transaction open since the 1st?
  3. You didn't COMMIT the results (or the statement failed) and are checking from a different session. It could also be that the transaction started before in the second session before the COMMIT completed. Working with MVCC across multiple sessions can trip anyone up.

There are likely other possible explanations. If you could provide DDL, sample data, and a simple test case so that others can recreate what you are seeing it would greatly narrow down the possibilities.

Upvotes: 1

Related Questions