Articles related to Postgres from the team that helps B2C companies build and defend revenue with AI.
Here's a great way to make Rails apps running on Postgres more inspectable. Just put this in config/initializers/application_name.rb: ActiveRecord::Base.connection.class.set_callback(:checkout, :after) { raw_connection.exec "set application_name = 'MyRailsApp'" } Now when you inspect pg_stat_activity, you can tell which
Read morepg_query is a really cool Ruby library that uses Postgres's query parser to give you programmatic access to SQL queries. One thing you can do with it is read a CREATE TABLE statement to map column names to column types: sql = <<-SQL CREATE TABLE example ( my_text
Read moreSo you've got a table with a UUID primary key. How do you search for only 1/4, or 1/8, or 1/128 of it? BETWEEN '00000000-0000-0000-0000-000000000000' AND '3fffffff-ffff-ffff-ffff-ffffffffffff' BETWEEN '40000000-0000-0000-0000-000000000000' AND '7fffffff-ffff-ffff-ffff-ffffffffffff' BETWEEN '80000000-0000-0000-0000-000000000000' AND 'bfffffff-ffff-ffff-ffff-ffffffffffff' BETWEEN 'c0000000-0000-0000-0000-000000000000' AND 'ffffffff-ffff-ffff-ffff-ffffffffffff' And how do you generate those ranges? Here
Read moreUPDATED with fixes from @jeremyevans Going back and forth between ActiveRecord and Sequel can be very confusing... especially because the Sequel documentation assumes that you use "datasets" instead of raw sql. What ActiveRecord Sequel One value ActiveRecord::Base.connection.select_value(sql) DB[sql].get One row as
Read moreThis is part of our series on PostgreSQL and things that are obvious once you see them. It's a 2017 update to our 2016 article How to merge JSON fields in Postgres Nice! Update We used to call this jsonb_collect, but then we realized it was very similar to
Read moreThis post is part of our PostgreSQL series. SQL cursors are a great way to stream data out of a database without loading it all into memory. Reading using a cursor Sequel has use_cursor: BATCH_SIZE = 1000 DB[:widgets]. select(:id, :name, :power). use_cursor(rows_per_fetch: BATCH_
Read moreCitus Cloud is the hosted Postgres that our app uses. We tried EnterpriseDB Postgres Plus Cloud and couldn't even get going; we used Amazon RDS for a year but migrated away about 6 months ago. Heroku Postgres wasn't bare-metal enough for us. 1. Log directly to Papertrail It's not a
Read moreThis post is part of our PostgreSQL series. Terraform's RDS support makes it easy to create a database instance. Here's a cheatsheet: resource "aws_db_instance" "mydb1" { allocated_storage = 256 # gigabytes backup_retention_period = 7 # in days db_subnet_group_name = "${var.rds_public_subnet_
Read more
This post is part of our data science and PostgreSQL series. UPDATED FOR 2017 Now with easy subquery support and a more sensible argument order! We adapted this excellent piece by Dmitri Fontaine and turned it into a function. It can be invoked like this: SELECT * FROM histogram($table_name_
Read more
This is part of our practical cartography and PostgreSQL series. Put a map on it! Sometimes it's a pain to open up QGIS and load a PostGIS-enabled DB. Sometimes I don't feel like writing a custom tileserver and hooking it up to Leaflet or Mapbox GL just so I can
Read moreThis post is part of our data science and PostgreSQL series. "How do you do strftime in postgres?" The answer: to_char(date, format). If you want to group by month, this is what you're looking for: psql=> select count(*), to_char(created_at, 'YYYY-MM') from employees
Read moreThis is part of our series on PostgreSQL and things that are obvious once you see them. Nice! Deprecated! Please see our new article How to aggregate JSONB in PostgreSQL 9.5+. How do you combine (merge) JSON fields in Postgres? You define a new function json_collect() # select * from
Read more