Redshift reserved words are not escaped about sqlalchemy-redshift HOT 17 CLOSED

@rayrrr you up for making a PR as described in #94 (comment)?

from sqlalchemy-redshift.

I'm going to pick this up.

from sqlalchemy-redshift.

Does this happen with regular postgres databases under the postgres SQLalchemy dialect?

from sqlalchemy-redshift.

Haven't tried it, but "tag" seems to be Redshift specific.

from sqlalchemy-redshift.

Can you create a PR that overrides the Preparer in sqla-redshift with a subclass with those reserved words in.

Also put those words in a line delimited file and load it with pkg_resources.resource_string

from sqlalchemy-redshift.

That's frustrating that they are different. Can you find out the differences. Are there some that redshift removed?

from sqlalchemy-redshift.

See http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html

from sqlalchemy-redshift.

I just ran into a problem with a column called open, which looks like another case of a word reserved in Redshift but not PG.

Looks like PG reserved words are documented in https://www.postgresql.org/docs/current/static/sql-keywords-appendix.html

from sqlalchemy-redshift.

Do we have a spec for redshift reserved words?

from sqlalchemy-redshift.

http://docs.aws.amazon.com/redshift/latest/dg/r_pg_keywords.html

from sqlalchemy-redshift.

Here's a stab at getting a full list of Redshift-specific reserved words that aren't reserved by PG:

# File keywords.txt is a copy-paste of the table in https://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
cat keywords.txt | grep '^[A-Z]*\treserved' | sed 's/\([A-Z]*\).*/\1/' > pg_reserved.txt

# File rs_reserved.txt is a copy-paste from http://docs.aws.amazon.com/redshift/latest/dg/r_pg_keywords.html
diff pg_reserved.txt rs_reserved.txt | grep '>' | sed 's/> //'

This gives the following:

AES128
AES256
ALLOWOVERWRITE
BACKUP
BETWEEN
BLANKSASNULL
BYTEDICT
BZIP2
CREDENTIALS
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
CURRENT_USER_ID
DEFLATE
DEFRAG
DELTA
DELTA32K
DISABLE
EMPTYASNULL
ENABLE
ENCODE
ENCRYPT
ENCRYPTION
EXPLICIT
GLOBALDICT256
GLOBALDICT64K
GZIP
IDENTITY
IGNORE
LUN
LUNS
LZO
LZOP
MINUS
MOSTLY13
MOSTLY32
MOSTLY8
NEW
NULLS
OFF
OFFLINE
OID
OLD
OPEN
PARALLEL
PARTITION
PERCENT
PERMISSIONS
RAW
READRATIO
RECOVER
RESPECT
REJECTLOG
RESORT
RESTORE
SESSION_USER
SNAPSHOT
SYSDATE
SYSTEM
TAG
TDES
TEXT255
TEXT32K
TIMESTAMP
TOP
TRUNCATECOLUMNS
WALLET
WITHOUT

from sqlalchemy-redshift.

@jklukas are there any that are reserved by PG but not Redshift?

from sqlalchemy-redshift.

These are reserved by PG but not Redshift:

› diff pg_reserved.txt rs_reserved.txt | grep '<' | sed 's/< //'
ASYMMETRIC
COLLATION
CONCURRENTLY
FETCH
LATERAL
RETURNING
SYMMETRIC
TABLESAMPLE
VARIADIC
WINDOW

from sqlalchemy-redshift.

That's based on list of reserved words in current PG. I'll bet most of those are new since whichever 8.x Redshift was originally forked from.

from sqlalchemy-redshift.

Sure, I'll give it a go this weekend.

from sqlalchemy-redshift.

See #117

from sqlalchemy-redshift.

Fixed by #117

from sqlalchemy-redshift.