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
33 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
36 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
17 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
38 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
52 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
56 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
116 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
0answers
29 views

template1 database being accessed - PostgreSQL 9.2

I'm currently using postgres 9.2 and as you can see below, the "template1" database was being accessed: The server is a 4-day-old backup DB - does a gzip of pg_dump, excluding some tables; also ...
0
votes
1answer
41 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
38 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
71 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
41 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
37 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
94 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
28 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
50 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
64 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
21 views

I don't have delete option under my question in stackexchange [migrated]

How to have delete option under my question in stackexchange?
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
33 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
80 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
37 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
58 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
32 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 ...
3
votes
1answer
33 views

Does updating a jsonb column trigger updates on all expression indexes on that column?

I am using the jsonb type in PostgreSQL. I frequently make updates of the following kind (where jdoc is a jsonb column): UPDATE my_table SET jdoc = jdoc || '{a: "Hello World!"}'::jsonb WHERE id = 123 ...
1
vote
1answer
41 views

How to store the result of multiple select queries into one csv file?

How to store the result of all of the queries below into one CSV file in PostgreSQL by executing one COPY (......) to.... statement. select id,name from first; select id,salary,father_name from ...
1
vote
1answer
24 views

Paged query against timestamp populated by trigger missing items

We're having a strange problem with a PostgreSQL 9.2 database that I've been racking my brain trying to figure out. First some background. We have a "tombstone" table that is populated using a ...
0
votes
1answer
77 views

psql: FATAL: password authentication failed for user “GuardDog_02”

In postgresql 9.5.1, I created a user called GuardDog_02. I gave it a password. And I gave it permissions to create db and user: CREATE USER GuardDog_02 WITH PASSWORD 'apple100' CREATEDB CREATEUSER; ...
0
votes
1answer
26 views

Adding and populating column in Postgres with data from existing column

I have a TEXT column in one of my existing database tables that holds both domain names and IP addresses. I'd like to add a new column to the existing table that's of type INET and populate it with ...
6
votes
1answer
143 views

Why is performance non-linear if I do many UPDATEs in a single transaction

An older question covers some reasons why the performance of multiple INSERTS within a single transaction is non-linear as the INSERT count grows. Following some of the advice there, I've been trying ...
1
vote
1answer
35 views

Deleting aged postgresql rows with tricky condition

I have two postgresql tables I need to clean up as part of maintenance. The tables, as described in my Django front-end, are: class Group(models.Model): owner = models.ForeignKey(User) unique ...
0
votes
2answers
47 views

Deleting old postgresql log files (Ubuntu)

In a Ubuntu machine hosting a postgresql database, I have 16G space taken up at /var/lib/postgresql/9.3/main/pg_log. Apparently, log files from many months ago are being stored as well. I manually ...
1
vote
1answer
74 views

Postgres: how to restore backup with WAL-E?

I am trying to setup Postgres and WAL-E. So far I have managed to install WAL-E and it appears to be working. I can make backups, WAL logs seem to be sent to S3, great. Here is what S3 looks like: ...
3
votes
2answers
52 views

PostgreSQL: Generate a series of dates for each group in a table

I have a balances table in PostgreSQL 9.3 that looks like this: CREATE TABLE balances ( user_id INT , balance INT , as_of_date DATE ); INSERT INTO balances (user_id, balance, as_of_date) VALUES ...
4
votes
2answers
147 views

Is there a way to SELECT n ON (like DISTINCT ON, but more than one of each)

I have a table us_customers that looks like this (with hundreds of thousands of rows): +----------+----------+ | id | us_state | +----------+----------+ | 12345678 | MA | | 23456781 | AL ...
2
votes
1answer
29 views

Schema name, table name and row count

My database has 50+ schemas. Each schema has around 100 tables and some views. I want a query that tells me the schema name, table name, and total number of rows in each table. The below query: ...
1
vote
0answers
38 views

Inner join on array columns from 3 tables

I have 2 tables that are related between them using integer data which is stored in arrays. Data table telemetria_data.historico_354677050164513 CREATE TABLE historico_354677050164513 ( imei ...
0
votes
0answers
7 views

Restore database exported with database_to_xml

I've exported a database to a XML file using the database_to_xml command found in documentation. Now I want to perform the opposite task. I want restore the database from an empty one using this XML ...
3
votes
0answers
49 views

Optimizing an Intersect query between two huge spatial tables

I am having a hard time trying to improve an intersect between two spatial tables and I would like to receive any tips about the table designs, queries or dba configs. Tables: Table ...
0
votes
2answers
44 views

One connection vs multi connection

I'm redesigning my client's website in order to add about 16 mini sites for their web site. It's a website using PostgreSQL. And my question is: Performance wise, what is the better database design: ...
1
vote
1answer
38 views

How to store user activity that supports undo?

I can track user activity on posts, so that users can see for example "2 updates!". The essence of the strategy I’m using leverages 2 tables: post_activity: post_id | user_id | time_created ...
2
votes
1answer
89 views

Optimize performance for query with CTE using a multicolumn index

I've got this slow query: WITH flks AS (SELECT id FROM public.ja_feedlog WHERE gtime BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2016-01-24')::BIGINT AND EXTRACT(EPOCH FROM ...
1
vote
1answer
62 views

PostgreSQL Write Ahead Logs Archive Mode

I'm trying to figure out various things surrounding PostgreSQL and how backups should work together with WAL and Commvault Simpana. Simpana is telling me that everything is ok, but leaves files lying ...
0
votes
1answer
75 views

Postgres hstore check if a key exists and does not match value

I have a hstore field in an audit table that stores all the fields that have changed as a result of an operation. On inserts, the key, updated_by in changed_fields is NULL and on system updates it is ...
0
votes
1answer
33 views

Poor Query performance in postgresql 9.3

I have a simple query which is taking about 2 minutes to complete. select distinct a.statename, a.districtname, count(b.rel_globalid) as samplescollected from fulladmin a ...
4
votes
2answers
62 views

Finding earliest connected value over two columns

I have a, perhaps slightly esoteric, use case. Given a table like the following: id1 | id2 | timestamp -----+-----+--------------------- 3 | 4 | 2016-03-22 09:52:15 1 | 2 | ...
0
votes
0answers
28 views

Postgres 8.1 logging slow queries twice

For every slow query logged, there is another STATEMENT follows it in my log file. 2016-03-22 11:11:23 KST [9998]: [1-1] LOG: duration: 4796.936 ms statement: SELECT 1 FROM DUAL 2016-03-22 ...
1
vote
1answer
32 views

Does creating a unique constraint on a Postgres column remove the need to index it?

Does creating a unique constraint on a Postgres column remove the need to index it? I expect that an index is automatically needed to maintain the constraint efficiently.