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 AGGREGATE
AGGREGATION ALIGNED ALL ALTER
ANALYSIS AND ANY ARITY
ARN 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 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
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
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 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 PHYSICAL PLAN
PLANS POLICIES POLICY PORT
POSITION POSTGRES PRECEDING PRECISION
PREFIX PREPARE PRIMARY PRIVATELINK
PRIVILEGES PROGRESS PROTOBUF PROTOCOL
PUBLIC PUBLICATION PUSHDOWN QUERY
QUOTE RAISE RANGE RATE
RAW READ READY REAL
REASSIGN RECURSION RECURSIVE REDACTED
REDUCE REFERENCE REFERENCES REFRESH
REGEX REGION REGISTRY 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 SERVICE SESSION SET
SHARD SHOW SINK SINKS
SIZE SMALLINT SNAPSHOT SOME
SOURCE SOURCES 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 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 ↑