patternModerate
Can't run digest() on PosrgreSQL 9.3 on RDS
Viewed 0 times
candigestrdsposrgresqlrun
Problem
Following the advice given on another answer, I tried using the
Even though pgcrypto exist:
btree_gin,btree_gist,chkpass,citext,cube,dblink,dict_int,dict_xsyn,earthdistance,fuzzystrmatch,hstore,intagg,intarray,isn,ltree,pgcrypto,pgrowlocks,pg_trgm,plperl,plpgsql,pltcl,postgis,postgis_tiger_geocoder,postgis_topology,sslinfo,tablefunc,tsearch2,unaccent,uuid-ossp
How can I enable the
Updates
I have co-asked on the AWS RDS forum, will posts mutually updated.
digest() function on PostgreSQL 9.3 on AWS RDS:devdb=> SELECT digest('blah', 'sha1');
ERROR: function digest(unknown, unknown) does not exist
LINE 1: SELECT digest('blah', 'sha1');
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.Even though pgcrypto exist:
devdb=> SHOW rds.extensions;btree_gin,btree_gist,chkpass,citext,cube,dblink,dict_int,dict_xsyn,earthdistance,fuzzystrmatch,hstore,intagg,intarray,isn,ltree,pgcrypto,pgrowlocks,pg_trgm,plperl,plpgsql,pltcl,postgis,postgis_tiger_geocoder,postgis_topology,sslinfo,tablefunc,tsearch2,unaccent,uuid-ossp
How can I enable the
digest() function?Updates
I have co-asked on the AWS RDS forum, will posts mutually updated.
digest() does not seem to exist on the db:devdb=> \df digest
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)Solution
The question was answered at the AWS forum by Shawn@AWS - The
Thank you for using RDS Postgres. The "rds.extension" parameter shows you what extensions are available in RDS Postgres. You still need to execute the "create extension" command.
Using your test case:
Addendum
rds.extension list shows available, rather than installed, modules.Thank you for using RDS Postgres. The "rds.extension" parameter shows you what extensions are available in RDS Postgres. You still need to execute the "create extension" command.
Using your test case:
postgres=> show rds.extensions;
... ,pgcrypto, ...
postgres=> create extension pgcrypto;
CREATE EXTENSION
postgres=> SELECT digest('blah', 'sha1');
digest
\x5bf1fd927dfb8679496a2e6cf00cbe50c1c87145
(1 row)Addendum
pg_available_extensions shows which extensions are available and which are already installed:postgres=> SELECT * FROM pg_available_extensions;
name | default_version | installed_version | comment
------------------------+-----------------+-------------------+---------------------------------------------------------------------
chkpass | 1.0 | | data type for auto-encrypted passwords
xml2 | 1.0 | | XPath querying and XSLT
plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
pgcrypto | 1.0 | | cryptographic functions
postgres_fdw | 1.0 | | foreign-data wrapper for remote PostgreSQL servers
...Code Snippets
postgres=> show rds.extensions;
... ,pgcrypto, ...
postgres=> create extension pgcrypto;
CREATE EXTENSION
postgres=> SELECT digest('blah', 'sha1');
digest
\x5bf1fd927dfb8679496a2e6cf00cbe50c1c87145
(1 row)postgres=> SELECT * FROM pg_available_extensions;
name | default_version | installed_version | comment
------------------------+-----------------+-------------------+---------------------------------------------------------------------
chkpass | 1.0 | | data type for auto-encrypted passwords
xml2 | 1.0 | | XPath querying and XSLT
plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
pgcrypto | 1.0 | | cryptographic functions
postgres_fdw | 1.0 | | foreign-data wrapper for remote PostgreSQL servers
...Context
StackExchange Database Administrators Q#81202, answer score: 17
Revisions (0)
No revisions yet.