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