How to add missing values of file on COPY command

user_6513660 0
Viewed 81

I have a table with a column that comes from a file, although I'm certain that for the other column the value is missing on the file.

Here's the table:

create table if not exists user(
    id varchar(36) primary key,
    relevance varchar(3) not null,
    constraint relevance_check check (relevance in ('ONE', 'TWO'))
);

The command I want to populated the table with:

copy user(id') from '/home/users_ids.txt';

The problem is that my column relevance is not null, and I'd like to set default values on the relevance column when copying, but I'm not sure if that's possible. I cant set a default value on the tables because I need to import data from many files and each one would have a different value on the relevance field.

Can I achieve what I want using the copy command or there's another approach for this?

postgresql sql
1 Answers

You can set the column's default value just for a while, e.g.:

alter table "user" alter relevance set default 'ONE';
copy "user"(id) from '/home/users_one.txt';

alter table "user" alter relevance set default 'TWO';
copy "user"(id) from '/home/users_two.txt';

alter table "user" alter relevance drop default;

The solution is simple and efficient when you are sure that the import takes place in only one session at a time but is not safe if you intend to use it simultaneously in more than one session. A safer alternative, in this case, could be to use a temporary table, e.g .:

create temp table ids(id text);
copy ids(id) from '/home/users_one.txt';
insert into "user"
select id, 'ONE'
from ids;
user_19957380
Related