Hi, I have an issue with accessing my app's DB hosted on Heroku, because Heroku requires SSL connection to be used if the connection is coming from outside Heroku's network. While it doesn't make my developement too much harder, I have to rely on tools like DataGrip in order to access the DB to read the data outside of my app. Is there a way to do this already, and I just missed it, or does this require a change to the code?

Hi,
I was able to connect to my postgres instance (PostgreSQL 9.4.7 on x86_64). I created a new .pgsql files, specified the connection and database. I'm able to explore the db in the navigator, however there is no auto-completion for tables/columns.
Is this feature fully supported? Anything else i should check?

"Output" Terminal Reads this error when attempting code completion:

[Error - 11:17:23 AM] Request textDocument/completion failed.
  Message: Request textDocument/completion failed with message: Cannot read property 'attname' of null
  Code: -32603 

The following error occurred when connecting to Postgres 11:
error: column p.proisagg does not exist

In Postgres 11, proisagg and proiswindow, in pg_proc, are removed. Instead, pg_proc.prokind is used to state the corresponding property.

See the doc here.

And also, apart from only aggregate and window functions, handling on normal function and procedure is needed?

I'm not sure if this is possible in vscode, but would be great if we could run query directly from selection from terminal:

Either via context-menu or via command (hotkey).

Similar to how GitLens allows you to see it as views inside Source Control panel.

Once filled the data Hostname, user, pass, port, and SSL an error message displays "Cannot read property 'context' of null". Additionally, when I click on the bottom bar on Select Postgres Server, it thorws "Cannot read property 'globalState' of null". It seems to happen on every command, except "New Query".

I can still connect and view tables, views, and columns in each. However, when I try to run a query I get:

This started after the latest update to vsc.

Hi,
I got an error saying "Cannot read property 'context' of null" when I tried to add new connections. I am using the version 1.24.0 on Windows.

explain select 1; just displays undefined in the output pane.

I can not direct connect my pg server.

As we expect support for stored proc and functions as currently we cannot find any of those.

In the explorer, I can see the list of databases, but when expanding them, each one returns the error:

error:schema "aq$agent" does not exist

I'm using EnterpriseDB Advanced Server 9.6, and each database has a number of built in schemas beginning with aq$, relating to Oracle compatibility.

I believe the issue is that the schema names need to be double quoted when including them in queries.

Continuing #6

From @peterbam

Most databases end up with "rubbish" in them, or different schema which you have no access to etc

Installed the plugin for VS Code on MacOSX. Added the postgreSQL connection successfully, and see my database in the explorer. Have also confirmed that when typing SQL that intellisense correctly completes tables and fields names. However, I'm simply unable to execute any queries against the database.

When I run the query (F5 or right-mouse-menu to Run the Query) it brings up another window to the right with two tabs -- my .sql file duplicated and a Results which reads:

Results not found: postgres-results://file:///my/file/dummy.sql

Further, if I attempt to run any queries from the Explorer (select top 1000, for example), I get the same behavior - a mother pane to the right with two tabs - the SQL and the results tab (which instead reads "Results not found: postgres-results://untitled:untitled-1"

Clearly I'm missing something in the config / setup but just can't figure it out.

Thanks in advance.

Add 'database' parametr in config

Possibly related to #32

When I try to connect to a db hosted at heroku, I get the "no pg_hba.conf entry for host ...." error both in the db explorer and when I try to run a script.

I have other clients (PGAdmin and Oracle SQL Developer) on my laptop (Mac) which connect ok and I can execute queries from them.

If, for layout purpose, the first character of query is one or more blank spaces, the syntax highlighting stops.

I created a database configuration but how do I edit that configuration?

I just installed this extension, and when I tried to create a new connnection, it threw a error message:
cannot read property 'context' of null

Version 1.24.1
Commit 24f62626b222e9a8313213fb64b10d741a326288
Date 2018-06-13T17:51:32.889Z
Shell 1.7.12
Renderer 58.0.3029.110
Node 7.9.0
Architecture x64

This is the stack trace from log window:

[2018-06-26 09:42:22.649] [renderer5] [error] Cannot read property 'document' of undefined: TypeError: Cannot read property 'document' of undefined
	at PostgreSQLLanguageClient.setConnection (C:\Users\fernando.vieira\.vscode\extensions\ckolkman.vscode-postgres-1.0.9\out\language\client.js:27:108)
	at EditorState.onDidChangeActiveTextEditor (C:\Users\fernando.vieira\.vscode\extensions\ckolkman.vscode-postgres-1.0.9\out\common\editorState.js:43:29)
	at e.fire (c:\Program Files\Microsoft VS Code\resources\app\out\vs\workbench\node\extensionHostProcess.js:96:784)
	at c:\Program Files\Microsoft VS Code\resources\app\out\vs\workbench\node\extensionHostProcess.js:727:112
	at e.fire (c:\Program Files\Microsoft VS Code\resources\app\out\vs\workbench\node\extensionHostProcess.js:96:764)
	at e.$acceptDocumentsAndEditorsDelta (c:\Program Files\Microsoft VS Code\resources\app\out\vs\workbench\node\extensionHostProcess.js:725:330)
	at e._doInvokeHandler (c:\Program Files\Microsoft VS Code\resources\app\out\vs\workbench\node\extensionHostProcess.js:636:832)
	at e._invokeHandler (c:\Program Files\Microsoft VS Code\resources\app\out\vs\workbench\node\extensionHostProcess.js:636:550)
	at e._receiveRequest (c:\Program Files\Microsoft VS Code\resources\app\out\vs\workbench\node\extensionHostProcess.js:635:631)
	at e._receiveOneMessage (c:\Program Files\Microsoft VS Code\resources\app\out\vs\workbench\node\extensionHostProcess.js:635:400)
	at c:\Program Files\Microsoft VS Code\resources\app\out\vs\workbench\node\extensionHostProcess.js:634:315
	at c:\Program Files\Microsoft VS Code\resources\app\out\vs\workbench\node\extensionHostProcess.js:637:395
	at c:\Program Files\Microsoft VS Code\resources\app\out\vs\workbench\node\extensionHostProcess.js:95:432
	at e.fire (c:\Program Files\Microsoft VS Code\resources\app\out\vs\workbench\node\extensionHostProcess.js:96:764)
	at Socket.<anonymous> (c:\Program Files\Microsoft VS Code\resources\app\out\vs\workbench\node\extensionHostProcess.js:154:338)
	at emitOne (events.js:96:13)
	at Socket.emit (events.js:191:7)
	at readableAddChunk (_stream_readable.js:178:18)
	at Socket.Readable.push (_stream_readable.js:136:10)
	at Pipe.onread (net.js:560:20)

E.g. int4 for integer and _text for text[].

Currently (1.1.16) there is no way of stopping a query (unless I'm mistaken). Queries which are badly designed roughly drafted and which run for a long time can be running in the background invisibly (sometimes locking resources on the database and stopping other queries completing) and there's no way of stopping them without going to the cmd line or pgadmin.

Obviously this is related to issue 52, in that you can't cancel a query unless you can see that it's running!

Many thanks for this extension - it is a godsend and my project members and I have been using it heavily for the last month or so.

select * from information_schema.columns; on a small-ish database takes 7 seconds to display 1885 rows in the results grid. Scrolling is also slow in that window.

I had a couple times when I would be trying to select from a table I didn't have permission for and it only prints things out into the "Output" panel, which I have hidden. A notification using vsc's built in notifications when an error occurs would be a nice enhancement

My company's database requires a SSL connection. Is it possible to do so with this extension?

Running Visual Studio Code with the Postgres extension on the same machine works fine. When I run VSCode with the extension from a different client machine on the same network, I get the following error:

no pg_hba.conf entry for host "XX.XX.XX.XX", user "NNN", database "NNN", SSL off

But there IS a valid entry in the pg_hba.conf file, and I've confirmed it is valid by virtue of using a different tool (JetBrains DataGrip) to access the database from the aforementioned client machine.

Any suggestions?

This is a split from #11 as part of that has completed.

When query start is triggered, there's no way to know which query is finished, which not, and which query has execution is in progress. Example: if you run one update query, you will not get any feedback on whether the query executed or not. Also, errors on execution are seen only in popup in down-right, and it is easy not to spot them.

This might be classified as an enhancement request, but when attempting to query for time intervals from two dates columns, the child column reads "[object Object]" for each row. Running the same query in pgAdmin returns a calculated number of days.

dics_id is a foreign key, but its icon is the same with other columns. It confused me.

So I was recreating the example here (https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/) of different joins using VSC and this extension.
Whenever I do SELECT * FROM table_a and the same for table_b I get the correct results.

When I do an INNER JOIN I also get the result that I'm expecting which is a match from both tables

But then, when I do a full outer join I get this:

Which has several problems. 1) The id in pirate on the left should be 1 2) you have rows where both are being shown as null and then the last 2 rows have a match when those should be only on table_ b
Running the same query on the same server using DataGrip gives out:

Which is the right answer according to the website.

LEFT JOIN gives out this:

Where it should be:

Again using the same database and same query on DataGrip

Doing a RIGHT JOIN gives:

When again using DataGrip we get the right answer:

I originally thought it was a problem with my database but considering other software is giving out the result as is using the same exact queries and database I'm wondering what could be causing these kind of problems?

Seems like .vscode/launch.json has "program": "${workspaceFolder}/test.js", probably should be "program": "${workspaceFolder}/out/extension.js"?

When that changed, won't start as cant find 'vscode'? npm install reports all OK

Previous builds worked OK?

Could also then add to intellisense.

Icons to use?
Mixed with tables or separate?
Intellisense icons are based on the CompletionKind - and are separate from icons in the explorer

Hi, I tried to connect Amazon Redshift database, but got a error

database "postgres" does not exist
[Error - 23:36:35] (node:11388) MaxListenersExceededWarning: Possible EventEmitter memory leak detected. 11 end listeners added. Use emitter.setMaxListeners() to increase limit

My DB are on heroku and need to set sslmode=require in order to connect. Is there a way to do this?

When query start is triggered, there's no way to know which query is finished, which not, and which query has execution is in progress. Example: if you run one update query, you will not get any feedback on whether the query executed or not. Also, errors on execution are seen only in popup in down-right, and it is easy not to spot them.

I think these should be feedbacks as important as tables with return results, and presented as them (it should be as easy to spot them as tables, probably in the same element). Confusion is even bigger because, whatever was the last command (update/delete/error), last select result is shown (in picture below last command was update, but I see only feedback on last select, before that).

Also, it would be really cool if query results could be in real table, like in mssql plugin. It would be easier to select, copy, etc.

PS. Apart from these problems, this is currently this is by far best PSQL plugin for VS Code :) Keep up good job!

If you have multiple databases open, say staging and production, and are querying one to update another, the quick change on selecting another database in explorer can lead to an unnoticed change of server for an editor. It would be great to be able to turn this off or only have it apply to editors without a database selected.

First, love this extension, thanks for your hard work! Second, my vote for a future feature :)

Some other tools have the ability to look at where the cursor is and run the query within that "context". i.e. it looks backwards until it finds the start of the query and then forward until it finds a terminating expression (i.e. semicolon) or the end of the file. Understandably this is a good bit harder because it requires some knowledge of the AST of the SQL file, but it's a big timesaver (moving the cursor is much quicker than highlighting a block).

for example
for hide pg_temp.... and pg_toast
or for search some function or table

Storing a connection password in clear text is not secure. Being able to add the password when the connection is made and not storing it would resolve this issue.

Possibly fixed by https://github.com/Borvik/vscode-postgres/projects/2.

begin;
select 1;
commit;

shows syntax errors for begin and commit.

PS: Your extension is awesome! I like the autocompletion, parameter and return type suggestions, and also being able to see multiple result sets at once.

Ability to refresh results from within the result pane (because the query editor may be closed etc.). Either via hotkey, contextmenu, or button:

INSERT/UPDATE/DELETE and other query type outputs should be shown in the results window.

Split from #11

Any ideas what's missing at my end? Some package? Would a VSCode/NodeJS pkg really rely directly on something from freedesktop? I'm on Void Linux, i3, half of LXDE present on the system.

This comes up routinely after having entered all prompts in the series of inputboxes following a click on 'Add Connection'. New connection also doesn't show up in your 'PostgreSQL Explorer' view. The conn details are correct and the conn can be successfully established when trying via LibreOffice Base connection wizard. (Base doesn't work at my end though, past its initial conn wizard.)

In my opinion should be option to disable "quickly change connection database by clicking the DB in the status bar". I don't need this option usually and I prefer to have clear status bar with only necessary content.

Support to make a connection a default for a project (for all files), or at least default, period.

If I use null, if I remove the "password" entry from the json, or if I put an empty string, I get:

password: "": Failed to save 'f3b63ee0-c748-11e8-ab74-631ef94fbdbf.json': Password is required.
password: null: Failed to save 'f3b63ee0-c748-11e8-ab74-631ef94fbdbf.json': Password is required.
(removing the line): Failed to save 'f3b63ee0-c748-11e8-ab74-631ef94fbdbf.json': Missing "password" key

What do I do?

Using a simple query:

select *
from table
limit 10

It prints Results not found: postgres-results://untitled:untitled-1 in the results tab, and opens the pgsql file next to the results. The same happens when I save the file and run the query.

In the output for the extension it prints out 10 rows selected every time I run the query. So it must be working, partially!

Hi, I added my database following the prompts (label, host, username, password, port, SSL left blank). The database entry gets added to the Postgresql Explorer sidebar, but when I expand this db it only shows the error message:
error: unrecognized privilege type: "TEMP, CONNECT"
instead of listing the db details.

If I try to run a query (F5) it throws a popup error on lower right "database does not exist" where is my username I entered when adding the connection.

This looks like it's trying to use the username in place of the database name, so perhaps it's mapping the config entries to wrong fields.

Hi, thanks for this extension !
If I understood it well, it can check the syntax using EXPLAIN, but it executes it for every statement instead of only the following:

SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, CREATE TABLE AS, or CREATE MATERIALIZED VIEW AS

Reference : https://www.postgresql.org/docs/10/sql-explain.html

Would it be possible to only execute EXPLAIN for these statements ? Currently it shows syntax errors for valid DROPs, CREATE OR REPLACE VIEW etc
Thank you !

Please add support for PostgreSQL 9.3.
The only thing that does not work with 9.3 is that it doesn't show table columns showing:

error: function json_build_object(....) does not exist

I was able to fix it by replacing

ELSE json_build_object(
  'constraint', fk.constraint_name,
  'catalog', fk.fk_catalog,
  'schema', fk.fk_schema,
  'table', fk.fk_table,
  'column', fk.fk_column
) 

with

ELSE row_to_json(
  SELECT d FROM (
    SELECT 
      fk.constraint_name as constraint,
      fk.fk_catalog as catalog,
      fk.fk_schema as schema,
      fk.fk_table as table,
      fk.fk_column as column
  ) d
)

in the tableNode.js file.
Seems like a small change and this excellent extension would work with PostgreSQL 9.3