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
NFC NFD NFKC NFKD
NO NOCREATECLUSTER NOCREATEDB NOCREATEROLE
NODE NOINHERIT NOLOGIN NON
NONE NORMALIZE 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
ZONE ZONES
Back to top ↑