PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

0
votes
0answers
43 views

How to check/know the highest run queries

I would like to gather statistics on the highest run queries in my database. For that I need to know how I can track queries so I can create views or materialized views to cache the results of the ...
1
vote
1answer
46 views

List MATERIALIZED VIEWs in PostgreSQL that do not have a UNIQUE index

Postgresql 9.4 allows to REFRESH materialized views CONCURRENTLY, if they have a UNIQUE index. I am looking fo a SQL-query that lists all materialized views that DO NOT have such a UNIQUE or PRIMARY ...
0
votes
1answer
25 views

Postgres DB user permission

I'm logged in as a user in postgres with PgAdmin tool in Linux connecting remotely but I don't think the user has enough rights. I'm a MySQL experienced user but not so much in PostGres. When I select ...
1
vote
2answers
47 views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
1
vote
1answer
69 views

Implementing json_object_agg() in Postgres 9.3

I feel like I need the json_object_agg() function of Postgres 9.4 but I will not be able to upgrade from 9.3 right now. Is there a way to do what I want in 9.3? Here's my scenario. I have a table ...
1
vote
1answer
23 views

Using the value of a JSON object that is stored in a PostgreSQL JSON array

I have a table that looks like this (simplified): Jobs ------------------------------- ID | Details ------------------------------- 1 | { 'tasks': [] } 2 | { 'tasks': [{'id': '1', 'due_on': ...
0
votes
1answer
358 views

Taking hot backup of slave node in postgres (master-slave config with repmgr)

I have a postgresql setup with master-slave configuration using repmgr. I'm struggling with taking a consistent snapshot backup (pg_start_backup) of the slave node, this is due the fact that the slave ...
0
votes
1answer
38 views

the cache hit rate using pg_statio_user_tables

I read from this blog that a small 20% of the data in your database is getting the vast 80% of reads. That means a small portion of the data in the database is frequented. Hence, you want to ensure ...
0
votes
2answers
551 views

why pg_restore ignores --create ? Error: failed: FATAL: database “new_db” does not exist

I am trying to run following command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db I get: failed: FATAL: database "new_db" does not exist
0
votes
1answer
619 views

pgpool-II configuring multiple master databases

I could not find a solution for this anywhere, not even in the pgpool manuals. How do I configure multiple - master postgresql databases with pgpool-II? Additional requirement is that each master ...
1
vote
1answer
37 views

Multi-tenant database constraints

I have a multi-tenant app in PostgreSQL that has a repeated tenant_id column in each table to help isolate the tenants from each other. My question is, is there any sort of way that I can use ...
2
votes
2answers
2k views

pgpool replication not allowing md5 authentication

I have installed both pgpool and postgresql via apt. In pgpool.conf, I have made the changes necessary to use replication mode, as well as my two postgresql databases (currently all running on the ...
1
vote
0answers
30 views

BDR Replication issue

I am trying to setup 2-node BDR replication and i am getting the following error while running bdr_init_copy. /usr/pgsql-9.4/bin/bdr_init_copy -d "host=10.10.145.111 dbname=apimgtdb port=5432" ...
0
votes
1answer
30 views

Cannot rename database (the user in --username is the owner)

I connect with psql and list my databases: List of databases Name | Owner | Encoding | Collate | Ctype | Access ...
0
votes
0answers
32 views

Calculating the row size of a table [duplicate]

Is there a way to calculate the row size (in bytes) of a particular table in postgresql / pgadmin3?
0
votes
0answers
28 views

Check if key exists in a JSON with PL/pgSQL? [migrated]

I'm trying to check if a key exists in a JSON sent as parameter in a PL/pgSQL function. Here is the function. CREATE FUNCTION sp_update_user(user_info json) RETURNS json LANGUAGE plpgsql AS ...
0
votes
1answer
23 views

Postgres - returning custom values ie. “true” rather than “t”

I am wondering if there is a way to show custom "values" in the output of a query purely for readability, similar to the AS statement for renaming column labels. Seems unlikely but would be useful ...
0
votes
1answer
25 views

Point PostgreSQL on Ubuntu to a new datadir

I have a new Ubuntu 14.04 install on a new drive, with the old main drive containing a PostgreSQL install from the previous Ubuntu 14.04 install. I've been trying to move the PG dbs from the old ...
1
vote
0answers
39 views

What is the best way to clear couple of tables each night at specific hour in Postgres SQL?

I need to clear couple of tables every night on my db so in the morning they will be empty and ready to use for the end users. I already wrote the query to truncate them but I'm not sure what is the ...
0
votes
0answers
27 views

Foreign table not using index

I have a table on database temp_db named external_objects and an index on the field id, which is an MongoDB id (like 5458d717cd07870859000003). When I explain this select at this database, I got this ...
6
votes
1answer
546 views

Is there a psql equivalent of bash's reverse-search-history?

I am very fond of bash's reverse-search-history (C-r) (command-line feature): Search backward starting at the current line and moving ‘up’ through the history as necessary. This is an incremental ...
0
votes
0answers
26 views

How can I set a value after Select query in PostgreSQL? [migrated]

I use simple select query: SELECT transactiontype FROM posfeed LIMIT 100; I result I have some rows with empty values. I need set some default value in SELECT query result for some filed. ...
0
votes
1answer
35 views

Postgresql :i want tro truncate column

I have select * from generale where titolo ~* 'hp-ux' order by titolo; autori | titolo ...
1
vote
1answer
45 views

Algorithm used to perform offset/limit over a union all result set

I have two result sets: rs1: id name serial text ________________ 1 Nick .................... 1233112 Pete rs2: id name serial text ...
0
votes
1answer
17 views

postgresql : find privileges in meta informations

With PostgreSQL 9.4 on linux centos 6.6. I give a privilege to a user with something like : ALTER DEFAULT PRIVILEGES FOR ROLE DBNAME_adm IN SCHEMA DBNAME_adm GRANT SELECT,INSERT,UPDATE,DELETE ON ...
1
vote
1answer
21 views

Query single column, all rows in Postgres 2D Array

Is there any way to fix the query below so I don't have to specify an arbitrary max row count? DROP TABLE IF EXISTS array_test; CREATE TABLE array_test ( id serial primary key, name text, ...
4
votes
3answers
6k views

COUNT and SUM at the same time after removing duplicate rows

I'm using PostgreSQL and I can't figure out a query for doing a COUNT and a SUM of two different columns. Basically I'm looking for something like a SUM(hits DISTINCT record_id) The table: status ...
1
vote
1answer
24 views

Postgresql ignoring a substring in the middle of a string

I may have phrased the title of this question badly. I have a documents table that has a filename, path, and file_content_type columns. I want to search against the filename column to determine if ...
0
votes
2answers
41 views

How to access row value from UPDATE sub query function?

I would need to use UPDATE like this: update offer set (bank, bank_id) = (cost, gid) from ( select cost, osm_points.gid from pgr_kdijkstraCost( 'SELECT gid as id, source, target, walk_cost as cost ...
0
votes
0answers
32 views

Optimize query with access to tiny fraction of data

I am trying to optimize some queries but I can only use a tiny portion of the actual data they will be executed against later. Probably the query optimizer will generate completely different plans ...
0
votes
1answer
23 views

Possible to use a field within a query in PostgreSQL?

I want to query all events that are going on right now. Each events record has at and minutes fields for the time range of the event. I might query the events table like this: SELECT * FROM events ...
0
votes
1answer
45 views

What is the purpose of the keyword “||” in PostgreSQL? [closed]

I'm new to PostgreSQL and I'm trying to figure out what does the operator || mean? Google didn't help with that...
0
votes
1answer
18 views

Select row if column data was not selected

How can I selected rows from a table when a column data is different. Table: id(pk), name, title, book_id, date I want to get all the rows where book_id is uniq. Data example: test1, test2, 1, ...
2
votes
1answer
42 views

is it possible to run postgres with no WAL files being produced?

I am looking at a way of running a 9.2 postgres database on redhat 6 without any WAL files being produced. We have a end of day batch process which generates a large number of WAL files (and has ...
0
votes
1answer
23 views

Postgresql select columns in join query [closed]

say that i do inner join in query... the result will give columns from both table. Is there a way to specify that i want only columns from one of the tables without specifying all columns names ...
0
votes
2answers
52 views

PostgreSQL: how do I list all triggers that use a specific field?

for example i have a student table with (studentid) i want to get a list of triggers that use this field... for example if in table X there is a trigger that uses studentid i want it to be shown in ...
2
votes
1answer
559 views

Why Django session table grows on PostgreSQL

I use PostgreSQL 9.1.6 via PgBouncer 1.4.2 in my Django 1.4.8 project. django_session table grows all the time. VACUUM does not help. VACUUM FULL runs for hours. Only TRUNCATE helps usually. The ...
0
votes
0answers
17 views

Postgresql list all functions associated with table and column [duplicate]

I have a table named SW and in it a column named ID. I want to list all functions from all Schemas that use this table and this column. I've wrote the following query: SELECT n.nspname AS ...
3
votes
1answer
63 views

Drop primary key without dropping an index

Is it possible to drop primary key constraint on a table without dropping the index? I have a situation when I want to remove the PRIMARY KEY to get rid of the NOT NULL constraints and set it to ...
0
votes
0answers
49 views

PostgreSQL -> MySQL Migration with MySQLWorkbench

I'm trying to migrate data from a PostgreSQL DB to a MySQL DB (it's for permissions in the VPC, so please nobody link me the 'choose-something-else'). I'm getting this error from MySQL Workbench when ...
1
vote
0answers
692 views

How to Change location of postgres cluster and database within the same machine? [duplicate]

I have my present database cluster of postgres at /mnt/my_hard_drive which I want to change to /home/myfolder. I also want to move all my databases present in the present cluster to /home/myfolder. Is ...
1
vote
0answers
130 views

Identical Postgres index scan taking 5x longer on server [closed]

I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject: Column | Type | Modifiers ...
1
vote
0answers
24 views

postgres \copy file to RDS timeout

Postgres version 9.3, hosted on Amazon RDS. I have a messages table with 10G of data. I connected the database using psql on the EC2. Then used \COPY (select * from messages) TO '/tmp/messages.csv' ...
0
votes
3answers
42 views

Boolean check on fields returning any of them except if all have same value

Assuming we have 4 boolean fields a,b,c,d How can we return any of the fields that are true, but not nothing when all 4 are true? where a=true or b=true or c=true or d=true will return a row where ...
0
votes
0answers
10 views

Query table with the same name on different schemas in PostgreSQL [duplicate]

I want to query table with the same name (for instances "walls") in multiple schemas on a PostgreSQL database. Is it possible? I am searching for something like this: select * from *.walls; I need ...
0
votes
0answers
36 views

How to list all tables associated with specific foreign key?

I have this Postgresql query: select (select r.relname from pg_class r where r.oid = c.conrelid) as table, (select r.relkind from pg_class r where r.oid = c.conrelid) as type, (select ...
1
vote
2answers
328 views

Restoring the superuser account on the postgres server

I have a complete curious situation with a postgres dbms. I have installed the database and due to a misconfiguration of my installation scripts I made the superuser role to be a regular user. Now I ...
1
vote
0answers
32 views

postgresql Insert into table B based on foreign key in table A, if exists in A else

the scenario is as follows: I have an invoice and supplier table. The invoice references supplier on supplier_id. I want to insert into invoice. If supplier_company does not exist in the supplier ...
2
votes
0answers
54 views

Postgresql upgrade issue

I'm trying to test the Postgresql 9.3 -> 9.4 upgrade process on the development environment on windows and I get weird issue during the pg_upgrade invocation: PS D:\bin\postgres-9.4> ...
0
votes
0answers
23 views

postgresql view all tables with specific FK

I have a table of suppliers with supplierID (PK) and supplierName in Schema X I want to find out all tables names in all Schemas where the supplierID is FK there. How can i write a query that does ...