Documentation
WebsiteDiscordBlog

Modify Rows in a Table

Writing data is every bit as important to applications as reading.

This activity will delve deeper into how to insert, update, and delete data from the table we created in previous lessons. If you don't have a SXTTemp.Test_<your UserID> table, go back and complete the "Create Your Own Table" activity first. We'll use the ADMIN_BISCUIT we generated in that activity.

Space and Time is a data warehouse, meaning its as large-scale database that operates on SQL. This means it accepts DML - data manipulation language - the same as any other database. SXT Labs has taken special efforts to align Space and Time Database SQL syntax to PostgreSQL, an open source database popular with development teams. This should make SXT feel very familiar, for those used to PostgreSQL.

Let's Get Started!

To be able to validate your script, we'd ask that you generate a new file called Modify_Rows_In_Table.sh that you can commit to your branch of the SXT-Community/SXTAccreditation repo at the end of the activity.


Primary Key

Every table in Space and Time has a Primary Key(PK), which is one or more columns that can be used to uniquely identify the row. This columns cannot be null, and the combination of the columns must be unique.

For example, the table you've built has a single column PK defined on line 11: primary key(MyID)

CREATE TABLE SXTTemp.Test_<your UserID>  
  ( MyID              INTEGER  
  , MyBigint          BIGINT  
  , MyText            VARCHAR  
  , MyDate            DATE  
  , MyBoolean         BOOLEAN  
  , MyDecimal         DECIMAL(18,2)  
  , MyFloat           FLOAT  
  , MyJson            JSONB  
  , MyTimestamp       TIMESTAMP  
  , primary key(MyID)  
  )  
  with "public_key=$RESOURCE_PUBLIC_KEY,access_type=permissioned"

This means that the column MyID must be unique, and cannot be null. It's important to understand this when modifying data, as doing inserts or updates that introduce duplication in PKs will always error.


Inserting Rows

The Basics:

INSERT statements are the primary way users of Space and Time insert custom data into the network. Broadly speaking, INSERT statements have three parts:

  1. INSERT INTO and Table Name
  2. Defining the column order
  3. Supplying the data

The order of columns and values must be the same between part 2 and 3. Unlike other data structures like JSON, SQL Inserts rely on positional coordination between the column names, and the data values being supplied.

For example - let's insert these 5 rows into our table above, using the SXTCLI so we can leave the heredoc structure, for readability:

INSERT_SQL=$(cat << EOM

INSERT INTO SXTTemp.Test_<your UserID>
(MyID, MyBigint, MyText, MyDate, MyBoolean, MyDecimal, MyFloat, MyJson, MyTimestamp)
VALUES  
  ( 1, 1e12, 'Foo', '2024-10-31', true, 1.1, 1.11111 * 1e-9, '{"a":1, "b":2}', '2024-10-31 00:00:00' )
, ( 2, 2e12, 'Foo', '2024-10-31', true, 2.2, 1.22222 * 1e-9, '{"a":1, "b":2}', '2024-10-31 06:59:59' )
, ( 3, 3e12, 'Foo', '2024-10-31', true, 3.3, 1.33333 * 1e-9, '{"a":1, "b":2}', '2024-10-31 12:59:59' )
, ( 4, 4e12, 'Foo', '2024-10-31', true, 4.4, 1.44444 * 1e-9, '{"a":1, "b":2}', '2024-10-31 18:59:59' )
, ( 5, 5e12, 'Foo', '2024-10-31', true, 5.5, 1.55555 * 1e-9, '{"a":1, "b":2}', '2024-10-31 23:59:59' )

EOM
)

The first line of the SQL (line 3 of the script) specifies the SCHEMA and TABLENAME. The line below that lists all the columns for which data can be expected, and in what order. The keyword VALUES indicates the beginning of data records, from one to several thousand.

Run the above example, using the SXTCLI. If you forgotten the `$ADMIN_BISCUIT, go back to the "Create Your Own Table" activity:

sxtcli sql \
  --url=$API_URL \
  --accessToken=$ACCESS_TOKEN \
  --sqlText=$INSERT_SQL \
  --biscuits=$ADMIN_BISCUIT

This will return the number of rows inserted:

[ {
  "UPDATED" : 5
} ]

Optional Column List

Specifying the column list is optional if you are supplying data for every column. This means the INSERT statement above could also be structured as:

INSERT_SQL=$(cat << EOM

INSERT INTO SXTTemp.Test_<your UserID>
VALUES  
  ( 6, 6e12, 'Foo', '2024-10-31', true, 6.6, 1.66666 * 1e-9, '{"a":1, "b":2}', '2024-10-31 23:59:59' )
, ( 7, 7e12, 'Foo', '2024-10-31', true, 7.7, 1.77777 * 1e-9, '{"a":1, "b":2}', '2024-10-31 23:59:59' )
, ( 8, 8e12, 'Foo', '2024-10-31', true, 8.8, 1.88888 * 1e-9, '{"a":1, "b":2}', '2024-10-31 23:59:59' )
EOM
)

sxtcli sql \
  --url=$API_URL \
  --accessToken=$ACCESS_TOKEN \
  --sqlText=$INSERT_SQL \
  --biscuits=$ADMIN_BISCUIT

[ {
  "UPDATED" : 3
} ]

Partial Column List

You can also omit certain columns, if you don't have data to supply. All columns are optional except for the PK, or any columns defined as NOT NULL.

For example, we could supply only MyID (PK) and MyBoolean, however we are then required to specify the column list, and match the column order with the data provided:

INSERT_SQL=$(cat << EOM

INSERT INTO SXTTemp.Test_<your UserID>
(MyID, MyBoolean)
VALUES  
  (  9, true)
, ( 10, true)
EOM
)

sxtcli sql \
  --url=$API_URL \
  --accessToken=$ACCESS_TOKEN \
  --sqlText=$INSERT_SQL \
  --biscuits=$ADMIN_BISCUIT

[ {
  "UPDATED" : 2
} ]

Remember, your PK can be multiple columns, but the combination of values must be unique. If you try to run the insert immediately above a second time, you'll get an error now instead:

sql failed
[500] Internal Server Error
Details: [73a40e47-a31a-421c-a847-5eda22029842] ERROR: duplicate key value violates unique constraint "test_<your UserID>_pkey"
  Detail: Key (myid)=(9) already exists.

This is because you've already inserted the keys 9 and 10, and the table cannot have duplicate PK values. Likewise, trying to insert a NULL value (or omitting the PK from the insert) will result in a null value error.


Updating Rows

Updating rows changes data that's already been inserted, and similarly have three major parts:

  1. UPDATE and Table Name
  2. SET followed by a comma-delimited list of name-value pairs
  3. WHERE statement, limiting which rows will be affected

As an example - let's change MyBoolean to False, and MyText to 'Bar' if MyID is an odd number:

UPDATE_SQL=$(cat << EOM

UPDATE SXTTemp.Test_<your UserID>
SET 
  MyBoolean = false
, MyText = 'Bar'
WHERE MyID in(1,3,5,7,9)

EOM
)

sxtcli sql \
  --url=$API_URL \
  --accessToken=$ACCESS_TOKEN \
  --sqlText=$UPDATE_SQL \
  --biscuits=$ADMIN_BISCUIT

As expected, this updates 5 rows. Note that in Space and Time Database, you can UPDATE the PK if you wish, so long as it doesn't cause a duplicate key violation.


Deleting Rows

Finally, you are allowed to delete rows, removing them from the database entirely.

🚧

Take Care! Once deleted, database records are simply gone - there is no "trash" for easy recovery!

Fortunately, DELETE statements are nearly identical to SELECT statements, minus the initial column selection list. Thus, it's a best practice to start writing DELETEs as a SELECT, so you can check the rows that will be deleted, before executing the DELETE statement.

DELETE_SQL=$(cat << EOM

SELECT * FROM SXTTemp.Test_<your UserID> 
WHERE MyID between 1 and 5

EOM
)

sxtcli sql \
  --url=$API_URL \
  --accessToken=$ACCESS_TOKEN \
  --sqlText=$DELETE_SQL \
  --biscuits=$ADMIN_BISCUIT

If the above SELECT returns the rows you expect to be deleted, swap out SELECT * for DELETE and run again:

DELETE_SQL=$(cat << EOM

DELETE FROM SXTTemp.Test_<your UserID>
WHERE MyID between 1 and 5

EOM
)

sxtcli sql \
  --url=$API_URL \
  --accessToken=$ACCESS_TOKEN \
  --sqlText=$DELETE_SQL \
  --biscuits=$ADMIN_BISCUIT

Get Credit:

You'll get credit for this activity if you:

  • Insert 10 rows into your table: SXTTemp.Test_<your UserID>
  • Update your table: SXTTemp.Test_<your UserID> so that MyBoolean is false where MyID is odd
  • Delete rows where MyID is 1 thru 5 in your table: SXTTemp.Test_<your UserID>
  • Commit your work file to the SXT-Community/SXTAccreditation repo
How should I commit my branch changes?

When you're complete with the activity, simply commit your changes to your branch of work, then push to the SXT-Community/SXTAccreditation repo. Many IDEs have automated processes to manage this for you, but to do this manually by opening a temrinal / WSL window, and typing:

  • Line 1: Change to your work folder in your SXT-Community/SXTAccreditation directory
  • Line 2: Make sure your branch is active
  • Line 3: Add all changed files to your local stage
  • Line 4: Commit your changes
  • Line 5: Push your commited changes to github!
cd ./your/path/SXTAccreditation/<your_folder>
git checkout <your_branch>
git add .  
git commit -m "added my new working file"
git push

If you get an error on line 5 saying, The current branch stephens_work has no upstream branch, this is because Github doesn't know about your branch yet. Simply run the command the git tool recommends - it will look something like:
git push --set-upstream origin <your_branch>

When you're done for the day, head over to SXT-Community/SXTAccreditation/Pulls page on Github, and (if you're logged in) you'll see a message like:

Click on the button above to create a Pull Request, give us some feedback in the description, and click "Create Pull Request". Done!

Updated 2 months ago

What’s Next