psql copy

[pgnovice]

I've greated my table with "not null default 0000" on several fields. Some of them are char and others numeric. When loading my flat file, and there is no data between the delimiters, shouldn't the default value get loaded? By having a default value defined, and the section on the flat file is comming in null, will the default value get loaded?

Thanks

[michaelb]

... and the section on the flat file is coming in null

What exactly do you mean by that? A small sample of your data may help.
What values these columns are set to, NULLs or empty strings ?
Are you using the CSV file?

[pgnovice]

I've attached the table build, and will follow it with some default data.

 
 1 drop table mco61_provider;
  2
  3 create table mco61_provider
  4 (
  5 business_prov_name varchar(30) not null,
  6 business_ind char(1) not null default '*',
  7 svr_addr_ln1 varchar(30) not null,
  8 svr_addr_ln2 varchar(20) default '*',
  9 svr_addr_city varchar(20) not null,
 10 svr_addr_state char(2) not null,
 11 svr_addr_zip numeric(9) not null default 000000000,
 12 svr_prov_cnty numeric(2) not null default 00,
 13 svr_loc_ind char(1) not null default 'N',
 14 svr_phone_num numeric(10) not null default 0000000000,
 15 svr_fax_num numeric(10) default 0000000000,
 16 svr_email_addr varchar(60) not null default '@',
 17 taxonomy varchar(10) not null,
 18 specialty_code char(3) not null,
 19 provider_type char(2) not null,
 20 board_cert_spec char(1) not null default 'N',
 21 ein numeric(9) not null default 000000000,
 22 ssn_num numeric(9) default '000000000',
 23 full_lic_num varchar(10) not null,
 24 npi numeric(10) not null,
 25 medicaid_id varchar(10) not null,
 26 tenncare_id varchar(15) not null,
 27 medicare_id varchar(7) not null,
 28 dea_number varchar(11) not null,
 29 mcc numeric(3) default 000,
 30 plan_ind char(1) not null default 'O',
 31 cntract_begin_dte numeric(8) not null default 00000000,
 32 cntract_end_dte numeric(8) not null default 00000000,
 33 pat_age_start numeric(2) not null default 00,
 34 pat_age_end numeric(3) not null default 000,
 35 presumptive_elig char(1) not null default 'N',
 36 prov_prenatal char(1) not null default 'N',
 37 new_pat char(1) not null default 'N',
 38 num_pat_assign numeric(4) not null,
 39 treat_all_sexes char(1) not null default 'B',
 40 ob_services char(1) not null default 'N',
 41 gs_services char(1) not null default 'N',
 42 pc_services char(1) not null default 'N',
 43 epsdt_services char(1) not null default 'N',
 44 best_practice_network_ind char(1) not null default ' ',
 45 bho_prov_serv_code char(2) not null default ' ' 
)

LIMESTONE DRUG|*|200 W MARKET ST||ATHENS|AL|3 56110709|96||25 02323811|0||333 600000X|240|24| N|83 0362786||112319 |1477568087||10 0886|||61|I|197 00101|0|0|999|N |N|Y|0|B|N|N|N| N|N|N

copy command;
copy mco61_provider FROM '/home/mike/psql/A61-a.TXT' with delimiter '|' null as '';

Hopefully this will help.

thanks

[michaelb]

I ran some tests and it appears that COPY command does not honor the DEFAULT values.
It does respect the NOT NULL directive, so when I had a null values (||) in my input file the COPY failed. When I took out the NOT NULL spec. in the table definition default values were not used by COPY, so I ended up with NULLs on the table.

Depending on how your input file is generated you may be able to replace the missing values with their defaults.
Another option, which you may consider (especially if this is just a one-time operation) is to run a post-copy update

UPDATE table_name set field_name = DEFAULT where field_name is NULL;

[michaelb]

... it appears that COPY command does not honor the DEFAULT values.

This was wrong, when I think about it this behavior is correct and consistent with what we get when we execute a comparable sql: "INSERT into ..."

Both, COPY and INSERT will use the default values when column in question is omitted in the insert list, and both will raise an error when explicit NULL is given for a field defined with NOT NULL. In this case the default value, if any, is irrelevant.

[pgnovice]

Thanks for the insight

[jengka]

Hai

I'm trying to use VB.NET to read a csv file, so to process its data
and eventually store (some of) it into a posgresql database. However,
I don't know how to copy csv data from the specific cell column , for example I want copy data start from cell C13 to the end .

this is my code

conn.Open()
'copy data from csv to posgresql
cmd = New NpgsqlCommand("copy tesTable9 (no1,no2,no3) from 'D:\\www\\try.c sv' WITH DELIMITER ','", conn)
cmd.ExecuteNonQ uery()
cmd.Clone()