# Table {#table}

Tables are database objects that hold all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record. For each column in a table, the column name and data type must be specified. You can insert, delete, update or query tables in DolphinDB.

Tables can be divided into the following categories:

-   In-memory tables

<table id="table_my4_tbq_5zb"><tbody><tr><td>

**Table**

</td><td>

**Function**

</td><td>

**Description**

</td></tr><tr><td>

In-memory table

</td><td>

[table](../../../Functions/t/table.md)

</td><td>

Regular in-memory table for temporary data storage

</td></tr><tr><td>

Partitioned in-memory table

</td><td>

[createPartitionedTable](../../../Functions/c/createPartitionedTable.md)

</td><td>

Enables parallel computing on large datasets through data partitioning

</td></tr><tr><td>

Indexed table

</td><td>

[indexedTable](../../../Functions/i/indexedTable.md)

</td><td>

Enables fast storage and retrieval of key-value pairs, optimized for range queries

</td></tr><tr><td>

Keyed table

</td><td>

[keyedTable](../../../Functions/k/keyedTable.md)

</td><td>

Enables fast storage and retrieval of key-value pairs, optimized for point queries

</td></tr><tr><td>

Stream table

</td><td>

[streamTable](../../../Functions/s/streamTable.md)

 [haStreamTable](../../../Functions/h/haStreamTable.md)

</td><td>

Enables real-time ingestion and storage of streaming data

</td></tr><tr><td>

MVCC table

</td><td>

[mvccTable](../../../Functions/m/mvccTable.md)

</td><td>

Optimized for read-intensive workloads using multi-version concurrency control

</td></tr><tr><td>

Cached table

</td><td>

[cachedTable](../../../Functions/c/cachedTable.md)

</td><td>

Enables caching and scheduled updates for data which does not require immediate synchronization

</td></tr><tr><td>

IMOLTP table

</td><td>

[createIMOLTPTable](../../../Functions/c/createIMOLTPTable.md)

</td><td>

Enables primary and secondary keys, optimized for low-latency queries

</td></tr></tbody>
</table>-   DFS tables

<table><tbody><tr><td>

**Table**

</td><td>

**Function**

</td><td>

**Description**

</td></tr><tr><td>

Partitioned DFS table

</td><td>

[createPartitionedTable](../../../Functions/c/createPartitionedTable.md)

</td><td>

Regular DFS table for distributed storage and parallel processing of large datasets

</td></tr><tr><td>

Dimension table

</td><td>

[createDimensionTable](../../../Functions/c/createPartitionedTable.md)

</td><td>

Non-partitioned DFS table for storing small datasets that are infrequently updated

</td></tr></tbody>
</table>
## Creating tables {#creating-tables}

Before version 1.30.14, column names can only use letters, digits or underscores \(\_\), and must start with letters.

Before version 2.00.2, column names can only use letters, digits or underscores \(\_\), and must start with letters.

Since version 1.30.14, column names generated by [pivot by](../../SQLStatements/pivotBy.md) or [addColumn](../../../Functions/a/addColumn.md) can contain special characters or start with digits.

Since version 2.00.2, column names generated by [pivot by](../../SQLStatements/pivotBy.md) or [addColumn](../../../Functions/a/addColumn.md) can contain special characters or start with digits.

Note:

-   When a column name containing special characters or starting with digits is referenced in a SQL statement, enclose it in double quotes and use an underscore as an identifier before it, for example: \_"IBM.N", \_"000001.SH";

-   Column names containing special characters or starting with digits can also be accessed by tb\["col"\] or tb."col".

-   If a column generated by pivot by is composed of null values, it is named as "NULL". To refer to the column, use \_"NULL" to follow the first note mentioned above.


To allow for code compatibility with previous versions, the configuration parameter removeSpecialCharInColumnName is introduced. The default value is false, indicating that column names can contain special characters. Set to true to be compatible with previous versions.

Example 1. 3 ways to create an in-memory table

\(1\) table\(X as col, \[X1 as col1\], \[X2 as col2\], .....\)

```
t0=table(1 2 3 as a, `x`y`z as b, 10.8 7.6 3.5 as c);
t0;
```

|a|b|c|
|---|---|---|
|1|x|10.8|
|2|y|7.6|
|3|z|3.5|

\(2\) table\(X, \[X1\], \[X2\], .....\)

```
x=1 2 3;
y=4 5 6;
t1=table(x,y);
t1;
```

|x|y|
|---|---|
|1|4|
|2|5|
|3|6|

\(3\) table\(capacity:size, colNames, colTypes\)

```
t2=table(200:10, `name`id`value, [STRING,INT,DOUBLE]);
t2;
```

|name|id|value|
|----|---|-----|
||0|0|
||0|0|
||0|0|
||0|0|
||0|0|
||0|0|
||0|0|
||0|0|
||0|0|
||0|0|

Example 2. Create and access tables whose column names contain special characters

```
t3=table(1 2 3 as `_a, 4 5 6 as "2 ab");
t3;
```

|\_a|2 ab|
|---|----|
|1|4|
|2|5|
|3|6|

```
select _"_a" as "_aa", _"2 ab" as "2ab" from t3;
```

|\_aa|2ab|
|----|---|
|1|4|
|2|5|
|3|6|

Example 3. Convert vectors/matrices into tables

```
a=([1,2],[3.2,4.3],[2019.01.02,2019.05.03]);
table(a);
```

|C0|C1|C2|
|---|---|---|
|1|3.2|2019.01.02|
|2|4.3|2019.05.03|

```
m=1..12$3:4;
table(m);
```

|C0|C1|C2|C3|
|---|---|---|---|
|1|4|7|10|
|2|5|8|11|
|3|6|9|12|

## Querying Tables {#topic_bjy_czz_nfc}

Execute the [rows](../../../Functions/r/rows.md) or [size](../../../Functions/s/size.md) function to view the number of rows.

``` {#codeblock_mgk_4zz_nfc}
rows(t1);
// output: 3
size(t1);
// output: 3
```

Execute the [cols](../../../Functions/c/cols.md) function to view the number of columns.

``` {#codeblock_cgv_pzz_nfc}
cols(t1);
// output: 2
```

Execute the [schema](../../../Functions/s/schema.md) function to view the table schema.

``` {#codeblock_z2h_rzz_nfc}
schema(t1);
/*
chunkPath->
partitionColumnIndex->-1
colDefs->
name typeString typeInt extra comment
---- ---------- ------- ----- -------
x    INT        4                    
y    INT        4                    
*/
```

## Accessing tables {#accessing-tables}

Example 1. Use `<tableName>([X],[Y])` to access tables, where X and Y are scalars/pairs for selecting rows and columns respectively. The range of table indexing starts from 0 and is upper bound exclusive. For examples, 1:3 means 1 and 2; 2:0 indicates 1 and 0.

```
t1[1:3, 1];
```

|y|
|---|
|5|
|6|

```
t1[,t1.columns()-1];
```

|y|
|---|
|4|
|5|
|6|

```
t1.keys();
// output
["x","y"]

t1.values();
// output: ([1,2,3],[4,5,6])
```

Example 2. Access tables with conditions specified.

```
t1[t1.x>2];      // retrieve the records where x>2
or
t1[t1[`x]>2];
```

|x|y|
|---|---|
|3|6|

```
t1[t1.x in (1 3)];       // retrieve the records where x=1 or x=3
```

|x|y|
|---|---|
|1|4|
|3|6|

```
t1[t1.x>1 && t1.y<6];       // retrieve the records where x>1 and y<6
```

|x|y|
|---|---|
|2|5|

## Updating tables {#updating-tables}

Example 1. Update in-memory tables with conditions specified.

```
t1[`x, t1[`x] < 2] = 3
or
t1[`x, <x < 2>] = 3
```

|x|y|
|---|---|
|3|4|
|2|5|
|3|6|

Example 2. Create an empty table and then update it

```
t = table(100:0, `x`y`z, `STRING`DATE`DOUBLE);
//Create a table with columns x, y, and z, and column types STRING, DATE, and DOUBLE. Its initial capacity is 100 and size is 0.

t;
```

|x|y|z|
|---|---|---|
||||

```
insert into t values(take(`MS,3),2010.01.01 2010.01.02 2010.01.03, 1 2 3);
t;
```

|x|y|z|
|---|---|---|
|MS|2010.01.01|1|
|MS|2010.01.02|2|
|MS|2010.01.03|3|

To add or update table columns:

```
t=table(1 2 3 as id, 4 5 6 as value);
t;
```

|id|value|
|---|-----|
|1|4|
|2|5|
|3|6|

```
t[`id`name]=[7 8 9, `IBM`MSFT`GOOG];
t;
```

|id|value|name|
|---|-----|----|
|7|4|IBM|
|8|5|MSFT|
|9|6|GOOG|

Example 3. Update tables with SQL update clause

```
n=10
colNames = `time`sym`id
colTypes = [DATE,SYMBOL,INT]
t = table(n:0, colNames, colTypes)
insert into t values(2020.01.05 13:30:10.008, `A1, 1)
insert into t values(2020.01.06 13:30:10.008, `A2, 2)
// When the data types of inserted temporal values do not match the column types, the inserted data is automatically converted.
insert into t values(2020.06M, `A3, 3)

update t set time=2020.06.13 13:30:10 where sym=`A1
select * from t
```

|time|sym|id|
|----|---|---|
|2020.06.13|A1|1|
|2020.01.06|A2|2|
|2020.06.01|A3|3|

## Dropping tables {#dropping-tables}

-   To delete data from a DFS table, you can use function [dropTable](../../../Functions/d/dropTable.md) or [truncate](../../../Functions/t/truncate.md).

-   To delete specific records from an in-memory or DFS table, you can use SQL [delete](../../SQLStatements/delete.md) statement.


See [Drop Database and Table](../../../Database/DatabaseOperations/DropDatabaseandTable.md) for details.

