postgres - Digital Craftsman

Digital Craftsman

Sign in Subscribe

postgres

A collection of 4 posts

Using Nowdoc instead of single or double quotes for Postgres queries in PHP

Using Nowdoc instead of single or double quotes for Postgres queries in PHP

When using aliases in a Postgres SQL query, you need to use double quotes in your query. $sql = ' SELECT facility.facility_id AS "facilityId" FROM facility '; When using strings (for example to build JSON objects), you need to use single quotes in your query. $sql = "

Delete JSON property in Postgres

Delete JSON property in Postgres

When migrating JSON data in Postgres, it's often necessary to delete properties within a JSON object. There's a neat operator for it #-. In this example the property comment was stored as an empty string instead of the property just missing. So we delete the property

Add NOT NULL constraint on column in Postgress

Add NOT NULL constraint on column in Postgress

You can't just add a new non-nullable column to a table when it already has entries in it, as those would be pre filled with NULL. An easy solution is to create it with DEFAULT NULL, update the data and then make it non-nullable. This can look like

Adapt timezones on Heroku

Adapt timezones on Heroku

I'm using the dynos of Heroku and the Postgres database addon from Heroku. Both are on UTC by default and Heroku is specifically advising against changing it. So make sure you know the repercussions. Dynos For the dynos you can simple use an environment variable called TZ and