How to Insert JSON data to BYTEA column in the table in POSTGRESQL?

Question

So, I came across the function script which is converting JSON data to BYTEA and then insert as a record in the table in a BYTEA column. (As I assumed what the code is doing)

In Oracle the function utl_raw.cast_to_raw converts the data to blob data and records the data in the table in Blob column. Giving the following output message, "anonymous block completed"

The following is the code,

CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
DECLARE
    v1 "TBL1"."COL1"%TYPE;
    v2 "TBL1"."COL2"%TYPE;
BEGIN
v1 := utl_raw.cast_to_raw('{
 "APPLICATION": {
   "MEMORY": {
   "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""              
     }');

 v2 := utl_raw.cast_to_raw('{
    "APPLICATION": {
   "MEMORY": {
   "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""  
                    }');

 INSERT INTO "TBL1" ("SN","COL1","COL2") values(1,v1, v2);
END;
$$

LANGUAGE 'plpgsql';
COMMIT;

[SOLVED] Edit: So generally the problem was nothing. So all I had to remove was the utl_raw.cast_to_raw line and execute it as it is. No errors, no problems. THANKS

Answer 1

There is no reason to convert anything, just insert the JSON strings (after making them valid JSON):

CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
BEGIN
 INSERT INTO "TBL1" ("SN","COL1","COL2") 
 values(1, 
    '{
      "APPLICATION": {
         "MEMORY": {
         "OPTIONS" :{
                      "SOMETHING" : "SOMETHING",
                      "format" : "SOMETHING",
                      "System" : "",
                      "IP" : "",
                      "Port" : "",
                      "template" : "",
                      "Path" : "" ,
                      "Name" : "QUEUE",
                      "URL" : ""              
        }}}}', --<< add the missing curly braces!
     '{
      "APPLICATION": {
       "MEMORY": {
       "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""  
        }}}}'); --<< add the missing curly braces!
END;
$$
LANGUAGE plpgsql;
COMMIT;

---

You also don't need PL/pgSQL for this. A language sql function would be enough (you would need to remove the begin and end though)

Answer 2

There is no reason to ever do that. PostgreSQL has a binary JSON type, jsonb. Just store your data as JSONB.

CREATE TABLE foo (
    jsondata  jsonb
);

INSERT INTO foo (jsondata) VALUES ( $${"foo": "bar"}$$ );

This will give you a ton of operators and functions that will natively work with this type. JSONB are varlena (just like bytea) under the hood anyway

See also,

• JSONB Functions and Operators