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 ANALYSIS 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
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 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
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 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
Back to top ↑