I switched my postgres version from 9.2.24 to 9.6 because I need jsonb compatibility along with other recent features. I am running a vm on centos 7. I decided to wipe all existing instances of po...

Is there a way I can rename all column names that are called 'location' to 'location_name' within all schemas in my PostgreSQL database? I am fairly new to SQL and am aware there is an ALTER TABLE...

How can I declare an array like variable with two or three values and get them randomly during execution? a := [1, 2, 5] -- sample sake select random(a) -- returns random value Any suggestion w...

I am trying to open a program for the first time on Windows XP Pro that uses PostgreSQL 9. I'm getting an error message that says : A problem was encountered while trying to log into or create t...

I am trying to dump a Postgresql database using the pg_dump tool. $ pg_dump books > books.out How ever i am getting this error. pg_dump: server version: 9.2.1; pg_dump version: 9.1.6 pg_du...

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 retu...

I would like to know for "pg_stat_statements" view in postgres. What is the timeframe for the data? Does it shows query executed in last 24 hours or overall queries executed? As the table doesn't ...

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');

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 fo...

I am having a problem changing the data directory on postgresql 9.2 on windows7: i`m trying to change my data directory: how can i change data directory on postgreSQL with pgAdmin?

I am getting FATAL: the database system is shutting down errors while creating PostgreSQL JDBC connections to a PostgreSQL 9.2 server. The specific exception path I'm getting from JDBC is here: Ca...

With the Play Framework 2.1 I have the following SQL defined in my evolution: CREATE OR REPLACE FUNCTION idx(myArray anyarray, myElement anyelement) RETURNS int AS $$ SELECT i FROM ( SELECT gen...

What's the CTE syntax to delete from a table, then insert to the same table and return the values of the insert? Operating on 2 hours of sleep and something doesn't look right (besides the fact th...

From postgresql documentation: RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this i...

For example , I use the following function to convert rows into json in PostgreSQL 9.2 select row_to_json(row(productid, product)) from gtab04; and this will returns below results row_to_json -...

I keep getting this error when i try to run my localhost using "$rails s": (Mac OSX 10.8.3) (ruby 2.0.0p195 (2013-05-14 revision 40734) [x86_64-darwin12.3.0]) (Rails 3.2.11) (psql (PostgreSQL) 9...

I installed postgresql on mac using brew. I wasn't asked about for the password for postgresql during the installation. Now I need to create a user and can't: Alexs-MacBook-Air:mydir alex$ su - po...

I have Windows Server 2003 machine on which I tried to install Postgres 9.2. At the end of the installation it pops a warning saying Problem running post-install step. Installation may not compl...

I am looking for a way to convert a postgres db schema to a JSON format data. e.g.: {"Table" : {"name": "varchar(20)", "Age" : "int" }, } Could someone help me with this?

I am using postgres 9.2. I need to change all column name to UPPER CASE for all tables in postgres db. Is there any way to do this?? Do i need to change any configurations in postgres?

I'm using "ALTER SEQUENCE sequence OWNED BY table.column" to change sequence association in Postgres. Is there a way to view this new information using \ds or something similar? \ds still shows the...

I have a column added_at of type timestamp without time zone. I want it's default value to be the current date-time but without time zone. The function now() returns a timezone as well. How do I s...

I upgraded my local Postgresql test server to PostgreSQL15 today morning. Opening pgAdmin gave me a warning that some of the features may not work correctly because I have a server with version 9 a...

I'm following the instructions found here. When I try to run $ heroku pg:psql or $ heroku pg:psql HEROKU POSTGRESQL_BROWN I recieve the following error message: ! The local psql command could n...

Facing issue with gitlab repository not available. When accessing project form gitlab page it shows below error No repository The repository for this project does not exist. This means you can n...