HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMajor

How do I install pgcrypto in PostgreSQL 8.4?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlpgcryptoinstallhow

Problem

I'm using Ubuntu Server 10.10 and I have installed PostgreSQL 8.4 using apt-get install postgresql. I would like to use the built-in sha1() function, but it seems that I have to install pgcrypto first. But I don't know how to install it.

There is no pgcrypto if I try to install it using apt-get install pgcrypto and I don't find any files starting with pgcrypto in my system (I tried find / -name "pgcrypto*").

How do I install pgcrypto so I can use the digest('word-to-hash','sha1') function in my database queries?

Update: I'm struggling to install pgcrypto on another Ubuntu machine. After installing the package using sudo apt-get install postgresql-contrib-8.4 how do I install it to my current PostgreSQL database?

Solution

PostgreSQL 9.1+

Note that I'm working on Ubuntu 12.04, which uses postgresql 9.1.

There, I needed to:

sudo apt-get install postgresql-contrib


And then in my database:

postgres@ztrustee:~$ psql test
psql (9.1.3)
Type "help" for help.
test=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION


And now I can use pgcrypto functionality, gen_random_bytes():

test=# create table test ( 
  id 
    text 
    not null 
    default encode( gen_random_bytes( 32 ), 'hex' ) 
    primary key, 
  value 
    text 
); 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
test=# \d test
                            Table "public.test"
 Column | Type |                         Modifiers                          
--------+------+------------------------------------------------------------
 id     | text | not null default encode(gen_random_bytes(32), 'hex'::text)
 value  | text | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

test=# insert into test (value) VALUES ('scoobydoo');
INSERT 0 1
test=# select * from test;
                                id                                |   value   
------------------------------------------------------------------+-----------
 76dd5bd0120d3df797f932fbcb4f8aa5088e215ee2b920dddbff59c8595fbac7 | scoobydoo

Code Snippets

sudo apt-get install postgresql-contrib
postgres@ztrustee:~$ psql test
psql (9.1.3)
Type "help" for help.
test=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION
test=# create table test ( 
  id 
    text 
    not null 
    default encode( gen_random_bytes( 32 ), 'hex' ) 
    primary key, 
  value 
    text 
); 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
test=# \d test
                            Table "public.test"
 Column | Type |                         Modifiers                          
--------+------+------------------------------------------------------------
 id     | text | not null default encode(gen_random_bytes(32), 'hex'::text)
 value  | text | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

test=# insert into test (value) VALUES ('scoobydoo');
INSERT 0 1
test=# select * from test;
                                id                                |   value   
------------------------------------------------------------------+-----------
 76dd5bd0120d3df797f932fbcb4f8aa5088e215ee2b920dddbff59c8595fbac7 | scoobydoo

Context

StackExchange Database Administrators Q#1883, answer score: 21

Revisions (0)

No revisions yet.