Try here for a basic intro to PostgreSQL and JSON.
Also, PostgreSQL documentation is pretty good, so try it here. Check out the pretty_bool option.
Your original question was "Is there a way to export postgres table data as JSON". You wanted it in this format
I didn't have a running instance of ...
.bacpac file = Schema + Data. Note that Data is BCP'ed out using Native format (not readable by Human).
You can rename the .bacpac to .zip to see the actual contents.
You can use ...DAC\bin\sqlpackage.exe commandline to extract the .bacpac contents programatically.
It is a snapshot that includes User data + Schema from SQL Server or Azure SQL Database.
impdp will create the user if it's not present yet, so you don't have to worry about it unless that's not what you want.
Do not run impdb or expdp as sysdba, only do that if Oracle support requests it in specific circumstances. Use an ordinary user for that - one that has been granted the dba role for instance. (There are the [IMPORT|EXPORT]_FULL_DATABASE ...
Both the approaches already suggested appear to be unnecessarily complicated.
Just use psql's built-in \copy command, which works just like server-side COPY but does a copy over the wire protocol to the client and uses client paths.
Because it's a psql backslash command you omit the trailing semicolon, eg:
\copy products TO '/tmp/products.csv' CSV ...
Please run this query:
Data_BB / POWER(1024,1) Data_KB,
Data_BB / POWER(1024,2) Data_MB,
Data_BB / POWER(1024,3) Data_GB
FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')) A;
This will give you a ballpark figure. The column index_length is ...
Since I happened to be researching this myself, here's a summary of what I found.
According to a 2015 blog post from the MySQL dev team, the main advantages of mysqlpump are that it can use multiple threads in parallel to speed up the dumping and that it doesn't share mysqldump's backwards compatibility requirements, which should open the door for further ...
I'll answer this at a high level for you. The two backup methods work at different levels. An RMAN backup is a physical backup and a Data Pump backup is a logical backup.
A database dump using expdp is a 1-time export of one or more database schemas. It backs up DDL (table structures, views, synonyms, stored procedures, packages, etc), plus data.
An RMAN ...
If you're using psql then there is no reason to use \COPY at all.
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 with PostGIS for quick tests, and also to generate script files with PostgreSQL extensions.
This is the solution I came up with:
Enable xp_cmdshell with
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 1
If needed create a directory with xp_cmdshell to get the needed permissions.
EXEC master..xp_cmdshell 'mkdir C:\exportdir'
Use BCP with queryout
EXEC master..xp_cmdshell 'BCP "...
You really just have to get creative. As we all know, there are many places that settings are stored, depending on what exactly you're looking to compare. For instance, to compare instance-wide configuration settings, you can simple do an EXCEPT query (you may have to create a linked server, or export/import the data depending on how you want to approach ...
Yes, by using # at the start of a line.
Actually everything after the # sign will be ignored, so this can be used after a parameter as well.
This is a bit hidden in the manual:
Table names specified on the command line cannot include a pound sign (#), unless the table name is enclosed in quotation marks. Similarly, in the parameter file, if a table ...
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 as not allowing for the following:
--tab, and its related options of:
Using SQL from the mysql command-line:
SELECT * from YOURTABLE
WHERE status=0 and id>20
INTO OUTFILE 'yourtable.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
or using mysqldump with the --where= option:
mysqldump -u youruser -p yourdbname yourtablename --where="status=0 and id>20">yourtable.sql
Using phpMyAdmin you can ...
You could do that in Oracle 11gR2 with expdp and the REMAP_DATA option.
Create a function in a package that takes a blob as argument, and returns null (or an empty blob perhaps).
Call expdp as usual, adding:
Short example (schema: mat):
create or replace
package remap as
Looping through all those articles is an option if you want to get old watching it execute.
Some different options that you should try are:
the bcp utility (mentioned above, too, it's a command line tool to import and export data to/from an SQL Server);
BULK INSERT statement - the T-SQL language sugar to work with data imports from scripts;
I had the same problem with the extra 4 bytes being added to the beginning of all of my files as well. Instead of using -N option in my bcp command, I changed it to -C RAW. When you do this, bcp will be prompted with the following questions:
Enter the file storage type of field FileData [image]:
Enter prefix-length of field FileData :
Enter length of ...
Rather than doing this, I'd advise on getting the character set info from the nls_database_parameters view & then using DBCA to create a new database, along with dbms_metadata.get_ddl to handle tablespace creation. - Much easier and less prone to error. The character set and national character set are really the only things that are a pain to change when ...
Since your source server is SQL Server 2000, you can't restore directly to SQL Server 2012. You will need to restore to another instance first (2005, 2008, or 2008 R2).
BACKUP DATABASE dbname TO DISK = 'D:\backups\db.bak' WITH INIT;
On 2005 or 2008 or 2008 R2:
RESTORE DATABASE dbname FROM DISK = 'D:\restores\db.bak'
WITH REPLACE, RECOVERY, ...
There really is an abundance of documentation and tools to do MS SQL Server to MySQL migrations - likely there would be no need to re-invent the wheel. Exporting your database tables' data to CSV just to re-import it to MySQL seems like a rather bad idea since you are going to lose all metadata (data types, constraints, indexes, views, ...).
Look at this ...
If you want access to all data (ie, all tables in all schemas), you would need to grant dataaccess.
db2 grant dataaccess on database to user winuser1
If you only want winuser1 to access just the 100 tables in the schema you are referring to, then unfortunately, there is no easy way, you would need to grant SELECT on each table. That being said, it can be ...
I'm sorry you haven't had a response since yesterday; here's at least a starting point for you.
You can try pulling the pieces you need out of. As always, read the discussion threads (I was, regrettably, unable to find a script endorsed by the big names I recognize, so test thoroughly! Schema, object, server, and column level permissions are often missing)...
since the database size is large you should make the dump command like the following:
mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.gz
--quick:This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than ...
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 >:
psql -c '\copy (SELECT 1, 2) to stdout' >> /path/to/file_to_append_to.csv
(You probably need more connection parameters.)
You can use SQL COPY as well:
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 header row, etc. So, I built my own tool to handle this. It currently exits as the DB_BulkExport Stored Procedure in the SQL# SQLCLR library (that I wrote), ...
The reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size. By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text.
It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL.
The script you need ...
You can use custom policies or predefined ones (so called best practices) and adjust them according to your developer SQL Server instance. Then, as explained in the SQL Server Policy Based Management – evaluating policies on multiple SQL Server instances online article, evaluate them against (even multiple) production instances
Many experts share many ways on how to overcome this problem. These are my suggestions to play a safe game.
Try to set the below command in a seperate session.
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT COLA, COLB into outfile '/tmp/data.csv' from TABLE_NAME;
Doing by this way the SELECT statements are ...
Idera SQL Permissions Extractor seems to be the product you're looking for.
It can script server and object permissions and it is free. There is also a commercial edition, called SQL Secure which has more features.
The feature comparison between the two editions can be found here.
For me @Vérace's answer didn't maintain the column names, but assigned default names (f1, f2, etc.) instead. I am using PostgreSQL 9.1 with the JSON extension.
If you want to export the entire table, there is no need for a subquery. In addition, this will maintain the column names. I used the folowing query:
COPY (SELECT row_to_json(t) FROM fred as t) to '/...