Menu
How should a (CSV?) text file be formatted so that it can be imported (with COPY?) into an array column in a PostgreSQL (8.4) table?
Given table testarray:
Column | Type |
---------+-------------------------|
rundate | date |
runtype | integer |
raw | double precision[] |
labels | character varying(16)[] |
results | double precision[] |
outcome | character varying(8)[] |
and
COPY testarray from '/tmp/import.txt' CSV
neither of the following contents of import.txt work:
2010/06/22,88,{{1,2},{3,4}},{{1,2},{3,4}},{{1,2},{3,4}},{{1,2},{3,4}}
2010/06/22,88,1,2,3,4,1,2,3,4,1,2,3,4,1,2,3,4
2010/06/22,88,'{{1,2},{3,4}}','{{1,2},{3,4}}','{{1,2},{3,4}}','{{1,2},{3,4}}'
2010/06/22,88,'1,2,3,4','1,2,3,4','1,2,3,4','1,2,3,4'
572
After creating the sample CSV file and table, you can now easily execute the PostgreSQL Import CSV task via any of the following methods: Method 1: Perform PostgreSQL Import CSV Job using the COPY Command. Method 2: Perform PostgreSQL Import CSV Job using PgAdmin.
PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.
Copy into pre-existing table: INSERT INTO [Table to copy To] SELECT [Columns to Copy] FROM [Table to copy From] WHERE [Optional Condition]; Copying data between tables is just as easy as querying data however it will take a bit longer to run than a normal query.
COPY testarray from '/tmp/import.txt' CSV
2010-06-22,88,"{{1,2},{3,4}}","{{1,2},{3,4}}","{{1,2},{3,4}}","{{1,2},{3,4}}"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
