/* Code Comments */27 posts tagged with "postgres"
group, count, and order a table in postgres
2022-11-28
|~4 min read
|627 words
I have a table that represents a many to one relationship. How, then, do I find out which of the “ones” have the most “manys”? Postgres has…
getting started with postgres on os x with homebrew
2022-11-28
|~4 min read
|793 words
Before you can connect to a Postgres database from the command line, it needs to be running. If it’s been a while, it’s easy to forget how…
postgres return types in node - parse strings to whatever you want
2022-11-28
|~2 min read
|260 words
By default the returns everything from PostgreSQL as a string. Brian Carlson, the author of , provides a solution for fixing this with his…
json operators and functions in postgres
2022-11-28
|~2 min read
|378 words
I wrote in the past about Array Intersections in Postgres, which alluded to operators and functions available for arrays within Postgres…
postgres tuples only
2022-11-28
|~2 min read
|233 words
If you’re just learning how to use Postgres from the terminal (as I am), sometimes you click the wrong button and don’t know what it does…
postgres: how to query column labels for a table
2022-11-28
|~1 min read
|120 words
What if, instead of a table’s data, you wanted to see the table’s metadata? That is, instead of querying all of the rows within a table…
server-side pagination with limit/offset
2022-11-28
|~3 min read
|517 words
How do you limit the number of rows returned from a query? Let’s consider a basic example with a members table. You’re building a front end…
postgres: getting the owner of tables
2022-11-28
|~2 min read
|264 words
I was trying to modify a table today when I hit an error: . To figure out who the owner was so that I could ask them to modify the table on…
adding columns to postgres tables after creation
2022-11-28
|~3 min read
|543 words
If you already have a table created in Postgres, how can you modify it to add a new column? Similar to adding constraints after a table has…
adding a unique constraint to postgres tables after creation
2022-11-28
|~3 min read
|409 words
How do you add a unique constraint to a table in Postgres if it’s already been created? Most of the time, we add unique constraints to a…
sequences in postgres
2022-11-28
|~3 min read
|503 words
The in Postgres is a number generator.1 Once created, they can be managed with sequence operators.2 So, why use them? One reason would be…
check for duplicates - count, case, and exist in postgres
2022-11-28
|~3 min read
|504 words
Before I write to a database, I want to make sure that I don’t create a duplicate record based on the name and parent record id. To…
using coalesce and dynamic updates to multiple records in postgres
2022-11-28
|~4 min read
|711 words
I wanted to update multiple fields on multiple records and handle the case that not all of the information is present in the update for each…
postgres constraints and arrays
2022-11-28
|~2 min read
|277 words
I wrote in the past about using constraints in the context of array operators in Postgres. Today, however, I didn’t have a pre-defined array…
inserting values into a table - postgres
2022-11-28
|~3 min read
|415 words
How do you insert multiple values? I always find myself looking this up. I decided I’d jot it down here for future reference. Hopefully it…
array lengths in postgres
2022-11-28
|~4 min read
|673 words
Yesterday, I wrote about the intersecting of Arrays in Postgres. Today, I wanted to go a step further and think about finding the maximum…
array intersection in postgres
2022-11-28
|~2 min read
|322 words
Recently, I was building an API to modify the data in a database. The field I was modifying happened to be an array, and I wanted to make…
the one about quotes in postgres - single, double, and handling `non_snake_case` fields
2022-11-28
|~2 min read
|255 words
Imagine the following situation - you’re trying to select columns from a Postgres table Let’s assume these are the only four fields you want…
select and calculate with postgres using subqueries
2022-11-28
|~2 min read
|225 words
I wanted to be able to retrieve a set of records from a database along with specific details from a related table. I knew SQL could…
handling empty arrays with postgres
2022-11-28
|~2 min read
|258 words
A few days ago, I wrote about handling arrays from Javascript to SQL. Turns out, I missed an edge case - empty arrays. If you try to set an…
testing - chai tests and database connection pools
2022-11-28
|~3 min read
|433 words
I’m writing some integration tests for an app that is confirming database queries are working the way I’m expecting. In order to prep the…
array handling: javascript to sql
2022-11-28
|~2 min read
|298 words
Yesterday we updated a Postgres record’s details based on the and returned the update in a single query. What if the details was not a…
postgres `returning` statement
2022-11-28
|~2 min read
|239 words
Often, after making a change to a database, we want to verify that the change was made and use the updated rows. A naïve approach would be…
adding comments to a sql statement
2022-11-28
|~1 min read
|123 words
There are two ways to add a comment to a SQL query - using and . The differences are as follows: With a comment surrounded by and , the…
postgres data types: array
2022-11-28
|~2 min read
|247 words
When looking around a table in Postgres today, I noticed a curious looking field. looked like a collection, but when I looked at the Data…
copying data from one column to another with sql
2022-11-28
|~2 min read
|214 words
If you need to copy all data from one column to another column in SQL, use the method. If it’s a simple copy for copy, without conditionals…
error handling: permission denied for sequence _id_seq…
2022-11-28
|~4 min read
|645 words
Recently, I was using NodeJS to write directly to a Postgres database when I got an error beginning with: What follows are the steps I took…