Today I Learned

A Hashrocket project

157 posts about #sql

Treat null as if it is a known value #postgresql

When you query a table sometimes you want to check if a nullable field is not equal to a value. For example:

select * from sometable where afield != 'avalue';

However the query above will exclude rows where afield is null, so you would typically add that as an additional condition:

select * from sometable where afield is null or afield != 'avalue';

When you are doing it once it may be ok but as queries get bigger this makes the query messy and harder to read. Fortunately Postgres offers a more idiomatic way to check if a value does not equal something, including null values: is distinct from and is not distinct from.

select * from sometable where afield is distinct from 'avalue';

This query will return all the rows where afield is null or anything but avalue. Conversely:

select * from sometable where afield is NOT distinct from (select x from y limit 1);

will return all the values that are equal to the result of the subquery above and is useful when the result of the subquery could be null.

h/t Jack Christensen

Original docs:

`NULLIF` in Postgres

If there multiple values in a database column that both mean the the column for that row has no data, then the NULLIF function can be used in queries to normalize the values in that column.

Null char is 😵

chriserin=# \pset null 😵
Null display is "😵".
chriserin=# select nullif('NOTHING', 'NOTHING');
(1 row)

chriserin=# select nullif('something', 'NOTHING');
(1 row)

Nulls Last, Nulls First

Postgres query sorting has a great feature,nulls first/nulls last, that helps us specify where in a query result null values appear.

Nulls in Postgres are sorted as larger than any other value, so this is a descending sort containing null values (💀 == null):

example=# select * from users order by full_name desc;
 Tracy Jordan
 Liz Lemon
 Jack Donaghy

Reverse the null position with nulls last:

 example=# select * from users order by full_name desc nulls last;
 Tracy Jordan
 Liz Lemon
 Jack Donaghy
(6 rows)

Here’s the opposite query, ascending:

example=# select * from users order by full_name asc;
 Jack Donaghy
 Liz Lemon
 Tracy Jordan
(6 rows)

Now, reverse the null position with nulls first:

example=# select * from users order by full_name asc nulls first;
 Jack Donaghy
 Liz Lemon
 Tracy Jordan
(6 rows)

Lots of ways to explore our data.

Create Database from a Template

Today I got to see Postgres’ createdb invoked with a specified template, as such:

$ createdb -T prod_dump myapp_development

The -T feature flag corresponds to the TEMPLATE modifier in a SQL CREATE DATABASE statement. In the example above, this will create myapp_development that is a deep clone (structure and data) of prod_dump.

For more info, see:

$ man createdb


$ psql
user=# \h create database;

h/t Brian Dunn

Determining The Age Of Things

In PostgreSQL, we can determine the age of something (or someone) by passing a timestamp to the age function.

For instance, if we want to know how long it has been since y2k, we can run the following query:

> select age(timestamp '2000-01-01');
 16 years 4 mons 12 days

Additionally, if we want to know the amount of time between two dates, we can pass two timestamps to the age function.

For example, we can find out how old Prince lived to be by passing in the date of death and then date of birth:

> select age(timestamp 'April 21, 2016', timestamp 'June 7, 1958');
 57 years 10 mons 14 days

h/t Josh Davey

Dump And Restore A PostgreSQL Database

PostgreSQL comes with two command-line utilities for dumping and then restoring a database — pg_dump and pg_restore, respectively.

Using the pg_dump with the -Fc flag will create a dump of the given database in a custom format. The output of this command can be redirected into a file (the .dump extension is a standard convention):

$ pg_dump -Fc my_database > my_database.dump

Using the custom format option provides a couple benefits. The output is significantly compressed in comparison to a generic SQL dump. The dump and restoration is more flexible. Lastly, the dump can be performed in parallel if your machine has multiple cores to work with. Likewise, the restoration can be done in parallel with multiple jobs.

To restore the dump, create a fresh database and then use pg_restore:

$ createdb my_new_database
$ pg_restore -d my_new_database my_database.dump

Note: the dumped tables will depend on some user role. You will need to ensure that this role exists on the database cluster where the restore is happening. You can use the createuser command if necessary.

See the pg_dump docs and pg_restore docs for more details.

List Database Objects With Disk Usage

I’ll often times use \d or \dt to check out the tables in my database. This shows the schema, object name, object type (e.g. table), and owner for each.

By adding the + to that meta-command, I can also see the disk usage for each database object.

Here is an example of look at all tables in a database with the additional Size (or disk usage) information:

> \dt+
                              List of relations
 Schema |        Name        | Type  |   Owner    |    Size    | Description
 public | amount_types       | table | jbranchaud | 16 kB      |
 public | ingredient_amounts | table | jbranchaud | 8192 bytes |
 public | ingredient_types   | table | jbranchaud | 16 kB      |
 public | ingredients        | table | jbranchaud | 48 kB      |
 public | recipes            | table | jbranchaud | 16 kB      |
 public | schema_migrations  | table | jbranchaud | 16 kB      |
 public | users              | table | jbranchaud | 16 kB      |

Add a Comment in Postgres

Today I learned about a neat Postgres feature, commenting. Here is a statement adding a comment to a table:

hr-til_development=#  comment on table developers is 'these are the authors';

Let’s check it out:

hr-til_development=# \dt+ developers;
List of relations
-[ RECORD 1 ]----------------------
Schema      | public
Name        | developers
Type        | table
Owner       | jwworth
Size        | 16 kB
Description | these are the authors

According to the documentation, you can comment on many things. Use this wherever you feel a comment is necessary or appropriate.

h/t Chris Erin and Jack Christensen

Get The Size Of An Index

Want to get an idea of how much disk space that additional index is taking up? In PostgreSQL, you can query for it with the same methods discussed in Get The Size Of A Table and Pretty Print Data Sizes.

For instance, if I have a table with a users_pkey index and a users_unique_lower_email_idx index, I can check the sizes like so:

> select pg_size_pretty(pg_relation_size('users_pkey'));
 240 kB

> select pg_size_pretty(pg_relation_size('users_unique_lower_email_idx'));
 704 kB


Change The Current Directory For psql

When you start a psql session, your current directory is what psql will use as its current directory. This is important for meta-commands that use relative paths based on the current directory — for instance, the \i meta-command for importing files.

You can change the current directory within a psql session using the \cd meta-command.

If my current directory is home and there is a sql directory in home, these commands will do the following: ```sql \! pwd -- /home \cd sql \! pwd -- /home/sql ``` The\cdmeta-command even supports tab completion relative to the current directory. You can also change to your home directory using just\cd`.

Use Not Valid To Immediately Enforce A Constraint

When adding a constraint to a table, you can optionally include not valid. This tells Postgres that it doesn’t need to enforce the constraint on existing records in the table. At least not immediately. This constraint will be enforced for any updates and subsequent insertions. Thus, you can immediately enforce the constraint while giving yourself time to clean up or massage any existing records that conflict with the constraint.

Here is an example of how you would add a constraint this way:

alter table boxes
add constraint check_valid_length
check (length > 0) not valid;

Eventually, you will want to ensure that all data in the table conforms to the constraint. Once you get to that point, you can mark the constraint as valid with a validate constraint command:

alter table boxes
validate constraint check_valid_length;

As long as all records are valid with respect to this constraint, it will be marked as valid.

h/t Chris Erin

Lower Is Faster Than ilike

There are a couple ways to do a case-insensitive comparison of data in PostgreSQL. One way is to use the ilike operator for comparison. Another way is to use the lower() function on both sides of the = operator for comparison. Using lower() is a bit faster than using ilike.

When comparing

select * from users where email ilike '';


select * from users where lower(email) = lower('');

we find (via explain analyze) that using lower() was taking around 12ms where as the ilike example was taking around 17ms.

We earn orders of magnitude in performance when adding a functional index that uses the lower() function like so:

create unique index users_unique_lower_email_idx on users (lower(email));

After adding this index, the example using lower() drops to around 0.08ms.

For the full example and explain analyze outputs, see this document.

Add ON DELETE CASCADE To Foreign Key Constraint

The alter table command lets you do quite a bit. But when it comes to altering existing constraints, there is not much you can do. If you want to add an on delete cascade to an existing foreign key constraint, you are going to need two statements.

The first statement will drop the constraint and the second statement will recreate it with the addition of the on delete clause. Furthermore, you’ll want to do this in a transaction to ensure the integrity of your data during the transition between indexes.

Here is an example:


alter table orders
drop constraint orders_customer_id_fkey;

alter table orders
add constraint orders_customer_id_fkey
foreign key (customer_id)
references customers (id)
on delete cascade;



Print The Query Buffer In psql

I’ll often be composing a PostgreSQL query in Vim and decide I want to give it a try in psql. I copy the relevant snippet of SQL to my system buffer and then paste into psql. I’m usually hit with a mess of text like this though:

jbranchaud=# create table nullable_fields (
jbranchaud(#   id serial primary key,
  first varchar,
  last varchar
  id serial primary key,
jbranchaud(#   first varchar,
  last varchar
  first varchar,
jbranchaud(#   last varchar
  last varchar
jbranchaud(# )

Yikes. That’s not readable. Fortunately, psql provides a command for printing the current contents of the query buffer. By typing \p I’ll see a more readable version of what I just pasted in.

jbranchaud-# \p
create table nullable_fields (
  id serial primary key,
  first varchar,
  last varchar

After taking another glance at the snippet of SQL, I decide to complete the query to create my new table.

jbranchaud-# ;

Using Expressions In Indexes With PostgreSQL

Though we usually see column names by themselves when defining an index, it is also possible to create an index with an expression.

Let’s say I have a users table with an email column. Then I may end up creating an index like this

create index email_idx on users (email);

If I always perform queries on the email column with the lower() function, like this

select * from users where lower(email) = lower('');

then I will want to also create an index with that full expression — lower(email)

I can do this with a statement like the following

create index lower_email_idx on users (lower(email));

Without an index that uses the full lower(email) expression, select statements like the one above will be forced to do full sequential scans instead of indexed scans.

List Various Kinds Of Objects

Our PostgreSQL database can end up with all kinds of objects: tables, sequences, views, etc. We can use a variety of psql meta-commands to list the different types of (user-created) objects in our database.

  • \dt will list all the tables
  • \dE will list all the foreign tables
  • \di will list all the indexes
  • \ds will list all the sequences
  • \dv will list all the views
  • \dm will list all the materialized views

These can also be combined. For instance, to see all the tables and sequences, we can run \dts.

Creating Custom Types in PostgreSQL

PostgreSQL has support for creating custom types. When you need something more expressive than the built-in types and you don’t want your data spread across multiple columns, you can instead create a custom type.

create type dimensions as (
  width integer,
  height integer,
  depth integer

This new type can then be used in the definition of a new table

create table moving_boxes (
  id serial primary key,
  dims dimensions not null

and when inserting data

insert into moving_boxes (dims) values (row(3,4,5)::dimensions);

See the create type docs for more details.

Show All Versions Of An Operator

We may be familiar with PostgreSQL’s containment operator (@>). Maybe we’ve used it with an array before, so we understand the general idea. But now we are curious about what are the other types with which this containment operator can be used.

We can quickly find out the answer with the \do command in psql:

> \do @>
                               List of operators
   Schema   | Name | Left arg type | Right arg type | Result type | Description
 pg_catalog | @>   | aclitem[]     | aclitem        | boolean     | contains
 pg_catalog | @>   | anyarray      | anyarray       | boolean     | contains
 pg_catalog | @>   | anyrange      | anyelement     | boolean     | contains
 pg_catalog | @>   | anyrange      | anyrange       | boolean     | contains
 pg_catalog | @>   | jsonb         | jsonb          | boolean     | contains

The Left arg type and Right arg type columns tell us what we need to know.

This \do command can be used with any operator for a similar set of information.

h/t Bruce Momjian

Clear The Screen In psql

The psql interactive terminal does not have a built-in way of clearing the screen. What I usually do if I really need the screen cleared is quit, run clear from the shell, and then open a new psql session. This is unnecessary though. Instead, we can use the \! command to execute a shell command, in this case, the clear command.

> \! clear

This clears the screen in one step and keeps our current session running.

See the psql docs for more details.

Storing Emails With citext

Email addresses should be treated as case-insensitive because they are. If a user is trying to sign in with their email address, we shouldn’t care if they type or Both of those email addresses should be treated as equal and ultimately lead us to the same User record.

With the citext extension in PostgreSQL, we can create a column that acts as a case-insensitive text type. Any comparisons on a column of that type will internally have the lower function executed on the arguments.

The following example shows this in action:

create extension if not exists citext;

create table citext_emails (
  id serial primary key,
  email citext not null unique

insert into citext_emails (email) values ('');

select * from citext_emails where email = '';
--  id |      email
-- ----+------------------
--   1 |

See citext_emails.sql for a full example.

Renaming A Table

Using the alter table command in PostgreSQL, you can rename an existing table. This command will also update any references to the table such as via foreign key constraints. Just run a command like the following:

alter table ingredient_types rename to item_types;

Note that this may result in breaking a number of conventions. Foreign keys, sequences, and constraints with names eponymous to the original table will no longer follow convention despite the references being updated. These can be renamed as well if desired.

See renaming_table.sql for a full example.

See the alter table docs for more details.

Psql Help

Psql includes a built-in help command that is amazing.

With no arguments, it returns all the help:

app_development=# \h
Available help:

With a Postgres command as an argument, it returns a specific help:

app_development=# \h abort;
Command:     ABORT
Description: abort the current transaction

And with an asterisk, it prints the full description for all the help commands.

Plus we get this nicety:

Note: To simplify typing, commands that consists of several words do not have to be quoted. Thus it is fine to type \help alter table.

Write better SQL in your REPL today!

h/t Jack Christensen

Salt And Hash A Password With Postgres' pgcrypto

The pgcrypto extension that ships with PostgreSQL can be used to do a number of interesting things. This includes functions for doing salted password hashing. Using the crypt and gen_salt functions, we can securely store a user password and later compare it to plain-text passwords for authentication purposes.

create extension pgcrypto;

select crypt('pa$$w0rd', gen_salt('bf'));

> select (
    '$2a$06$Z7wmrkYMOyLboLcULUYzNe6nHUcWywSZTt6nSrT5Xdv/VLdJ4g99K' =
  ) as matched;

> select (
    '$2a$06$Z7wmrkYMOyLboLcULUYzNe6nHUcWywSZTt6nSrT5Xdv/VLdJ4g99K' =
  ) as matched;

The salt value is generated using the blowfish encryption algorithm (hence, the 'bf'). There is support for other algorithms such as md5.

See the pgcrypt documentation for more details.

Types and type casting in #PostgreSQL

To see the type of column or any entity in PostgreSQL use pg_typeof. Here’s an example:

select pg_typeof(array['thing']);


-- pg_typeof
-- ---------
-- text[]

To cast to another type use the :: operator:

select pg_typeof(array['thing']::varchar[]);


--      pg_typeof
-- -------------------
-- character varying[]

h/t Josh Branchaud

Terminating A Connection

Consider the scenario where you are trying to drop a database, but there are existing connections.

$ dropdb sample_db
dropdb: database removal failed: ERROR:  database "sample_db" is being accessed by other users
DETAIL:  There is 1 other session using the database.

If you don’t know where these connections are, you can terminate them within a psql session. You just have to figure out the pid of those connections. In List Connections To A Database, I explained how to get at the pid values of connections. Using the pid value and pg_terminate_backend(), you can terminate a connection.

> select pg_terminate_backend(12345);

To terminate all connections to a particular database, use a query like the following:

select pg_terminate_backend(
from pg_stat_activity
where pg_stat_activity.datname = 'sample_db'
  and pid <> pg_backend_pid();

This excludes the current session, so you’ll need to exit psql as well before dropping the database.


List Connections To A PostgreSQL Database

The pg_stat_activity table can be used to determine what connections there currently are to the PostgreSQL server and to a particular database. To see the process ids and usernames of all connection to your PostgreSQL server, run the following query:

> select pid, usename from pg_stat_activity;
  pid  |  usename
 57174 | jbranchaud
 83420 | jbranchaud

Include datname in the requested columns to figure out the database of each connection.

> select pid, usename, datname from pg_stat_activity;
  pid  |  usename   |  datname
 57174 | jbranchaud | hr_hotels
 83420 | jbranchaud | pgbyex

The results can be restricted to a particular database as necessary.

> select pid, usename from pg_stat_activity where datname = 'hr_hotels';
  pid  |  usename
 57174 | jbranchaud

Query Size of Postgres Array

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;
 Because Javascript
 Percent Notation
 DIY Grids for Designing UI in Illustrator
(3 rows)

When the dimension is 1, a more terse solution is cardinality:

hr-til_development=# select title from posts
hr-til_development=# where cardinality(slack_notified_at_likes_threshold) = 1;
 Because Javascript
 Percent Notation
 DIY Grids for Designing UI in Illustrator
(3 rows)


Default to Empty Array in Postgres

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.

This led me to learn how to set the default value to an empty array using Postgres array literal syntax. I was then able to include a not null constraint as an added benefit:

# db/migrate/20160211043316_add_slack_notified.rb

def up
  execute <<-SQL
    alter table posts
      add column slack_notified integer[] not null default '{}';

Sets With The Values Command

You can concisely create sets of values in PostgreSQL using the values command.

> values (1), (2), (3);

You can even create multiple columns of values.

> values (1, 'a', true), (2, 'b', false);
 column1 | column2 | column3
       1 | a       | t
       2 | b       | f

This is most often used with an insert command, but can be used on its own, as a subquery, within a CTE, etc.


Listing All Rows In A Table

In PostgreSQL, perhaps the more common way to list all rows in a table is with the following select command:

select * from bedding_types;

There is an alternative approach that also selects all rows from a table. It’s essentially a shorthand — the table command.

> table bedding_types;
 No Bed
 1 Full
 1 Double
 2 Double
 1 Twin
 2 Twins
 1 Queen
 2 Queen
 1 King
 2 Kings
 3 Kings
 Sofa Bed

h/t Jack Christensen

Be aware! Postgres rounds.

Yesterday, my pair and I created a test that calculated a value and compared that to the value of a calculation in the code we were testing. This worked out great except for one hitch, we were asserting about the derived value after it had been inserted into the database. What we didn’t count on is that Postgres rounds. Check this out:

create table money (amount numeric(4, 2));
insert into money (amount) values (10.342) returning amount;

insert into money (amount) values (10.347) returning amount;

Postgres rounds!

Postgres permissions to insert, but not return.

Postgres permissions can be harsh. If you don’t have the select permission on a table you might not be able to do some very rails like things that you think you ought to be able to do, take my friend Jimmy.

set role 'admin';
create role 'jimmy';
create table fruits (id serial primary key, name varchar);
grant insert on fruits to jimmy;
grant usage on fruits_id_seq to jimmy;

Given these permissions it’s possible for Jimmy to make inserts into the fruit table like so:

set role 'jimmy';
insert into fruits (name) values ('Apple');

But Rails wants a little more, it wants to know the id of the thing that was just created which is a problem because Jimmy doesn’t have select permissions.

set role 'jimmy';
insert into fruits (name) values ('Orange') returning id;
ERROR:  permission denied for relation fruits

Argh this complicates matters, but I’ll relent and give Jimmy the appropriate permissions so that he can add records through ActiveRecord.

set role 'admin';
grant select on fruits to jimmy;
set role 'jimmy';
insert into fruits (name) values ('orange') returning id;

Add value to Postgres enum type, dropping tho...

An enum type in Postgres is a type that can restrict a value to only certain values. It can be defined like this:

create type fruit as Enum ('orange', 'apple');

We can now cast a string to a fruit.

chriserin=# select 'orange'::fruit;

Well… some strings…

chriserin=# select 'rasberry'::fruit;
ERROR:  invalid input value for enum fruit: "rasberry"

It’s all good! We can add rasberry to the enum type.

chriserin=# alter type fruit add value 'rasberry';
chriserin=# select 'rasberry'::fruit;

Postgres allows you to add values to an enum type, but you can’t drop values which is a bit of an inconvenience when creating up/down db migrations like you do in Rails or with a tool like tern.

Set A Seed For The Random Number Generator

In PostgreSQL, the internal seed for the random number generator is a run-time configuration parameter. This seed parameter can be set to a particular seed in order to get some determinism from functions that utilize the random number generator. The seed needs to be something between 0 and 1.

We can see this in action by setting the seed and then invoking random() a couple times. Doing this twice, we will see the reproducibility we can achieve with a seed.

> set seed to 0.1234;

> select random();

> select random();
(1 row)

> set seed to 0.1234;

> select random();

> select random();

The seed can also be configured with the setseed() function.

See the PostgreSQL docs for more details.

Shuffle an array in postgres

Somtimes an array is just too sequential

select ARRAY[1,2,3,4,5];
chriserin=# select ARRAY[1,2,3,4,5];
(1 row)

I want to shuffle this array into an unpredictable mess of numbers, and one way to do this in PostgreSQL is to turn these array elements into rows, order them randomly and then squash the rows back into an array like so:

chriserin=# select array_agg(foo.x) from (select unnest(ARRAY[1,2,3,4,5]) x order by random()) foo;
(1 row)

The key here is the order by random() clause, which is a special postgres case. order by 0.284325 which is a number that random() could return, is not valid sql. There are dragons in order by, read here for more info

Pretty Print Data Sizes

Use the pg_size_pretty() function to pretty print the sizes of data in PostgreSQL. Given a bigint, it will determine the most human-readable format with which to print the value:

> select pg_size_pretty(1234::bigint);
 1234 bytes

> select pg_size_pretty(123456::bigint);
 121 kB

> select pg_size_pretty(1234567899::bigint);
 1177 MB

> select pg_size_pretty(12345678999::bigint);
 11 GB

This function is particularly useful when used with the pg_database_size() and pg_relation_size() functions.

> select pg_size_pretty(pg_database_size('hr_hotels'));
 12 MB

Get The Size Of A Table

In Get The Size Of A Database, I showed a PostgreSQL administrative function, pg_database_size(), that gets the size of a given database. With the pg_relation_size() function, we can get the size of a given table. For instance, if we’d like to see the size of the reservations table, we can executing the following query:

> select pg_relation_size('reservations');

This gives us the size of the reservations table in bytes. As you might expect, the referenced table needs to be part of the connected database and on the search path.

See the Postgres docs for more details.

array indexes in postgres

Lets say I have an array of fruit types (Apple, Pear, Banana, Tangerine) and I want to turn each element in the array into a row I can do this.

select unnest(fruits) 
from (select Array['Apple', 'Pear', 'Tangerine', 'Banana'] fruits) as list;

which returns:

(4 rows)

But it would be nice to have an array index as column in case the order of the elements in this array had some meaning. Postgres has a function generate_subscripts to help you with array index elements.

select generate_subscripts(fruits, 1), unnest(fruits) from (select Array['Apple', 'Pear', 'Tangerine', 'Banana'] fruits) as list;

which returns:

 generate_subscripts |  unnest
                   1 | Apple
                   2 | Pear
                   3 | Tangerine
                   4 | Banana

Throw a minus 1 in there if you want to be base zero :).

Day Of Week By Name For A Date

In Day Of Week For A Date, I explained how to determine what day of the week a date is as an integer with PostgreSQL. This used the date_part() function. By using the to_char() function with a date or timestamp, we can determine the day of the week by name (e.g. Monday). For instance, to determine what day today is, try a statement like the following:

> select to_char(now(), 'Day');

The Day part of the second argument is just one of many template patterns that can be used for formatting dates and times.

See Data Type Formatting Functions in the Postgres docs for more details.

pg Prefix Is Reserved For System Schemas

Have you ever tried to create a schema with pg_ as the first part of the name of the schema? If so, you probably didn’t get very far. Postgres won’t let you do that. It reserves the pg_ prefix for system schemas. If you try to create a schema in this way, you’ll get an unacceptable schema name error.

> create schema pg_cannot_do_this;
ERROR:  unacceptable schema name "pg_cannot_do_this"
DETAIL:  The prefix "pg_" is reserved for system schemas.

Compute Hashes With pgcrypto

The pgcrypto extension that comes with PostgreSQL adds access to some general hashing functions. Included are md5, sha1, sha224, sha256, sha384 and sha512. Any of these hashing functions can be applied to an arbitrary string using the digest function. Here are example of the md5 and sha1 algorithms:

> create extension pgcrypto;

> select digest('Hello, World!', 'md5');

> select digest('Hello, World!', 'sha1');

See the pgcrypto docs for more details.