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 NAMESPACE
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 ↑