The WorkingWithBlobs document describes how you’re supposed to be able to work with binary columns in Class::DBI. The example shows is for Oracle, and the corresponding Postgres code should work like this:

__PACKAGE__->data_type(column_name => { pg_type => DBD::Pg::PG_BYTEA } );

Alas, when I tried this approach, I kept getting the following error:

Can't change TYPE of param $3 to 0 after initial bind

Tracing the DBI call didn’t show anything funky, so I dug into the DBD::Pg source and discovered that it didn’t correctly deal with pre-bound columns. I’ve sumitted a bug report along with the following patch which fixes the problem:

DBD-Pg-1.32: dbdimp.c

771a772,778
> 	/* get the place holder */
> 	phs_svp = hv_fetch(imp_sth->all_params_hv, name, name_len, 0);
> 	if (phs_svp == NULL) {
> 		croak("Can't bind unknown placeholder '%s' (%s)",
> 					name, neatsvpv(ph_namesv,0));
> 	}
> 	phs = (phs_t*)(void*)SvPVX(*phs_svp);
798c805,810
<
---
> 	} else if (phs->is_bound) {
> 		bind_type = phs->ftype;
> 		sql_type_info = pg_type_data(bind_type);
> 		if(!sql_type_info)
> 			croak("%s is bound to bad type %d",
> 				name, bind_type);
807,816d818
<
< 	/* get the place holder */
< 	phs_svp = hv_fetch(imp_sth->all_params_hv, name, name_len, 0);
< 	if (phs_svp == NULL) {
< 		croak("Can't bind unknown placeholder '%s' (%s)",
< 					name, neatsvpv(ph_namesv,0));
< 	}
< 	phs = (phs_t*)(void*)SvPVX(*phs_svp);
<
<
818,819c820,821
< 		croak("Can't change TYPE of param %s to %d after initial bind",
< 					phs->name, sql_type);
---
> 		croak("Can't change TYPE of param %s to %d after initial bind to %d",
> 					phs->name, bind_type, phs->ftype);