Copying result from pgAdmin to a spreadsheet

I am copying every result of Query (application pgAdmin) into Google Docs Sheet.

I am wonder if it is possible to set Postgres to output tabs instead of semicolon ; to separate values in columns - I would like to simply copy and paste from Postgres to Google Docs sheet.

If it is not possible, is there any way to write a macro in Google Docs sheet?

I would like to ask one more q开发者_如何学Cuestion - how can I divide two values which I get by using select command (and how to demand from Postgres SQL to store values as for example double)?

---

In PGAdmin 4, you can achieve this by executing your sql query and then press F8 and it will export the query results.

Step 1:

Step 2: Now select the query result and click download query result in CSV format as shown below:

Step 3: Verify the query result

You are done.

---

Since JDBC within Apps Script doesn't yet support Postgres (for some reason), you can't script this directly in Google Sheets. You could definitely script this the Google Sheets API and the language of your choice (e.g. Python and Pandas make this a relatively simple task).

If you don't want to roll your own solution, check out SeekWell. It allows you to connect to databases and write SQL queries directly in Sheets. You can create a run a “Run Sheet” that will run multiple queries at once and schedule those queries to be run without you even opening the Sheet.

Disclaimer: I made this.

---

It is now possible to configure PgAdmin3 to copy/paste with Tab as the field delimiter instead of ;

From the PgAdmin3 MAIN window:

• File
• Options
• Query Tool section
  • Results Grid area
    • Click pull down next to "Result copy field separator"
    • Choose "Tab" from the picklist

You might have to restart PgAdmin for this to take effect. Works perfectly for pasting directly into Google spreadsheets!

And regarding your division question, if you just divide the two numbers, you might not always get what you expect - integers don't divide automatically and become a float result - you have to cast them:

select 1::numeric/2::numeric

This returns 0.50

Whereas:

select 1/2

Returns 0 (not generally what you want)

---

psql can do this in a number of ways:

% psql -At -F, -c 'SELECT * FROM my_table'
1,login,http://stackjet.com/login
2,mod3,https://127.0.0.1:5000/mod3/
3,mod2,http://127.0.0.1:5000/mod2/
4,logout,https://127.0.0.1:5000/logout

Escaping is an issue with that scheme. COPY is your friend.

---

Use the export ability.

---

Save output of COPY or \copy as a .tsv file and load that using File/Import. Alternatively, you can use the CSV format, in the same way.

---

Maybe something like this could be usefull :)

select relname || chr(9) || reltype || chr(9) || relam from pg_class

---

• If it is not possible, is there any way to write a macro in Google Doc's sheet?

Macros don't do it unfortunately. If you want to write your own script, you'd have to use app script, but app script JDBCs don't support postgres, so you'd have to build your own querying sever.

You can also use addons like Castodia, Seekwell and Kpibees. You can find them here

• I would like to ask one more question - how can I divide two values which I get by using select command ...

If you want to sync DB data with sheet and then change that data, your best bet would be to add a separate column and use formula (ex: =A1/2). If you're using addon, you might want to check how the data is updated first, so it doesn't overwrite all columns. I can only speak for Castodia addon since I worked on it. When it updates your sheets, it only updates the columns that have data. So if you have columns "age, occupation, income", it will only overwrite those 3 columns, so you can safely add forth column with a formula.

• ...(and how to demand from Postgres SQL to store values as for example double)?

Data type for a column is stored in your Postgres database, so if you store the data as a text, it will return it as a text. You can set your column to be a specific type in your sheet, however. Select your column, go to "Format" and select "Number". After that, when the data is updated, the column will remain in desired format, unless the script that updates the sheet changes column formats as well, so make sure to check for that first. Castodia script does not change column formats, so this solution should work with at least one addon.

---

I would perform your insertions / interpolations using vanilla SQL and then use a tool like QueryClips to get your data into the google sheet.