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
).
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)
#> Query not executed:
#> CREATE INDEX "tbl_geom_idx" ON "sch"."tbl" USING GIST ("geom");
#> --
## Regular BTREE index on multiple columns
dbIndex(conn, name = c("sch", "tbl"), colname = c("col1", "col2",
"col3"), exec = FALSE)
#> Query not executed:
#> CREATE INDEX "tbl_col1_col2_col3_idx" ON "sch"."tbl" ("col1", "col2", "col3");
#> --