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

learn more… | top users | synonyms (1)

0
votes
0answers
19 views

What is the easiest way to set up automatic replication of tables/schemas on two PostgreSQL servers?

I have two PostgreSQL database servers, one uses SSD, the other HDD. We struggled with the full SSD disc and we didn't need all the data on SSD all the time so we moved the majority of them to the ...
4
votes
1answer
149 views

Upgrade from Postgres 9.3 to 9.5

I’m attempting my first upgrade of Postgres from 9.3 to 9.5. I’ve run into two roadblocks but not sure how to get around them. I have my 9.3 PGDATA set to /data/postgres, which is a separate ...
0
votes
1answer
17 views

Restoring postgresql 9.4 crashed database

Can anyone help me to restore my crashed database. Is there a way to use pg_resetxlog. I read a number of documentations telling about it but i'm always seeing the same log when i restart my ...
0
votes
0answers
29 views

Fast min / max values of column in big table with index [duplicate]

I have a huge table public.data_minutesample: Column | Type | Modifiers ----------+---------+----------- probe_id | integer | not null power | integer | not null minute | integer | not ...
1
vote
1answer
56 views

PostgreSQL 9.5: Permission to deny functions body

I create new role without any grants. New user can't select on tables, but can see functions body (pl/PgSQL) I want permissions for new role: allow INSERT into one table. deny see tables, ...
0
votes
1answer
60 views

open/execute huge sql file

I have SQL file using PostgreSQL with size about 7GB. How can I open/execute the SQL file? I have researched and tried using navicat premium,razor SQL, HeidiSQL, workbench. In all I get out of memory. ...
1
vote
2answers
108 views

Converting rows to columns

I have data like following: created_at | status ---------------------+------------- 2016-04-05 1:27:15 | info 2016-04-05 3:27:15 | info 2016-04-05 5:27:15 | warn 2016-04-05 ...
2
votes
1answer
24 views

How to count multiple values of a column as one group? [duplicate]

I have a table main with the following rows: id | name | rank ----+--------+------ 1 | Ali | a 2 | Sami | b 3 | Khan | c 4 | Kamran | d 5 | Imran | e 6 | Asad | a 7 | ...
1
vote
1answer
29 views

Column must appear in the GROUP BY clause error even though it DOES appear there

I have a table that looks like this: order_number | key_a | key_b | key_c | key_d | quantity -------------|-------|-------|-------|------------------ 14400 | 2 | 3 | 4 | 54 | 40 ...
0
votes
1answer
27 views

How to select one column by two different names in the same query?

I have a table named Main that contains two fields id,name. I want to select the name column by two different names. For ex: Table: Main-> id:int, name:varchar(20) I have inserted the following ...
1
vote
1answer
73 views

Wildcard search using parameters in function with dynamic SQL

What is the proper way to implement a wildcard search in PostgreSQL when using a parameter in a function that uses dynamic SQL? As a starting point, here is an example from Erwin Brandstetter ...
0
votes
2answers
35 views

postgresql function index not being used by query

I am trying to match a text field so I created an md5 function index as it suggested since a full index was not allowed because the text data was too large. But I can't understand why postgres is not ...
2
votes
0answers
41 views

Order by index unexpectedly slowing query down

I have two tables: houses has ~415,000 records, and account_houses has ~525,000 records. I've changed the table names for security reasons, but basically I am using this query: SELECT "houses".* FROM ...
3
votes
1answer
40 views

Postgres restore from WAL-E backup never finishes

Doing a restore on a WAL-E backup to S3, postgres never seems to complete the restore process. The log repeats the following: < 2016-04-08 13:41:55.801 PDT >LOG: connection received: ...
1
vote
1answer
51 views

Creating an Updating trigger

I have to create a trigger that calculate the the quantity in stock and this is my first one on PostgreSQL. When entering the quantity bought at a Provider (Fournisseur) or a delivery at a client, ...
1
vote
1answer
44 views

Shipping big WAL archives to hot standby

I'm looking to extend my PostgreSQL 9.4 master with a few slaves in hot standby read-only for load balancing. The idea would be to update the slaves only at defined times (once every 24/48 hours) to ...
0
votes
0answers
11 views

Find missing associations in many to many relationship [duplicate]

I have client and category tables with a many to many relationship via client_category_association. table: client id name 1 ek 2 do table: category id name 1 un 2 dos 3 tres table: ...
7
votes
2answers
176 views

Looking for a simpler alternative to a recursive query

The actual query is more involved, but the problem I'm facing can be distilled to this: A query to filter a rowset of monotonically increasing integers so that - in the final result set, ...
0
votes
0answers
29 views

postgres vacuuming query: value of datfrozenxid increased after vacuuming

I checked the datfrozenxid values, and performed "vacuumdb --analyze -a", Normally postgres, template1 values are very high and they return to around 1,000,000,000 after vacuum. But I found something ...
1
vote
1answer
31 views

Storing blobs outside the database

I had a PostgreSQL database with loads of scanned documents, as a document bytea column in the table scans, with hundreds of thousands of documents that was large and inconvenient to backup. Also, ...
1
vote
1answer
75 views

postgres wal sender replication timeout during pg_basebackup

Let me start with the caveat that I am still green with Postgres. I am working on a postgres 9.2 Active/Standby cluster on Debian wheezy for an application, based off of the ClusterLabs pgsql cluster ...
1
vote
2answers
44 views

UPDATE from subquery updates rows that shouldn't be updated

Using PostgreSQL, I want to update a column. The information how to update is in another table. My try was this, but it also updates rows I don't want to update: update table_1 set column_x = ( ...
1
vote
0answers
101 views

Basic PostgreSQL 9.x Performance Optimization Strategies for Reads/Analysis

Postgres comes, out of the box, configured as a general purpose database for both OLTP and OLAP types of loads. My need is to do use Postgres, in this case, purely as an OLAP running aggregate queries ...
1
vote
1answer
50 views

Get total size for all Postgres or Greenplum tables including partitions

Is there an easy way to get the total size of all tables including partitions? I've written this query for Greenplum, but it's a bit round-a-bout: with part_sz as ( select ts.sotdschemaname sch ...
1
vote
0answers
42 views

pg_restore slow after adding a check function

I have a postgres database with 10 schemas, and recently I had to find a way for some tables between schemas to have common records. The way I did this was to remove the foreign key on some tables in ...
3
votes
2answers
86 views

UPSERT with ON CONFLICT using values from source table in the UPDATE part

Given: CREATE TABLE A ( PK_A INT8 NOT NULL, A INT8, PRIMARY KEY (PK_A) ); CREATE TABLE B ( PK_B INT8 NOT NULL, B INT8, PRIMARY KEY (PK_B) ); This query: insert into table_b (pk_b, b) select ...
1
vote
1answer
39 views

How to copy a Postgres database within the same DB cluster?

I want to replicate a Postgres database without dumping data using pg_dump. Both source and target database are on the same machine (Test and Production server). I would like to create a ...
0
votes
1answer
39 views

BIG Binary column - PostgreSQL 9.2

I've got the following table: CREATE TABLE gorfs.inode_segments ( st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms the table's primary key ...
1
vote
0answers
18 views

pypl store function to update a record is not working

I am using PostgreSQL 9.4.7 and Python 2.7.6. I am writing a plpython function to update a row in user table and my code is as below: CREATE FUNCTION update_user(myid int, mymail text, myname text) ...
0
votes
1answer
58 views

How to insert values into a table with default values from a select query in PostgreSQL?

Is it possible to INSERT values into a PostgreSQL table from a SELECT statement and to use DEFAULT values for the columns that are null? In my case, the SELECT statement is selecting from JSON. In ...
2
votes
1answer
56 views

Grouping data by year based on two timestamp columns

My data table has the following columns: id INTEGER, name TEXT, created TIMESTAMP, deleted TIMESTAMP I want to product a report of the count of each name (which can appear several times in the ...
1
vote
1answer
92 views

Convert Postgres TIMESTAMP to TIMESTAMPTZ

I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller. Stupidly, when I initially designed ...
1
vote
1answer
141 views

Postgres sharding installation with citusdb

We are developing a prototype for a BIG data product. We have almost 2 billion records. We have used PostgresSQL 9.5 as a back-end and Python as front-end. We are using a 16*2.4 GHz processors with ...
0
votes
1answer
57 views

“Unrecognized configuration parameter: qreplace_function” trying to backup a database

I am new to PostgreSQL and I am trying to backup a database with the pg_dump utility. I am using this command: /opt/PostgresPlus/9.4AS/bin/pg_dump -U (username) -f ...
0
votes
1answer
43 views

How to check if a postgresql backup is finished or not? [closed]

I started my backup yesterday, but now I don't know if it is finished or not. Can you please help me?
3
votes
1answer
77 views

DELETE rows which are not referenced in other table

I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group. I want to delete all rows from link_group where no related row in ...
0
votes
0answers
53 views

When to reindex postgres index?

I want to know if I need to reindex a PostgreSql index, ith SQL Server, I check for index fragmentation and then decides whether a Rebuild, a Reorganize or no action is needed. When should I reindex ...
1
vote
1answer
49 views

regexp_replace() vs. replace() to replace all matches in a string

By default, in PostgreSQL, regexp_replace() replaces the first occurrence of a substring in a string, while replace() replaces all occurrences of a substring in a string. Why is the default behavior ...
2
votes
2answers
95 views

Eliminate duplicates and optimize performance for big count and small LIMIT

I have a big query in my Postgres 9.1 database: SELECT *, count(*) OVER () AS full_count FROM ( SELECT DISTINCT ON(message.messagetime,message.messageid) message.messagetime, ...
0
votes
0answers
30 views

Why does Postgresql not write the username to the log file

I am trying to understand why, my database is logging without username in the queries. I discovered today that I can't see by looking at the logs who did a query. I think it is a configuration ...
1
vote
1answer
57 views

Structure for storing table data, lists, text, pictures within PostgreSQL JSON field

We are extracting content sections from word documents and have been going back-and-forth on how to store this arbitrary data. We are using PostgreSQL and our plan was to use JSON fields to save ...
1
vote
1answer
79 views

Moving data from PostgreSQL 32bit to 64bit

I use Synology's DS1515+ as a PostgreSQL (PG) DB server. Last week Synology upgraded their DSM (Linus based OS) to version 6.0. Doing so, they've upgraded their PG to 64bit and potentially to a newer ...
0
votes
1answer
38 views

Postgresql sequence always 1 [closed]

I have an application that often does insertion into PostgreSQL database, for some tables I have id column with serial type with auto-generated sequences. Running SELECT ...
2
votes
0answers
39 views

insert/update from web server or database

I have a data-model on the front-end that I want to insert/update in the database which of the following approaches (or neither!) is considered best practice: # in webserver some_model = {json} ...
0
votes
0answers
23 views

Database design for efficient read queries given lots of writes

I have a PostgreSQL database, and an application which receives around 200 requests per second. Each of those requests contains a message with an identifier representing a journey id, a timestamp, ...
0
votes
0answers
34 views

Pooling at the app layer or in an intermediate layer?

I need to use a connection pool with PostgreSQL. I do not need load balancing or replication, just pooling. What would be the best option? Pooling at the app layer (using a JDBC connection pool) or ...
1
vote
1answer
86 views

How to give title to the result of a select query in postgresql?

For example I want to do like this: select id,name from first; It should look like this: The Query Result // this should be the title of the query id name 1 Ali 2 Sami
1
vote
1answer
44 views

Read only postgres table, except by trigger

Suppose the following situation: I have a product_stock table and a stock_transaction_log table. Currently whenever there is an increase/decrease in a product's stock, I update the product_stock ...
1
vote
1answer
59 views

Select column values linked to exact set of values in another column

Given the below chats_users table in Postgres 9.5, with integer columns chat_id and user_id, I want to find the chat_id associated with an exact set of user_ids, for example: user_id IN (1, 3) => ...
1
vote
1answer
33 views

Started using UUID's as primary key, would like to execute union of entities with BigSerial Primary Key

Some of my newer tables are now using UUID type fields as PKey and older tables are still using bigserial. I have several tables (let's call them entities) I would like to query returning the primary ...