awslabs / awslabs/amazon-redshift-utils

v_generate_tbl_ddl: Apostrophes not escaped in table comments

Open
#726 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Python
Stars
2.8k
Forks
1.2k
PR merge metrics
No merged PRs in 30d

Description

I have a multi-line table comment, such as

```sql
COMMENT ON TABLE foo IS '
Roses are red
Violets are blue
Redshift doesn''t mind long comments
And so shouldn''t you
';
```

When this is exported via `v_generate_tbl_ddl`, it comes out as:

```sql
COMMENT ON table foo IS '
Roses are red
Violets are blue
Redshift doesn't mind long comments
And so shouldn't you
';
```

Notice that both contractions are no longer correctly doubling up `''`, which results in a syntax error when I try to run the generated DDL. I expected the generated DDL to correctly escape these quotes in a comment.

I haven't had time to look into this, including:

* Whether it matters that there are exactly a balanced pair of apostrophes, or whether this would work with just one apostrophe, or if you just need more than one apostrophe
* Whether it matters that it's multi-line
* Whether it could affect e.g. column comments

Thanks for giving us a way to get DDL out of Redshift!

Contributor guide

Open the contributing guide

Research direction

Start with the v_generate_tbl_ddl view and reproduce the issue using the multi-line COMMENT ON TABLE example in the report. Check whether the generated DDL preserves doubled apostrophes, including in column comments if that view handles them; done means the exported statement runs without a syntax error.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Bug
Difficulty
2/5
Estimated time
1-3 hours
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
50/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.