The COPY statement can be used to transfer data between tables and files. COPY TO copies from tables to files. COPY FROM copies from files to tables.
Example:
yugabyte=# CREATE TABLE dim (c1 INT PRIMARY KEY, c2 VARCHAR);
CREATE TABLE
yugabyte=# INSERT INTO dim SELECT 1, 'A';
INSERT 0 1
yugabyte=# INSERT INTO dim SELECT 1, 'B';
INSERT 0 1
yugabyte=# INSERT INTO dim SELECT 1, 'C';
INSERT 0 1
yugabyte=# CREATE TABLE fact (c1 INT PRIMARY KEY, dim_c1 INT NOT NULL);
CREATE TABLE
yugabyte=# ALTER TABLE fact ADD CONSTRAINT fact_dim_c1_fk FOREIGN KEY (dim_c1) REFERENCES dim(c1);
ALTER TABLE
yugabyte=# \! cat /root/fact.txt
1|1
2|2
3|3
4|4
yugabyte=# COPY fact FROM '/root/fact.txt' WITH (DELIMITER '|');
ERROR: insert or update on table "fact" violates foreign key constraint "fact_dim_c1_fk"
DETAIL: Key (dim_c1)=(4) is not present in table "dim".
CONTEXT: COPY fact, line 5: ""
Yikes! That failed because of the Foreign Key Constraint violation.
Starting in YugabyteDB 2.15, we can use the DISABLE_FK_CHECK option to skip any foreign key checks when copying new rows to the table!
Note that the primary use case for disabling foreign key constraints is to get better performance on your load. You would only disable the check if you know your data doesn’t have any vioaltions – especially when loading into a production database!
However, in this case I am loading into a development database, and I don’t want my entire load to fail becuase of some missing look up data.
So I will disable the foreign key constraints, then remove the rows after that do not belong.
Example:
yugabyte=# SELECT substring(version() from 'YB-([^\s]+)') AS "YugabyteDB Version";
YugabyteDB Version
--------------------
2.15.0.0-b0
(1 row)
yugabyte=# COPY fact FROM '/root/fact.txt' WITH (DELIMITER '|', DISABLE_FK_CHECK);
COPY 4
yugabyte=# SELECT * FROM fact;
c1 | dim_c1
----+--------
1 | 1
4 | 4
2 | 2
3 | 3
(4 rows)
yugabyte=# DELETE FROM fact WHERE NOT EXISTS (SELECT NULL FROM dim WHERE dim_c1 = c1);
DELETE 1
yugabyte=# SELECT * FROM fact;
c1 | dim_c1
----+--------
1 | 1
2 | 2
3 | 3
(3 rows)
Have Fun!