Save data to file txt or csv from postgres

Question

Please tell is it possible to save any data fo file when I execute update statement ?

I execute this function in postgres pgadmin / psql

do
$$
declare 
    nr int;
    status varchar(100);
begin
    select count(*) into nr from film where title = 'Academy Dinosaur';
    if nr = 1 then
        update film
        set length = 1000
        where title = 'Academy Dinosaur';
        status := 'Success';
        end if;
end;$$

and I want to write to file (if update was executed succesfully ) something like this: Title = title, Status = Succes (if successful) but I don't know how to do this and how to return value of status to file ? I don't want to copy of all table.

The whole PL/pgSQL block is completely unnecessary. You can just run the UPDATE directly which will be much faster as no useless select count(*) is executed. — user330315
– user330315, Commented Feb 11, 2021 at 18:02
@a_horse_with_no_name I know this is just an example, and I do not know how to log information when this block was executed succesfully.In my real block I check taht ony row has been found. If not I want to log info: Status: File does not exist — Andreas
– Andreas, Commented Feb 11, 2021 at 18:09

Stefanov.sm · Accepted Answer · 2021-02-11 18:10:22Z

1

do language plpgsql
$$
brgin
 update film
    set length = 1000
  where title = 'Academy Dinosaur';
 if FOUND then
   copy (select format('Title = %s, Status = %s', 'Academy Dinosaur', 'Success')) 
     to 'path-to-your-file';
 enf if;
end;
$$;

format and text substitution is there only for the case when you want to use variables. Otherwise just (select 'Title = Academy Dinosaur, Status = Success')

edited Feb 11, 2021 at 18:10

answered Feb 11, 2021 at 18:05

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Andreas Over a year ago

One question I completed the path 'C: \ Desktop' and can I have problem with run in windows terminal ? like this: psql -h localhost -p 5432 -U user-d db -f C:\Desktop\script.sql In this file I have script where I put output file but I got error character with byte sequence 0xc5 0xbc in encoding "UTF8" has no equivalent in encoding "WIN1252" I'm looking in google but still nothing help

Andreas Over a year ago

unless I am able to run it on a server where there is a linux base. Now I still problem with copy on windows: ERROR: syntax error in or near \copy (select 'Title = aaaa, Status =wwwww' I'm trying this: \copy (select 'Title = aaaa, Status =success') to 'C:\Users\Desktop\output.csv';

Stefanov.sm Over a year ago

COPY reads and writes files on the server. 'path-to-your-file' should be local on the server as seen by the server process. It can not refer to a location on your terminal. And btw plpgsql scripts and psql commands are not the same thing.

Andreas Over a year ago

Ok,tomorrow I will try run this script on my postgres server which works on linux, I hope taht this works :) Today I run this script by psql because I create a postgres server on Wndows and I hoped when I run like this psql -h localhost -p 5432 -U user-d db -f C:\Desktop\script.sql in windows terminal that will works.

Andreas Over a year ago

I know that exist \copy for run in psql in windows and this works but \copy take all table for example and I want to specific information logged to file.

|

Collectives™ on Stack Overflow

Save data to file txt or csv from postgres

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related