Data stored as scientific notation, unable to convert to numeric

[Dara Unglaube]

Greetings.

I am having a problem with scientific notation in a field. I have multiple
variables that are in a spreadsheet and loaded via CSV into postgres. The
results have a variety of significant digits which I would like to retain
(from integer to 0.000001). I import into a double precision field and
anything with 5 or more places right of the decimal gets automatically
converted to scientific notation (even if in the CSV it is imported as
0.000001). If I try to CAST these as numeric field or replace within the
double precision field, they revert back to scientific notation. Does
anyone have any idea on how to fix this?

Any help is greatly appreciated!

Dara



*--Dara J. Olson Unglaube*
Database Manager
Great Lakes Indian Fish & Wildlife Commission
[phone] ext.2129
***@glifwc.org <***@glifwc.org>

[Stephen Froehlich]

It sounds like the number is getting imported to the database properly. The problem you’re having is with the text formatting of the output.

Typically the formatting of the output is something one does in one’s application (R, Python, Excel, etc.).

To format numbers to text within PostgreSQL, see https://www.postgresql.org/docs/current/static/functions-formatting.html

--Stephen

From: Dara Unglaube <***@glifwc.org>
Sent: Friday, June 29, 2018 6:41 AM
To: pgsql-***@postgresql.org
Subject: Data stored as scientific notation, unable to convert to numeric


Greetings.

I am having a problem with scientific notation in a field. I have multiple variables that are in a spreadsheet and loaded via CSV into postgres. The results have a variety of significant digits which I would like to retain (from integer to 0.000001). I import into a double precision field and anything with 5 or more places right of the decimal gets automatically converted to scientific notation (even if in the CSV it is imported as 0.000001). If I try to CAST these as numeric field or replace within the double precision field, they revert back to scientific notation. Does anyone have any idea on how to fix this?

Any help is greatly appreciated!

Dara