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
2answers
152 views

SELECT 1 - idle in transaction

Across my problems with the load on the database, I just have found a new, odd one. I understand, that some queries might be in IDLE IN TRANSACTION status, when I did something wrong in my application ...
0
votes
1answer
19 views

Independent serial for each foreign key value

I have a table shared by many different users called players and a table called users. Each player has the primary key id (serial) and a foreign key called owner_id which matches to users.id. In ...
0
votes
0answers
60 views

Filter on UUID data type in Navicat

I am a first-time user of Navicat for Postgres 11.1.15 on Mac OS X. Tried to filter a list of rows on a column named "uuid_" of type UUID. I pasted in a string of the hex representation of a UUID ...
0
votes
0answers
89 views

PostgreSQL streaming replication with slots works for one slave, but not for another slave

Master (10.0.0.1): Edit postgresql.conf listen_addresses = '*' wal_level = logical max_wal_senders = 3 max_replication_slots = 3 hot_standby = on Edit pg_hba.conf host replication ...
1
vote
0answers
159 views

Suddenly unable to connect to PostgreSQL

I have a simple backend running on a server back at my house while I'm away at college. I had it just sitting there, doing nothing, with daily endpoint tests for about a month, when suddenly yesterday ...
0
votes
0answers
50 views

What role for plpythonu function's in the file system?

As a preliminary test for a further work I'm trying to use a simple plpythonu function in Postgresql 9.2 to create a folder in my filesystem. So I have this code : CREATE OR REPLACE FUNCTION "...
0
votes
1answer
40 views

How to handle this complicated join condition(s)?

drop table if exists company_tags; drop table if exists project_tags; drop table if exists companies; drop table if exists projects; drop table if exists tags; create table companies (id serial ...
0
votes
0answers
143 views

Postgres user credentials for copying WAL files to a network storage in Windows

I have 3 postgres server setup with streaming replication - 1 primary and 2 secondary servers, one intended as a failover server and another as DR. I want to setup WAL archiving on to a network ...
2
votes
2answers
107 views

Union tables by mask in PostgreSQL [closed]

In database I have many tables like with names: "t_2015_09_01_z15" "t_2015_09_02_z15" "t_2015_09_03_z15" "t_2015_09_04_z15" "t_2015_09_05_z15" ... "t_2015_10_01_z15" "t_2015_10_02_z15" "...
1
vote
1answer
96 views

Low cardinality fields: integer or string

A Ticket has the statuses: new in_progress on_hold closed I could make ticket.status a string (on_hold) or an unique int(2). It is indexed. Int pros: smallest size in index Int con: low clarity ...
1
vote
1answer
180 views

Where can I see the exact row count of a table in pgAdmin III?

Where can I see the exact row count of a table in pgAdmin III? I know where to find the approximate row count: I am also aware I can SELECT COUNT(*) FROM my_table. But I want to know whether the ...
0
votes
1answer
55 views

Why is this PostgreSQL query table scanning

I am running PostgreSQL 9.3. The database is of all of the medical NPIs (national provider numbers). I have created the following indexes: CREATE INDEX ...
4
votes
2answers
842 views

Why Django session table grows on PostgreSQL

I use PostgreSQL 9.1.6 via PgBouncer 1.4.2 in my Django 1.4.8 project. django_session table grows all the time. VACUUM does not help. VACUUM FULL runs for hours. Only TRUNCATE helps usually. The ...
3
votes
2answers
592 views

Optimise huge Postgres table for fast read

I'm about to create a Postgres table with 1.5 billion rows. The table will just have a single TEXT column. The table is effectively a blacklist. When a user of my software saves certain data, this ...
5
votes
1answer
112 views

Fastest query for distinct IDs in a many-to-many relationship

I have this table in PostgreSQL 9.4: CREATE TABLE user_operations( id SERIAL PRIMARY KEY, operation_id integer, user_id integer ) The table consists of ~1000-2000 different ...
1
vote
1answer
44 views

Create a quick report for a client

I have an Amazon RDS (postgres) database and an client asked me to have access to certain data stored in this database preferably in the form of a comma separated file. If the data changes, the client ...
0
votes
1answer
84 views

Improve execution time of query in POSTGRES with multiple joins

I'm having the trouble with the following query due to the growing size of the database ( websites is around a million rows, misspelled goes up to a couple millions ) It can reach up to 165 seconds ...
2
votes
1answer
165 views

Return type of JOINing stored procedure - PostgreSQL

Is it possible to express return type of stored procedure that returns joined tables using table type of one of joined tables? My stored procedure looks like this: CREATE or REPLACE FUNCTION ...
1
vote
1answer
43 views

Extract start and end per group of rows, where only the end can be identified

I have a table that looks like this. id date_from date_to description vehicle 1 2015-10-21 08:00 2015-10-21 10:00 GARAGE TO CLIENT 1 1 2 2015-10-21 12:00 ...
0
votes
1answer
41 views

What is the best way to create tables for email campaigns?

I have table email_sent with fields: id user_id template_name sent_at opened clicks And now I need to save data which was send for each email. I have several campaigns with differnt data. For ...
1
vote
1answer
50 views

What is the valid string representation of tstzrange[]?

I am trying to find string representation of tstzrange[] type (array of timestamp ranges): select '{[2014-01-01,2015-01-01]}'::tstzrange[]; select '{''[2014-01-01,2015-01-01]''}'::tstzrange[]; select ...
1
vote
0answers
114 views

PostgreSQL DML statements

I am trying to trick an application into executing a DML statement AFTER starting a SELECT statement without using stacked queries so I cannot use the ; character anywhere in my query. I have resorted ...
8
votes
1answer
8k views

Most efficient way of bulk deleting rows from postgres

I'm wondering what the most efficient way would be to delete large numbers of rows from PostgreSQL, this process would be part of a recurring task every day to bulk import data (a delta of insertions +...
3
votes
2answers
2k views

How to do a minor upgrade postgresql 9.3.0 windows to 9.3.1?

What is the recommended way to perform an upgrade from PostgreSQL 9.3.0 to 9.3.1 (minor upgrade) using the Enterprise DB built windows installer? Should I uninstall first or just install over the ...
4
votes
2answers
2k views

PostgreSQL unexplained table bloat

I have a table in a Postgres 8.2.15 database. The table bloated to almost 25GB but after running vacuum full and cluster the table size was dramatically smaller, well under 1GB. A few weeks later ...
0
votes
1answer
26 views

Assign value to a field after checking a condition

I have this query: ALTER TABLE MERCHANTS ADD COLUMN merchant_image bytea, ADD COLUMN merchant_logo bytea, ADD COLUMN merchant_address VARCHAR(100), ADD COLUMN merchant_phone VARCHAR(50), ...
0
votes
1answer
63 views

JSON array as text to join in Postgres?

I have a table t1 with a TEXT column (t1textcolumn) that looks like: [ {"id":"x.f","bar":""}, {"id":"f.foo","bar":""}, {"id":"f.bar","bar":""} ] This array's "id" elements have a relation to ...
1
vote
1answer
188 views

How to transform Streaming Replication Master-Slave to Cascaded Master-Slave1-Slave2 setup?

I've setup streaming replication as part of HA solution on my production enviroment. I've two ubuntu servers PROD1 and PROD2. PROD1 is master and PROD2 is slave. In case of failover the slave database ...
-2
votes
1answer
697 views

Error subquery uses ungrouped (columns correlation)

Recently I tried to create a query to obtain some statistical data, but I got this error: subquery uses ungrouped column "vegetacionpotencial.ident" from outer query select veg_pot , sum(...
0
votes
2answers
30 views

PostgreSQL: What is data replication?

I read from a post about comparing MySQL and PostgreSQL, there is one who said: The biggest disadvantage of PG for me had been the lack of replication, but it's recently gotten both asynchronous ...
1
vote
1answer
49 views

Postgres 9.3 copy data from another table with relation [closed]

Hi I have two tables in my database which is related, which is forms and qa_forms. forms ->id ->name -address -phone -calldatetime and then ` qa_forms ->id ->forms_id ->name ->...
8
votes
2answers
1k views

What fillfactor for caching table?

I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around ...
0
votes
1answer
37 views

Using default values in primary key

Good day. I have a table like this: CREATE TABLE mytable ( myname varchar(30), mydate date DEFAULT CURRENT_DATE, PRIMARY KEY(myname, mydate) ); EDIT: Forgot to mention I am using a ...
5
votes
2answers
3k views

PostgreSQL streaming replication broken: invalid magic number, out-of-sequence timeline ID

For some reason, my PostgreSQL slave no longer streaming-replicates changes on the master. It was working before, for a while, but I recently noticed that the slave's database contents is old, and ...
0
votes
1answer
114 views

postgresql - How to limit access to specific database only, and restrict access to system tables

I have a user test, which can view data from the system tables such as pg_class. The idea is to isolate him to have access to a specific database only and not have any access to system resources. The ...
1
vote
0answers
54 views

How to check the program/applicationname that triggered function?

There are two applications that used the same function (trigger) in postgresql. I have to ignore notification by applicatonname. How do I identify the current program/application that called the ...
1
vote
1answer
2k views

Upgrade Postgres 9.3 to 9.4 after Ubuntu 14.04 to 14.10 upgrade

I've just recently upgraded my Ubuntu install from 14.04 to 14.10. This automatically pushed Postgres 9.4 onto the system. I'm trying to migrate my cluster like this: sudo pg_dropcluster 9.4 main --...
1
vote
0answers
159 views

CREATE TEMPORARY TABLE, temp_buffers and Postgres performance

I have a bunch of queries that use temporary tables, and most of them are working really good, but from time to time they take an usual amount of time: 3-5 seconds. My temp_buffers is set to the ...
1
vote
2answers
106 views

Select query not giving result for inner query values

Here is my schema and data create table mytable (id numeric, val text); create table mytable1 (id1 numeric, id text); insert into mytable values (123, 'aaa'); insert into mytable values (124, 'bbb');...
0
votes
2answers
82 views

Elements inside a container: How to perform atomic operations on them?

In my DB (Postgres 9.4) I have the following items: Tshirts, Jeans, Shoes and Boxes. Tshirts, Jeans, Shoes can be placed inside a box and they can have a color (the color may be null). Each element ...
35
votes
3answers
40k views

Find highest level of a hierarchical field: with vs without CTEs

note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database. I ...
2
votes
2answers
261 views

How to convert numeric values to text in sql for select query

I have string values like this '[123, 124]' I could trim the value using below command select trim('[123, 124]', '[]'); --returning '123, 124' as text I want to pass the value above as select * ...
0
votes
0answers
35 views

Increasing system load due to multiple inserts every millisecond

In my project I need to do multiple database inserts every millisecond and thus Postgres takes huge amount of CPU. I need to find a way to reduce CPU load as I need to process about millions of rows. ...
1
vote
1answer
64 views

Understanding index fillfactor

I'm trying to understand the index FILLFACTOR parameter on a simple example. I have a table that is generally updated only once in ten days, but the update is quite heavy (requires 5-6 hours). Now, ...
1
vote
1answer
154 views

Cannot read from /tmp with PostgreSQL COPY, but able to read the same file from another directory with the exact same permissions

I have a file /tmp/foo.csv that looks like this: 1,2 10,20 100,200 I have the same exact file in another directory /mydata/foo.csv. Both directories and files have the same exact permissions, owner,...
1
vote
1answer
63 views

Scaling `ntile(N)` over a set where M < N

Env: Postgres 9.4.4 on Mac OS X Env: Postgres 9.3.6 on Heroku I have a "large" dataset (75k) containing multiple groups (30), and for each group I am generating box plot percentiles. In most ...
0
votes
1answer
59 views

When to use stored procedure / UDF? [closed]

I'm confused about usage of SP / UDF. Typically, it is also possible to write code in programs outside of database. So is there any general advice to decide when to use them?
3
votes
0answers
480 views

Restoring from pg_dump with foreign key constraints

In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored. An example: ERROR: insert or update on table "channelproducts" ...
0
votes
2answers
46 views

An SQL query to derive data from one to many rows based on another field

I have an issue I can't get my head around. This is the table: Table1 c1 c2 c3 1 A 2 A DD 3 A DD 4 B HH 5 B 6 B This is the ...
51
votes
2answers
45k views

Granting a user account permission to create databases in PostgreSQL

How do I give a user account in PostgreSQL the ability to create and drop databases? Is there a way to do this with GRANT?