Hot answers tagged

40

“Elephant” icon menu When pgAdmin is running, an elephant icon menu appears. Look at the upper right corner of the screen. The elephant refers to Slonik, the Postgres mascot. Choose New pgAdmin Window menu item. FYI, pgAdmin 4 is built as a web app, not a desktop app, even when run locally on your own computer. Note that the internal web server provided ...


33

You can use the function pg_get_constraintdef(constraint_oid) in a query like the following: SELECT conrelid::regclass AS table_from , conname , pg_get_constraintdef(oid) FROM pg_constraint WHERE contype IN ('f', 'p ') AND connamespace = 'public'::regnamespace -- your schema here ORDER BY conrelid::regclass::text, contype DESC; Result: ...


32

I found this answer elsewhere. If your DB is on the local host, try leaving the host field blank in the connection as opposed to using “localhost” or “127.0.0.1″. This tells PgAdmin to connect via the local unix socket instead of TCP.


25

The line in your netstat report shows that the database is only listening on localhost:5432 (127.0.0.1) for incoming tcp connections. Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 3561/postgres So it can only accept local tcp connections regardless of what ...


20

Configure pgAdmin->Paths->Binary paths and set "PostgreSQL Binary Path" as shown in the screenshot. Depending on your OS and installation details, the binaries may be located elsewhere. Try `which psql` from the command line on linux/Unix systems. A Windows example: PostgreSQL Binary Path: "C:\Program Files\PostgreSQL\9.6\bin"


16

If you want to do this in PGAdmin, it is much easier than using the command line. It seems in PostgreSQL, to add a auto increment to a column, we first need to create a auto increment sequence and add it to the required column. I did like this. 1) Firstly you need to make sure there is a primary key for your table. Also keep the data type of the primary ...


16

Under Windows this information is stored in the Registry under the key: HKEY_CURRENT_USER\Software\pgAdmin III In Linux systems I would expect some file in the user home directory.


15

The lack of access to temporary tables in other sessions is not a matter of permissions, it's a technical limitation of the design. A PostgreSQL backend can't access temporary tables of another backend because none of the usual housekeeping to allow concurrent access is done for temporary tables. In 9.2 you will want to use an UNLOGGED table instead; this ...


15

Under Windows, pgAdmin4 stores the server list in the users directory in a SQLLite 3 format. Here: %your-user-home%\AppData\Roaming\pgAdmin\pgadmin4.db


15

It is worth mentioning that you are free to define any existing database in the cluster as "Maintenance DB". You are not limited to the options suggested by the interface, which are just typical defaults. This is particularly useful for users with limited permissions or read-only access. Access to a single DB is simpler with the same DB as "Maintenance DB". ...


15

I found nothing in the pgAdmin documentation, but the source code reveals the query behind these entries (added for Postgres 9.2+): It boils down to: SELECT temp_files AS "Temporary files" , temp_bytes AS "Size of temporary files" FROM pg_stat_database db; And the Postgres manual has details for pg_stat_database: tmp_files bigint Number of ...


15

To get all members of all roles: SELECT r.rolname as username,r1.rolname as "role" FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m ON (m.member = r.oid) JOIN pg_roles r1 ON (m.roleid=r1.oid) WHERE r.rolcanlogin ORDER BY 1; Add r1.rolname='council_stuff' to filter on only that one. Be aware that users ...


15

Select "Servers" item. Select "Configure PgAdmin" Enter to local path Postgresql "bin" folder. On OSX the path is /Applications/Postgres.app/Contents/Versions/latest/bin On Mac OSX Sierra the path was /Library/PostgreSQL/9.6/bin - I believe this is the default location from the EnterpriseDB installer for 9.6 that automatically installs PgAdmin 4. On Linux ...


14

There are instructions on the pgAdmin site how to install with pip: https://www.pgadmin.org/download/pip4.php But there is no official Debian Package (.deb), yet. A request for packaging is pending: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=834129 For now, consider the Postgres Apt repository: https://wiki.postgresql.org/wiki/Apt/FAQ


11

In Ubuntu 13.10 pgAdmin stores configuration in user's home directory: /home/<<username>>/.pgadmin3 or shorter ~/.pgadmin3 Note that this file is hidden, so you may need to enable some kind of "Show hidden files" option in your file manager.


11

I'm assuming that you want to create this extension in another server. I'm guessing that the server didn't get the "postgresql contrib" package installed. This package contains the "standard" extensions that are available from the PostgreSQL source code. I think you need to get the system administrator to install the required package for your OS. For ...


11

According to: http://www.postgresql.org/message-id/BLU0-SMTP179B92C5102247CD961A4B3CF2A0@phx.gbl The temp counter (files and space used) shows a total of all temp files used since probably cluster creation. It does not reflect the current space used by temp files. My system for example shows almost 700GB of temp files used, but the actual space taken up ...


10

pgScript is a local script extension of pgAdmin, which you most probably do not want here. pgAdmin is a GUI, not a console application - there is no stdin you could easily use. If you need stdin to stream your content, use psql, which is a console application - with the \copy meta-command of psql. If you have a file (which you obviously do), just use SQL ...


10

In pgAdmin… To edit the postgresql.conf file:Choose Tools > Server Configuration > postgresql.conf To edit the pg_hba.conf file:Choose Tools > Server Configuration > pg_hba.conf Avoid the red-herring File menu: File > Open postgresql.conf File > Open pg_hba.conf


10

Debunking If you are doing all your updates in the same transaction, each of them will have to work an increasingly bigger set of (physical) tuples. See the following example: CREATE TABLE explode (id integer, something text); INSERT INTO explode SELECT i, md5(i::text) FROM generate_series(1, 100000) t(i); \dt+ explode -- done in psql ...


9

Based on Erwin solution: SELECT conrelid::regclass AS "FK_Table" ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), 14, position(')' in pg_get_constraintdef(c.oid))-14) END AS "FK_Column" ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), ...


9

There is a setting for that in the options: Max characters per column - useful when dealing with big columns. Obviously your setting is 256 characters. Set it higher or set it to -1 to disable the feature. Consult the fine manual here for more details.


9

From the docs: The maintenance DB field is used to specify the initial database that pgAdmin connects to, and that will be expected to have the pgAgent schema and adminpack objects installed (both optional). On PostgreSQL 8.1 and above, the maintenance DB is normally called ‘postgres’, and on earlier versions ‘template1’ is often used, though it ...


9

Open pgAdminIII, Select your database, Use the Magnifying Glass button, i.e. "Execute arbitrary SQL queries" Instead of running your query using the green triangle "Execute Query", choose the button two to the right of that - i.e. "Execute Query, write result to file". Choose your destination, then you can view arbitrary length text in your chosen file ...


9

pgAdmin (both III and 4) execute both statements. For the first one, it just discards the output, unless there is an error. You can actually chain as many statements as you want: -- We create one table CREATE TABLE digit_names ( digit integer PRIMARY KEY, digit_name text, UNIQUE (digit_name) ) ; -- Fill it with values INSERT INTO ...


9

This nomenclature all comes down to knowing about MVCC, or Multi-Version Concurrency Control, which is how PostgreSQL handles transactions and concurrency. When you update or delete a row, doesn't actually remove the row. When you do an UPDATE or DELETE, the row isn't actually physically deleted. For a DELETE, the database simply marks the row as ...


8

I don't think there is a global parameter to tweak in pgAdmin 1.16.* As an alternative you can open tables with: Tools -> View Data -> View Top 100 rows This is also available from the context menu of a table in the object browser. Once the data grid is open, a "Limit Bar" is available, where you can set a maximum for returned rows. I would ...


8

If started from the same SQL window, then no - you will always get the latest result set (or nothing, if the last command returns no rows, like in the case of COMMIT). Not sure if this is OK for you, but you can open as many query windows as you want and so have as many result sets. (The above means that I'm not aware any such plugins.)


8

When a cursor is defined at the SQL level with DECLARE, there is an option WITH HOLD that makes it continue to exist after commiting the current transaction. Quoting the doc: WITH HOLD specifies that the cursor can continue to be used after the transaction that created it successfully commits On the other hand, a refcursor opened by a plpgsql function ...


8

Your client (Windows) is running on a different machine than your server (Ubuntu). You cannot read the remote file system. Inspecting the logs only works if your client is on the same machine (or has access to the remote file system by way of some advanced trickery.) To suppress the error message remove the log file from the display. In the Server Status ...


Only top voted, non community-wiki answers of a minimum length are eligible