Airflow PostgresOperator SQL parameter syntax error

Question

I have the following task in my DAG:

create_features_table = PostgresOperator(
   task_id="create_features_table",
   postgres_conn_id="featuredb",
   sql="src/test.sql "
)

But when I test the task, I get this error:

psycopg2.errors.SyntaxError: syntax error at or near "src"
LINE 1: src/test.sql

The content of the test.sql script is:

CREATE TABLE test(
C1 int,
C2 int,
);

I can't point out the error in the syntax, but that's because it is my first DAG. Any help would be greatly appreciated.

If I run the script directly from the postgres container's psql using "\i src/text.sql" it works fine.

I have tested the connection from the airflow web server and the connection works.

I found that I had to put a space before closing the quotes to avoid a jinja2.exeptions.TemplateNotFound error, but haven't been able to find the syntax error.

Answer 1

According to documentation (https://airflow.apache.org/docs/apache-airflow-providers-postgres/stable/_api/airflow/providers/postgres/operators/postgres/index.html#airflow.providers.postgres.operators.postgres.PostgresOperator) if you are defining your sql script path, it must ends with .sql

You have a white space in your path in the end so Operatort things it’s a query to be executed on your postgre, not file with query. You can see it in the response from postgre. Run this query on your postgre instance src/test.sql and you will get the same syntax error.

You can fix it easily by removing that white space

sql="src/test.sql"