migrating to agensgraph create foreign table error

Question

So, I'm taking a first look at migrating a PostgreSQL db to agensgraph db. I'm using the manual https://bitnine.net/wp-content/uploads/2016/11/AgensGraph_Quick_Guide.pdf

first export as csv:

SET CLIENT_ENCODING TO 'utf8';
\COPY samples.samples TO 
'C:\Users\garyn\Documents\graph_migration\pg_csv\samples_samples.csv' 
WITH DELIMITER E'\t' CSV;

And on page 20 I follow the first steps, creating the foreign table:

CREATE EXTENSION file_fdw;
CREATE SERVER import_server FOREIGN DATA WRAPPER file_fdw; 
CREATE FOREIGN TABLE vlabel_profile ( id graphid, properties text) SERVER import_server 
OPTIONS( FORMAT 'csv', HEADER 'false', 
FILENAME 'C:\Users\garyn\Documents\graph_migration\pg_csv\samples_samples.csv', 
delimiter E'\t');

ERROR: cannot create table in graph schema SQL state: XX000

Now, I haven't set any column names (as header=false) and I haven't changed the id graphid, properties text since the manual says it is setting up the table, but it states the file directory, any ideas how to get past this error? I'm back to being a noob.

The next steps will be:

CREATE FOREIGN TABLE elabel_profile ( id graphid, start graphid, "end" graphid, properties text) SERVER import_server OPTIONS( FORMAT 'csv', HEADER 'false', FILENAME '/path/file.csv', delimiter E'\t');

Then execute the import

CREATE VLABEL test_vlabel; LOAD FROM vlabel_profile AS profile_name CREATE (a:test_vlabel =row_to_json(profile_name)::jsonb);

CREATE ELABEL test_elabel; LOAD FROM elabel_profile AS profile_name MATCH (a:test_vlabel), (b:test_vlabel) WHERE (a).id::graphid = (profile_name).start AND (b).id::graphid = (profile_name).end CREATE (a)-[:test_elabel]->(b);

------------ UPDATE ------------

I'm now trying with the northwind dataset, again following the agens tutorial: https://bitnine.net/tutorial/english-tutorial.html

DROP GRAPH northwind CASCADE;
CREATE GRAPH northwind;
SET graph_path = northwind;
DROP SERVER northwind;
CREATE SERVER northwind FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE categories (
CategoryID int,
CategoryName varchar(15),
Description text,
Picture bytea
) 
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME 'D:\northwind\categories.csv', delimiter ',', quote '"', null '');

Same error

Answer 1

re: cannot create table in graph schema This is an error you will get when your schema is the same as the name of a graph - or there is some other problem related to the default schema.

The default schema is called public. To check your current schema enter

select current_schema();

If it's not public you can set it with

set schema public;

then try to create a table

create table mytable(id int);

Answer 2

I have tried to create a foreign table with northwind dataset you mentioned but it works just fine for me as you see the below screen shot.

I installed the agensgraph and tried the sample with its latest version which is 2.1.0 since I didn't have agensgraph on my window OS.

If you let me know the version of agensgraph you are currently using and how you are accessing to agensgraph, I would be able to help you out more.