In Materialize, identifiers are used to refer to columns and database objects like sources, views, and indexes.

Naming restrictions

Materialize has the following naming restrictions for identifiers:

Position	Allowed Characters
First character	ASCII letters (a-z, A-Z), underscore (_), or any non-ASCII character
Remaining	ASCII letters (a-z, A-Z), digits (0-9), underscores (_), dollar sign ($), or any non-ASCII character

To override these restrictions, you can enclose the identifier in double quotes; e.g., "123_source" or "fun_source_@". Inside double quotes, characters are interpreted literally, except for the double-quote character itself. To include a double quote within a double-quoted identifier, escape it by writing two adjacent double quotes, as in “includes"“quote”.

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.

To avoid conflicts, double-quote all field names ("field_name") when working with case-sensitive sources.

Renaming restrictions

You cannot rename an item if any of the following are true:

• It is not uniquely qualified across all dependent references.

  For example, suppose you have:

  • Two views named v1 in different databases (d1 and d2) under the same schema name (s1), and
  • Both v1 views are referenced by another view.

  You may rename either v1 only if every dependent query that mentions both views fully qualifies all such references, e.g.:

  CREATE VIEW v2 AS
  SELECT *
  FROM d1.s1.v1
  JOIN d2.s1.v1
  ON d1.s1.v1.a = d2.s1.v1.a;
  

  If the two views were instead in schemas with distinct names, qualifying by schema alone would be sufficient (you would not need to include the database name).

• Renaming would cause any identifier collision with a dependent query.

  • An existing collision: a dependent query already uses the item’s current identifier for some database, schema, object, or column, so changing the item’s name would change how that identifier resolves.

    In the examples below, v1 cannot be renamed because dependent queries already use the identifier v1:

    • Any dependent query references a database, schema, or column that uses the same identifier.

      In the following examples, v1 could not be renamed:

      CREATE VIEW v3 AS
      SELECT *
      FROM v1
      JOIN v2
      ON v1.a = v2.v1
      

      CREATE VIEW v4 AS
      SELECT *
      FROM v1
      JOIN v1.v2
      ON v1.a = v2.a
      

  • A proposed-name collision: the new name matches any identifier referenced in a dependent query, whether that identifier is referenced explicitly or implicitly.

    Consider this example:

    CREATE VIEW v5 AS
    SELECT *
    FROM d1.s1.v2
    JOIN v1
    ON v1.a = v2.b
    

    You could not rename v1 to:

    • a
    • b
    • v2
    • s1
    • d1
    • materialize or public (implicitly referenced by materialize.public.v1 using the default database and schema)

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.

ABORT	ACCESS	ACTION
ADD	ADDED	ADDRESS	ADDRESSES
AFTER	AGGREGATE	AGGREGATION	ALIGNED
ALL	ALTER	ANALYSE	ANALYSIS
ANALYZE	AND	ANY	APPEND
APPLY	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	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
MATCHING	MATERIALIZE	MATERIALIZED	MAX
MECHANISMS	MEMBERSHIP	MEMORY	MESSAGE
METADATA	MINUTE	MINUTES	MOCK
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
REPLACEMENT	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	TEMP
TEMPORARY	TEST	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	UNIT	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