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
0answers
67 views

Alternative to CASE

What alternatives are there to replace the use of CASE, and will also improve the speed of the query. SELECT created_at, COUNT(CASE WHEN cp > 0 THEN 1 END)::int4 rk, COUNT((CASE WHEN ...
0
votes
1answer
22 views

Can I create a unique constraint across schema in Postgres?

I have a task that I am trying to accomplish where the email address is unique. The email address is stored in the a3.user table as user_name. CREATE UNIQUE INDEX ix_user_user_name ON a3."user" ...
0
votes
0answers
50 views

Why “0 pages are entirely empty” after mass delete + vacuum?

I have a huge table for logging the user actions (audit log) in my PostgreSQL 9.4 database. It gets about 100 new records per second. I've removed very old records recently. I assume this affected a ...
1
vote
1answer
62 views

Improve the speed of a BETWEEN search

I have a query witch is selecting some data between two dates: SELECT * FROM rk.p_reports WHERE project_id = 1 AND engine_id = 1 AND type_id = 1 AND created_at::date BETWEEN now()::date - 14 AND ...
0
votes
1answer
52 views

mysql to postgresql, migrate files directly ( longblob > pg_largeobject )

I'm working on a python(2.6.6.) script and I'm evaluating what approach to take to migrate files from mysql-server to PostgreSQL . I've read the following posts: How to insert (file) data into a ...
0
votes
1answer
17 views

How does the correlation statistic parameter affect the cost of index scan?

I have the table customers having customerid SERIAL as a primary key. Initially I had the following statistic for that column: attname n_distinct correlation customerid -1 ...
2
votes
2answers
59 views

Performance issue during count Postresql 9.4 - Creating index on pivot table key…?

Disclaimer: I am new to Postgres, I need some advice... I have a huge table, with full of poker hands.(will contain 20M records, right now 2M records in it) aggregated_hands I have a pivot table, ...
0
votes
1answer
21 views

Postgresql 9.4: How to find out the stack level of nested function

Here are the two cases: Function fA fetches some rows and returns a table Function fB calls function fA and returns a table Within fA, how could I know whether it's been called from the program or ...
1
vote
1answer
41 views

How do I in “Navicat for PostgreSQL” run a simple PL/pgSQL script like I would in MS SQL Server Management Studio?

I don't want a function or anything. I just want to run/test a simple script with some PL/pgSQL features in it. Something like the following: IF 1=1 THEN SELECT concat('Hello', 'World'); END IF; ...
0
votes
0answers
106 views

pgAgent not working on linux (Ubuntu 14.04)

I am running Postgres 9.3 on Ubuntu 14.04 and I am unable to get pgAgent daemon/service to run. I have confirmed that the daemon is not running using service --status-all. When trying to run ...
0
votes
1answer
22 views

Cumulative data and dates - my joins aren't working

So I want to get cumulative data, for every day since a start date until an end date (or today). I have constructed a query that will get the data from a table, but for some reason, some dates are ...
1
vote
1answer
71 views

“Merge” two rows in a Postgres table, with foreign keys

I am keeping a database of books I've read, using the following two tables in PostgreSQL: CREATE TABLE authors ( id SERIAL PRIMARY KEY, name text ); CREATE TABLE books ( id SERIAL ...
1
vote
1answer
59 views

How do I index two table having a one-to-many relation but only the latter is being frequently updated?

Take the following two tables: table A has ~100k rows and 50 columns, and it is being updated only twice per month, how long this update takes is not an issue (as long as it doesn't take more than ...
1
vote
1answer
65 views

Is it possible to obtain number of prepared statements a connection holds?

I have a heavy loaded application that holds connections persistently (it creates them on start and never releases). During the lifetime it creates and (re)uses prepared statements, which are stored ...
0
votes
1answer
36 views

How simplify/optimize a search of keywords in two tables at the same time?

I'm trying to model a function which will be used by a search engine (of cars for the example). The last version of PostgreSQL will be installed on a Windows server (I don't know server's ...
0
votes
1answer
37 views

Postgresql: Optimize finding all distinct JSON keys from a colulmn

I'm looking for a way to index / optimize finding only the keys in a JSONB column. The use case is an ETL operation where I need to take data stored in the JSONB column and output it as delimited ...
1
vote
0answers
113 views

Configure HAProxy to load balance Postgresql

I'm trying to load balance between servers running Postgresql (Master-Slave configuration with Streaming Replication). First I've tried setting up PgPool-II, and it worked fine when I connected some ...
4
votes
1answer
81 views

Order result by count of common array elements

Using Postgres 9.4, I'm interested in having an array of integers like user_ids_who_like and provide an array of users (like user_ids_i_am_following) to sort that intersection. Something like: ...
0
votes
0answers
41 views

Simple/Easy PostgreSQL HA multi-master for high latency / unreliable network

I have an environment consisting of two servers running JBOSS and PostgreSQL. Each server writes to its own database and programmaticly synchronises some data to the other database. This causes ...
0
votes
0answers
27 views

ERROR: duplicate key value violates unique constraint “repl_stat”

Error messages constantly showing up in log files and I don't know what to do with them: 2015-11-26 11:08:05 MSK ERROR: duplicate key value violates unique constraint "repl_stat" 2015-11-26 11:08:05 ...
4
votes
2answers
133 views

Duplicate row with Primary Key in PostgreSQL

Assume I have a table as follows named people, where id is a Primary Key: +-----------+---------+---------+ | id | fname | lname | | (integer) | (text) | (text) | ...
0
votes
2answers
36 views

Combining two similar queries into one (PostgreSQL)

I have 2 queries like this: SELECT c.id FROM component c, base_vee bv WHERE c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199) AND c.id = ...
0
votes
1answer
48 views

Pass tablename as sql script argument

I'm trying to automatize a bunch of postgreSQL process, namely to use PostGIS and pgRouting extensions, so for that purpose I created a sql script file that I could run on command line passing in an ...
1
vote
0answers
64 views

Zero downtime copy/restore of a PostgreSQL database

I have a production server with static (no deletes/updates) PostgreSQL database, serving a huge amount of read queries. Once a week or two I have to update my database with new data. Right now the ...
0
votes
0answers
55 views

A “best practice” Postgresql function template for ETL processes

I use SQL Server in my day job, and have a personal project to build a star schema data warehouse in Postgres 9.4 on Ubuntu 14.04 to extend my knowledge (it'll be approximately 300GB uncompressed). ...
0
votes
1answer
22 views

postgresql trigger unexpected behaviour by many copies of inserted row

On insert in tbl1, the trigger I am working on, copies rows in tbl2 with some updates. Trigger: create or replace function myprocedure() returns trigger as begin IF (TG_OP = 'INSERT')THEN insert ...
1
vote
1answer
28 views

Adding new user in Postgres

I am using AWS RDS PostgreSQL and currently up and running. I would like to know how I can add a new user but only grant SELECT on an existing DB. The way I connect to my RDS from my PC is by using a ...
1
vote
0answers
48 views

Connecting Tableau and PgPool

I'm trying to connect Tableau to our database Postgresql via PgPool, for load Balance. I have Postgresql configured with Streaming Replication between one master and two slaves. And have another ...
1
vote
1answer
54 views

Discover missing foreign keys and/or indexes

We have a pretty big database (around 700GB of data), which has been inherited to us, and has had several DBAs. With time we've realized that there are several missing foreign keys and indexes. I'd ...
1
vote
1answer
30 views

how to do a count on a grouped by value in Postgres 9.4

I have a list of orders and would like to output a list by day of when they were created at of (1) total orders and (2)orders that have a fulfillment_status of 'paid' id | fulfillment_status | ...
0
votes
1answer
40 views

RHEL Postgresql 9.4 and PostGIS

I'm a dba new to postgresql. I have Linux RedHat 7 without openshift. I have to install some DB on 9.4 version with postgis. My sysadmin has published the following repositories : RHEL 7 server ...
0
votes
0answers
169 views

Execute Multiple Queries in a single transaction using Npgsql - C#

I have a Web System using C#.NET + NancyFX and PostgreSQL as my database, and in some functions I have to make insertion in more than one table. For example just to make it clear: I have two tables: ...
0
votes
1answer
70 views

Microsoft SQL Server -> Postgres Data Migration

I have data in a Microsoft SQL Server that needs to be migrated to Postgres. The problem is that the SQL Server gets continuous updates and is hosted on our local net, while the Postgres instance is ...
2
votes
2answers
57 views

What parts make up pg_database_size?

I'm trying to write a munin plugin to graph DB sizes. Alongside using pg_database_size I want to graph the components thereof as well. So far, I've come up with the following: SELECT ...
1
vote
1answer
38 views

Understanding rows count computation

PostgreSQL 9.4 I have a table called customers which has a column income integer. After runnig ANALYZE against it I got the following statistic for the column: most_common_vals ...
0
votes
1answer
30 views

User cannot create schema in PostgresSQL database

I'm trying to setup a "deployment" user which can create and alter tables on an existing database in addition to selecting, updating, inserting and deleting records. Here is what I've tried so far: ...
3
votes
1answer
76 views

For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE

I have a typical tree structure stored as an adjacency list in PostgreSQL 9.4: gear_category ( id INTEGER PRIMARY KEY, name TEXT, parent_id INTEGER ); As well as a list of items ...
0
votes
0answers
42 views

psycopg2 - drawbacks of reusing cursors?

I am new to Python and the psycopg2 module. context Working on a script that select data from MySQL, perform some operations (conversion of data-types and other "transformations"), and finally ...
5
votes
1answer
176 views

Restrict Postgres roles by IP address outside of the pg_hba.conf file

I am currently adding Postgres support to a deployment tool that currently only supports MySQL. This tool dynamically provisions and configures a database, among other things. In MySQL, we can ...
1
vote
1answer
36 views

Get triggers/table names in PostgreSQL

How can I get a listing of all triggers for a database along w/ the table that it belongs to? I assume that I need pg_trigger and pg_tables but I'm not quite sure how to join them together.
0
votes
0answers
32 views

Which would be a recommended approach for migrating a Postgresql database from one server to another?

We are moving out of our current dataserver based on Santiago, Chile to Amazon (AWS) in North Carolina, all other servers have been successfully migrated with no problem, but our "migration consultant ...
1
vote
1answer
46 views

Join external data in SELECT query

I have few (~20) names of products corresponding to an integer id that needs to be joined on products while exporting products data (using COPY (<query>) TO). The burden is that these names do ...
0
votes
0answers
18 views

What command should I use to make my void function display a paticular table using a variable from the function in the where clause?

I basically have a void function that creates a tuple on a existing table. Now at the end of the function I want display the table with the updated tuple. I am running it problems when trying to do ...
0
votes
2answers
69 views

SELECT fields from table having some conditions

I have two tables 1. Named as t1 2. Named as t2 example of the tables are : t1 : id name 1 one 2 two 3 three t2 : id name 2 five I need to select all the data ...
0
votes
1answer
18 views

First field from my return table is being eaten up

Another problem with some PostgreSQL stored procedure code... Same from this question: I have this function drop function uptimes(inicio date, fim date); create or replace function uptimes(inicio ...
0
votes
1answer
50 views

postgres window functions: using CASE to partition by age group

I am using postgres window functions to get a list of users taking part in a competition and their corresponding ranks based on a number of columns, all good so far....now I need to get rank based on ...
0
votes
0answers
11 views

PostgreSQL dump import only imprts tables and not data

I'm trying to import a postgreSQL dump into my DB. The dump is saved in a .sql file. I use the following command : psql -U postgres -d dhris < dbDump.sql and the table was created before. The ...
1
vote
0answers
52 views

Which indexes should I create?

I need assistance in which indexes to create on a table, and the best approach for querying it. We use PostgreSQL. Here’s the table: create table myt ( the_time timestamp, oid int, ...
0
votes
1answer
57 views

See entire string from a lengthy text field in pgAdmin?

Using pgAdmin, how can I see the entire string value for a row when the value is very long? For example, the value may be comma-separated values (CSV) content with hundreds or thousands of ...
0
votes
1answer
89 views

Simple select query taking too long

I have been having a hard time with this query, I have a webpage which is supposed to run this query a dozen of times, and with an execution time up to 40 - 50 seconds per query, you can see how this ...