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 table
standard in-memory table (table)
indexed table (indexedTable)
keyed table (keyedTable)
stream table (streamTable, haStreamTable)
mvcc table (mvccTable)
partitioned in-memory table (createPartitionedTable)
cached table (cachedTable)
DFS table
partitioned DFS table (createPartitionedTable)
dimension table (createTable)
Creating tables
Before version 1.30.14 / 2.00.2, column names can only use letters, digits or underscores (_), and must start with letters.
Since version 1.30.14 / 2.00.2, column names generated by pivot by or addColumn 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 |
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();
["x","y"]
$ t1.values();
([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
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 = timesymid
$ 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
To delete data from a DFS table, you can use function dropTable or truncate.
To delete specific records from an in-memory or DFS table, you can use SQL delete statement.
See Drop Databases and Tables for details.