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
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
141 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
45 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
72 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
50 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
144 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
88 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
60 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
71 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.
2
votes
2answers
54 views

Index for OR operator: a=x or b=x

I have table with three integer columns id, a and b. I want to get all records where a or b match specified parameter ordered by id: select id, a, b from t where a=x or b=x order by id Note that x ...
1
vote
1answer
44 views

Difficult migration from Postgres 8.1 (postgis 1.5) to Postgres 9.4 (postgis 2.1)

I am in charge of a system developed 8 years ago. When I make a dump of the postgres 8.1 database, all the postgis 1.5 functions are included. I have successfully migrated it to a postgres 9.1, ...
0
votes
0answers
34 views

Query hung or way too long?

As part of the proccess of new items added to our system, some tables and relations are created programmaticaly via trigger, but it seems to be failing quite often right now. The DDL seems to be ...
1
vote
1answer
55 views

Empty string values - PostgreSQL 9.2 [closed]

I've got some empty string values being putting into my DB. Code I've used: CREATE FUNCTION get_as_byte_array(anyelement) RETURNS INTEGER[] AS $$ SELECT CASE WHEN ...
1
vote
0answers
25 views

Postgres query optimization: why could two very similar join tables have vastly different querying times?

I'm struggling with optimizing the performance of a single query, which is sometimes taking up to 180s for certain tenants. When I re-run this later in pgadmin on the production database server (using ...
1
vote
1answer
24 views

can we call CRUD operations as service methods?

I was asked this question by one of my superiors. The usual operations we do on database table from Service layer in java is Create, Update, Retrieve, Delete. These operations are basically services, ...
2
votes
2answers
61 views

Database “frozen” on ALTER TABLE

Our production environment just froze* this morning for a while when altering a table, adding a column actually. Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[]; * ...
1
vote
0answers
40 views

How to identify file for “The system cannot find the file specified.” during a pg_ctl -D start?

I'm getting one message during pg_ctl -D start of the standby server. Perhaps more of a general troubleshooting question. This is what I'm seeing when I do a pg_ctl -D start: D:\>pg_ctl -D ...
4
votes
1answer
105 views

Why isn't this PosgreSQL LIKE query working?

PostgreSQL 9.2. [local] postgres@host=# select * from units where ip_address LIKE '192.168.43.%'; ERROR: 42883: operator does not exist: inet ~~ unknown LINE 1: select * from units where ip_address ...
4
votes
1answer
97 views

Foreign key with additional constraints?

There is a table called Item(id, name, cost) and Orders(id, bill_id, item_id, units), which is created to track orders placed, where same bill_id means it belongs to a single order. How to impose an ...
2
votes
1answer
54 views

Dynamic Column Label in Postgres 9.5

I am using postgres 9.5 and have built the following function: CREATE or REPLACE FUNCTION func_getratio_laglag(_numeratorLAG text, _n1 int, _denominatorLAG text, _n2 int, _table text) RETURNS ...
1
vote
1answer
40 views

Reuse subquery as in another result

In a typical social-network-like application I have the following query that gathers news from a set of followed channels. The main query gets the news, while the subquery gets the followed channels: ...
3
votes
1answer
66 views

Converting standard_conforming_strings from off to on when importing Postgres dump

I've got a Postgres 8.1 instance which has standard_conforming_strings set to off (== handles slashes as escape characters). Recent Postgres versions have this setting on by default (for good ...
0
votes
1answer
25 views

Setting up PostgreSQL Connection with DBVisualiser

Apologies for the rudimentary question, I'm new to setting up databases. Am aiming to create a new Postgres connection and load some CSV files into a table using DBVisualiser. Working on a Mac running ...
0
votes
1answer
54 views

contradiction of 3rd normal form on address table

I have an Address table and its columns are address_id, address_1, address_2, city, state, country, pin code .Its primary key is address_id . According to 3rd normal form no non-key attribute should ...
0
votes
0answers
20 views

Discrete range type without canonical function

I want to create and use a discrete range type but I'm not willing to create a canonical function as there are about 30 years that I don't write a single line of C code, and it seems it is the only ...
1
vote
2answers
35 views

Does the PostgreSQL COPY command have the option of choosing with fields to map the CSV columns to?

Does the PostgreSQL COPY command have the option of choosing with fields to map the CSV columns to? The PostgreSQL COPY command appears to expect that the target table matches its columns exactly. Am ...
0
votes
1answer
18 views

Export roles *with* privileges under PostgreSQL 9.3

New to backups under PostgreSQL. I've been backing up my database with the general commands, and it works well. Except no users or privileges are stored with it. I understand that I need to do that ...
0
votes
0answers
15 views

Date of create sequence, function

I'm using PostgreSQL 9.3 . Question: 1/ How to know date of create sequence, function ? 2/ How to know last date of change sequence, function ?
0
votes
2answers
47 views

Blank (but not null) columns - PostgreSQL 9.2

Doing by: select * from ja_customers where id = 8154501 I receive: You can see that all the required fields are "blank" with some invisible character, so they're not null, so they're valid ...
0
votes
0answers
22 views

List Schema Size - PostgreSQL 9.2

I'm trying to get a real schema size... But I'm getting different results using two queries. Can you please help with a real trusted query to get the size of a schema? Schema name: gorfs Query 1: ...
0
votes
0answers
29 views

database Deadlock again

Once again I have a deadlock problem with the following log: [17/03/16 16:42:20.020 -0300] FATAL OPERACIONES: SQLException: CreateEquipo: ERROR: deadlock detected Detail: Process 2923 waits for ...
1
vote
0answers
24 views

How to view phppgadmin as the application name in PostgreSQL's log files?

I am using PostgreSQL 9.2.2 (Windows) with phpPgAdmin 5.1. I need to monitor who connects to the server, and with which application. In the logging parameters of postgresql.conf, I use the following ...
1
vote
1answer
48 views

Create dynamic WHERE clause from a JSON field

We have tables like this: people_people ( "id" integer... "attributes" HSTORE ) object_permission ( "permissions" JSON ) An example of an object_permission.permissions is: ...
1
vote
2answers
52 views

PostgreSQL: Calculate balance working backwards from current balance

I have a balances table that stores the current_balance for bank accounts; and a transfers table that contains a row for every transfer to- and from an account (deposits are positive numbers, ...
1
vote
1answer
32 views

INSERT INTO using the RETURNING values from another INSERT

I'd like to do something like this: INSERT INTO table2(name) INSERT INTO table1(name, address) VALUES ('me', 'home') RETURNING name; And then I would expect a new record in table2 with 'me' in ...
0
votes
1answer
93 views

Permission denied to crate “pg_catalog.tablename” during pg_dump targetting another machine

I am trying to dump some tables from a 9.5 beta 2 to a 9.4.4 server. The form of the command I am using is the fairly standard: pg_dump -t table dbname | psql -h hostname -d dbname I am using the ...
1
vote
0answers
26 views

Postgresql 9.4 replication not working

I have setup my replication as per https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps. When I start my slave and check for ...
4
votes
4answers
144 views

Extract MIN and MAX values related to datetime values on Postgres 9+

I am trying to query a table that has a collection of student grades recorded over time. I would like to produce a result set that grabs the student id, the first grade, first grade date, last grade, ...
0
votes
0answers
16 views

pg_toast growth out - PostgreSQL 9.2

Trying to understand how can the pg_toast table be 90% of my entire DB? SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT ...