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
andA
-Z
), an underscore (_
), or any non-ASCII character. -
The remaining characters of an identifier must be ASCII letters (
a
-z
andA
-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
ADD
ADDED
ADDRESSES
AGGREGATE
AGGREGATION
ALIGNED
ALL
ALTER
ANALYSIS
AND
ANY
ARITY
ARN
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
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
CREATE
CREATECLUSTER
CREATEDB
CREATEROLE
CREATION
CROSS
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
DISCARD
DISK
DISTINCT
DOC
DOT
DOUBLE
DROP
EAGER
ELEMENT
ELSE
ENABLE
END
ENDPOINT
ENFORCED
ENVELOPE
ERROR
ERRORS
ESCAPE
ESTIMATE
EVERY
EXCEPT
EXCLUDE
EXECUTE
EXISTS
EXPECTED
EXPLAIN
EXPOSE
EXPRESSIONS
EXTERNAL
EXTRACT
FACTOR
FALSE
FAST
FEATURES
FETCH
FIELDS
FILE
FILTER
FIRST
FIXPOINT
FLOAT
FOLLOWING
FOR
FOREIGN
FORMAT
FORWARD
FROM
FULL
FULLNAME
FUNCTION
FUSION
GENERATOR
GRANT
GREATEST
GROUP
GROUPS
HAVING
HEADER
HEADERS
HISTORY
HOLD
HOST
HOUR
HOURS
HUMANIZED
HYDRATION
ID
IDENTIFIERS
IDS
IF
IGNORE
ILIKE
IMPLEMENTATIONS
IMPORTED
IN
INCLUDE
INDEX
INDEXES
INFO
INHERIT
INLINE
INNER
INPUT
INSERT
INSIGHTS
INSPECT
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
MESSAGE
METADATA
MINUTE
MINUTES
MODE
MONTH
MONTHS
MUTUALLY
MYSQL
NAME
NAMES
NATURAL
NEGATIVE
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
PHYSICAL
PLAN
PLANS
PORT
POSITION
POSTGRES
PRECEDING
PRECISION
PREFIX
PREPARE
PRIMARY
PRIVATELINK
PRIVILEGES
PROGRESS
PROTOBUF
PROTOCOL
PUBLIC
PUBLICATION
PUSHDOWN
QUERY
QUOTE
RAISE
RANGE
RATE
RAW
READ
READY
REAL
REASSIGN
RECURSION
RECURSIVE
REDACTED
REDUCE
REFERENCE
REFERENCES
REFRESH
REGEX
REGION
REGISTRY
RENAME
REOPTIMIZE
REPEATABLE
REPLACE
REPLAN
REPLICA
REPLICAS
REPLICATION
RESET
RESPECT
RESTRICT
RETAIN
RETURN
RETURNING
REVOKE
RIGHT
ROLE
ROLES
ROLLBACK
ROTATE
ROUNDS
ROW
ROWS
SASL
SCALE
SCHEDULE
SCHEMA
SCHEMAS
SECOND
SECONDS
SECRET
SECRETS
SECURITY
SEED
SELECT
SEQUENCES
SERIALIZABLE
SERVICE
SESSION
SET
SHARD
SHOW
SINK
SINKS
SIZE
SMALLINT
SNAPSHOT
SOME
SOURCE
SOURCES
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
TIMELINE
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
VERSION
VIEW
VIEWS
WAIT
WARNING
WEBHOOK
WHEN
WHERE
WINDOW
WIRE
WITH
WITHIN
WITHOUT
WORK
WORKERS
WORKLOAD
WRITE
YEAR
YEARS
YUGABYTE
ZONE
ZONES