Skip to main content

Converting From postgreSQL to bSQL

Overview

An explanation of how to migrate postgreSQL to bSQL. This tutorial includes basic differences between the two languages and their functionality. Here's an overview of the key differences:

postgreSQLbSQL
Immutable
Snapshots
Time Travel Queries
Integrity Proofs

Data Type Conversions

Numeric

The following table provides approximate numeric data type conversions. PostgreSQL doesn't support unsigned numeric types, so it is important to use this table only as general guidelines. For a reference look at the bSQL data types.

Storage SizepostgreSQLbSQL
2 bytessmallintINT16 or UINT16
4 bytesintegerINT32 or UINT32
8 bytesbigintINT64 or UINT64
4 bytesrealFLOAT32
8 bytesdouble precisionFLOAT64
2 bytessmallserialUINT16 column with AUTO INCREMENT
4 bytesserialUINT32 column with AUTO INCREMENT
8 bytesbigserialUINT64 column with AUTO INCREMENT
variablenumericno equivalent

String

The following table provides bSQL equivalents to the character types in postgreSQL. The bSQL equivalents include additional column level arguments such as SIZE and PACKED.

Storage SizepostgreSQLbSQL
<=nvarchar(n)STRING PACKED SIZE = n
ncharacter(n)STRING SIZE = n
variabletextno equivalent

Other

The following table provides bSQL equivalents to the character types in postgreSQL. The bSQL equivalents include column additional column level arguments.

postgreSQLbSQL
byteaBYTEARRAY PACKED
timestampTIMESTAMP
timeuse numeric types
dateuse TIMESTAMP
booleanBOOL

Blockchain vs. Table

Creating a table in postgreSQL is equivalent to creating a blockchain in bSQL. For example creating the following table in postgreSQL:

CREATE TABLE "Batters"
(
Player VARCHAR(101),
Team VARCHAR(50),
League VARCHAR(2),
Yr SMALLINT,
Games DOUBLE PRECISION,
AB DOUBLE PRECISION,
R DOUBLE PRECISION,
H DOUBLE PRECISION,
Doubles DOUBLE PRECISION,
Triples DOUBLE PRECISION,
HR DOUBLE PRECISION,
RBI DOUBLE PRECISION,
SB DOUBLE PRECISION,
CS DOUBLE PRECISION,
BB DOUBLE PRECISION,
SO DOUBLE PRECISION,

IBB DOUBLE PRECISION,
HBP DOUBLE PRECISION,
SH DOUBLE PRECISION,
SF DOUBLE PRECISION,
GIDP DOUBLE PRECISION
);

Would be equivalent to the following bSQL statement:

CREATE BLOCKCHAIN batters HISTORICAL (
player STRING SIZE = 101 PACKED NULLABLE,
team STRING SIZE = 50 PACKED NULLABLE,
league STRING SIZE = 2 PACKED NULLABLE,
yr UINT16 NULLABLE,
games FLOAT64 NULLABLE,
ab FLOAT64 NULLABLE,
r FLOAT64 NULLABLE,
h FLOAT64 NULLABLE,
doubles FLOAT64 NULLABLE,
triples FLOAT64 NULLABLE,
hr FLOAT64 NULLABLE,
rbi FLOAT64 NULLABLE,
sb FLOAT64 NULLABLE,
cs FLOAT64 NULLABLE,
bb FLOAT64 NULLABLE,
so FLOAT64 NULLABLE,
ibb FLOAT64 NULLABLE,
hbp FLOAT64 NULLABLE,
sh FLOAT64 NULLABLE,
sf FLOAT64 NULLABLE,
gidp FLOAT64 NULLABLE
);

A few things to note here:

  • Unlike postgreSQL, bSQL columns don't allow null entries by default and NULLABLE must be specified.
  • The HISTORICAL blockchain is an immutable log and DOESN'T allow any mutations other than INSERT.
  • Column and blockchain names in bSQL are interpreted as lower case and can't include spaces, use an underscore instead.
  • The "y" column can be made a UINT16 in bSQL because years are positive.

Mutations

Insertions

The following insertion inserts a value into the films postgreSQL table:

INSERT INTO films VALUES
('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

This is equivalent to the following bSQL insertion:

INSERT films VALUES
("UA502", "Bananas", 105, "1971-07-13", "Comedy", "82 minutes");

Note the few differences:

  • The INTO keyword is omitted in the bSQL statement.
  • STRING values in bSQL require double quotes.

Update

The equivalent to an update statement in postgreSQL is an AMEND statement. The key difference is that the bSQL statement doesn't modify existing data, instead it only appends the new version.

There are two ways of updating data in bSQL:

  1. Using an AMEND with static values dependent on primary key.
  2. Using AMEND INTO using a blockchains values and a search constraint.

The first case depends on the primary key and would look like this in postgreSQL:

UPDATE films SET kind = 'Dramatic' WHERE film_id = 101;

In bSQL it would look like the following:

AMEND films (film_id, kind) VALUES (101, "Dramatic");

Assuming that films is a SPARSE blockchain, the bSQL statement above inherits the previous values of the original record for all values not contained in the column list (in this case film_id and kind). This mutation doesn't rely on scanning through values to arrive at a match, and uses the index built on film_id.

Updating a values whose primary key is unknown must be resolved from the existing data. In postgreSQL this statement doesn't change and resembles the following:

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

In bSQL we use the AMEND INTO statement to pull data:

AMEND INTO films 
SELECT film_id, name, release_date, "Dramatic"
FROM films
WHERE kind = "Dramatic";

Assuming that the column list in the select statement encompasses all columns, this statement can be used for all blockchains that allow amends.

DELETE

The equivalent to a DELETE statement in postgreSQL is a DISCONTINUE statement. The key difference is that the bSQL statement doesn't remove existing data, instead it only appends the new version.

DELETE FROM films WHERE film_id = 101;

In bSQL this would translate to:

DISCONTINUE films (film_id) VALUES (101);
  • When discontinuing a value based off of a primary key it isn't necessary to provide all columns; the only column required is the primary key.
  • When a values are discontinued, a tombstone record is added to the log to mark the primary key as no longer existing in the current system.

QUERIES

Queries in bSQL are very similar to queries in postgreSQL and follow a nearly identical format. Here are a few of the noticeable differences:

  • LIMIT is replaced with FILTER
  • Double quotes indicate string values, not column names

See Also