All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

1
vote
1answer
69 views

RDS Postgres Peformance

I have recently set up a new RDS instance to test out working with a AWS postgres instance vs a heroku postgres instance. What I've found is that when I load an instance of 1000 entries in RDS its ...
1
vote
1answer
37 views

Moving postresql 9.1 database - live

This question has probably been asked and answered before, and if so, I would appreciate if that could be pointed out to me. I have googled this point quite a bit, but I want to be very sure as to ...
3
votes
3answers
235 views

Optimizing a query which runs through all hours in a day

I need help optimizing a query. I'm using PostgreSQL 9.3.4 currently but can upgrade to 9.4 if needed. I have a table with 60+ millions of records which looks like this: ...
2
votes
2answers
214 views

Setting shared_buffers in postgresql.conf does not seem to take effect

We're on CentOS release 6.6, PostgreSQL version 8.4.20. (Yes, this is not bleeding edge.) In postgresql.conf, we have: shared_buffers = 4096MB kernel shm values are set nice and high: [root@green ...
0
votes
2answers
66 views

Can I Show Multiple “WHERE” Filtered Results In A Single Query/Output Table?

I am using Postgres 9.2.4 on Red Hat 6. Tables --------------------------------------- | Table 1 | --------------------------------------- | WorkflowID | DataSet | ...
0
votes
3answers
1k views

check key if exists in other table without fk constraint

Lets say i have: table a (id_a int); table b (id_b int, id_a int); I want to check if b.id_a exists in "a" before a new row in "b" will be inserted. Normally I would just add a fk constraint on ...
0
votes
1answer
62 views

How can we configure the Streaming Replication(master-slave) on single SAN storage in PostgreSQL?

I want to configure the Streaming Replication in PostgreSQL.My data directories of Primary and Standby are in same SAN storage.Is it Possible to do replication.
0
votes
0answers
84 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
220 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
35 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
1answer
114 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': ...
2
votes
1answer
106 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 ...
1
vote
0answers
106 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
191 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
34 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
1answer
35 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
40 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
60 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 ...
1
vote
0answers
146 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 ...
8
votes
1answer
1k 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
1answer
109 views

Postgresql :i want tro truncate column

I have select * from generale where titolo ~* 'hp-ux' order by titolo; autori | titolo ...
1
vote
1answer
201 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
42 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
100 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, ...
5
votes
3answers
9k 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 ...
2
votes
1answer
33 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
184 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
34 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 ...
1
vote
1answer
28 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
47 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
25 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, ...
4
votes
1answer
90 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
27 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 ...
3
votes
1answer
280 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
226 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
1k 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
139 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
239 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
0answers
55 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
445 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
60 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 ...
3
votes
0answers
152 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
26 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 ...
0
votes
0answers
61 views

Loop through fields on a before-insert trigger?

Here's my table: CREATE TABLE foo ( i_am_pk CHARACTER VARYING(255) PRIMARY KEY NOT NULL, dont_touch_this CHARACTER VARYING(255) NOT NULL, name CHARACTER VARYING(255) NOT NULL, addr CHARACTER ...
2
votes
1answer
140 views

Query for sub-array in multi-dimentional array

How do I fix the following sql query: DROP TABLE IF EXISTS array_test; CREATE TABLE array_test ( id serial primary key, name text, array_column integer[][] ); insert into array_test (name, ...
3
votes
0answers
3k views

Achieve PostgreSQL hstore functionality in MySQL

Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table. I don't know if this feature is available out of the box in MySQL like ...
0
votes
0answers
2k views

Postgres error ( XX000: invalid memory alloc request size 4294967290 ) [closed]

I'm getting the following error in my postgres 8.4 database: XX000: invalid memory alloc request size 4294967290 Operating System : Windows 7 64 bit Memory : 8GB Database Version: Postgres 8.4 ...
2
votes
0answers
890 views

Best book on query optimization, rewriting, refactoring [closed]

I have a specific problem: huge amounts of Web-related data in Postgres db. The queries running against the data are often very inefficient. But in a more general sense, I need a book that explains ...
0
votes
1answer
51 views

Access operator added by extension inside a schema

I am writing an application that uses postgres. Each microservice uses its own schema inside the database. So connect to the database schema I use this jdbc string: ...
1
vote
1answer
43 views

postgresql double count query

I have a table with 3 columns: key, status and rank. status contains numbers and rank contains t or f. key is auto numbered and irrelevant here. my goal is to count how many in each status are t and ...