> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Basic Table Operations

> Create tables, search vectors, and append data in LanceDB.

Now that you've completed the [LanceDB quickstart](https://docs.lancedb.com/quickstart.md), you're ready to
explore some more table operations you'll typically need when working with LanceDB.

* **Ingest data into tables** from JSON data (and in Python, Pandas or Polars DataFrames)
* **Create empty tables** by defining explicit Arrow schemas
* **Vector similarity search** with filtering and projections
* **Filtered queries** that can operate on nested structs
* **Query Lance tables in DuckDB** via the Lance extension for SQL analytics (including joins)

> [!NOTE]
>
> This page uses **synchronous** Python snippets. If your Python app uses `asyncio`,
> the same flow works with `connect_async(...)` and `await`-based table/query calls.
> Use the example below as a template, and see [Quickstart](https://docs.lancedb.com/quickstart.md#python-sync-and-async-apis)
> for example snippets on both sync and async Python usage.

## Dataset

We'll work with this small dataset based on characters from the legends of Camelot. Note that
the `vector` column holds 4-dimensional embeddings, and the `stats` column is a nested struct
with several integer fields, indicating each character's attributes.

```json camelot.json icon="brackets-curly" expandable=true theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
[
  {
    "id": 1,
    "name": "King Arthur",
    "role": "King of Camelot",
    "description": "The legendary ruler of Camelot, wielder of Excalibur, and leader of the Knights of the Round Table.",
    "vector": [0.72, -0.28, 0.60, 0.86],
    "stats": { "strength": 2, "courage": 5, "magic": 1, "wisdom": 4 }
  },
  {
    "id": 2,
    "name": "Merlin",
    "role": "Wizard and Advisor",
    "description": "A powerful wizard and prophet who mentors Arthur and shapes the destiny of Camelot through magic and foresight.",
    "vector": [0.05, 0.88, 0.62, 0.85],
    "stats": { "strength": 2, "courage": 4, "magic": 5, "wisdom": 5 }
  },
  {
    "id": 3,
    "name": "Queen Guinevere",
    "role": "Queen of Camelot",
    "description": "Arthur's queen, admired for her grace and diplomacy, whose romances and loyalties influence Camelot's fate.",
    "vector": [0.22, -0.22, 0.42, 0.82],
    "stats": { "strength": 1, "courage": 3, "magic": 1, "wisdom": 4 }
  },
  {
    "id": 4,
    "name": "Sir Lancelot",
    "role": "Knight of the Round Table",
    "description": "Arthur's most skilled knight, famed for unmatched combat prowess and his tragic love for Queen Guinevere.",
    "vector": [0.86, -0.35, 0.38, 0.55],
    "stats": { "strength": 5, "courage": 5, "magic": 1, "wisdom": 3 }
  },
  {
    "id": 5,
    "name": "Sir Gawain",
    "role": "Knight of the Round Table",
    "description": "A noble and honorable knight known for his courtesy and his encounter with the Green Knight.",
    "vector": [0.82, -0.32, 0.52, 0.60],
    "stats": { "strength": 4, "courage": 5, "magic": 1, "wisdom": 4 }
  },
  {
    "id": 6,
    "name": "Sir Galahad",
    "role": "Knight of the Round Table",
    "description": "The purest and most virtuous knight, chosen to achieve the Holy Grail due to his unwavering spiritual purity.",
    "vector": [0.80, -0.20, 0.70, 0.78],
    "stats": { "strength": 4, "courage": 5, "magic": 2, "wisdom": 5 }
  },
  {
    "id": 7,
    "name": "Sir Percival",
    "role": "Knight of the Round Table",
    "description": "A loyal and innocent knight whose bravery and sincerity make him one of the key seekers of the Holy Grail.",
    "vector": [0.78, -0.36, 0.48, 0.52],
    "stats": { "strength": 4, "courage": 4, "magic": 1, "wisdom": 3 }
  },
  {
    "id": 8,
    "name": "Mordred",
    "role": "Traitor Knight",
    "description": "Arthur's treacherous son or nephew who ultimately rebels against him, leading to Camelot's downfall.",
    "vector": [0.68, -0.30, -0.65, 0.20],
    "stats": { "strength": 4, "courage": 2, "magic": 1, "wisdom": 2 }
  }
]
```

> [!NOTE]
>
> The `vector` arrays here are synthetic and for demonstration purposes only. In your real-world
> applications, you'd generate these vectors from the raw text fields using a suitable embedding model.

## Connect to a database

### Option 1: Direct table access

We start by connecting to a LanceDB database path. The example below uses a local path in LanceDB OSS.

import lancedb

uri = "ex\_lancedb"
db = lancedb.connect(uri)


import \* as lancedb from "@lancedb/lancedb";

async function connectExample(uri: string) \{
&#x20; const db = await lancedb.connect(uri);
&#x20; return db;
}


async fn connect\_example(uri: \&str) \{
&#x20;   let db = connect(uri).execute().await.unwrap();
&#x20;   let \_ = db;
}


You can also connect LanceDB OSS directly to object storage. For credentials, endpoints, and provider-specific options, see
[Configuring storage](https://docs.lancedb.com/storage/configuration.md).

### Option 2: Remote tables

If you're using LanceDB [Enterprise](https://docs.lancedb.com/enterprise.md), you can connect using a `db://` URI,
along with any necessary credentials. Simply replace the local path with a remote `uri`
that points to where your data is stored, and you're ready to go.

import lancedb



uri = "db://your-database-uri"
api\_key = "your-api-key"
region = "us-east-1"
host\_override = "https\://your-enterprise-endpoint.com"

db = lancedb.connect(
&#x20;   uri=uri,
&#x20;   api\_key=api\_key,
&#x20;   region=region,
&#x20;   host\_override=host\_override,
)


import \* as lancedb from "@lancedb/lancedb";



const uri = "db://your-database-uri";
const apiKey = "your-api-key";
const region = "us-east-1";
const hostOverride = "https\://your-enterprise-endpoint.com";

const db = await lancedb.connect(uri, \{
&#x20; apiKey,
&#x20; region,
&#x20; hostOverride,
});


use lancedb::connect;



let uri = "db://your-database-uri";
let api\_key = "your-api-key";
let region = "us-east-1";
let host\_override = "https\://your-enterprise-endpoint.com";


> [!NOTE]
>
> * When you connect to a remote URI (Enterprise), `open_table(...)` returns a *remote* table.
>   Remote tables support core operations (ingest, search, update, delete), but some convenience
>   methods for bulk data export are not available.
> * In the Python SDK, `table.to_arrow()` and `table.to_pandas()` are not implemented for remote tables.
>   To retrieve data, use search queries instead: `table.search(query).limit(n).to_arrow()`.

## Create a table and ingest data

### From JSON

LanceDB stores records in Lance tables. Each row is a record and each column
holds a field or related metadata. The simplest way to start is to obtain the source
data as a list of JSON records that includes a vector column and any metadata
fields you care about.

import json

import lancedb
import pandas as pd
import polars as pl
import pyarrow as pa


import \* as lancedb from "@lancedb/lancedb";
import \* as arrow from "apache-arrow";


use arrow\_array::types::Float32Type;
use arrow\_array::\{
&#x20;   FixedSizeListArray, Int8Array, Int16Array, RecordBatch, RecordBatchIterator, StringArray,
&#x20;   StructArray,
};
use arrow\_schema::\{DataType, Field, FieldRef, Schema};
use futures\_util::TryStreamExt;
use lancedb::database::CreateTableMode;
use lancedb::query::\{ExecutableQuery, QueryBase, Select};
use lancedb::\{connect, table::NewColumnTransform};


Load the data from the JSON file:

with open(data\_path, "r") as f:
&#x20;   data = json.load(f)


const data = JSON.parse(fs.readFileSync(dataPath, "utf-8"));


let data: Vec\<Character> =
&#x20;   serde\_json::from\_str(\&fs::read\_to\_string(camelot\_json\_path()).unwrap()).unwrap();


You can now create a LanceDB table from the loaded data. By default, creating a table with a name
that already exists raises an error. Use `mode="overwrite"` only when you intentionally want to
replace the existing table and its data, or use `exist_ok` / `existOk` when repeatable setup should
reuse the existing table instead of writing the supplied rows again.

table = db.create\_table("camelot", data=data, mode="overwrite")


let table = await db.createTable("camelot", data, \{
&#x20;  mode: "overwrite",
});


let mut table = db
&#x20;   .create\_table("camelot", characters\_to\_reader(schema.clone(), \&data))
&#x20;   .mode(CreateTableMode::Overwrite)
&#x20;   .execute()
&#x20;   .await
&#x20;   .unwrap();


> [!NOTE]
>
> If you want to avoid overwriting an existing table, omit the overwrite mode. For append-only
> ingestion into a table that already exists, open the table and call `add(...)` instead of
> `create_table(...)`. For repeatable setup with `exist_ok` / `existOk`, see
> [Handle existing tables](https://docs.lancedb.com/tables/create.md#handle-existing-tables).

For more ingestion patterns, including PyArrow tables, Python `pyarrow.dataset.Dataset` inputs,
empty tables, and Python `LanceModel` schemas with nested fields, see
[Ingesting data](https://docs.lancedb.com/tables/create.md).

### From Pandas DataFrames

Python Only

You can create LanceDB tables directly from [Pandas](https://pandas.pydata.org/) DataFrames. Simply
obtain the source data as a Pandas DataFrame, then create the table
and directly ingest to it.

pandas\_df = pd.DataFrame(data)
table\_pd = db.create\_table("camelot\_pd", data=pandas\_df, mode="overwrite")


### From Polars DataFrames

Python Only

You can also create LanceDB tables directly from [Polars](https://www.pola.rs/) DataFrames. Simply
obtain the source data as a Polars DataFrame, then create the table
and directly ingest to it.

polars\_df = pl.DataFrame(data)
table\_pl = db.create\_table("camelot\_pl", data=polars\_df, mode="overwrite")


### From an Arrow schema

If you want to create an *empty* table without any data -- say you want to
define the schema first and then incrementally add data later -- you can
do so by defining an Arrow schema explicitly.

schema = pa.schema(
&#x20;   \[
&#x20;       pa.field("id", pa.uint16()),
&#x20;       pa.field("name", pa.string()),
&#x20;       pa.field("role", pa.string()),
&#x20;       pa.field("description", pa.string()),
&#x20;       pa.field("vector", pa.list\_(pa.float32(), 4)),
&#x20;       pa.field(
&#x20;           "stats",
&#x20;           pa.struct(
&#x20;               \[
&#x20;                   pa.field("strength", pa.int8()),
&#x20;                   pa.field("courage", pa.int8()),
&#x20;                   pa.field("magic", pa.int8()),
&#x20;                   pa.field("wisdom", pa.int8()),
&#x20;               ]
&#x20;           ),
&#x20;       ),
&#x20;   ]
)
db.create\_table("camelot\_pa", schema=schema, mode="overwrite")


const schema = new arrow\.Schema(\[
&#x20; new arrow\.Field("id", new arrow\.Int16()),
&#x20; new arrow\.Field("name", new arrow\.Utf8()),
&#x20; new arrow\.Field("role", new arrow\.Utf8()),
&#x20; new arrow\.Field("description", new arrow\.Utf8()),
&#x20; new arrow\.Field(
&#x20;   "vector",
&#x20;   new arrow\.FixedSizeList(
&#x20;     4,
&#x20;     new arrow\.Field("item", new arrow\.Float32(), true),
&#x20;   ),
&#x20; ),
&#x20; new arrow\.Field(
&#x20;   "stats",
&#x20;   new arrow\.Struct(\[
&#x20;     new arrow\.Field("strength", new arrow\.Int8()),
&#x20;     new arrow\.Field("courage", new arrow\.Int8()),
&#x20;     new arrow\.Field("magic", new arrow\.Int8()),
&#x20;     new arrow\.Field("wisdom", new arrow\.Int8()),
&#x20;   ]),
&#x20; ),
]);
await db.createEmptyTable("camelot\_empty", schema, \{ mode: "overwrite" });


let schema = Arc::new(Schema::new(vec!\[
&#x20;   Field::new("id", DataType::Int16, false),
&#x20;   Field::new("name", DataType::Utf8, false),
&#x20;   Field::new("role", DataType::Utf8, false),
&#x20;   Field::new("description", DataType::Utf8, false),
&#x20;   Field::new(
&#x20;       "vector",
&#x20;       DataType::FixedSizeList(Arc::new(Field::new("item", DataType::Float32, true)), 4),
&#x20;       false,
&#x20;   ),
&#x20;   Field::new(
&#x20;       "stats",
&#x20;       DataType::Struct(arrow\_schema::Fields::from(vec!\[
&#x20;           Arc::new(Field::new("strength", DataType::Int8, false)),
&#x20;           Arc::new(Field::new("courage", DataType::Int8, false)),
&#x20;           Arc::new(Field::new("magic", DataType::Int8, false)),
&#x20;           Arc::new(Field::new("wisdom", DataType::Int8, false)),
&#x20;       ])),
&#x20;       false,
&#x20;   ),
]));
db.create\_empty\_table("camelot\_empty", schema)
&#x20;   .mode(CreateTableMode::Overwrite)
&#x20;   .execute()
&#x20;   .await
&#x20;   .unwrap();


Once the empty table is defined, LanceDB is ready to accept new data via
the `add` method, as shown in the next section.

> **Display table schema**
>
> LanceDB tables are type-aware, leveraging Apache Arrow under the hood.
> You can display a given table's schema using the `schema` property or
> method. For example, in Python, running `print(table.schema)` would show
> something like the following:
>
> ```txt expandable=true theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
> id: int64
> name: string
> role: string
> description: string
> vector: fixed_size_list<item: float>[4]
>   child 0, item: float
> stats: struct<courage: int64, magic: int64, strength: int64, wisdom: int64>
>   child 0, courage: int64
>   child 1, magic: int64
>   child 2, strength: int64
>   child 3, wisdom: int64
> ```

## Append data to a table

LanceDB tables are mutable, and you can append new records to existing tables.
If you're starting with a fresh session, connect to the database and open the
existing table named `camelot`.

table = db.open\_table("camelot")


table = await db.openTable("camelot");


table = db.open\_table("camelot").execute().await.unwrap();


Prepare the new records to add. Here, we add two new magical characters
via the `add` method. For the Rust snippet, you can find the helper functions in the
[code](https://github.com/lancedb/docs/blob/deploy-freeze/tests/rs/basic_usage.rs).

magical\_characters = \[
&#x20;   \{
&#x20;       "id": 9,
&#x20;       "name": "Morgan le Fay",
&#x20;       "role": "Sorceress",
&#x20;       "description": "A powerful enchantress, Arthur's half-sister, and a complex figure who oscillates between aiding and opposing Camelot.",
&#x20;       "vector": \[0.10, 0.84, 0.25, 0.70],
&#x20;       "stats": \{ "strength": 2, "courage": 3, "magic": 5, "wisdom": 4 }
&#x20;   },
&#x20;   \{
&#x20;       "id": 10,
&#x20;       "name": "The Lady of the Lake",
&#x20;       "role": "Mystical Guardian",
&#x20;       "description": "A mysterious supernatural figure associated with Avalon, known for giving Arthur the sword Excalibur.",
&#x20;       "vector": \[0.00, 0.90, 0.58, 0.88],
&#x20;       "stats": \{ "strength": 2, "courage": 3, "magic": 5, "wisdom": 5 }
&#x20;   }
]
table.add(magical\_characters)


const magicalCharacters = \[
&#x20; \{
&#x20;   id: 9,
&#x20;   name: "Morgan le Fay",
&#x20;   role: "Sorceress",
&#x20;   description:
&#x20;     "A powerful enchantress, Arthur's half-sister, and a complex figure who oscillates between aiding and opposing Camelot.",
&#x20;   vector: \[0.1, 0.84, 0.25, 0.7],
&#x20;   stats: \{ strength: 2, courage: 3, magic: 5, wisdom: 4 },
&#x20; },
&#x20; \{
&#x20;   id: 10,
&#x20;   name: "The Lady of the Lake",
&#x20;   role: "Mystical Guardian",
&#x20;   description:
&#x20;     "A mysterious supernatural figure associated with Avalon, known for giving Arthur the sword Excalibur.",
&#x20;   vector: \[0.0, 0.9, 0.58, 0.88],
&#x20;   stats: \{ strength: 2, courage: 3, magic: 5, wisdom: 5 },
&#x20; },
];
await table.add(magicalCharacters);


let magical\_characters = vec!\[
&#x20;   Character \{
&#x20;       id: 9,
&#x20;       name: "Morgan le Fay".to\_string(),
&#x20;       role: "Sorceress".to\_string(),
&#x20;       description: "A powerful enchantress, Arthur's half-sister, and a complex figure who oscillates between aiding and opposing Camelot.".to\_string(),
&#x20;       vector: \[0.10, 0.84, 0.25, 0.70],
&#x20;       stats: Stats \{
&#x20;           strength: 2,
&#x20;           courage: 3,
&#x20;           magic: 5,
&#x20;           wisdom: 4,
&#x20;       },
&#x20;   },
&#x20;   Character \{
&#x20;       id: 10,
&#x20;       name: "The Lady of the Lake".to\_string(),
&#x20;       role: "Mystical Guardian".to\_string(),
&#x20;       description: "A mysterious supernatural figure associated with Avalon, known for giving Arthur the sword Excalibur.".to\_string(),
&#x20;       vector: \[0.00, 0.90, 0.58, 0.88],
&#x20;       stats: Stats \{
&#x20;           strength: 2,
&#x20;           courage: 3,
&#x20;           magic: 5,
&#x20;           wisdom: 5,
&#x20;       },
&#x20;   },
];
table
&#x20;   .add(characters\_to\_reader(camelot\_schema(), \&magical\_characters))
&#x20;   .execute()
&#x20;   .await
&#x20;   .unwrap();


We now have two new records in the table. Let's begin to query our data!

## Vector search

It's straightforward to run vector similarity search in LanceDB. Let's answer
some questions about the data using vector search with projections (returning only
the desired columns).

> Q1: *Who are the characters similar to "wizard"?*

\# Who are the characters similar to  "wizard"?
query\_vector\_1 = \[0.03, 0.85, 0.61, 0.90]
r1 = (
&#x20;   table.search(query\_vector\_1)
&#x20;   .limit(5)
&#x20;   .select(\["name", "role", "description"])
&#x20;   .to\_polars()
)
print(r1)


// Who are the characters similar to  "wizard"?
const queryVector1 = \[0.03, 0.85, 0.61, 0.9];
const r1 = await table
&#x20; .search(queryVector1)
&#x20; .limit(5)
&#x20; .select(\["name", "role", "description"])
&#x20; .toArray();
console.log(r1);


// Who are the characters similar to  "wizard"?
let query\_vector\_1 = \[0.03, 0.85, 0.61, 0.90];
let r1 = table
&#x20;   .query()
&#x20;   .nearest\_to(\&query\_vector\_1)
&#x20;   .unwrap()
&#x20;   .limit(5)
&#x20;   .select(Select::Columns(vec!\[
&#x20;       "name".to\_string(),
&#x20;       "role".to\_string(),
&#x20;       "description".to\_string(),
&#x20;   ]))
&#x20;   .execute()
&#x20;   .await
&#x20;   .unwrap()
&#x20;   .try\_collect::\<Vec\<\_>>()
&#x20;   .await
&#x20;   .unwrap();
println!("\{r1:?}");


| name | role | description |
| - | - | - |
| Merlin | Wizard and Advisor | A powerful wizard and prophet |
| The Lady of the Lake | Mystical Guardian | A mysterious supernatural figu… |
| Morgan le Fay | Sorceress | A powerful enchantress, Arthur… |
| Queen Guinevere | Queen of Camelot | Arthur's queen, admired for he… |
| Sir Galahad | Knight of the Round Table | The purest and most virtuous k… |

We have Merlin, The Lady of the Lake, and Morgan le Fay in the top results, who
all have magical abilities.

Next, let's try to answer a different question that involves vector search while
filtering on a nested struct field. Filtering is done using the `where` method,
into which you can pass SQL-like expressions.

> Q2: *Who are the characters similar to "wizard" with high magic stats?*

\# Who are the characters with high magic stats?
query\_vector\_2 = \[0.03, 0.85, 0.61, 0.90]
r2 = (
&#x20;   table.search(query\_vector\_2)
&#x20;   .where("stats.magic > 3")
&#x20;   .select(\["name", "role", "description"])
&#x20;   .limit(5)
&#x20;   .to\_polars()
)
print(r2)


// Who are the characters similar to "wizard" with high magic stats?
const queryVector2 = \[0.03, 0.85, 0.61, 0.9];
const r2 = await table
&#x20; .search(queryVector2)
&#x20; .where("stats.magic > 3")
&#x20; .select(\["name", "role", "description"])
&#x20; .limit(5)
&#x20; .toArray();
console.log(r2);


// Who are the characters similar to "wizard" with high magic stats?
let query\_vector\_2 = \[0.03, 0.85, 0.61, 0.90];
let r2 = table
&#x20;   .query()
&#x20;   .nearest\_to(\&query\_vector\_2)
&#x20;   .unwrap()
&#x20;   .only\_if("stats.magic > 3")
&#x20;   .select(Select::Columns(vec!\[
&#x20;       "name".to\_string(),
&#x20;       "role".to\_string(),
&#x20;       "description".to\_string(),
&#x20;   ]))
&#x20;   .limit(5)
&#x20;   .execute()
&#x20;   .await
&#x20;   .unwrap()
&#x20;   .try\_collect::\<Vec\<\_>>()
&#x20;   .await
&#x20;   .unwrap();
println!("\{r2:?}");


| name | role | description |
| - | - | - |
| Merlin | Wizard and Advisor | A powerful wizard and prophet |
| The Lady of the Lake | Mystical Guardian | A mysterious supernatural figu… |
| Morgan le Fay | Sorceress | A powerful enchantress, Arthur… |

Only three characters have magical abilities greater than 3. Merlin is
clearly the most magical of them all!

## Filtered search

You can also run traditional analytics-style search queries that do not
involve vectors. For example, let's find the strongest characters in
the dataset. In the query below, we leave the `search` method empty to indicate
that we don't want to use any vector for similarity search (in TypeScript/Rust,
use `query()` instead), and use the `where` method to filter on the `strength` field.

> Q3: *Who are the strongest characters?*

\# Who are the strongest characters?
r3 = (
&#x20;   table.search()
&#x20;   .where("stats.strength > 3")
&#x20;   .select(\["name", "role", "description"])
&#x20;   .limit(5)
&#x20;   .to\_polars()
)
print(r3)


// Who are the strongest characters?
const r3 = await table
&#x20; .query()
&#x20; .where("stats.strength > 3")
&#x20; .select(\["name", "role", "description"])
&#x20; .limit(5)
&#x20; .toArray();
console.log(r3);


// Who are the strongest characters?
let r3 = table
&#x20;   .query()
&#x20;   .only\_if("stats.strength > 3")
&#x20;   .select(Select::Columns(vec!\[
&#x20;       "name".to\_string(),
&#x20;       "role".to\_string(),
&#x20;       "description".to\_string(),
&#x20;   ]))
&#x20;   .limit(5)
&#x20;   .execute()
&#x20;   .await
&#x20;   .unwrap()
&#x20;   .try\_collect::\<Vec\<\_>>()
&#x20;   .await
&#x20;   .unwrap();
println!("\{r3:?}");


| name | role | description |
| - | - | - |
| Sir Galahad | Knight of the Round Table | The purest and most virtuous k… |
| Sir Gawain | Knight of the Round Table | A noble and honorable knight k… |
| Sir Percival | Knight of the Round Table | A loyal and innocent knight wh… |
| Sir Lancelot | Knight of the Round Table | Arthur's most skilled knight, … |
| Mordred | Traitor Knight | Arthur's treacherous son or ne… |

Clearly, the strongest characters are all Knights of the Round Table!

> [!NOTE]
>
> Need SQL analytics like filters, aggregations, or joins on Lance tables? Use the DuckDB
> Lance extension to query Lance tables directly with SQL. See the
> [DuckDB integration guide](https://docs.lancedb.com/integrations/data/duckdb.md).

## Add column

We can also add new columns to an existing LanceDB table using the `add_columns` method.
For this example, let's add a new float column named `power` that shows the average
of each character's strength, courage, magic, and wisdom stats.

table.add\_columns(
&#x20;   \{
&#x20;       "power": "cast(((stats.strength + stats.courage + stats.magic + stats.wisdom) / 4.0) as float)"
&#x20;   }
)


await table.addColumns(\[
&#x20; \{
&#x20;   name: "power",
&#x20;   valueSql:
&#x20;     "cast(((stats.strength + stats.courage + stats.magic + stats.wisdom) / 4.0) as float)",
&#x20; },
]);


table
&#x20;   .add\_columns(
&#x20;       NewColumnTransform::SqlExpressions(vec!\[(
&#x20;           "power".to\_string(),
&#x20;           "cast(((stats.strength + stats.courage + stats.magic + stats.wisdom) / 4.0) as float)"
&#x20;               .to\_string(),
&#x20;       )]),
&#x20;       None,
&#x20;   )
&#x20;   .await
&#x20;   .unwrap();


The example above sums up the individual stats and divides by 4 to compute the average.
The resulting average total stats is cast to an Arrow float type under the hood for the
Lance table.

We can display the results of this column in descending order of power.

> Q4: *Who are the most powerful characters?*

\# Who are the strongest characters?
r4 = (
&#x20;   table.search()
&#x20;   .select(\["name", "role", "description", "power"])
&#x20;   .to\_polars()
)
print(r4)


// Who are the strongest characters?
const r4 = await table
&#x20; .query()
&#x20; .select(\["name", "role", "description", "power"])
&#x20; .toArray();
console.log(r4);


// Who are the strongest characters?
let r4 = table
&#x20;   .query()
&#x20;   .select(Select::Columns(vec!\[
&#x20;       "name".to\_string(),
&#x20;       "role".to\_string(),
&#x20;       "description".to\_string(),
&#x20;       "power".to\_string(),
&#x20;   ]))
&#x20;   .execute()
&#x20;   .await
&#x20;   .unwrap()
&#x20;   .try\_collect::\<Vec\<\_>>()
&#x20;   .await
&#x20;   .unwrap();
println!("\{r4:?}");


Note that LanceDB's `where` only filters rows, but doesn't sort them by applying an `ORDER BY`
clause that you may be used to when working with SQL databases.

You can also sort the results after converting them to a Polars DataFrame.
In TypeScript/Rust, you can sort the
returned array in application code.

\# Sort Polars DataFrame by power in descending order
print(r4.sort("power", descending=True).limit(5))


| name | role | description | power |
| - | - | - | - |
| Merlin | Wizard and Advisor | A powerful wizard and prophet … | 4.0 |
| Sir Galahad | Knight of the Round Table | The purest and most virtuous k… | 4.0 |
| The Lady of the Lake | Mystical Guardian | A mysterious supernatural figu… | 3.75 |
| Sir Lancelot | Knight of the Round Table | Arthur's most skilled knight, … | 3.5 |
| Sir Gawain | Knight of the Round Table | A noble and honorable knight k… | 3.5 |

Merlin and Sir Galahad are the most powerful characters when considering the average of
all their abilities! Sir Lancelot and the Lady of the Lake follow closely behind.

For column renames, type changes, nullability changes, and grouping multiple schema changes into
one operation, see [Schema and data evolution](https://docs.lancedb.com/tables/schema.md).

## Delete data

You can delete rows from a LanceDB table using the `delete` method with
a filtering expression.

Say we want to remove Mordred, the traitor knight, from our table.

table.delete('role = "Traitor Knight"')


await table.delete('role = "Traitor Knight"');


table.delete("role = 'Traitor Knight'").await.unwrap();


This will delete the row(s) where the `role` value matches "Traitor Knight".
You can verify that the row has been deleted by running a search query again,
and confirming that Mordred no longer appears in the results.

## Drop column

If you want to remove or delete a column from an existing LanceDB table, you can use
the `drop_columns` method.

table.drop\_columns(\["power"])


await table.dropColumns(\["power"]);


table.drop\_columns(&\["power"]).await.unwrap();


This will remove the `power` column we added earlier from the table schema.

## Drop table

If you want to delete an entire table from the database, you can use the
`drop_table` method.

db.drop\_table("camelot")


await db.dropTable("camelot");


db.drop\_table("camelot", &\[]).await.unwrap();


This will delete the `camelot` table from the connected LanceDB database.

> [!NOTE]
>
> See the full code for these examples (including helper functions) in the
> `basic_usage` file for the appropriate client language in the
> [docs repo](https://github.com/lancedb/docs/tree/deploy-freeze/tests).

## What about vector indexes?

LanceDB supports vector indexes to speed up similarity search on large datasets.
For datasets up to a few hundred thousand vectors, LanceDB's highly efficient kNN
(brute-force) retrieval of nearest neighbors is often sufficient. As your dataset
grows larger, you can create vector indexes on your vector columns to accelerate
search. See the [indexing](https://docs.lancedb.com/indexing.md) documentation for details on how to create and use
vector indexes in LanceDB.

## What's next?

Now that you've learned the basics of creating tables, adding data, running
vector search, and modifying table schemas, you're ready to explore more
advanced features of LanceDB. Below are some suggested next pages.

> **[Ingesting data](https://docs.lancedb.com/tables/create.md)**
>
> Learn the different approaches to creating and ingesting data into LanceDB tables from various sources.

> **[Update and modify tables](https://docs.lancedb.com/tables/update.md)**
>
> Learn how to update and modify existing LanceDB tables and their data.

> **[Schema & data evolution](https://docs.lancedb.com/tables/schema.md)**
>
> Understand how to evolve your table schemas and data over time with LanceDB.

> **[Versioning and time travel](https://docs.lancedb.com/tables/versioning.md)**
>
> Explore LanceDB's built-in table versioning and time travel capabilities.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.