I have noticed on some of my imports with copy I will get a funny character now and then in some of the data. When it shows up it will always be the first character in the string. I did a simple test to try to find it.
Here is my setup:
Table –
-- Table: pigdig
-- DROP TABLE pigdig;
CREATE TABLE pigdig
(
"name" character varying
)
WITH (
OIDS=FALSE
);
ALTER TABLE pigdig OWNER TO postgres;
Data in forimport.txt :
mary
tary
lary
copy command being used:
COPY pigdig (name)
FROM E'\\Documents and Settings\\username\\My Documents\\postgres\\Pg Daily Inventory\\forimport.txt'
WITH DELIMITER AS ','
NULL AS 'NULL'
Now, when looking at the data in the table I get:
"mary" but marry has a box in front of “mary”, the box has a solid border with a clear center.
"tary"
"lary"
Problem – why did this come into the table, what is the character?
I pasted the data into excel and isolated the character, in excel it shows up as a box with a question mark. In excel , I did =CHAR(ROW()) in rows 1 to 255 with the offending character in E1, then tried to equate it to all of the characters in rows 1 through 255 – it was not equivalent to any.
So I do not know what the character is and how it made its way into the import process.
That in and of itself is an issue, plus since it is now in postgres when I query I cannot query on ‘mary’
Further, when I am in pgadmin III and view table data then past that mary into a query window, the character in question is no longer a box but is a light bullet point looking character.
Any ideas on:
How and why this came into the import process and got into my data?
What the character is?