Cansada de ser feliz

Bienvenidos a mi flujo de conciencia

Cheatsheet

| Comments

PostgreSQL, Heroku…

PostgreSQL

Reset sequence

1
SELECT setval('mytable_id_seq', (SELECT MAX(id) FROM mytable));

Make a dump of existing database

1
$ pg_dump -O -U postgres database_name | gzip > /tmp/dump.sql.gz

Create dump without data

1
$ pg_dump test_db_development -s > /tmp/createdb.sql

Load database from dump

1
2
3
4
$ psql -U username -f /tmp/dump.sql.gz
$ psql -U username -d activar -f ~/Downloads/dump.sql

$ sudo -u postgres psql db_development < ~/Downloads/dump.sql

Duplicate database

1
CREATE DATABASE newdb WITH TEMPLATE olddb;

or

1
$ createdb -T olddb newdb

Download as csv

1
2
\COPY enterprises TO '/tmp/countries.csv' DELIMITER ';' CSV HEADER;
COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';

Allow user to acces tables, functions and sequences

1
2
3
4
5
GRANT ALL ON ALL TABLES IN SCHEMA public TO user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO user;

GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO user;

Create extension

1
2
CREATE EXTENSION unaccent;
CREATE EXTENSION hstore;

Set password for all users to ‘demo’

1
2
UPDATE auth_user
SET password ='bcrypt$$2a$12$udndek2c62VlLqWnAYU5qePYQ7SS9rmfnxIuGNhGR4EMfFadQsMuG';

Rename constraint

1
ALTER TABLE name RENAME CONSTRAINT constraint_name TO new_constraint_name;

Create unique constraint

1
ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE (columns);

Settings for query result tables

1
2
3
4
\pset expanded auto
\pset border 1
\pset pager on
\pset null '(NULL)'

Finding long running queries

1
2
3
4
5
6
7
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE now() - pg_stat_activity.query_start > interval '5 minutes';

Cancel running queries

1
SELECT pg_cancel_backend(__pid__);

Joins

Heroku

Login to Heroku from console

1
$ heroku login

Run Heroku app locally

1
$ heroku local web

Deploy your code

1
$ git push heroku master

View logs of your application

1
$ heroku logs --tail

Other commends

1
2
3
4
$ heroku run bash
$ heroku run python
$ heroku run python manage.py shell
$ exit

Elixir

1
2
3
4
5
6
7
8
defmodule Account do
  def investment_return(initial, interest) do
    initial + (initial * interest)
  end
end

amount = Account.investment_return(1000, 0.0001)
IO.puts "Investment return: $#{amount}"

Pipe function to string:

1
2
    # IO.puts String.upcase("Elixir")
    IO.puts "Elixir" |> String.upcase
1
2
3
4
5
6
7
8
9
10
11
12
13
14
defmodule Person do
  def format_name(full_name) do
    # format(String.split(full_name))
    full_name |> String.split |> format
  end

  def format(parts) do
    first = Enum.at(parts, 0)
    last = Enum.at(parts, 1)
    "#{String.upcase(last)}, #{first}"
  end
end

IO.puts Person.format_name("José Valim")

= - match operator.

1
2
    language = "Elixir"
    IO.puts language  # Elixir

<> - string concatenation.

1
2
"José " <> last_name = "José Valim"
IO.puts last_name  # Valim

`last_name``- empty variable.

1
2
"José " <> last_name = "Valim"
IO.puts last_name  # (MatchError))
1
2
3
4
5
data = ["Elixir", "Valim"]
IO.puts data  # ElixirValim

[lang, author] = data
IO.puts ""#{lang}, {#author}"  # Elixir, Valim

Comments