Sql

“Post-gres-Q-L”, “Post-grey-sequel”, “Postgres”… how do you say it? And where did the name come from? PostgreSQL was originally named POSTGRES– all caps. The name was chosen because it was the successor to the Ingres database developed at UC Berkeley. As the system adopted SQL standards and features, the name was changed to PostgreSQL. From the docs: Many people continue to refer to PostgreSQL as “Postgres” (now rarely in all capital letters) because of tradition or because it is easier to pronounce. This usage is widely accepted as a nickname or alias. ...

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: ...

Today I added an array of integers to a Postgres table, and like all such migrations, it will default to null for each new record. This was a problem, because I wanted to use Rails 4’s built-in Postgres array support to make decisions based on that data. Ruby array methods like include? will raise NoMethodError: undefined method 'include?' for nil:NilClass if that array is ever nil, which it is by 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 SQL 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: ...