I am looking for some docs and/or examples for the new JSON functions in PostgreSQL 9.2. Specifically, given a series of JSON records: [ {name: “Toby”, occupation: “Software Engineer”}, {name: “Zaphod”, occupation: “Galactic President”} ] How would I write the SQL to find a record by name? In vanilla SQL: SELECT * from json_data WHERE … Read more
Is there any query available to list all tables in my Postgres DB. I tried out one query like: SELECT table_name FROM information_schema.tables WHERE table_schema=”public” But this query returns views also. How can i get only table names only, not views? 7 Answers 7
Whenever I try to drop database I get: ERROR: database “pilot” is being accessed by other users DETAIL: There is 1 other session using the database. When I use: SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname=”TARGET_DB”; I terminated the connection from that DB, but if I try to drop database after that somehow someone automatically connects … Read more
I tried to run simple SQL command: select * from site_adzone; and I got this error ERROR: permission denied for relation site_adzone What could be the problem here? I tried also to do select for other tables and got same issue. I also tried to do this: GRANT ALL PRIVILEGES ON DATABASE jerry to tom; … Read more
What is the command to find the size of all the databases? I am able to find the size of a specific database by using following command: select pg_database_size(‘databaseName’); 10 Answers 10
