Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kussw-0001rQ-Ie for pgsql-admin@arkaria.postgresql.org; Thu, 31 Dec 2020 08:00:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kussv-00026v-2O for pgsql-admin@arkaria.postgresql.org; Thu, 31 Dec 2020 08:00:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kussu-00026o-BM; Thu, 31 Dec 2020 08:00:36 +0000 Received: from out1-45.antispamcloud.com ([185.201.16.45]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kusss-0000Ow-04; Thu, 31 Dec 2020 08:00:35 +0000 Received: from hp309.hostpapa.com ([69.90.160.170]) by mx134.antispamcloud.com with esmtpsa (TLSv1.2:AES128-GCM-SHA256:128) (Exim 4.92) (envelope-from ) id 1kussk-000eyr-Oi; Thu, 31 Dec 2020 09:00:30 +0100 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=pendari.org ; s=default; h=Content-Transfer-Encoding:Content-Type:MIME-Version:References :In-Reply-To:Message-ID:Date:Subject:Cc:To:From:Sender:Reply-To:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=r7d2+Zoq7Ps9p6Fmf9a/vVNEv7xmPLSVrNQJUURt0gs=; b=DQcUMexgr+xpgMcDbA2mjtJpRk +FxoOpH+ZG8NJ9eYwki3ENww9a95QNElXX94h9A9Ci8Yuk+yVbYZhkWBUVt26uwU28+uyuE6CoGWj ZobUBhw/AWTVzS4UleCO38D3MjBpq7n2YoJi9lkJvRiV40Ylb1wAy27688DIlIgXYA++4CDYcsKx0 6IkRFtldLVXM1U9hXDrG4ZTm4ywRiiIeX5Qxbck0eiCvs4y84QAvjfmt+AJ8SGae3H62JNLGi++sX vzckB9OcSUEzOwQOQssm2KMnQWFjOiOp+u3oPWcYFwKVfBIvPviryiY9q7shfjuXyZfNI4kzBBJ7H MqqbFWUg==; Received: from [1.129.211.174] (port=50726 helo=[192.168.20.53]) by hp309.hostpapa.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.93) (envelope-from ) id 1kuss0-00DKZn-Uo; Thu, 31 Dec 2020 18:59:42 +1100 From: "Gavan Schneider" To: "Sachin Kumar" Cc: pgsql-admin@lists.postgresql.org, pgsql-admin@postgresql.org, krishna@thewebconz.com Subject: Re: How to manage null value while copy data from csv Date: Thu, 31 Dec 2020 18:59:33 +1100 X-Mailer: MailMate (1.14r5757) Message-ID: <95624F2E-5D82-4B3B-BC0A-B20F3DCC5150@pendari.org> In-Reply-To: References: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_MailMate_15FCBC75-10CF-40AB-9525-A30B1F7DC57D_=" Content-Transfer-Encoding: 8bit X-OutGoing-Spam-Status: No, score=-1.0 X-Get-Message-Sender-Via: hp309.hostpapa.com: authenticated_id: list.pg.gavan@pendari.org X-Authenticated-Sender: hp309.hostpapa.com: list.pg.gavan@pendari.org X-AuthUser: list.pg.gavan@pendari.org X-Originating-IP: 69.90.160.170 X-Spampanel-Domain: outgoing-shared.hostpapa.com X-Spampanel-Username: outgoingshared Authentication-Results: antispamcloud.com; auth=pass (login) smtp.auth=outgoingshared@outgoing-shared.hostpapa.com X-Spampanel-Outgoing-Class: unsure X-Spampanel-Outgoing-Evidence: Combined (0.15) X-Recommended-Action: accept X-Filter-ID: Pt3MvcO5N4iKaDQ5O6lkdGlMVN6RH8bjRMzItlySaT9WLQux0N3HQm8ltz8rnu+BPUtbdvnXkggZ 3YnVId/Y5jcf0yeVQAvfjHznO7+bT5yOHT8DVHTcWdamK47FfTMVD+E+PFMzq5Q8DE2vo/sRxRxC vtlzpSsfQqu/FVon3WhPLT67qRVP7kwiZHWd1qJ35WtYzPhSi2G2OS6bWUjBtUR+WaQi/qe3fZXY 9wSqAC9Kao+OhSHHaZgb7Z/ed9ms6ETxlXz1moWVHS+t7bNixFJKaS16KwJqe6ayXxTNpELO2NIh LJd3vZdNtZEKARqEVnetkOLX+8NZ+eoC+Ud4k0QtF7clZGPkIyxL763oK4VJtwo6ccGxGVWorGjz kbYMsXf62OTYzgXj1l+in7Z7+RMhknk554Mpgi5Y2oS/SK1T8IC5jOdZDW5R2sjdxSv2y140b41n 8EDhYqcO3jZPvB8HqSV16yvCzvjnIKXYKx37iWMOeS8tCROtoe5Wt2fYVZ7Siwc2iIHTWqeRj4x+ ILDZxF6/klpL0ZY+NjEf90QhYlM69+3dj0PcPmN7oWLVBHTimOWR11aa1Dn23QEGQ0VrS0dDJ9gE sqV0wk3wKYrFWT1ROcYC8FxG576lHCGHEPNbsQhwAs85AiyUODY3l3z7Z1++xZDvXRSpoN8g2PVQ D/edt3mC+NVeGzJ4hKPR/s8o8aPw1d4YK6Uv9AkSo3oSKlHMxVnkurBPHtW+f1Lk4FnztaYXJcGb +ggC0uY69Sj2GHhVg4yr6+WUDxZh4xSHBJ626RU1uDxVDqaeWxyUa1f5jBPfC406tFcEYDiWCtJg m6eJwYHybkfZDFShaCtkDCRyQb3u2sYT2rLF0tBvqUCnw/rUhZhNUCq/JyrC52ac1u0pJMaCyhb6 55eqJ/eUOERFOR/GbIqHXN46x5IhGbBw771zLLT+3FIUKbdMZg/VJYd75DNbZsWG1k3VoSwmTNyz mADxV2xdSGWrzks8CVsONrMJuGzuoGnKTKcyTBeztrUj85K8JfdMkYcxI5ZqtJ6Of1YZV+zsAUKS Svmsa4RWMaykPqczABQ/gV8qRN8Rk3AbXYQrPW9MwdQkzN40eTXlWiUAYdLmsJdAoPLRJ85krgs7 /xGLCJCJYR4JYcNZtCSTuN0JXEXQRnSMYUgrmUVwCI97T/Y2VgOGo7Drb8jjJ5kTAAhMpmW6Ebjp 58aPvVhcOsRpeOfKMJPRFw== X-Report-Abuse-To: spam@quarantine10.antispamcloud.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=_MailMate_15FCBC75-10CF-40AB-9525-A30B1F7DC57D_= Content-Type: text/plain; charset="UTF-8"; format=flowed Content-Transfer-Encoding: 8bit On 31 Dec 2020, at 17:25, Sachin Kumar wrote: > when I am uploading data (CSV File) using the COPY command. I am > getting > below mention error. > > ERROR: null value in column "adj_type" violates not-null constraint. > This indicates the relevant column in the database table has been defined to **never** allow a NULL value. > When I define null it gives no error message, but how to manage null > value > n data without defining it, because there is many more columns. when > it > defines null it gives an error if null value comes you are violating > null. > What is meant by “define null”? Is this where you put a real value in the CSV, or a dummy text value such as “null”? You could massage the csv so all the gaps get a dummy value, then handle this in the SQL table. Otherwise > Please suggest how to manage null value in postgresql? > Basic options 1. Fix the raw csv data so there are no gaps — the person who defined the database wasn’t expecting any 2. Fix the database so it will accept NULL values — assuming this is ok with the business model 3. Accept the csv data into a temporary table which tolerates NULL, massage the data then insert into the real database Regards Gavan Schneider --=_MailMate_15FCBC75-10CF-40AB-9525-A30B1F7DC57D_= Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On 31 Dec 2020, at 17:25, Sachin Kumar wrote:

when I am uploading data (CSV File) using the COPY command. I am get= ting
below mention error.
ERROR: null value in column "adj_type" violates not-null constraint.

This indicates the relevant column in the database table = has been defined to never allow a NULL value.

When I define null it gives no error message, but how to manage null= value
n data without defining it, because there is many more columns. when it
defines null it gives an error if null value comes you are violating null= =2E

What is meant by =E2=80=9Cdefine null=E2=80=9D? Is this w= here you put a real value in the CSV, or a dummy text value such as =E2=80= =9Cnull=E2=80=9D?
You could massage the csv so all the gaps get a dummy value, then handle = this in the SQL table.

Otherwise

Please suggest how to manage null value in postgresql?

Basic options

  1. Fix the raw csv data so there are no gaps =E2=80=94 the person who de= fined the database wasn=E2=80=99t expecting any
  2. Fix the database so it will accept NULL values =E2=80=94 assuming thi= s is ok with the business model
  3. Accept the csv data into a temporary table which tolerates NULL, mass= age the data then insert into the real database

Regards
Gavan Schneider

--=_MailMate_15FCBC75-10CF-40AB-9525-A30B1F7DC57D_=--