PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – using 'copy to' command and change type postgrsql

I have a file that I want to copy into a table, the table has three columns,

create table item (
    name Text,
    t Time without time zone,
    status Text);

the File looks like this:

book, 2022-08-13 13:30:01,out
char,2022-08-13 13:35:22, inside 
lather,2022-08-13 13:36:00, out

is there a way to convert the Datetime value to time value without do a table with Datetime and then Altering it?

Tags:

2

Answers


  1. 0

    The best possible way that I think would be to create a staging table with a timestamp column to temporarily hold the data. Use the COPY command to load the CSV data into the staging table. Then, insert the data into your target table, converting the datetime values to time using datetime::time in the SELECT statement. Also you can drop the staging table if it’s no longer needed.

    Create a staging table to hold the raw data:

    CREATE TABLE staging_item (
    name TEXT,
    datetime TIMESTAMP,
    status TEXT
);

    Copy the data from the file into the staging table:

    COPY staging_item (name, datetime, status)
FROM '/path/to/your/file.csv' 
DELIMITER ',' 
CSV;

    Insert the transformed data into your target table:

    INSERT INTO item (name, t, status)
SELECT 
    name,
    datetime::time,
    status
FROM 
    staging_item;
    Login or Signup to reply.
  2. 0

    The CSV file:

    cat time_test.csv 
book, 2022-08-13 13:30:01,out
char,2022-08-13 13:35:22, inside 
lather,2022-08-13 13:36:00, out

    Create the table:

    create table item (
    name Text,
    t Time without time zone,
    status Text);

    Import the data using psql:

    copy item from time_test.csv with csv
COPY 3

    The result:

      name  |    t     |  status  
--------+----------+----------
 book   | 13:30:01 | out
 char   | 13:35:22 |  inside 
 lather | 13:36:00 |  out

    The datetime string will automatically be cast to time on import.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search