bit.io currently supports a number of commands that follow the PostgreSQL 12 syntax, which are outlined below. If you would like to see a currently unsupported sql statement on bit.io, you can send a feature request to [email protected]

📘
Repository operations can only be executed in the UI, so ALTER SCHEMA, CREATE SCHEMA, and DROP SCHEMA statements are not supported.

`ALTER TABLE`

Usage

The ALTER TABLE command can be used to modify a table and its columns. Usage includes:

table renames
column renames
add/delete columns
change a column's default value
change a column's data type
change a column's NULL status

Supported Syntax

ALTER TABLE [ IF EXISTS ] [ ONLY ] table_name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] table_name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] table_name
    RENAME TO new_name

where action is one of:

    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [USING expression]
    ALTER [ COLUMN ] column_name SET DEFAULT expression
    ALTER [ COLUMN ] column_name DROP DEFAULT
    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL

and column_constraint is:

{ NOT NULL |
  NULL |
  DEFAULT default_expr |
}

Unsupported sub-commands

Only the clauses referenced above can be used in ALTER TABLE statements.

Example

ALTER TABLE "user_a/my_repo"."table_1"
    ALTER COLUMN "col_1" DROP DEFAULT

The above example drops the default value from "col_1" in "user_a/my_repo"."table_1".

Notes

Reference full PostgreSQL ALTER TABLE documentation.

`ALTER VIEW`

Usage

The ALTER VIEW command is used to modify a view's definition.

Supported Syntax

ALTER VIEW [ IF EXISTS ] view_name RENAME TO new_name

Unsupported Sub-commands

The following variants of ALTER VIEW are not supported:

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )

Example

ALTER VIEW "user_a/MyRepo"."OldViewName" RENAME TO "NewName"

The above example changes the name of OldViewName in the repository user_a/MyRepo to NewName.

Notes

Reference full PostgreSQL ALTER VIEW documentation.

`BEGIN`

Usage

The BEGIN command can be use to start a new database transaction block.

Supported Syntax

BEGIN [ WORK | TRANSACTION ]

Unsupported Sub-commands

Setting the transaction mode to the following is unsupported:

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY
    [ NOT ] DEFERRABLE

Notes

Transactions are not supported for queries made from the bit.io website.

Reference full PostgreSQL BEGIN documentation.

`COMMIT`

Usage

The COMMIT command can be used to end the current transaction block and commit all changes made during the transaction.

Supported Syntax

COMMIT [ WORK | TRANSACTION ]

Unsupported Sub-commands

The [ AND [ NO ] CHAIN ] clause is unsupported.

Notes

Transactions are not supported for queries made from the bit.io website.

Reference full PostgreSQL COMMIT documentation.

`COPY`

Usage

The COPY command can be used to copy data rows from STDIN to a table or to STDOUT from a table or query.

Supported Syntax

COPY table_name [ ( column_name [, ...] ) ]
    FROM { STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

where option can be one of:

    FORMAT format_name
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL ( column_name [, ...] )
    FORCE_NULL ( column_name [, ...] )
    ENCODING 'encoding_name'

Unsupported sub-commands

We only support COPY commands FROM STDIN or TO STDOUT.

COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | PROGRAM 'command'} and

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | PROGRAM 'command' }

are not supported.

The FREEZE option is also not supported.

Example

COPY "annie/Apartments"."Noe Valley"
    TO STDOUT
  DELIMITER '|'

The above example prints the rows of the Noe Valley table in the repository annie/Apartments to STDOUT with the delimiter |.

Notes

Reference full PostgreSQL COPY documentation.

`CREATE TABLE`

Usage

The CREATE TABLE command defines a new table with the specified columns.

Supported Syntax

CREATE [ { TEMPORARY | TEMP } ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ column_constraint [ ... ] ] }
    [, ... ]
] )

where column_constraint is:

{ NOT NULL |
  NULL |
  DEFAULT default_expr
}

Unsupported Sub-commands

Only the clauses referenced above can be used in CREATE TABLE statements.

Example

CREATE TABLE IF NOT EXISTS "alex_l/Olympics"."Speed Skating"
    (year DATE, location TEXT, gold TEXT, silver TEXT, bronze TEXT)

The above example creates the Speed Skating table in the repository alex_l/Olympics with five columns: year, location, gold, silver, and bronze.

Notes

Reference full PostgreSQL CREATE TABLE documentation.

`CREATE VIEW`

Usage

The CREATE VIEW command defines a new view from a SELECT or VALUES command. Note that, unlike tables, views are not materialized, so the query is re-run each time the view is referenced.

Supported Syntax

CREATE [ OR REPLACE ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    AS query

Unsupported Sub-commands

View parameters specified in a [ WITH ( view_option_name [= view_option_value] [, ... ] ) ] clause are not supported.

Example

CREATE VIEW "wesley/Media"."Family Friendly Movies"
    AS SELECT * FROM "wesley/Media"."Movies" WHERE rating = 'G' OR rating = 'PG'

The above example creates the view Famiily Friendly Movies in the wesley/Media repository with the specified SELECT statement.

Notes

Reference full PostgreSQL CREATE VIEW documentation.

`DELETE`

Usage

The DELETE command deletes all rows from a table that satisfy a certain WHERE condition.

Supported Syntax

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING from_item [, ...] ]
    [ WHERE condition ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Unsupported Sub-commands

The WHERE CURRENT OF cursor_name is not supported.

Example

DELETE FROM "ricardo/Astronomy"."Planets" WHERE name = 'Pluto'

The above example deletes all rows from the table Planets in the repository ricardo/Astronomy whose value in the name column is Pluto.

Notes

Reference full PostgreSQL DELETE documentation.

`DROP TABLE`

Usage

The DROP TABLE command deletes a table or set of tables from the database.

Supported Syntax

DROP TABLE [ IF EXISTS ] name [, ...] [ RESTRICT ]

Unsupported Sub-commands

The CASCADE parameter is not supported.

Example

DROP TABLE "miriam/Inventory"."Books"

The above example deletes the table Books in the repository miriam/Inventory.

Notes

Reference full PostgreSQL DROP TABLE documentation.

`DROP VIEW`

Usage

The DROP VIEW command removes a view or set of views from the database.

Supported Syntax

DROP VIEW [ IF EXISTS ] name [, ...] [ RESTRICT ]

Unsupported Sub-commands

The CASCADE parameter is not supported.

Example

DROP VIEW IF EXISTS "phoebe_17/Taxonomy"."Arthropods"

The above example deletes the view Arthropods from the repository phoebe_17/Taxonomy.

Notes

Reference full PostgreSQL DROP VIEW documentation.

`EXECUTE`

Usage

The EXECUTE command runs a previously prepared statement with an optional set of parameters. See PREPARE documentation for more details on prepared statements.

Supported Syntax

EXECUTE name [ ( parameter [, ...] ) ]

Example

EXECUTE new_order ('user_2349', 34.12, '2021-02-15')

Notes

EXECUTE statements are not supported in the web app.

Reference full PostgreSQL EXECUTE documentation.

`INSERT`

Usage

The INSERT command inserts data rows from a SELECT or VALUES query into the specified table.

Supported Syntax

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Unsupported Sub-commands

OVERRIDING and ON CONFLICT clauses are not supported.

Example

INSERT INTO "alessandro/Climate"."Hurricanes" 
    VALUES ('Ivan', 'Category 5', 'September 2004', 168)

The above example inserts a row containing the specified values into the Hurricanes table in the repository alessandro/Climate.

Notes

Reference full PostgreSQL INSERT documentation.

`PREPARE`

Usage

The PREPARE command creates a prepared statement, which is a server-side object that is often created to optimize performance. When called, PREPARE parses and analyzed the provided statement. Once prepared, a call to EXECUTE on the same statement will issue and execute the command.

Supported Syntax

PREPARE name [ ( data_type [, ...] ) ] AS statement

Example

PREPARE new_order (TEXT, REAL, DATE) AS INSERT INTO "yvette/Orders"."Pending"
    VALUES ($1, $2, $3)

Notes

PREPARE statements are not supported in the web app.

Reference full PostgreSQL PREPARE documentation.

`RELEASE SAVEPOINT`

Usage

The RELEASE SAVEPOINT command destroys a savepoint created in the current transaction block.

Supported Syntax

RELEASE [ SAVEPOINT ] savepoint_name

Notes

Transactions are not supported for queries made from the bit.io website.

Reference full PostgreSQL RELEASE SAVEPOINT documentation.

`ROLLBACK`

Usage

The ROLLBACK command aborts the current transaction and cancels changes made from any queries executed within the transaction block.

Supported Syntax

ROLLBACK [ WORK | TRANSACTION ]

Unsupported Sub-commands

The [ AND [ NO ] CHAIN ] clause is unsupported.

Notes

Transactions are not supported for queries made from the bit.io website.

Reference full PostgreSQL ROLLBACK documentation.

`ROLLBACK TO SAVEPOINT`

Usage

The ROLLBACK TO SAVEPOINT command cancels any queries executed after the referenced savepoint but keeps the current transaction block open.

Supported Syntax

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

Notes

Transactions are not supported for queries made from the bit.io website.

Reference full PostgreSQL ROLLBACK TO SAVEPOINT documentation.

`SAVEPOINT`

Usage

The SAVEPOINT command creates a new savepoint in the current transaction block.

Supported Syntax

SAVEPOINT savepoint_name

Notes

Transactions are not supported for queries made from the bit.io website.

Reference full PostgreSQL SAVEPOINT documentation.

`SELECT`

Usage

The SELECT command retrieves data rows from a set of tables or views.

Supported Syntax

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
                [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
    [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and grouping_element can be one of:

    ( )
    expression
    ( expression [, ...] )
    ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
    CUBE ( { expression | ( expression [, ...] ) } [, ...] )
    GROUPING SETS ( grouping_element [, ...] )

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

Unsupported Sub-commands

The locking clause (FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ) is not supported.

Example

SELECT "Date", "Location" FROM "nadia/G7"."Summits"
    WHERE "Host" = 'Japan'
  ORDER BY 'Date' ASC

The above example selects all rows from the columns Date and Location in the table Summits where the value in the column Host is equal to Japan and sorts the results in ascending order according to the Date value.

Notes

Reference full PostgreSQL SELECT documentation.

`START TRANSACTION`

Usage

The BEGIN command can be use to start a new database transaction block.

Supported Syntax

START TRANSACTION

Unsupported Sub-commands

Setting the transaction mode to the following is unsupported:

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY
    [ NOT ] DEFERRABLE

Notes

Transactions are not supported for queries made from the bit.io website.

Reference full PostgreSQL START TRANSACTION documentation.

`TRUNCATE`

Usage

The TRUNCATE command deletes all rows from a table or set of tables.

Supported Syntax

TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
    [ CONTINUE IDENTITY ] [ RESTRICT ]

Unsupported Sub-commands

The RESTART IDENTITY and CASCADE parameters are not supported.

Example

TRUNCATE "holly/Weather"."Daily Temperatures"

The above query removes all rows from the table Daily Temperatures in the repository holly/Weather.

Notes

Reference full PostgreSQL TRUNCATE documentation.

`UPDATE`

Usage

The UPDATE command modifies the contents of all rows from a table that meet the specified WHERE condition.

Supported Syntax

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Unsupported Sub-commands

The WHERE CURRENT OF clause is not supported.

Example

UPDATE "priyanka/Sales"."Orders" SET (first_name, last_name) =
    (SELECT first_name, last_name FROM "priyanka/Sales"."Customers"
     WHERE "Customers".id = "Orders".customer_id);

The above example updates the columns first_name and last_name of the table Orders in the repository priyanka/Sales with the result of the SELECT query.

Notes

Reference full PostgreSQL UPDATE documentation.

`VALUES`

Usage

The VALUES command calculates a row or set of rows with the specified values. VALUES commands are often used in the target list of a SELECT command or within INSERT commands, but can be used by itself.

Supported Syntax

VALUES ( expression [, ...] ) [, ...]
    [ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

Unsupported Sub-commands

The PostgreSQL 12 VALUES syntax is fully supported.

Example

VALUES (trunc(4.234), 14 * .6, 0 > 2)

The above example returns a row with values 4, 8.4, and FALSE.

Notes

Reference full PostgreSQL VALUES documentation.