39
votes
Export Postgres table as json
If you're using psql then there is no reason to use \COPY at all.
\t
\a
\o file.json
SELECT row_to_json(r) FROM my_table AS r;
This is the same method we use to get png/jpgs/tifs out of the database ...
- 64.7k
29
votes
Export Postgres table as json
Please find below the only answer that outputs actually valid JSON (i.e. array of objects).
\t
\a
\o data.json
select json_agg(t) FROM (SELECT * from table) t;
(source)
- 409
21
votes
mysqldump vs mysqlpump
I have also been looking into the differences between these two utilities, and in addition to what is mentioned in @Neil's answer, it seems that mysqlpump also has fewer options than mysqldump, such ...
- 69.5k
10
votes
Using Postgres COPY TO for data export without overwriting file
You could use the meta command \copy in psql (which encapsulates SQL COPY) on the command line, send it to stdout and and redirect output to be appended to a file - with >> instead of >:
...
- 182k
9
votes
What is the best way to export/extract varbinary images in a SQL Server database to flat file via t sql, anything better than sp_OA methods?
If you really must do it from T-SQL, and your SQL Server instance version is 2012 or more recent, You could use a FileTable. It relies on the FileStream feature, so you'll have to enable this first.
...
- 711
8
votes
Accepted
In SQL Server, how do I save the results of a query to a txt file?
Options:
Use sqlcmd at the command line to execute your SQL statement; the results will arrive as text and can be trivially saved.
See https://stackoverflow.com/questions/425379/how-to-export-data-...
- 1,718
7
votes
Accepted
Export Postgres data without superuser privileges
COPY ... TO <file> creates the output file on the server, which, in addition to requiring elevated privileges, in many cases is not useful since not everyone has access to the server file system....
- 26.9k
6
votes
mysqldump vs mysqlpump
Here is the summary of what I understood between both the backup tools.
The definition for mysqldump given in manual page
The mysqldump client utility performs logical backups, producing a set of ...
6
votes
Postgres 9.6 export the database structure as pdf
You need a Entity Relation Diagram (ERD) generator. The most popular and advanced one that I know of that is FOSS is SchemaCrawler.
It does everything you want anyway. Though the diagrams are ...
- 64.7k
6
votes
Very large SQL Server 2016 result sets (over 75 GB) to a CSV file?
I, too, used to have problems exporting large result sets (7 - 8 GB) to delimited files. Neither SQLCMD nor BCP nor SSIS could handle dynamic result sets, dynamic text-qualification, adding a column ...
- 69.5k
6
votes
SQL Server: SSMS "Save Results As" creates CSV that Excel mangles
First, make sure you've got the query options set up the way you want.
Go to the Query menu, and choose Query Options:
The first of the two highlighted options tells SSMS to include the column ...
- 11.7k
5
votes
Accepted
Oracle SQL Developer: Set the <null> value when exporting to CSV
Just set the below in your session, as you would do in SQL*Plus:
set null "null"
Note: this will not affect how NULLs are displayed in the data grid (you can set that from the menu: Tools - ...
- 41.1k
5
votes
Accepted
How can I transfer the entire content of several tables to another database, not in the same network?
There's a command line utility packaged with SQL Server called BCP. It will do what you need.
It supports several file formats. The one you want is "native". This is a proprietary binary ...
- 25.1k
4
votes
Accepted
Export image file from bytea column
It's a horrible idea to store jpegs in the database.
That said, if you want to get it out of the database, you can use psql. This gets it out as hex.
psql -t -A -o "/tmp/imagetest.jpg" -c \
"...
- 64.7k
4
votes
Accepted
How to export database structure from Firebird?
There are several ways to do this:
A metadata-only backup using gbak:
gbak -backup -meta_data employee employee.meta.fbk
And then restore that backup.
A metadata-only restore using gbak:
gbak -...
- 1,206
4
votes
Accepted
What is the 131072 in the call to lo_open()
This variable is called the "mode parameter" and it's the int representation of INV_READ (x'20000')
-- lo_open(lobjId oid, mode integer) returns integer
-- The mode parameter to lo_open uses two ...
- 64.7k
4
votes
How to back up an entire database to text files
The following is not a good backup solution. Unless you’re quitting your job and feel like pranking your boss on the way out the door, you shouldn’t use it. But I learned a few things and had fun ...
4
votes
How to export base and discover its structure
You can generate an SQL file (of metadata) with built-in commandline tools (available on all database installations).
Just log in to the database server, create a full=y, content=metadata_only export:...
- 41.1k
3
votes
mysqldump vs mysqlpump
As of today, mysqlpump utility is deprecated.
mysqlpump is deprecated as of MySQL 8.0.34; expect it to be removed in
a future version of MySQL. You can use such MySQL programs as
mysqldump and MySQL ...
- 131
3
votes
Accepted
Fastest Way to Move Data from Current Database for Multiple Tables to New Database?
You say that you're worried that your proposed solution won't work because it won't run in parallel. You already have a PowerShell cmdlet which deletes from all 160 tables, right? Deleting rows can ...
- 32.6k
3
votes
Accepted
Encountering "Snapshot too old" error during execution of expdp command
The cause of the ORA-01555 error is undo_retention parameter has the lower value as compared to the time taken to complete the export with the parameter consistent=y(which is deprecated in 11g and ...
- 8,650
3
votes
Very large SQL Server 2016 result sets (over 75 GB) to a CSV file?
PowerShell may work well for you.
Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DBName -Query $ExportSQL | Export-CSV -Path $ExportFile -NoTypeInformation
Invoke-Sqlcmd is included in the ...
- 254
3
votes
SQL Server: SSMS "Save Results As" creates CSV that Excel mangles
I was having same trouble with field and limiting the length of the output didn't help at all because it has to do with the carriage returns.
Before you run the data you want output to Excel, change ...
- 131
3
votes
exporting a sql view to a csv file
bcp is a command-line command utility - you can't call it as a T-SQL command. However, if you enable xp_cmdshell, you can run it via the command-line using this code inside a SQL Server Agent Job (or ...
- 70.5k
3
votes
Accepted
Postgresql, export to json
Is possible export to json file? Something like csv export.
Yes. Try this:
select row_to_json(tablename.*,true) from tablename;
or
copy (select row_to_json(tablename.*,true) from tablename) to ...
- 32.3k
3
votes
Using pg_dump on a view always produces an empty file
Is there any way of using pg_dump against a view. The postgres
documentation suggests it should be possible
This is not an answer to your question per-se, but the PostgreSQL documentation is ...
- 116
3
votes
Accepted
Export and import a table without a specific column
Another solution is to use LOAD DATA [LOCAL] INFILE, but override the columns list so the auto-increment value is inserted into a dummy variable:
Here's a demo:
I have a text file I call c.csv:
1 ...
- 16k
3
votes
Accepted
Export Postgres sequences only
I'm not sure what you mean by "export sequences".
I suppose that you mean to extract all statements as:
CREATE SEQUENCE [sequence name] ....;
ALTER SEQENCE [sequence name] OWNED BY [table ...
- 213
3
votes
Export Postgres sequences only
pg_dump -h localhost -p 5432 -d namedb -U postgres -t '*_id_seq' > dump-seq.sql
3
votes
MySQL Dump only views, triggers, events and procedures
Include this:
--no-create-info
It means: "Do not write CREATE TABLE statements that create each dumped table." -- https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
- 79.4k
Only top scored, non community-wiki answers of a minimum length are eligible