I want to read a text file and add it to the table.
nowdate contains the date of the day.
The date is entered in the text file. To specify the file, get it with nowdate and connect it.
I think the date is probably a problem, but it's what I expect to output.
The content of the text file is aaa date.log.
Applicable source code
COPY table name FROM 'path where the file is' || nowdate || '.log' DELIMITER ','; The contents of the text file below aaa, admin01,111, login, 2019-11-01 13:32:37 bbb, admin01,222, logout, 2019-11-01 13:46:30 ccc, 0611001,333, login, 2019-10-01 13:47:32
If i try the above, || returns a syntax error.
COPY table (aaa, bbb, ccc, ddd, eee) FROM 'path string 2019-11-30' (DELIMITER (','));
Then, it is registered normally.
Please include more detailed information here.
Answer # 1
I'm not sure why the text file can't be read, but I'd like to give you a sample of a function that loads a csv file with a date in the file name below.
CREATE OR REPLACE FUNCTION load_csv (var_nowdate VARCHAR) RETURNS boolean AS $BODY $ DECLARE var_sql VARCHAR; var_csvfile VARCHAR; BEGIN var_csvfile: = 'C: \\ temp \\ demo-' || var_nowdate || '.csv'; RAISE NOTICE 'LOAD [%] file', var_csvfile; var_sql: = 'COPY demo FROM' '' || var_csvfile || '' '' || 'CSV DELIMITER' ',' ''; RAISE NOTICE '%', var_sql; EXECUTE var_sql; RAISE NOTICE 'LOADED [%] file', var_csvfile; RETURN true; END; $BODY $ LANGUAGE plpgsql;
The CSV file is assumed to be stored in the following image under
C: \\ temp.
demo-2019-12-03.csv demo-2019-12-04.csv demo-2019-12-05.csv
Function calls are
select load_csv ('2019-12-05');
select load_csv (TO_CHAR (NOW (), 'YYYY-MM-DD'));
It has been confirmed to work with PostgreSQL 11.0.1.
- postgresql get data for today's date
- postgresql - specifying the restore/backup version when a different postgressql version is installed with pgadmin
- [php] specifying the source file for include
- in postgresql, how can i delete a row by specifying "no data" as the deletion condition?
- oracle - how to include today's dynamically obtained date in the table name when creating the table
- [visual studio] how to include without specifying relative path [vervisualstudiocommunity 2015]
- specifying multiple table values as extraction conditions in postgresql