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
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
63 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.
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
35 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
62 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
41 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
55 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
21 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
48 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
23 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
31 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
50 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
99 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
146 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
17 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 ...
1
vote
1answer
31 views

Insert multiples rows/columns - PostgreSQL 9.2

I've got a table that has several columns, But I need to insert data into two columns of that table. I need to add data into those columns, from a select statement. Would it be something like: ...
0
votes
0answers
42 views

Moving slow sub-query to With clause

I am trying to optimize this query : SELECT sum(a-alias.s_count) AS count FROM a-table a-alias WHERE ( a-alias.created_at_minute_id >= (floor(extract(epoch from( select ...
1
vote
0answers
27 views

Moving extensions in AWS RDS (Postgres)

Initially we installed all extensions in default "public" schema. Now we have more and more other schemas - thus we want to add extensions and common functions to the schema common. Unfortunately AWS ...
1
vote
0answers
48 views

Postgresql string array vs join for user management schema?

I am creating a database schema for user management in PostgreSQL. As, it supports array, so I am trying with three approaches. Which one is best, querying with joins or getting/setting role data ...
0
votes
0answers
41 views

How to make sure vacuum is happening in postgres sql db

I have created a table with two columns and created index for the first column. Then inserted 2 million data using generate series function for first column (2nd column is having null). And executed ...
0
votes
1answer
37 views

PostgreSQL Bloat Warning: Vaccuum does not help

We use check_postgres to monitor our DB. We get this warning: POSTGRES_BLOAT WARNING: DB "foo" (db foo) index foo_text_index_id_uniq rows:? pages:485435 shouldbe:330673 (1.5X) wasted ...
6
votes
1answer
1k views

Why is PL/Python untrusted?

According to the docs: PL/Python is only available as an "untrusted" language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpythonu. A trusted ...
0
votes
1answer
23 views

Constraint to prevent overlapping ranges in an array field in PostgreSQL

If I have a tztrange field in a table, I can make sure that no two rows have values that overlap using an exclusion constraint. To make sure that no two appointments can be scheduled at the same time, ...
0
votes
1answer
40 views

INSERT INTO + Selects - PostgreSQL 9.2 [duplicate]

I wanna add some data into two columns in a table. Would be something like: INSERT INTO dm.billables_links (billable_id) VALUES ((SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike ...
0
votes
1answer
45 views

Postgres Data Dump problem

I am trying to dump this data into Postgres: COPY address_types (game_id, device_type) FROM stdin; 1 Ps3 2 Xbox 3 Other \. But I always get this error: [Err] ERROR: syntax error at or ...
0
votes
2answers
46 views

Declare variable for SELECT statement in Postgres function

I have modified my function, but I have problems with declaring variables. I use Postgres 8.4. CREATE OR REPLACE FUNCTION requestcounterid(_mindate timestamptz, _maxdate timestamptz) RETURNS TABLE ...
1
vote
0answers
23 views

CTE ordering guarantees [duplicate]

I'm starting to make heavy use of CTEs for my postgres based application, and am wondering if selecting from a previous CTE statement guarantees any ordering? E.g. is the order of rows for this query ...