Category: postgresql-10

Auto Added by WPeMatico

Deleting child record doesn’t delete foreign key reference

I am using postgreSQL 10.7. I have this strange occurrence where I fail to see the problem.
Whenever I delete all child-records referring to a parent, somehow the reference between these two tables persists.

I have a child and parent table:

CREATE TABLE IF NOT EXISTS parent
(
    ID SERIAL NOT NULL CONSTRAINT PK_parent PRIMARY KEY,
    reference VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS child
(
    ID SERIAL NOT NULL CONSTRAINT PK_child PRIMARY KEY,
    parentID INT NOT NULL,
);

ALTER TABLE child
ADD CONSTRAINT FK_child_parent_ID FOREIGN KEY (parentID) REFERENCES parent (ID) MATCH FULL;

I delete all the records from the child table referring to the parent:

DELETE FROM child WHERE parentID = 1;

I select all the records from the child table referring to the parent:

SELECT * FROM child WHERE parentID = 1; // 0 records returned

I try to delete the parent record from the parent table

DELETE FROM parent WHERE ID = 1;

// result
ERROR: update or delete on table "parent" violates foreign key constraint "fk_child_parent_id" on table "child" Detail: Key (id)=(1) is still referenced from table "child".

I know about the ON DELETE option on the FOREIGN KEY constraint, but I just want to do this once…

What do I fail to see?

Go to Source
Author: leon de vries

postgresql error while using column values in jsonb_set function

I am trying to update a field value in jsonb column with text value present in another column but getting some syntax errors; not getting any solution.

i am trying to swap values of OutgoingVertexid & IncomingVertexId in below JSONB

'{
   "Owner":[
      {
         "Edgeid":10,
         "Weight":100,
         "Active":1,
         "OutgoingVertexid":"",
         "IncomingVertexid":"G",
         "EdgeColor":"Black"
      }
   ]
}'

so used below code by putting all values OutgoingVertexid & IncomingVertexid value in temp table.

UPDATE temp_table 
        SET 
        owner_property = CASE 
                            WHEN owner_outgoing_edge IS NOT NULL 
                                THEN jsonb_set(owner_property, '{OutgoingVertexid}', '""')
                            ELSE 
                                jsonb_set(owner_property, '{OutgoingVertexid}', ''||owner_incoming_edge::jsonb||'') 
                                END;

but getting below error:

ERROR: invalid input syntax for type json LINE 7: … jsonb_set(owner_property, ‘{OutgoingVertexid}’, ”””||ow… ^ DETAIL: Token “‘” is invalid.

If

Thanks in Advance

Go to Source
Author: VBAGuy