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

Postgresql – I'm trying to add a fixed value in the method copy

I’m trying to add a fixed value in the method copy.
There is a csv file, but in addition to the data from it, I want to add a fixed value to the table.
In vertica there is column‑as‑expression.

create table if not exists public.test
    (
    ID int,
    OPTION_TYPE varchar(500),
    dttm date, 
    NUM_CSV int
    )

Copy:

 COPY public.test(
            ID,OPTION_TYPE,DTTM,
            NUM_CSV AS 1
            )
            FROM STDIN
            WITH (FORMAT CSV)

I tried to do it via query, but it didn’t work.

Tags:

2

Answers


  1. 0

    Randomly inventing new syntax is rarely the path to success.
    The only way I can think of is changing the default value:

    ALTER TABLE public.test ALTER num_csv SET DEFAULT 1;

    Then omit that column in the COPY statement:

    COPY public.test (id, option_type, dttm) FROM STDIN WITH (FORMAT 'csv');
    Login or Signup to reply.
  2. 0

    You can achieve this by by providing the fixed value directly in the COPY command.
    The query is as follows:

    COPY public.test (ID, OPTION_TYPE, DTTM, NUM_CSV)
FROM '/path/to/your/csv/file.csv' 
WITH (FORMAT CSV, DELIMITER ',');

UPDATE public.test SET NUM_CSV = 1;

    Use the COPY command to load data from the CSV file into the table. Then, you run an UPDATE statement to set the value of NUM_CSV to 1 for all rows in the table.

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

Back To Top
Search