473,326 Members | 2,125 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

psql copy

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
Apr 27 '07 #1
6 7421
michaelb
534 Expert 512MB
... 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?
Apr 30 '07 #2
I've attached the table build, and will follow it with some default data.
Expand|Select|Wrap|Line Numbers
  1.  
  2.  1 drop table mco61_provider;
  3.   2
  4.   3 create table mco61_provider
  5.   4 (
  6.   5 business_prov_name varchar(30) not null,
  7.   6 business_ind char(1) not null default '*',
  8.   7 svr_addr_ln1 varchar(30) not null,
  9.   8 svr_addr_ln2 varchar(20) default '*',
  10.   9 svr_addr_city varchar(20) not null,
  11.  10 svr_addr_state char(2) not null,
  12.  11 svr_addr_zip numeric(9) not null default 000000000,
  13.  12 svr_prov_cnty numeric(2) not null default 00,
  14.  13 svr_loc_ind char(1) not null default 'N',
  15.  14 svr_phone_num numeric(10) not null default 0000000000,
  16.  15 svr_fax_num numeric(10) default 0000000000,
  17.  16 svr_email_addr varchar(60) not null default '@',
  18.  17 taxonomy varchar(10) not null,
  19.  18 specialty_code char(3) not null,
  20.  19 provider_type char(2) not null,
  21.  20 board_cert_spec char(1) not null default 'N',
  22.  21 ein numeric(9) not null default 000000000,
  23.  22 ssn_num numeric(9) default '000000000',
  24.  23 full_lic_num varchar(10) not null,
  25.  24 npi numeric(10) not null,
  26.  25 medicaid_id varchar(10) not null,
  27.  26 tenncare_id varchar(15) not null,
  28.  27 medicare_id varchar(7) not null,
  29.  28 dea_number varchar(11) not null,
  30.  29 mcc numeric(3) default 000,
  31.  30 plan_ind char(1) not null default 'O',
  32.  31 cntract_begin_dte numeric(8) not null default 00000000,
  33.  32 cntract_end_dte numeric(8) not null default 00000000,
  34.  33 pat_age_start numeric(2) not null default 00,
  35.  34 pat_age_end numeric(3) not null default 000,
  36.  35 presumptive_elig char(1) not null default 'N',
  37.  36 prov_prenatal char(1) not null default 'N',
  38.  37 new_pat char(1) not null default 'N',
  39.  38 num_pat_assign numeric(4) not null,
  40.  39 treat_all_sexes char(1) not null default 'B',
  41.  40 ob_services char(1) not null default 'N',
  42.  41 gs_services char(1) not null default 'N',
  43.  42 pc_services char(1) not null default 'N',
  44.  43 epsdt_services char(1) not null default 'N',
  45.  44 best_practice_network_ind char(1) not null default ' ',
  46.  45 bho_prov_serv_code char(2) not null default ' ' 
  47.  
LIMESTONE DRUG|*|200 W MARKET ST||ATHENS|AL|356110709|96||2502323811|0||33360000 0X|240|24|N|83 0362786||112319|1477568087||100886|||61|I|19700101 |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
Apr 30 '07 #3
michaelb
534 Expert 512MB
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
Expand|Select|Wrap|Line Numbers
  1. UPDATE table_name set field_name = DEFAULT where field_name is NULL;
  2.  
May 1 '07 #4
michaelb
534 Expert 512MB
... 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.
May 2 '07 #5
Thanks for the insight
May 7 '07 #6
jengka
1
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.csv' WITH DELIMITER ','", conn)
cmd.ExecuteNonQuery()
cmd.Clone()
Oct 26 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

15
by: Daniel Schuchardt | last post by:
Hi @ all, i'm sure there was a psql-function to transfere my Blob-Data to the server but I can't remember. I have a script like this : UPDATE xy SET z = lo_import('localpath_and_file'); ...
4
by: Phil Campaigne | last post by:
Hi All, I just installed postgresql 7.3.4 as an upgrade to 7.3.2 and all went well untill I tried to log into a database that I successfully created. Here are the steps in question: bash-2.05b$...
7
by: Willem Herremans | last post by:
I am developing a client application for postgreSQL in Tcl/Tk (see http://gborg.postgresql.org/project/pfm ). It mainly uses PgTcl or pgintcl. I don't have any problems with those, but I am also...
1
by: Josué Maldonado | last post by:
Hello List, I'm having this issue with beta 8.0 C:\pgsql\bin>pg_dump -U postgres farmacia > xfar.sql Password: C:\pgsql\bin>psql -U postgres farmacia2 < xfar.sql Password: psql: FATAL: ...
2
by: John DeSoi | last post by:
I'm looking for a way to build psql on OS X so that it can be copied to another computer without any external dependencies. In particular, if I build the standard distribution and then copy psql to...
2
by: Russ Brown | last post by:
Hello, Today I tried connecting to my database locally via psql. I got the usual welcome & basic help messages, but it never got to the prompt: it just hung. So I checked top and the psql...
2
by: Jeffrey W. Baker | last post by:
I just noticed something unexpected when using psql. I had written a shell script to bulk-load some hundreds of files into a database, and move each file to success/ or failure/ directories...
33
by: John Sidney-Woollett | last post by:
With the advent of postgres v8, would it be possible to change the default behaviour of psql from AUTOCOMMIT=ON to AUTOCOMMIT=OFF ? Although this might break backward compatibility, it might be...
4
by: Kevin Murphy | last post by:
This is a tip for the record in case it helps somebody else in the future. I have an import script that relies on a stored procedure that runs as a trigger on inserts into a temporary table. ...
3
by: oksofar | last post by:
Hello - I'm running PG 8.1 on Windows XP. I've installed the server to run as a service. The psql command fails to connect to the server, although I can connect with other clients. When I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.