Hot answers tagged pgadmin
7
The short answer is "No". Temporary tables in
other sessions are invisible by design. It makes no difference if two sessions have the same user. Even:
The autovacuum daemon cannot access and therefore cannot vacuum or
analyze temporary tables
7
We had the same problem 2 times on two different computer, in just a few days of interval.
PgAdmin started to crash each time we open query builder.
Restarting the computer, reinstalling pgAdmin with differents versions... Nothing resolved the problem. But we found the solution to this problem!
PgAdmin uses a file where it saves the last queries (kind of ...
7
The line in your netstat report shows that the database is only listening on localhost:5432 (127.0.0.1) for incoming tcp conections.
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 ...
5
Other than not using features that cause errors, no, there is no workaround. Since in this case one of the features that causes an error appears to be listing tablespaces during startup, you're out of luck.
Just compile a newer version of PgAdmin-III from sources or use the command-line psql client that comes with PostgreSQL 9.2.
PostgreSQL's system ...
4
Or, to retrieve all rows, just press the blue data grid.
The tooltip "View data in the selected object" is almost making it too easy.
Try the button with the funnel next to it to retrieve only a selection (for big tables).
4
Right click on the table and choose "View data"
For a list of alternatives, check out the Postgres wiki:
http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
4
You write:
Each customer can have multiple sites, but only one should be
displayed in this list.
Yet, your query retrieves all rows. That would be a point to optimize. But you also do not define which site is to be picked.
Either way, it does not matter much here. Your EXPLAIN shows only 5026 rows for the site scan (5018 for the customer scan). So ...
4
You tell the COPY command to look for commas as delimiters (DELIMITERS ','), but there are no commas in your CSV. Use the 'text' format instead (it's the default, so you don't have to specify it) and do not specify a delimiter:
The default is a tab character in text format.
(source)
4
pgadmin has it's own reverse-engineering functionality - it just examines the system tables like pg_class and pg_attribute to find the details.
If what you're looking for is actually an easy way to show the CREATE statements for your objects that you can use from something else, you should look at pg_dump instead of pgadmin, it is much simpler. In ...
3
DROP DATABASE is a very special command that cannot be undone. To my knowledge there is no way to drop a database inside a transaction. I quote the manual:
DROP DATABASE cannot be executed inside a transaction block.
Whenever you run two commands in a script, they are automatically wrapped into a transaction. You can explicitly begin and commit ...
3
Since you installed multiple versions of PostgreSQL, there is a good chance your cluster is running on a different port than 5432. Additional clusters automatically use the next free port unless instructed otherwise. Like 5433, 5434 etc.
Check your setting in postgresql.conf to find out and connect to the proper port. postgresql.conf is usually installed ...
3
You don't need to do this through the pg_hba.conf.
Simply revoke the connect privilege on the database from that user.
However by default public is granted the connect privilege. So you need to first revoke that:
revoke connect on db1 from public;
grant connect on db1 to dbuser;
You need to run the revoke ... from public statement for all databases to ...
3
I can connect to my postgres instance from pgAdmin III without a password for any user including superusers such as postgres.
Because you are connecting ok from another client, there is no reason you should not be able to connect from pgAdmin if they are on the same workstation - unless some firewall rule on the client itself is allowing one program but ...
3
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 ...
2
two options: Use the "datatype" SERIAL or create a sequence and use this sequence as a default value for your integer:
CREATE SEQUENCE your_seq;
CREATE TABLE foo(
id int default nextval('your_seq'::regclass),
other_column TEXT
);
INSERT INTO foo(other_column) VALUES ('bar') RETURNING *;
2
"I don't know how they got there in the first place"
It could have happened as described here - although this generates an error on 8.4:
If you create a table with any text type (i.e. text, varchar(10),
etc.), then you can insert an invalid byte sequence into that field
using octal escapes.
For instance, if you have a UTF8-encoded database, ...
2
There is no explicit switch to just export functions. I did some research since it's a nice question.
I can think of some ways to do this:
Using pgAdmin, you get the code that makes up the function when you right-click on the function, and choose "Scripts->Ceate". You could copy&paste the contens and put it into a sql file that you can import like ...
2
Backup without password
If you run the script as OS user postgres it should not request a password in a standard setup, because password-less peer access (or ident on older versions) is enabled in pg_hba.conf for the postgres user.
So make it a cronjob of postgres if you can. Or enable password-less access for the OS user running the job. You can do that ...
2
I found the answer to my problem. The pgadmin3 tool won't let you specify both a server and a database. The server itself HAS a default database and when you select it with the --server option, it won't let you pick a database. An error message would have been nice!
The 'defiant' server I made has in itself a default connection to a database, you have to ...
2
Thanks to @araqnid on Stack Overflow for the answer here.
Quoted Verbatim from there:
Open the "Properties" of the Heroku server in pgAdminIII and change the "Maintenance DB" value to be the name of the database you want to connect to.
The default setup is suitable for DBAs et al who can connect to any database on the server, but apparently that isn't true ...
2
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 ...
2
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 ...
1
deszo is right, of course, but it's not the whole story.
pgAdmin actually reverse engineers the code, and if all criteria for a serial column are met, a serial column is displayed as such.
CREATE TABLE foo
(
foo_id serial ...
There was a bug in some old version that caused pgAdmin to fail in this respect, but it has long since been fixed. You fail to ...
1
This is not a pgAdmin feature but that of PostgreSQL. If you check your table from an other client (eg. psql) you will see the exact same column definition. Why is it so?
The data types serial and bigserial are not true types, but merely a
notational convenience for creating unique identifier columns (similar
to the AUTO_INCREMENT property ...
1
1 Why oids=false?
OIDS=FALSE/TURE, the default setting depends upon the default_with_oids configuration parameter，The parameter is off by default。in PostgreSQL 8.0 and earlier, it was on by default.
2 Why ownered by postgres?
Please check the role you use log in when using pgAdmin3 ， if you log in as postgres role ,then the owner of new created table ...
1
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(c.oid)
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
WHERE contype IN ('f', 'p ')
AND n.nspname = 'public' -- your schema here
ORDER BY ...
1
FATAL: permission denied for database "postgres" indicates that it is trying to connect to the postgres database. We indeed don't grant such privileges. You can connect to your own database though, that should be fine. You can prove that by connecting via the psql CLI.
It seems that you are setting heroku's dbname to something called Name in pgadmin. That ...
1
What you see is pgAdmin connecting to the configured Maintenance DB - which is postgres by default. Select a server in the object browser pane and choose "Properties" from the context menu (right-click). The drop-down menu only offers the typical choices, but you can just type in any database name.
If a user's access is limited to one database in ...
1
The root nodes in the "Object browser" are actually connections, not servers. And a connection requires a user name. There is a bit of mis-labelling floating around, I have raised this issue on the developer mailing list before.
However, you can just edit the properties of a connection before you open it. If you don't want to store your password for ...
1
There's a difference between pg_dump on the system running pg_dump and postgres on your database server that's too big for them to be considered compatible. Find out what version of postgres is on your server and then install the same version of pg_dump locally.
The most recent version of PgAdmin will support your database. You're a couple releases behind. ...
Only top voted, non community-wiki answers of a minimum length are eligible
