Identifiers

In Materialize, identifiers are used to refer to columns and database objects like sources, views, and indexes.

Naming restrictions

The first character of an identifier must be an ASCII letter (a-z and A-Z), an underscore (_), or any non-ASCII character.
The remaining characters of an identifier must be ASCII letters (a-z and A-Z), ASCII digits (0-9), underscores (_), dollar signs ($), or any non-ASCII characters.

You can circumvent any of the above rules by double-quoting the identifier, e.g. "123_source" or "fun_source_@". All characters inside a quoted identifier are taken literally, except that double-quotes must be escaped by writing two adjacent double-quotes, as in "includes""quote".

Additionally, the identifiers "." and ".." are not permitted.

Case sensitivity

Materialize performs case folding (the caseless comparison of text) for identifiers, which means that identifiers are effectively case-insensitive (foo is the same as FOO is the same as fOo). This can cause issues when column names come from data sources which do support case-sensitive names, such as Avro-formatted sources or CSV headers.

To avoid conflicts, double-quote all field names ("field_name") when working with case-sensitive sources.

Keyword collision

Materialize is very permissive with accepting SQL keywords as identifiers (e.g. offset, user). If Materialize cannot use a keyword as an identifier in a particular location, it throws a syntax error. You can wrap the identifier in double quotes to force Materialize to interpret the word as an identifier instead of a keyword.

For example, SELECT offset is invalid, because it looks like a mistyping of SELECT OFFSET <n>. You can wrap the identifier in double quotes, as in SELECT "offset", to resolve the error.

We recommend that you avoid using keywords as identifiers whenever possible, as the syntax errors that result are not always obvious.

The current keywords are listed below.

`ACCESS`	`ACTION`	`ADD`
`ADDED`	`ADDRESS`	`ADDRESSES`	`AFTER`
`AGGREGATE`	`AGGREGATION`	`ALIGNED`	`ALL`
`ALTER`	`ANALYSE`	`ANALYSIS`	`ANALYZE`
`AND`	`ANY`	`ARITY`	`ARN`
`ARRANGED`	`ARRANGEMENT`	`ARRAY`	`AS`
`ASC`	`ASSERT`	`ASSUME`	`AT`
`AUCTION`	`AUTHORITY`	`AVAILABILITY`	`AVRO`
`AWS`	`BATCH`	`BEGIN`	`BETWEEN`
`BIGINT`	`BILLED`	`BODY`	`BOOLEAN`
`BOTH`	`BPCHAR`	`BROKEN`	`BROKER`
`BROKERS`	`BY`	`BYTES`	`CAPTURE`
`CARDINALITY`	`CASCADE`	`CASE`	`CAST`
`CERTIFICATE`	`CHAIN`	`CHAINS`	`CHAR`
`CHARACTER`	`CHARACTERISTICS`	`CHECK`	`CLASS`
`CLIENT`	`CLOCK`	`CLOSE`	`CLUSTER`
`CLUSTERS`	`COALESCE`	`COLLATE`	`COLUMN`
`COLUMNS`	`COMMENT`	`COMMIT`	`COMMITTED`
`COMPACTION`	`COMPATIBILITY`	`COMPRESSION`	`COMPUTE`
`COMPUTECTL`	`CONFIG`	`CONFLUENT`	`CONNECTION`
`CONNECTIONS`	`CONSTRAINT`	`CONTINUAL`	`COPY`
`COUNT`	`COUNTER`	`CPU`	`CREATE`
`CREATECLUSTER`	`CREATEDB`	`CREATENETWORKPOLICY`	`CREATEROLE`
`CREATION`	`CROSS`	`CSE`	`CSV`
`CURRENT`	`CURSOR`	`DATABASE`	`DATABASES`
`DATUMS`	`DAY`	`DAYS`	`DEALLOCATE`
`DEBEZIUM`	`DEBUG`	`DEBUGGING`	`DEC`
`DECIMAL`	`DECLARE`	`DECODING`	`DECORRELATED`
`DEFAULT`	`DEFAULTS`	`DELETE`	`DELIMITED`
`DELIMITER`	`DELTA`	`DESC`	`DETAILS`
`DIRECTION`	`DISCARD`	`DISK`	`DISTINCT`
`DOC`	`DOT`	`DOUBLE`	`DROP`
`EAGER`	`ELEMENT`	`ELSE`	`ENABLE`
`END`	`ENDPOINT`	`ENFORCED`	`ENVELOPE`
`EQUIVALENCES`	`ERROR`	`ERRORS`	`ESCAPE`
`ESTIMATE`	`EVERY`	`EXCEPT`	`EXCLUDE`
`EXECUTE`	`EXISTS`	`EXPECTED`	`EXPLAIN`
`EXPOSE`	`EXPRESSIONS`	`EXTERNAL`	`EXTRACT`
`FACTOR`	`FALSE`	`FAST`	`FEATURES`
`FETCH`	`FIELDS`	`FILE`	`FILES`
`FILTER`	`FIRST`	`FIXPOINT`	`FLOAT`
`FOLLOWING`	`FOR`	`FOREIGN`	`FORMAT`
`FORWARD`	`FROM`	`FULL`	`FULLNAME`
`FUNCTION`	`FUSION`	`GENERATOR`	`GRANT`
`GREATEST`	`GROUP`	`GROUPS`	`HAVING`
`HEADER`	`HEADERS`	`HINTS`	`HISTORY`
`HOLD`	`HOST`	`HOUR`	`HOURS`
`HUMANIZED`	`HYDRATION`	`ID`	`IDENTIFIERS`
`IDS`	`IF`	`IGNORE`	`ILIKE`
`IMPLEMENTATIONS`	`IMPORTED`	`IN`	`INCLUDE`
`INDEX`	`INDEXES`	`INFO`	`INHERIT`
`INLINE`	`INNER`	`INPUT`	`INSERT`
`INSIGHTS`	`INSPECT`	`INSTANCE`	`INT`
`INTEGER`	`INTERNAL`	`INTERSECT`	`INTERVAL`
`INTO`	`INTROSPECTION`	`IS`	`ISNULL`
`ISOLATION`	`JOIN`	`JOINS`	`JSON`
`KAFKA`	`KEY`	`KEYS`	`LAST`
`LATERAL`	`LATEST`	`LEADING`	`LEAST`
`LEFT`	`LEGACY`	`LETREC`	`LEVEL`
`LIKE`	`LIMIT`	`LINEAR`	`LIST`
`LOAD`	`LOCAL`	`LOCALLY`	`LOG`
`LOGICAL`	`LOGIN`	`LOWERING`	`MANAGED`
`MANUAL`	`MAP`	`MARKETING`	`MATERIALIZE`
`MATERIALIZED`	`MAX`	`MECHANISMS`	`MEMBERSHIP`
`MEMORY`	`MESSAGE`	`METADATA`	`MINUTE`
`MINUTES`	`MODE`	`MONTH`	`MONTHS`
`MUTUALLY`	`MYSQL`	`NAME`	`NAMES`
`NATURAL`	`NEGATIVE`	`NETWORK`	`NEW`
`NEXT`	`NO`	`NOCREATECLUSTER`	`NOCREATEDB`
`NOCREATEROLE`	`NODE`	`NOINHERIT`	`NOLOGIN`
`NON`	`NONE`	`NOSUPERUSER`	`NOT`
`NOTICE`	`NOTICES`	`NULL`	`NULLIF`
`NULLS`	`OBJECTS`	`OF`	`OFFSET`
`ON`	`ONLY`	`OPERATOR`	`OPTIMIZED`
`OPTIMIZER`	`OPTIONS`	`OR`	`ORDER`
`ORDINALITY`	`OUTER`	`OVER`	`OWNED`
`OWNER`	`PARTITION`	`PARTITIONS`	`PASSWORD`
`PATH`	`PATTERN`	`PHYSICAL`	`PLAN`
`PLANS`	`POLICIES`	`POLICY`	`PORT`
`POSITION`	`POSTGRES`	`PRECEDING`	`PRECISION`
`PREFIX`	`PREPARE`	`PRIMARY`	`PRIORITIZE`
`PRIVATELINK`	`PRIVILEGES`	`PROGRESS`	`PROJECTION`
`PROTOBUF`	`PROTOCOL`	`PUBLIC`	`PUBLICATION`
`PUSHDOWN`	`QUALIFY`	`QUERY`	`QUOTE`
`RAISE`	`RANGE`	`RATE`	`RAW`
`READ`	`READY`	`REAL`	`REASSIGN`
`RECURSION`	`RECURSIVE`	`REDACTED`	`REDUCE`
`REFERENCE`	`REFERENCES`	`REFRESH`	`REGEX`
`REGION`	`REGISTRY`	`RELATION`	`RENAME`
`REOPTIMIZE`	`REPEATABLE`	`REPLACE`	`REPLAN`
`REPLICA`	`REPLICAS`	`REPLICATION`	`RESET`
`RESPECT`	`RESTRICT`	`RETAIN`	`RETURN`
`RETURNING`	`REVOKE`	`RIGHT`	`ROLE`
`ROLES`	`ROLLBACK`	`ROTATE`	`ROUNDS`
`ROW`	`ROWS`	`RULES`	`SASL`
`SCALE`	`SCHEDULE`	`SCHEMA`	`SCHEMAS`
`SECOND`	`SECONDS`	`SECRET`	`SECRETS`
`SECURITY`	`SEED`	`SELECT`	`SEQUENCES`
`SERIALIZABLE`	`SERVER`	`SERVICE`	`SESSION`
`SET`	`SHARD`	`SHOW`	`SINK`
`SINKS`	`SIZE`	`SKEW`	`SMALLINT`
`SNAPSHOT`	`SOME`	`SOURCE`	`SOURCES`
`SQL`	`SSH`	`SSL`	`START`
`STDIN`	`STDOUT`	`STORAGE`	`STORAGECTL`
`STRATEGY`	`STRICT`	`STRING`	`STRONG`
`SUBSCRIBE`	`SUBSOURCE`	`SUBSOURCES`	`SUBSTRING`
`SUBTREE`	`SUPERUSER`	`SWAP`	`SYNTAX`
`SYSTEM`	`TABLE`	`TABLES`	`TAIL`
`TASK`	`TASKS`	`TEMP`	`TEMPORARY`
`TEXT`	`THEN`	`TICK`	`TIES`
`TIME`	`TIMEOUT`	`TIMESTAMP`	`TIMESTAMPTZ`
`TIMING`	`TO`	`TOKEN`	`TOPIC`
`TPCH`	`TRACE`	`TRAILING`	`TRANSACTION`
`TRANSACTIONAL`	`TRANSFORM`	`TRIM`	`TRUE`
`TUNNEL`	`TYPE`	`TYPES`	`UNBOUNDED`
`UNCOMMITTED`	`UNION`	`UNIQUE`	`UNKNOWN`
`UNNEST`	`UNTIL`	`UP`	`UPDATE`
`UPSERT`	`URL`	`USAGE`	`USER`
`USERNAME`	`USERS`	`USING`	`VALIDATE`
`VALUE`	`VALUES`	`VARCHAR`	`VARIADIC`
`VARYING`	`VERBOSE`	`VERSION`	`VIEW`
`VIEWS`	`WAIT`	`WARNING`	`WEBHOOK`
`WHEN`	`WHERE`	`WHILE`	`WINDOW`
`WIRE`	`WITH`	`WITHIN`	`WITHOUT`
`WORK`	`WORKERS`	`WORKLOAD`	`WRITE`
`YEAR`	`YEARS`	`YUGABYTE`	`ZONE`
`ZONES`