Inserting data from pgAdmin4 using csv
🔢DB Connections🟢SQL
Hi i want to know how i can insert data from a csv into my database?
Whenever i try to insert data i either get:
ERROR: "pg_read_server_files" role memberships are reserved, only superusers can grant them
or
Error:
NOTICE: table "tmp_ideology_mods_csv" does not exist, skipping ERROR: permission denied to COPY from a file Only roles with privileges of the "pg_read_server_files" role may COPY from a file. SQL state: 42501 Detail: Only roles with privileges of the "pg_read_server_files" role may COPY from a file. Hint: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
I cant do a simple insert from dashboard as it will not consider my keys which with i want to link.
I dont have a lot of knowledge about databases or supabase
My SQL code at the moment is like this if needed:
Whenever i try to insert data i either get:
ERROR: "pg_read_server_files" role memberships are reserved, only superusers can grant them
or
Error:
NOTICE: table "tmp_ideology_mods_csv" does not exist, skipping ERROR: permission denied to COPY from a file Only roles with privileges of the "pg_read_server_files" role may COPY from a file. SQL state: 42501 Detail: Only roles with privileges of the "pg_read_server_files" role may COPY from a file. Hint: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
I cant do a simple insert from dashboard as it will not consider my keys which with i want to link.
I dont have a lot of knowledge about databases or supabase
My SQL code at the moment is like this if needed:
DROP TABLE IF EXISTS tmp_ideology_mods_csv;
CREATE TEMP TABLE tmp_ideology_mods_csv
(
ideology_name TEXT,
modifier_name TEXT,
modifier_value NUMERIC
);
COPY tmp_ideology_mods_csv(ideology_name, modifier_name, modifier_value)
FROM 'F:/Game Development/Office/Gitbash Repo/World-in-your-hand-api/Setup Data/Ideology Categories/arts_culture.csv'
DELIMITER ','
CSV HEADER;
INSERT INTO ideology.ideology_criteria_modifiers (ideology_id, category, modifier_name, modifier_value)
SELECT i.ideology_id,
'arts_culture', -- category
t.modifier_name,
t.modifier_value
FROM tmp_ideology_mods_csv t
JOIN ideology.ideologies i
ON t.ideology_name = i.ideology_name;
