Exporting and Importing Data with HEREDOC and Files in SQL and PGSQL

When working with PostgreSQL, you may need to export or import data from a specific row. Using HEREDOC and files can simplify this process, especially when dealing with large datasets or complex queries. In this article, we’ll explore how to use HEREDOC and files to export and import data in PostgreSQL.

Exporting Data to a File

To export data from a specific row, you can use the COPY command in PostgreSQL. This command allows you to copy data to a file or standard output. Here’s an example of exporting data to a JSON file:

❯ psql -U alice -h 127.0.0.1 -d example_db <<SQL > data.json
COPY(
  SELECT json_field 
  FROM FOO_TABLE 
  WHERE guid = '85f265ed-9484-4aa5-9007-2948022b6343'
) TO STDOUT;
SQL

In this example, the COPY command exports the json_field column from the FOO_TABLE table to data.json. The TO STDOUT clause directs the output to the standard output, which is then redirected to the file.

Importing Data from a File

To import data from a file back into a PostgreSQL table, you can use the UPDATE command along with the \set meta-command in psql. Here’s how you can do it:

❯ psql -U alice -h 127.0.0.1 -d example_db <<SQL
\set content `cat data.json`
UPDATE FOO_TABLE
SET json_field = :'content'
WHERE guid = 'acf46376-4b7d-44ba-bc8d-b24c3c729fa2';
SQL

In this example, the \set meta-command reads the content of data.json into a variable named content. The UPDATE command then uses this variable to update the json_field column for a specific row identified by its guid.

Using HEREDOC for Complex Queries

HEREDOC is particularly useful when you need to execute complex queries or scripts directly from the command line. It allows you to write multi-line SQL commands without needing to escape special characters. Here’s an example of using HEREDOC to export and import data in one go:

❯ psql -U alice -h 127.0.0.1 -d example_db <<SQL > data.json
COPY(
  SELECT json_field 
  FROM FOO_TABLE 
  WHERE guid = '85f265ed-9484-4aa5-9007-2948022b6343'
) TO STDOUT;
SQL

❯ psql -U alice -h 127.0.0.1 -d example_db <<SQL
\set content `cat data.json`
UPDATE FOO_TABLE
SET json_field = :'content'
WHERE guid = 'acf46376-4b7d-44ba-bc8d-b24c3c729fa2';
SQL

This approach ensures that the data is exported and imported in a single, cohesive process, reducing the risk of errors.

Best Practices

1. Use Environment Variables: Store sensitive information like database credentials in environment variables to avoid hardcoding them in your scripts.

2. Validate Data: Before importing data, validate it to ensure it meets the required format and constraints.

3. Backup Data: Always backup your data before performing bulk operations like updates or imports.

4. Automate with Scripts: Use shell scripts to automate repetitive tasks, ensuring consistency and reducing manual errors.

Conclusion

Using HEREDOC and files to export and import data in PostgreSQL is a powerful technique that can simplify your workflow. Whether you’re working with large datasets or complex queries, this approach ensures that your data is handled efficiently and securely. By following the best practices outlined in this article, you can ensure that your data operations are robust and reliable.