Squeal.PostgreSQL.Definition.Index

create and drop indexes

Create

createIndex

Arguments

:: (Has sch db schema, Has tab schema (Table table), KnownSymbol ix)

=> Alias ix

index alias

-> QualifiedAlias sch tab

table alias

-> IndexMethod method

index method

-> [SortExpression Ungrouped '[] '[] db '[] '[tab ::: TableToRow table]]

sorted columns

-> Definition db (Alter sch (Create ix (Index method) schema) db)

Create an index.

>>> :{
type Table = '[] :=>
  '[ "a" ::: 'NoDef :=> 'Null 'PGint4
   , "b" ::: 'NoDef :=> 'Null 'PGfloat4 ]
:}

>>> :{
let
  setup :: Definition (Public '[]) (Public '["tab" ::: 'Table Table, "ix" ::: 'Index 'Btree])
  setup =
    createTable #tab (nullable int `as` #a :* nullable real `as` #b) Nil >>>
    createIndex #ix #tab btree [#a & AscNullsFirst, #b & AscNullsLast]
in printSQL setup
:}
CREATE TABLE "tab" ("a" int NULL, "b" real NULL);
CREATE INDEX "ix" ON "tab" USING btree (("a") ASC NULLS FIRST, ("b") ASC NULLS LAST);

createIndexIfNotExists

Arguments

:: (Has sch db schema, Has tab schema (Table table), KnownSymbol ix)

=> Alias ix

index alias

-> QualifiedAlias sch tab

table alias

-> IndexMethod method

index method

-> [SortExpression Ungrouped '[] '[] db '[] '[tab ::: TableToRow table]]

sorted columns

-> Definition db (Alter sch (CreateIfNotExists ix (Index method) schema) db)

Create an index if it doesn't exist.

Drop

dropIndex :: (Has sch db schema, KnownSymbol ix) => QualifiedAlias sch ix -> Definition db (Alter sch (DropSchemum ix Index schema) db)

Drop an index.

>>> printSQL (dropIndex #ix :: Definition (Public '["ix" ::: 'Index 'Btree]) (Public '[]))
DROP INDEX "ix";

dropIndexIfExists :: (Has sch db schema, KnownSymbol ix) => QualifiedAlias sch ix -> Definition db (Alter sch (DropSchemumIfExists ix Index schema) db)

Drop an index if it exists.

Index Method

newtype IndexMethod ty

PostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST, GIN and BRIN. Each index type uses a different algorithm that is best suited to different types of queries.

Constructors

UnsafeIndexMethod

Fields

• renderIndexMethod :: ByteString

Instances

Eq (IndexMethod ty)

Defined in Squeal.PostgreSQL.Definition.Index

Methods

(==) :: IndexMethod ty -> IndexMethod ty -> Bool

(/=) :: IndexMethod ty -> IndexMethod ty -> Bool

Ord (IndexMethod ty)

Defined in Squeal.PostgreSQL.Definition.Index

Methods

compare :: IndexMethod ty -> IndexMethod ty -> Ordering

(<) :: IndexMethod ty -> IndexMethod ty -> Bool

(<=) :: IndexMethod ty -> IndexMethod ty -> Bool

(>) :: IndexMethod ty -> IndexMethod ty -> Bool

(>=) :: IndexMethod ty -> IndexMethod ty -> Bool

max :: IndexMethod ty -> IndexMethod ty -> IndexMethod ty

min :: IndexMethod ty -> IndexMethod ty -> IndexMethod ty

Show (IndexMethod ty)

Defined in Squeal.PostgreSQL.Definition.Index

Methods

showsPrec :: Int -> IndexMethod ty -> ShowS

show :: IndexMethod ty -> String

showList :: [IndexMethod ty] -> ShowS

Generic (IndexMethod ty)

Defined in Squeal.PostgreSQL.Definition.Index

Associated Types

type Rep (IndexMethod ty) :: Type -> Type

Methods

from :: IndexMethod ty -> Rep (IndexMethod ty) x

to :: Rep (IndexMethod ty) x -> IndexMethod ty

RenderSQL (IndexMethod ty)

Defined in Squeal.PostgreSQL.Definition.Index

Methods

renderSQL :: IndexMethod ty -> ByteString

type Rep (IndexMethod ty)

Defined in Squeal.PostgreSQL.Definition.Index

type Rep (IndexMethod ty) = D1 (MetaData "IndexMethod" "Squeal.PostgreSQL.Definition.Index" "squeal-postgresql-0.6.0.0-56EGnKmL3FAInHQPvmCKa1" True) (C1 (MetaCons "UnsafeIndexMethod" PrefixI True) (S1 (MetaSel (Just "renderIndexMethod") NoSourceUnpackedness NoSourceStrictness DecidedLazy) (Rec0 ByteString)))

btree :: IndexMethod Btree

B-trees can handle equality and range queries on data that can be sorted into some ordering.

hash :: IndexMethod Hash

Hash indexes can only handle simple equality comparisons.

gist :: IndexMethod Gist

GiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented.

spgist :: IndexMethod Spgist

SP-GiST indexes, like GiST indexes, offer an infrastructure that supports various kinds of searches.

gin :: IndexMethod Gin

GIN indexes are “inverted indexes” which are appropriate for data values that contain multiple component values, such as arrays.

brin :: IndexMethod Brin

BRIN indexes (a shorthand for Block Range INdexes) store summaries about the values stored in consecutive physical block ranges of a table.