PostgreSQL was originally named “POSTGRES”— all caps. The name was chosen because it was the successor (“post-”) to the “Ingres” database. As the system adopted SQL standards and features, the “ql” as added and capitalization refined. ...
Today I learned how to query the size of a Postgres array. There are (at least) two methods that work. The first is array_length. This requires you to know the array dimension you seek; a useful filter, although most of the time this will probably be 1: hr-til_development=# select title from posts hr-til_development=# where array_length(slack_notified_at_likes_threshold, 1) = 1; title ------------------------------------------- Because Javascript Percent Notation DIY Grids for Designing UI in Illustrator (3 rows) When the dimension is 1, a more terse solution is cardinality: ...
We can add a PostgreSQL array column with a default empty array using: add column things integer[] not null default '{}'; ...
Today I learned that I have thirty-nine Postgres databases on my computer, after running this command inside psql: \l ; Each one is small, but I don’t like to carrying around old data. I ended up dropping nine of them, with: drop database foo_development; For a lighter storage and cognitive load.
Using a Postgres table for caching? You might want to try making it unlogged. unlogged tables are not written to the write-ahead log, which makes them much faster. This also means they are not crash-safe, and are truncated when a crash or unclean shutdown occurs. For caching purposes, that’s likely to be an acceptable tradeoff. Documentation
You can store a PostgreSQL query with prepare. db=# prepare posts_search as select title from posts limit 5; PREPARE Call the method with execute. db=# execute posts_search; title ----------------------------------- Hello World! My First Pull Request: HAML My First Pull Request: Sinatra My First Pull Request: Capistrano My First Pull Request: SASS (5 rows) Deallocate the query with deallocate, and you can set it again. db=# deallocate posts_search; DEALLOCATE db=# prepare posts_search as select title from posts limit 10; PREPARE
Want to change database connections from inside psql? You can! Psql, the REPL for Postgres, has a useful meta-command called \connect, or \c. This lets you establish a new connection to a Postgres server, while closing the current connection. Here is the required format: \c or \connect [ dbname [ username ] [ host ] [ port ] ] | conninfo Only \c [my_database] is required; omitted parameters are taken from the previous connection. ...
To maintain data integrity, certain actions like rake db:drop will not execute when there are active sessions on a Postgres database. You’ll get an error like this: ERROR: database "database_name" is being accessed by other users DETAIL: There are 2 other session(s) using the database. Sessions can be killed from the command line, but a safer route is to kill them with a database method. First, revoke all public access from the database: ...