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

Arguments

:: (Has sch db schema, KnownSymbol ix)

=> QualifiedAlias sch ix

index alias

-> 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

Arguments

:: (Has sch db schema, KnownSymbol ix)

=> QualifiedAlias sch ix

index alias

-> 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.8.0.0-HHFIvalMWy63oPSAK2xG8g" '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.