PostgreSQL - Left pad value on COPY

Question

I'm bringing in data from Excel into a PostgreSQL Db. There's a lot wrong with this data, but one thing that seems to connect several tables is a customer_id. However, in the customer table I've a unique char(8) that always has a leading zero. Yes, if it were up to me I'd enforce this data weren't so screwy upstream, but I'm dealing with sales folks here, manufacturing there, financing, etc. And, the customer id ALMOST matches through these various sources! It is just that the customer_id some data doesn't have the leading zero, so customers.id = '01234567' does represent orders.customer_id = '1234567'.

I'm using COPY command in Postgres, which is a new thing to me. Unfortunately, I cannot define a foreign key relationship on customer.id because of this small discrepancy.

How would I do a COPY and tell the column value to add a leading zero? Is this possible? I'm hoping I can do it right in the COPY statement? Thanks for any insight in how to do this!

EDIT: A comment lead me to this documentation. I'll update with an answer after I figure this out. Looks like an ON BEFORE INSERT is what I'll need.

CREATE TRIGGER trigger_name 
   {BEFORE | AFTER} { event }
   ON table_name
   [FOR [EACH] { ROW | STATEMENT }]
       EXECUTE PROCEDURE trigger_function

You can't do it in `COPY`. You could create an `ON INSERT` trigger on the table for that column and have the trigger function pad the id. — Adrian Klaver, Nov 02 '20 at 22:19
Are there leading zeros in the remaining 7 digits? Otherwise you can cast the values to integer. — clamp, Nov 02 '20 at 22:26
Define your column as integer (may need to validate the excel, I never trust excel). If you must have the leading 0 then use lpad when you select the column. But that will still allow tour foreign key. If users push back on that then them they can either have the date correct or they can have a leading 0, but NOT both. — Belayer, Nov 02 '20 at 23:26
Looks like this is going to make me learn how to ignore a foreign key mismatch on the COPY op, if I'm to use ON INSERT to fix things. Thanks @AdrianKlaver — Neil Gaetano Lindberg, Nov 04 '20 at 14:03
It is the cost of dealing with mixed systems. I have a similar situation where data comes in space padded to a fixed string size and is used against tables that use `varchar` for the same field. I use a trigger to get rid of the extraneous spaces and all is well. — Adrian Klaver, Nov 04 '20 at 15:55
Would an `ON INSERT` trigger fire "on before" a row is added by COPY? I'll need to get into the docs. — Neil Gaetano Lindberg, Nov 04 '20 at 17:04

Neil Gaetano Lindberg · Answer 1 · 2020-11-06T22:49:54.300

I'm the original poster and this is the answer to my question. I was bringing in data from XLS to PG and the leading zeros on customer_id(s) were dropped when exporting XLS to CSV for a COPY into PG. Thanks be to an answer here that really pointed me down the right path: Postgresql insert trigger to set value

-- create table
CREATE TABLE T (customer_id char(8));

-- draft function to be used by trigger. NOTE the double single quotes.
CREATE FUNCTION lpad_8_0 ()
RETURNS trigger AS '
BEGIN
  NEW.customer_id := (SELECT LPAD(NEW.customer_id, 8, ''0''));
  RETURN NEW;
END' LANGUAGE 'plpgsql';

-- setup on before insert trigger to execute lpad_8_0 function
CREATE TRIGGER my_on_before_insert_trigger
BEFORE INSERT ON T
FOR EACH ROW
EXECUTE PROCEDURE lpad_8_0();

-- some sample inserts
INSERT INTO T
VALUES ('1234'), ('7');

Here's a working fiddle: http://sqlfiddle.com/#!17/a176e/1/0 NOTE: If the value here were larger than char(8) the COPY will still fail.

PostgreSQL - Left pad value on COPY

1 Answers1