Create an index.

Defines a new index on a PostgreSQL table.

Usage

dbIndex(
  conn,
  name,
  colname,
  idxname,
  unique = FALSE,
  method = c("btree", "hash", "rtree", "gist"),
  display = TRUE,
  exec = TRUE
)

Arguments

conn

A connection object.

name

A character string specifying a PostgreSQL table name.

colname

A character string, or a character vector specifying the name of the column to which the key will be associated; alternatively, a character vector specifying the name of the columns to build the index.

idxname

A character string specifying the name of the index to be created. By default, this uses the name of the table (without the schema) and the name of the columns as follows: <table_name>_<column_names>_idx.

unique

Logical. Causes the system to check for duplicate values in the table when the index is created (if data already exist) and each time data is added. Attempts to insert or update data which would result in duplicate entries will generate an error.

method

The name of the method to be used for the index. Choices are "btree", "hash", "rtree", and "gist". The default method is "btree", although "gist" should be the index of choice for PostGIS spatial types (geometry, geography, raster).

display

Logical. Whether to display the query (defaults to TRUE).

exec

Logical. Whether to execute the query (defaults to TRUE).

Value

If exec = TRUE, returns (invisibly) TRUE if the index was successfully created.

See also

The PostgreSQL documentation: http://www.postgresql.org/docs/current/static/sql-createindex.html; the PostGIS documentation for GiST indexes: http://postgis.net/docs/using_postgis_dbmanagement.html#id541286

Author

Mathieu Basille mathieu@basille.org

Examples

## Examples use a dummy connection from DBI package
conn <- DBI::ANSI()

## GIST index
dbIndex(conn, name = c("sch", "tbl"), colname = "geom", method = "gist",
    exec = FALSE)
#> ℹ CREATE INDEX "tbl_geom_idx" ON "sch"."tbl" USING GIST ("geom");
#> ✖ Query not executed

## Regular BTREE index on multiple columns
dbIndex(conn, name = c("sch", "tbl"), colname = c("col1", "col2",
    "col3"), exec = FALSE)
#> ℹ CREATE INDEX "tbl_col1_col2_col3_idx" ON "sch"."tbl" ("col1", "col2", "col3");
#> ✖ Query not executed