How to store string spaces as null in numeric column

Question

I want to get records from my local txt file to postgresql table.

I have created following table.

create table player_info ( Name varchar(20), City varchar(30), State varchar(30), DateOfTour date, pay numeric(5), flag char )

And, my local txt file contains following data.

John|Mumbai| |20170203|55555|Y David|Mumbai| |20170305| |N Romcy|Mumbai| |20170405|55555|N Gotry|Mumbai| |20170708| |Y

I am just executing this,

copy player_info (Name, City, State, DateOfTour, pay_id, flag) from local 'D:\sample_player_info.txt' delimiter '|' null as '' exceptions 'D:\Logs\player_info'

What I want is, For my numeric column, If 3 spaces are there, then I have to insert NULL as pay else whatever 5 digits numeric number.

pay is a column in my table whose datatype is numeric.

Is this correct or possible to do this ?

can't you run an update query to make 3 spaces to null after copy command? — YogeshR
– YogeshR, Commented Oct 13, 2017 at 14:44
@WannaBeSqlExpert : I can run but firstly I want that 3 spaces data in my numeric column. Which is not coming as is — Jay
– Jay, Commented Oct 14, 2017 at 14:12
@Andrew : Yes As I have a log file.. So it is showing an error about numeric. And remaining records get inserted into table. — Jay
– Jay, Commented Oct 14, 2017 at 14:15
I don't understand what you mean by wanting 3 spaces in the numeric column. I recommend pre-processing the txt file with awk or sed to delete the three spaces. Then it will be imported as a NULL, matching the NULL AS clause. (Works in your State column already.) If you mean you want to store either a number or spaces, numeric won't do that, but why would you want to? You can always convert NULL to spaces on output. — Andrew Lazarus
– Andrew Lazarus, Commented Oct 14, 2017 at 19:39

Paul Maxwell · Accepted Answer · 2017-10-14 21:09:34Z

1

You cannot store strings in a numeric column, at all. 3 spaces is a string, so it cannot be stored in the column pay as that is defined as numeric.

A common approach to this conundrum is to create a staging table which uses less precise data types in the column definitions. Import the source data into the staging table. Then process that data so that it can be reliably added to the final table. e.g. in the staging table set a column called pay_str to NULL where pay_str = ' ' (or perhaps LIKE ' %')

edited Oct 14, 2017 at 21:09

answered Oct 14, 2017 at 21:03

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to store string spaces as null in numeric column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related