Top 60 Oracle Blogs

Recent comments

roachcrib… Cockroach Database SQL crib-sheet

Recently, I was creating a developer workshop for CockroachDB and wanted a way to share various commands so people could quickly cut-and-paste various SQL commands while doing the workshop. I had various files where I kept my notes but decided to create a simple github repository to be able to share with the greater CockroachDB Community.

Here are some example SQL tidbits you will find in the repository:

  • How to Partition Tables

    ALTER TABLE vehicles PARTITION BY LIST (city) (
      PARTITION us_west VALUES IN ('san francisco', 'portland'),
      PARTITION us_east VALUES IN ('new york','boston'),
      PARTITION eu_west VALUES IN ('paris','rome')

  • How to pin partitions to specific regions

    ALTER PARTITION us_west OF INDEX bigwide2@*
      CONFIGURE ZONE USING constraints='[+region=us-west1]';
    ALTER PARTITION us_east OF INDEX bigwide2@*
      CONFIGURE ZONE USING constraints='[+region=us-east4]';
    ALTER PARTITION eu_west OF INDEX bigwide2@*
      CONFIGURE ZONE USING constraints='[+region=europe-west2]';

  • How to time-travel with queries

    --  TIME travel with Queries
    SELECT id, fname, lname  
    FROM customer AS OF SYSTEM TIME INTERVAL '-2hr' 
    WHERE id = 42;

Please share and add issues to make roachcrib more useful for the greater CockroachDB Community.