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	ACCOUNT
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	AUTO	AVAILABILITY
AVRO	AWS	BATCH	BEGIN
BETWEEN	BIGINT	BILLED	BODY
BOOLEAN	BOTH	BPCHAR	BROKEN
BROKER	BROKERS	BY	BYTES
CANCELLATION	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	CONSTRAINTS	COPY	CORRELATED
COUNT	COUNTER	CPU	CREATE
CREATECLUSTER	CREATEDB	CREATENETWORKPOLICY	CREATEROLE
CREATION	CREDENTIAL	CROSS	CSE
CSV	CTE	CURRENT	CURSOR
DATABASE	DATABASES	DATUMS	DAY
DAYS	DEALLOCATE	DEBEZIUM	DEBUG
DEBUGGING	DEC	DECIMAL	DECLARE
DECODING	DECORRELATED	DEFAULT	DEFAULTS
DELEGATION	DELETE	DELIMITED	DELIMITER
DELTA	DESC	DETAILS	DIRECTION
DISCARD	DISK	DISTINCT	DOC
DOT	DOUBLE	DROP	DURATION
EAGER	ELEMENT	ELSE	ENABLE
END	ENDPOINT	ENFORCED	ENVELOPE
EQUIVALENCES	ERROR	ERRORS	ESCAPE
ESTIMATE	EVERY	EXCEPT	EXCLUDE
EXECUTE	EXISTS	EXPECTED	EXPERIMENTAL
EXPLAIN	EXPOSE	EXPRESSIONS	EXTERNAL
EXTRACT	FACTOR	FALSE	FAST
FEATURES	FETCH	FIELDS	FILE
FILES	FILTER	FIRST	FIXED
FIXPOINT	FLOAT	FOLLOWING	FOR
FOREIGN	FORMAT	FORWARD	FROM
FULL	FULLNAME	FUNCTION	FUSION
GCP	GENERATOR	GLUE	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
LINGER	LIST	LOAD	LOCAL
LOCALLY	LOG	LOGICAL	LOGIN
LOWERING	MANAGED	MANUAL	MAP
MARKETING	MATCHING	MATERIALIZE	MATERIALIZED
MAX	MECHANISMS	MEMBERSHIP	MEMORY
MESSAGE	METADATA	METRIC	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
OAUTH2	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	PROVIDER	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	SCALING
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