Question 89133 in PostgreSQL Question posted in
The official documentation can be found here.

How can I declare Primary Key in Apache AGE – Postgresql

Here, I am creating a SoftwareEngineer node. I want to ensure that only one SoftwareEngineer can exist against one SocialSecurityNumber. How can I achieve this functionality using Apache AGE?

test=# SELECT * FROM cypher('staff_details', $$
CREATE(e: SoftwareEngineer {
name: 'Muneeb',
SocialSecurityNumber: '12345',
date: pg_catalog.now()
}) RETURN e
$$) as (e agtype);

When I run the above query twice, I got two nodes created that contain exactly same data.

test=# Select * from cypher('staff_details', $$ MATCH (v:SoftwareEngineer) RETURN v $$) as (v agtype);

Gives:

 {"id": 1407374883553281, "label": "SoftwareEngineer", "properties": {"date": "2023-04-01T07:23:10.069163+05:00", "name"
: "Muneeb", "SocialSecurityNumber": "12345"}}::vertex
 {"id": 1407374883553282, "label": "SoftwareEngineer", "properties": {"date": "2023-04-01T07:28:39.245981+05:00", "name"
: "Muneeb", "SocialSecurityNumber": "12345"}}::vertex
(2 rows)

Although id attribute is different, but the data is same. I want Apache AGE to restrict creating two nodes with same SocialSecurityNumber like Primary Key does.

Tags:

3

Answers


  1. 0

    There is a discussion on a similar issue on Github. Kindly check it out. From what I have tried and that works for your situation is:

    1. Create Function "get_ssn" that will return the property SocialSecurityNumber from the "properties" column

      CREATE OR REPLACE FUNCTION get_ssn(properties agtype) 
RETURNS agtype
AS
$BODY$
select agtype_access_operator($1, '"SocialSecurityNumber"');
$BODY$
LANGUAGE sql
IMMUTABLE;

    2. Create a unique index on the property "SocialSecurityNumber"

      CREATE UNIQUE INDEX person_ssn_idx ON staff_details."SoftwareEngineer"(get_ssn(properties)) ;

    You might need to first create vertex label (if it is not created already) before running the above CREATE INDEX query as

    SELECT * FROM create_vlabel('staff_details', 'SoftwareEngineer');

    Now when you try to add another node with the same SocialSecurityNumber, you get the error:

    ERROR: duplicate key value violates unique constraint "person_ssn_idx" DETAIL: Key (get_ssn(properties))=("12345") already exists.

    Login or Signup to reply.
  2. 0

    Create a unique SocialSecurityNumber for everyone so that two Softwareengineer nodes have same id(SocialSecurityNumber)

    CREATE CONSTRAINT ON (n:SoftwareEngineer) ASSERT n.SocialSecurityNumber IS UNIQUE;
    Login or Signup to reply.
  3. 0

    You can achieve that using MERGE as show below assuming SocialSecurityNumber is the primary key.

    SELECT * FROM cypher('staff_details', $$
    MERGE (e: SoftwareEngineer {
        SocialSecurityNumber: '12345'
    })
    SET e.name = 'Muneeb', e.date = COALESCE(e.date, timestamp())
    RETURN e
$$) as (e agtype);

SELECT * FROM staff_details."SoftwareEngineer";

    References

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search