Is there an equivalent of MySQL's SHOW CREATE TABLE in Postgres? Is this possible? If not what is the next best solution?
I need the statement because I use it to create the table on an remote server (over WCF).
I'm trying to grant all privileges on all tables of a given database to a new postgres user (not the owner). It seems that GRANT ALL PRIVILEGES ON DATABASE my_db TO new_user; does not do that. After running said command successfully (as the postgres user), I get the following as new_user:
$ psql -d my_db
my_db => SELECT * FROM a_table_in_my_db;
ERROR: permission denied for relation a_table_in_my_db
Two questions:
1) What does the command above do, then, if not granting all permissions on all tables on my_db?
2) What's the proper way to grant all permissions on all tables to a user? (including on all tables created in the future)
I'm investigating an issue with DB connections being left open indefinitely, causing problems on the DB server. How do I see currently open connections to a PostgreSQL server, particularly those using a specific database? Ideally I'd like to see what command is executing there as well. Basically, I'm looking for something equivalent to the "Current Activity" view in MSSQL.
I have just installed postgres 8.4 on Ubuntu 9.10 and it has never asked me to create a superuser. Is there a default superuser and its password? If not, how do I create a new one?
Does anyone know if Postgres has a way to display query results "prettily", like how MySQL does when ending a query with \G on the command line? For instance, "select * from sometable\G" as opposed to "select * from sometable;"
Many thanks!