whether batch insertion avaliable?

[jq679]

How to import data in batches ?
Use create to insert data one by one seems inconvenient sometimes.

[pdpotter]

When working directly in SQL, it can be more convenient to use prepared statements. I haven't found a way to actually create multiple nodes or edges in one query though. An exemple of using prepared statements can be found in the tests: https://github.com/apache/incubator-age/blob/7ba89b01c8e249ae2df77854698fc87eec90dabb/regress/expected/cypher_create.out#L457-L468

When working on code connecting to the database, some libraries provide functions to execute the same statement with multiple values, examples: asyncpg (Python), node-postgress (not tested) (Node.js)

[JoshInnis]

If your data is already in Postgres, you could make a pl/pgsql function that takes in your arguments of name and title and creates a node, such as

CREATE OR REPLACE FUNCTION public.create_person(name text, title text)
RETURNS void
LANGUAGE plpgsql
VOLATILE
AS $BODY$
BEGIN
	load 'age';
	SET search_path TO ag_catalog;
	EXECUTE format('SELECT * FROM cypher(''graph_name'', $$CREATE (:Person {name: %s, title: %s})$$) AS (a agtype);', quote_ident(name), quote_ident(title));
END
$BODY$;

Then you could call the function like:

SELECT public.create_person(sql_person.name, sql_person.title) 
FROM sql_schema.Person AS sql_person;

This would create a vertex for every row in sql_schema.Person.

[kysmou]

@JoshInnis By using function create_person, I used the following syntax for inserting data at version 0.4.0.

select public.create_person('1a', '2a'); --> success
select public.create_person('a', '2a'); --> fail

2021-05-17 10:29:09.416 KST [215722] ERROR: variable a does not exist at character 61
2021-05-17 10:29:09.416 KST [215722] QUERY: SELECT * FROM cypher('test_graph', $$CREATE (:Person {name: a, title: "2a"})$$) AS (a agtype);
2021-05-17 10:29:09.416 KST [215722] CONTEXT: PL/pgSQL function public.create_person(text,text) line 5 at EXECUTE
2021-05-17 10:29:09.416 KST [215722] STATEMENT: select public.create_person('a', '2a');
ERROR: variable a does not exist
LINE 1: ...ROM cypher('test_graph', $$CREATE (:Person {name: a, title: ...
^
QUERY: SELECT * FROM cypher('test_graph', $$CREATE (:Person {name: a, title: "2a"})$$) AS (a agtype);
CONTEXT: PL/pgSQL function public.create_person(text,text) line 5 at EXECUTE

In my opinion, It seems that an error occurs when the first letter is not a number among the argument values ​​of the create_person function.

[mingfang]

It would be great if we can simulate Neo4j's LOAD CSV.
Imagine a function called load_csv_with_headers(url) that returns an array of lines.
Then we can write Cypher like this

UNWIND load_csv_with_headers("http://<data url>") as line
CREATE (:Artist {name: line.Name, year: toInteger(line.Year)})

[mingfang]

After much trial and error this is what I came up with to load data from CSV

CREATE EXTENSION IF NOT EXISTS http;
CREATE EXTENSION IF NOT EXISTS age;

LOAD 'age';
SET search_path = ag_catalog, "$user", public;


CREATE OR REPLACE FUNCTION public.load_http(url text) RETURNS agtype AS $$
    LOAD 'age';
    SET search_path = ag_catalog, "$user", public;

    SELECT agtype_build_list(content) FROM http_get(url);
$$ LANGUAGE sql;


--- SELECT * FROM public.load_http('https://docs.google.com/spreadsheets/d/1pBbCabAK6u6EIuyu_2XUul4Yxvf2w_Od6QYC_yEc4q4/gviz/tq?tqx=out:csv&sheet=Population_projections');

SELECT * FROM cypher('playground', $$
    WITH public.load_http(toString('https://docs.google.com/spreadsheets/d/1pBbCabAK6u6EIuyu_2XUul4Yxvf2w_Od6QYC_yEc4q4/gviz/tq?tqx=out:csv&sheet=Population_projections&/popn')) as content
    WITH split(content[0], '\n') as lines
    UNWIND lines as line
    WITH split(line, ',') as row
    MERGE (v:ROW {id: row[0]})
    SET v.year = row[0], v.n18_to_19 = row[1], v.total = row[2]
    RETURN v
$$) as (v agtype);

[jrgemignani]

@rafsun42 @muhammadshoaib

[mingfang]

In some cases you might want to create a graph using the result of a SQL query.
Here is a example of how that can be done.

CREATE EXTENSION IF NOT EXISTS age;

LOAD 'age';
SET search_path = ag_catalog, "$user", public;


CREATE OR REPLACE FUNCTION public.load_sql(query agtype) RETURNS SETOF agtype AS $$
BEGIN
    RETURN QUERY EXECUTE FORMAT('
        WITH query AS (%s) 
        SELECT agtype_build_list(query) 
        FROM query
    ', query::text);
END
$$ LANGUAGE plpgsql VOLATILE;


SELECT * FROM cypher('playground', $$
    WITH public.load_sql('select * from movies') AS rows
    UNWIND rows AS row
    MERGE (v:MOVIE {id: row.title})
    SET v=row
    RETURN v
$$) AS (v agtype);

Note this method doesn't work exactly as of now due to this bug #1634.
The work around is instead of SET v=row you have to set each field, e.g. SET v.title = row.title.