0

In pgAdmin3, via the SQL Editor, there is an Execute pgScript button which (unlike Execute Query) would run through the entire script, skipping errors. The SQL editor in pgAdmin4 doesn't appear have this, is there an alternative that I haven't been able to find, or is there a parameter/command I can add to my script in order to skip errors at runtime?

In this instance the script is auto-generated and only renames tables/columns which may not exist so the error output is not required.

I can still run the script in pgAdmin 3 but connecting to a version 9 or above database with pgAdmin3 raises several warnings which don't seem to cause any problems but I'd rather find a solution than a workaround

Using: PostGreSQL 9.5, pgAdmin4 1.6


UPDATE: according to this:

How do we execute pgScripts in pgAdmin4?

You cannot I'm afraid. They've been more or less unsupported for years as they really became obsolete when Postgres introduced the DO command for executing anonymous blocks of code in v9.0.

...it looks like I might be able to use DO to force it to behave like pSQL?

2
  • AFAIK, pgAdmin 4 doesn't have pgScript. Can you use ALTER TABLE IF EXISTS ... ?
    – joanolo
    Jul 20, 2017 at 20:38
  • @joanolo doesn't look like it! IF EXISTS works for tables but not for columns so if the table is there but not the column it will still error - and annoyingly it doesn't tell you you which line is raising the error
    – Alex
    Jul 21, 2017 at 13:41

2 Answers 2

1

On psql, it's the default, but you can (un)set it with \set ON_ERROR_STOP 0|1.

So I suggest trying \set ON_ERROR_STOP 0 at the beginning of the script.

From documentation:

ON_ERROR_STOP

By default, command processing continues after an error. When this variable is set to on, processing will instead stop immediately. In interactive mode, psql will return to the command prompt; otherwise, psql will exit, returning error code 3 to distinguish this case from fatal error conditions, which are reported using error code 1. In either case, any currently running scripts (the top-level script, if any, and any other scripts which it may have in invoked) will be terminated immediately. If the top-level command string contained multiple SQL commands, processing will stop with the current command.

2
  • This is good for pSQL, but not for pgAdmin, AFAIK.
    – joanolo
    Jul 21, 2017 at 7:24
  • yes as @joanolo says this just raises a syntax error in pgAdmin. generally i'm happy to work with pSQL but in this instance it would require transferring the scripts from my desktop PC to the db server, with pgAdmin running locally I can just open them in the editor and run
    – Alex
    Jul 21, 2017 at 13:36
1

I've found it best to just install the psql client on my desktop to run legacy scripts without having to log into the db server. You can download the posgresql installation for windows because it comes with psql.exe

Newer Versions of the Installation (I think 10+) have an option in the install to install client only.

Else you can download the install and manually delete everything from the download except whats needed for psql.exe. I got it working by keeping only these files.

libcrypto-1_1-x64.dll 
libcurl.dll 
libeay32.dll
libecpg.dll
libecpg_compat.dll
libiconv-2.dll
libintl-8.dll
libpgtypes.dll
libpq.dll 
libssl-1_1-x64.dll 
psql.exe

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.