GRANT READ ON DIRECTORY exportdir TO PUBLIC Create a directory object for UTL_FILE and grant permissions CREATE DIRECTORY exportdir AS 'd:' Enable UTL_FILE GRANT EXECUTE ON UTL_FILE TO PUBLIC Note that before using UTL_FILE package in Oracle you may need to enable it and create a directory object : DEFINE_COLUMN (cur, 1, row_value, 4000 ) ĭBMS_SQL. Select_stmt VARCHAR2 ( 100 ) : = 'SELECT name || ' ',' ' || state FROM cities' īEGIN - Open a cursor for the specified SELECT statementĭBMS_SQL. Using DBMS_SQL package CREATE OR REPLACE PROCEDURE export_cities (dir VARCHAR2, file_name VARCHAR2 ) IS The DBMS_ SQL package can be useful when you need to get column metadata (number of columns, their data types i.e) and data dynamically: You can also DBMS_ SQL built-in package to export data to a CSV file. All columns were concatenated into single value in SELECT Export rows one by one FOR rec IN cur LOOP You can use UT_FILE package and a cursor to export data into a CSV file:ĬREATE OR REPLACE PROCEDURE export_cities (dir VARCHAR2, file_name VARCHAR2 ) IS CURSOR cur IS SELECT name || ',' || state AS row_value FROM cities įile : = UTL_FILE. INSERT INTO cities VALUES ( 'Boston', 'MA' ) Sample data INSERT INTO cities VALUES ( 'San Francisco', 'CA' ) Test table for export to CSV CREATE TABLE cities
#PSQL EXPORT TABLE TO CSV HOW TO#
In this tutorial, we have shown you how to use COPY statement and \copy command to export data from a table to CSV files.Let's create the same sample table in Oracle:
#PSQL EXPORT TABLE TO CSV CODE#
It does not require PostgreSQL superuser privileges.įor example, if you want to export all data of the persons table into persons_client.csv file, you can execute the \copy command from the psql client as follows: \copy ( SELECT * FROM persons) to 'C:\tmp\persons_client.csv' with csv Code language: SQL (Structured Query Language) ( sql ) To use \copy command, you just need to have sufficient privileges to your local machine. However, instead of server writing the CSV file, psql writes the CSV file, transfers data from the server to your local file system. The \copy command basically runs the COPY statement above. In case you have the access to a remote PostgreSQL database server, but you don’t have sufficient privileges to write to a file on it, you can use the PostgreSQL built-in command \copy. Export data from a table to CSV file using the \copy command The CSV file also needs to be writable by the user that PostgreSQL server runs as. It means that the CSV file must reside on the database server machine, not your local machine. Notice that the CSV file name that you specify in the COPY command must be written directly by the server. TO 'C:\tmp\persons_email_db.csv' DELIMITER ',' CSV Code language: SQL (Structured Query Language) ( sql ) The following statement exports only data from the email column of the persons table to a CSV file. If you don’t want to export the header, which contains the column names of the table, just remove the HEADER flag in the COPY statement. TO 'C:\tmp\persons_partial_db.csv' DELIMITER ',' CSV HEADER Code language: SQL (Structured Query Language) ( sql ) For example, the following statement exports data from the first_name, last_name, and email columns of the persons table to person_partial_db.csv COPY persons(first_name,last_name,email) To do this, you specify the column names together with table name after COPY keyword. In some cases, you want to export data from just some columns of a table to a CSV file. PostgreSQL exports all data from all columns of the persons table to the persons_db.csv file.
For example, if you want to export the data of the persons table to a CSV file named persons_db.csv in the C:\tmp folder, you can use the following statement: COPY persons TO 'C:\tmp\persons_db.csv' DELIMITER ',' CSV HEADER Code language: SQL (Structured Query Language) ( sql ) The easiest way to export data of a table to a CSV file is to use COPY statement. Export data from a table to CSV using COPY statement Persons Code language: SQL (Structured Query Language) ( sql )
We will use the persons table that we created for importing data from a CSV file. In the previous tutorial, we showed you how to import data from a CSV file into a table. Summary: in this tutorial, you will learn various techniques to export data from PostgreSQL tables to CSV files.
0 kommentar(er)
