Skip to content

Instantly share code, notes, and snippets.

View stephencweiss's full-sized avatar
🎉

Stephen Weiss stephencweiss

🎉
47 followers · 20 following
View GitHub Profile
@stephencweiss
stephencweiss / psql List of databases
Last active December 8, 2018 15:27
#psql
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+---------+----------+-------------+-------------+---------------------
my_database | sdc | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/sdc +
| | | | | sdc=CTc/sdc
postgres | Stephen | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
sdc | Stephen | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/Stephen +
| | | | | Stephen=CTc/Stephen+
@stephencweiss
stephencweiss / psql Access Privileges
Created November 20, 2018 17:33
```psql
sdc=# \dp descriptions
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------------+-------+-------------------------+-------------------+----------
public | descriptions | table | Stephen=arwdDxt/Stephen+| |
| | | sdc=arwdDxt/Stephen | |
```
@stephencweiss
stephencweiss / nodeJS - insertToPostgres.js
Last active November 20, 2018 17:47
A gist showing how to connect and insert to Postgres
```js
//insertToPostgres.js
const { client } = require('pg');
const config = require('../config.json');
...
const table = 'descriptions';
const fields = 'product_id, product_name, features, tech_specs'
const host = config.host;
@stephencweiss
stephencweiss / psql Sample Query of SDC
Last active November 21, 2018 16:16
Sample Queries on a Postgres Database with 10m primary records
SELECT (product_id, product_name, created_at) FROM descriptions WHERE product_id = 19282;
SELECT COUNT(product_id) FROM descriptions;
SELECT (product_id, product_name, created_at) FROM descriptions ORDER BY created_at DESC LIMIT 10;
@stephencweiss
stephencweiss / mongoDB Sample Query of SDC
Last active November 24, 2018 22:28
db.descriptions.find( { productId: 1928222 } )
db.descriptions.find( { productId: { $gt : 9999990 } } )
@stephencweiss
stephencweiss / db.descriptions.createIndex()
Created November 21, 2018 19:52
Mongo createIndex() example
//mongo sh
> db.descriptions.createIndex( { productId: 1 } )
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
@stephencweiss
stephencweiss / db.descriptions.getIndexes()
Created November 21, 2018 19:55
Mongo getIndexes() example - DB = trailblazer; Collection = descriptions
// mongo sh
> db.descriptions.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "trailblazer.descriptions"
@stephencweiss
stephencweiss / db.descriptions.dropIndex()
Last active November 21, 2018 19:58
Mongo dropIndex() example with and without index name.
// mongo sh
> db.descriptions.dropIndex("product_id_1")
{ "nIndexesWas" : 2, "ok" : 1 }
> db.descriptions.dropIndexes()
{
"nIndexesWas" : 2,
"msg" : "non-_id indexes dropped for collection",
"ok" : 1
}
@stephencweiss
stephencweiss / db.collections.find() - with Index
Created November 21, 2018 20:00
An example find() command on a DB with 10m documents with an index explained
//mongo sh
db.descriptions.find( {productId: {$gt : 9999990 } } ).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "trailblazer.descriptions",
"indexFilterSet" : false,
"parsedQuery" : {
"productId" : {
"$gt" : 9999990
@stephencweiss
stephencweiss / db.collections.find() - no Index
Created November 21, 2018 20:00
An example find() command on a DB with 10m documents but no index explained
//mongo sh
db.descriptions.find( {productId: {$gt : 9999990 } } ).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "trailblazer.descriptions",
"indexFilterSet" : false,
"parsedQuery" : {
"productId" : {
"$gt" : 9999990