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
CATALOG 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 CREDENTIAL 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
ICEBERG 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 SCOPE
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 WAREHOUSE WARNING
WEBHOOK WHEN WHERE WHILE
WINDOW WIRE WITH WITHIN
WITHOUT WORK WORKERS WORKLOAD
WRITE YEAR YEARS YUGABYTE
ZONE ZONES
Back to top ↑