MarkLogic 9 Product Documentation

SQL Data Modeling Guide — Chapter 8

Supported Statements

MarkLogic SQL does not support updates, so only the SQL statements in the following table are supported.

Supported Functions

MarkLogic supports the SQL functions in the SQL92 standard. In addition, MarkLogic supports SQL functions that are not part of the SQL92 standard, as shown in the table below. The SQL functions are listed along with the MarkLogic builtin functions that support them. The syntax for the SQL function is the same as that of the respective builtin function.

You can also call any MarkLogic builtin function in a SQL query, as described in Calling Built-in Functions from SQL.

Supported Types

The table below lists all of the supported SQL types in MarkLogic, along with the mapping from the SQL types to XML Schema (or MarkLogic) types. MarkLogic also supports a number of SQL type that go beyond those supported by the SQL92 standard, as well as some vendor specific types.

Limits on datatypes are not enforced. For example, if you enter DECIMAL(p,s), the precision and scale are ignored.

Search Grammar

The following table lists the search grammar that can be used by the MATCH operator.

* To use wildcards in a search expression, you must enable trailing wildcard searches and word lexicons (codepoint collation) on your database.

** Searches are constrained to the named field or column values. The field or column text must have the correct case. For example, 'Position:Manager' is not the same as 'position:Manager'. Because you cannot specify fields in a template view, you cannot MATCH on field names.

Examples

The following queries will work on both range views and template views:

SELECT * FROM employees WHERE employees MATCH 'Manager'

SELECT * FROM employees WHERE employees MATCH 'J*'

SELECT employeeid, firstname, lastname, position FROM employees 
    WHERE employees MATCH 'Steve OR John OR Goodall'

SELECT employeeid, firstname, lastname, position FROM employees 
    WHERE employees MATCH 'Steve AND Manager'

SELECT * from employees WHERE firstname MATCH 'John OR Jane' 
    AND lastname MATCH 'Lead'

The following queries will work on range views only:

SELECT * FROM employees WHERE employees MATCH 'position:Manager'

SELECT firstname, lastname FROM employees WHERE employees 
    MATCH 'employeeid LE 3'

SELECT employeeid, firstname, lastname, position FROM employees 
    WHERE firstname MATCH 'Steve OR John OR Goodall'

SELECT * FROM employees WHERE employees MATCH 'firstname:J*'

timezone or time zone

Sets the timezone offset to that for the given timezone name. The standard permitted formats and keywords can be used.

For example, to set the timezone to UTC, enter:

SET timezone 'UTC'

statement_timeout

Sets the timeout for statement execution (milliseconds).

For example:

SET statement_timeout 5000

lc_messages

Sets the locale for error messages.

For example:

SET lc_messages 'en_US'

lc_collate

Sets the default collation in the dynamic environment.

The form we will see from the Postgres client is:

SET lc_collate 'en_US.utf8'

This maps to the collation: http://marklogic.com/collation/en_US

You can also specify a full collation string:

SET lc_collation 'http://marklogic.com/collation/en_US/S1/MO'

lc_numeric

Sets the locale for formatting numeric values.

For example:

set lc_numeric 'de_DE'

lc_time

Sets the locale for formatting date/time values.

For example:

set lc_time 'en_US.UTF-8'

DateType

Sets the output format for dates.

For example:

SET DateType 'ISO'

extra_float_digits

Sets the number of digits displayed for floating point types.

For example:

SET extra_float_digits 2

client_encoding or NAMES

Declares the encoding of data coming from the client.

For example:

SET client_encoding 'UTF8'

SET NAMES is the standard syntax for the same thing.

SET NAMES 'UTF8'

coordinate_system

Set the default coordinate system for geospatial operations.

For example:

SET coordinate_system 'wgs84/double'

For more details, see The Governing Coordinate System and Controlling Coordinate System and Precision in the Search Developer's Guide.

SCHEMA or search_path

Sets the default schema referenced by names in SQL statements.

For example:

SET search_path 'main'

mls_default_xquery

Set the default XQuery version.

For example:

SET mls_default_xquery '1.0-ml'

mls_redundant_check

Enable or disable the redundant check on normal (on full-text) query constraints on rows. Value is 1 (enable) or 0 (disable). The default is 0.

For example:

SET mls_redundant_check 1; 
SELECT title, year FROM songs WHERE year=1991;