Postgres: Can you insert null into a bytea column?

[Mark Mandel]

Sorry about the slightly OT nature, but quick question on postgres -

I have a column that is of type bytea, is it actually possible to
insert a NULL value into it?

It is confgured to be nullable, but whenever I try and insert null
into the database using a <cfqueryparam>
like so:

<cfquery datasource="transfer_unit_postgres">
INSERT INTO tbl_BigStuff(bigstuff_clob,bigstuff_blob,bigstuffid)
VALUES
( <cfqueryparam null="true" cfsqltype="cf_sql_varchar"> ,
<cfqueryparam null="true" cfsqltype="cf_sql_blob"> ,
<cfqueryparam value="#createUUID()#" cfsqltype="cf_sql_varchar"> )
</cfquery>

I get the following error:

ERROR: column "bigstuff_blob" is of type bytea but expression is of type oid

However, if I write it like this:

<cfquery datasource="transfer_unit_postgres">
INSERT INTO tbl_BigStuff(bigstuff_clob,bigstuff_blob,bigstuffid)
VALUES
( <cfqueryparam null="true" cfsqltype="cf_sql_varchar"> ,
NULL ,
<cfqueryparam value="#createUUID()#" cfsqltype="cf_sql_varchar"> )
</cfquery>

Works just fine... so now I'm totally confused! :oD

Anyone got any help?

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com

[Zac Spitzer]

oracle requires an empty_lob() in this case, lobs can be a wee bit special

--
Zac Spitzer -
http://zacster.blogspot.com (My Blog)
+61 405 847 168

[Mark Woods]

> I have a column that is of type bytea, is it actually possible to
> insert a NULL value into it?

Yes, but bytea is not a large object, it a binary data type. Change the
cfsqltype to cf_sql_longvarbinary and it should work fine.

I'm not sure what magic is making your current code work for inserting a
value, but the error message when you try a null is due to the fact that
a large object in postgres is referenced using an object id but the data
type of your column is bytea.

Mark

[Chris Velevitch]

Leave out the column name in the insert statement and postgresql will
set it to the value of the default expression for that column. If
there no default expression then the default is null. Unless the
column constraint is not null.

Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
m: 0415 469 095
www.flashdev.org.au

Sydney Flash Platform Developers Group
April meeting: TBD
Date: Mon 28th April 6pm for 6:30 start
Details soon