olivermeyer opened a new issue, #30341:
URL: https://github.com/apache/airflow/issues/30341
### Apache Airflow Provider(s)
amazon
### Versions of Apache Airflow Providers
7.2.1
### Apache Airflow version
2.5.1
### Operating System
Ubuntu
### Deployment
Official Apache Airflow Helm Chart
### Deployment details
_No response_
### What happened
I am trying to use the `S3ToRedshiftOperator` to copy data into an existing
table which has a column defined as non-null with default.
The copy fails with the following error:
```
redshift_connector.error.ProgrammingError: {'S': 'ERROR', 'C': '42601', 'M':
'NOT NULL column without DEFAULT must be included in column list', 'F':
'../src/pg/src/backend/commands/commands_copy.c', 'L': '2727', 'R': 'DoTheCopy'}
```
This is happening because when using the `UPSERT` method, the operator first
creates a temporary table with this statement
([here](https://github.com/apache/airflow/blob/47cf233ccd612a68bea1ad3898f06b91c63c1964/airflow/providers/amazon/aws/transfers/s3_to_redshift.py#L173)):
```
CREATE TABLE #bar (LIKE foo.bar);
```
And then attempts to copy data into this temporary table. By default,
`CREATE TABLE ... LIKE` does not include default values: _The default behavior
is to exclude default expressions, so that all columns of the new table have
null defaults._ (from the
[docs](https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html)).
### What you think should happen instead
We should be able to include default values when creating the temporary
table.
### How to reproduce
* Create a table with a column defined as non-null with default value
* Use the operator to copy data into it using the `UPSERT` method
### Anything else
_No response_
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of
Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
