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