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

learn more… | top users | synonyms (1)

3
votes
1answer
37 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
50 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
25 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
107 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
30 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
148 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
36 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
73 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
107 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
62 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
156 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
34 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
42 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
55 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
45 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
41 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
91 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
88 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
104 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
34 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
66 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
29 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
35 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
48 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
44 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
57 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
28 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
77 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
44 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
107 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
98 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
56 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
43 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
67 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
58 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
22 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
38 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
19 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
51 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
42 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
30 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
57 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
58 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
158 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 ...