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
3answers
393 views

How to prevent a PostgreSQL trigger from being fired by another trigger?

I have 2 triggers on one table; one works for INSERTs : CREATE TRIGGER "get_user_name" AFTER INSERT ON "field_data" FOR EACH ROW EXECUTE PROCEDURE "add_info"(); This updates some values in the ...
1
vote
1answer
1k views

pgpool2 setup on master-slave replication using postgreql 9.1

I have already setup 2 ubuntu server 12.04 LTS on my local machine using virtual box and is using the master-slave replication (postgresql 9.1). 192.168.0.109 = master 192.168.0.110 = slave my ...
0
votes
1answer
46 views

Multiple select for single row insert

I need to insert a row into a table by selecting value from multiple tables. I want a row to be inserted from multiple tables. insert into tab(id,name,date) values ((select max(id)+1 from ...
2
votes
1answer
196 views

A good use case for Postgres JSONB?

I am looking to create an Asset Management System to track assets (laptops/desktops, monitors, keyboards, mice, bags, software keys, etc), basically anything that can be assigned to an employee. My ...
20
votes
2answers
19k views

Superuser is not permitted to login

I created a superuser portal with the following command create role portal with superuser password 'portal' When I tried to login into postgres with portal user I am getting the following error in ...
3
votes
1answer
132 views

pg_dump does not honor -n

I'm using pg_dump (9.2 database), to extract one schema and load this into another database (9.4). well, here's the command: pg_dump -f file.sql -F plain --inserts -n schema database Ok. My ...
10
votes
1answer
185 views

Why is query thousands of times slower with DESC?

I have a PostgreSQL database (AWS RDS) and ran the following queries in psql: livenet=> EXPLAIN ANALYZE SELECT * FROM outputs ORDER BY height LIMIT 10; Limit (cost=0.57..36.82 rows=10 ...
0
votes
2answers
38 views

PostgreSQL user only drop some tables

Is there a way to have a PostgreSQL user that can drop all but specified tables? We need the user to be able to drop some tables but not all. It would be preferred to be able to use something like ...
1
vote
0answers
60 views

Postgres search and replace

What is the difference between the search in search and replace, (the replace function) and full text search (tsvector) in Postgres? Or, put another way, can tsvector be used in search and replace ...
4
votes
2answers
246 views

GIN index not used when adding order clause

I'm trying to speed up a query that executes three ILIKE queries and reduces these with or (returning the overall count and 10 entries) SELECT *, count(*) OVER() as filtered_count FROM "users" ...
4
votes
1answer
152 views

pg_dump - schema not found

I am trying to dump a schema from Postgres 9.4, running on Cents 6.5, but I get an error: pg_dump: No matching schemas were found Here is the command I am running: pg_dump -U postgres -n ...
6
votes
1answer
145 views

PostgreSQL partial index unused when created on a table with existing data

In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: ...
5
votes
1answer
200 views

Time dimension or timestamp in fact table?

Which would you use, and why? A separate time dimension or putting a timestamp in a fact table? Or perhaps both? I am building a data warehouse, and need to represent the time of day that events ...
1
vote
1answer
69 views

Migrating from PostgreSQL to SQL Server

I have been tasked to migrate a database from PostgreSQL 9 to SQL Server 2008 R2, and I have found some issues I need to address. I have quite limited experiences with PostgreSQL I'm afraid. Most ...
4
votes
0answers
3k views

How to properly monitor the number of PostgreSQL database connections?

I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 ...
0
votes
1answer
24 views

Copy postgis layer from S3 to Heroku

I have a dump of a postgis layer (layer.dump), which I am trying to add to my heroku database (mydatabase). The postgis layer is stored on S3 (https://s3.amazonaws.com/layer.dump). I would like to add ...
1
vote
0answers
45 views

MATLAB® export double numbers into PostgreSQL by `datainsert` (of Database Toolbox)

How is it possible to pass double numers into numeric postgreSQL columns from MATLAB? Here the list of matlab data: var1 197x1 cell var2 197x1 double var3 197x2 double var4 197x2 double ...
1
vote
0answers
130 views

Generate tables from entities with hibernate

I'm using postgresql with a spring/hibernate to create a multi-tenant application where every company will have its own seperate schema and tables in the database. So when the super administrator ...
0
votes
0answers
25 views

postGIS dump size seems strange

I have this table on postGIS 2.1.5 (postgres 9.4.3). A pg_relation_size shows a size of 300MB. However when I try to dump the schema containing this table (and only this table), I end up with a 27GB ...
1
vote
3answers
79 views

Create database design with requirement

I has a small database structure with an Account and Person. I want apply the following requirement: Account have one Person Person do not have necessarily one Account (can be null) When I delete ...
0
votes
1answer
211 views

Restoring a database using Postgres data files instead of .sql files

I backed up my Ubuntu 14.04 system using rsync to a USB stick, but apparently it wasn't formatted for booting. The original disk has been erased and I put a fresh install of Ubuntu on it. In migrating ...
1
vote
0answers
47 views

3 tables joins performance issue

DB: Postgres 9.4 schema: table a: id, score table ab: a_id, b_id, b_type table b: id, name, type I am trying to get top 10 result in the following format: name, count(group by name), ...
0
votes
2answers
554 views

Lower() vs ilike on postgresql

What I want to do is to check if value of column equals some string, but case insensitive. Is there a performance difference when using ilike without wildcards (percents) and lower() ? I was planning ...
1
vote
1answer
44 views

PostgreSQL, how to extract all the possible couples of a primary key in a table?

I would like to find out a PostgreSQL command that reads the content of a table, and returns all the possible couplings of the primary key of the table. Something like this: input_table (ID is the ...
4
votes
2answers
120 views

Extremely slow query on indexed column

I'm getting a extremely slow query on an indexed column. Given the query SELECT * FROM orders WHERE shop_id = 3828 ORDER BY updated_at desc LIMIT 1 explain analyze returned: QUERY PLAN ...
3
votes
1answer
73 views

GROUP BY uninterrupted sequence of logs for same location

I'm having a hard time describing my problem or finding it via Google. I want to be able to group rows based on location and employee, but the group should be split if a row with the same location ...
2
votes
1answer
256 views

How to keep the sync active with Bucardo

I have a master-master sync setup between 3 nodes or 3 different host machines using bucardo. All works good. However when one of the database or a host is not reachable or goes down for some reason, ...
1
vote
0answers
68 views

Postgresql FDW oracle out of memory

I have a linux box with centos 6.7 and postgres 9.3.9. I have a foreign data wrapper throught an oracle 11.2 box and a jdbc driver. It's working fine since several months, but yesterday I had to ...
2
votes
1answer
759 views

Can I do VACUUM FULL to pg_largeobject table?

I have two tables (table1, table2) in a Postgres 9.1 database. Both having oid type. Each table 1 million records. And pg_largeobject table size is around 40GB. I have removed 0.9 million records from ...
-4
votes
1answer
2k views

No operator matches the given name and argument type(s). You might need to add explicit type casts [closed]

postgres=# select * from json_test; id | data ----+----------------------------------- 1 | {} 2 | {"a": 1} 3 | {"a": 2, "b":["C", "d"]} 4 | { "a":1, ...
3
votes
1answer
970 views

Postgres Count with different condition on the same query

EDIT Postgres 9.3 I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2 The current query is working fine which looks like this: Basically it is a 3 table ...
6
votes
1answer
1k views

Pros and cons of using many schemas in PostgreSQL as opposed to just one?

For a large SAAS application (backed by PostgreSql 9.4), with over 300,000 accounts (and growing), what are the pros and cons of using a schema per account to partition the data vs putting all the ...
0
votes
0answers
85 views

Multiple Tables as source and Target in Talend Open Studio

Loading data from mysql to postgres through Talend is working for single table loading. How to load whole db loading(multiple tables). can anyone tell me?
1
vote
2answers
78 views

Can I make postgres more verbosely tell me what it's doing?

I'm trying to use ALTER TABLE on a live db (not in production, just poking around at a dev box and trying to learn some things) Here is the command I'm trying: psql -h $IP -p 5432 -U $DBUSER -d ...
0
votes
0answers
155 views

Connection Failure for postgres at Talend Open Studio

I have tried to connect postgresql database in Talend open studio. After giving login, password, port and server, i have issued check. Its saying "Connection Failure. You must change the DB Setting". ...
0
votes
0answers
31 views

Deleted data from pg_authid

This is my first post on this site. I've recently by mistake deleted all data from pg_authid. As a result i couldn't log into database cluster (no user 'postgres'). I've managed to restore database ...
2
votes
0answers
26 views

postgresql: deleting from a view

In Postgresql 9.4, we're doing a view as a UNION of to tables A and B, and we'd like to have a single DELETED N ROWS message when we 'delete' the records in the underlying tables (via a a trigger on ...
3
votes
1answer
75 views

PostgreSQL table partitioning problem

I partitioned my table day by day. The child tables are look like points_20150830. Every day, I run a cron job to create table and I insert records without using any trigger function. I insert ...
0
votes
1answer
28 views

Restore Postgres Database on New Machine

Trying to restore a postgres database on a new Macbook Pro. I recreated the database using createdb dbname then tried to restore the old database using psql dbname < dbname_dump.bin but ended up ...
3
votes
1answer
80 views

Trigger function using current row and current table name as variables (final part)

Like detailed in my first question I have a Postgres 9.1 database with multiple tables that have the exact same column names, they only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... ...
3
votes
1answer
51 views

Is it safe to use set-returning sql functions instead of security-barrier views?

Since 9.2, it's been possible to use the security_barrier attribute to guard against maliciously-chosen functions and operators accessing data 'hidden' behind filters in views (full info in the ...
0
votes
1answer
51 views

PostgreSQL failed on Select

I am trying to use a result from a select for another select: DECLARE _port int; BEGIN select alter_port into _port from database where ....; select data inot data_list from database where ...
5
votes
2answers
105 views

Improve performance for order by with columns from many tables

Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two ...
2
votes
1answer
40 views

Distributing a semi-large key/value list

I have a semi-large (around 30 million records) key/value list which I want to load into redis. The data is stored in PostgreSQL (as master data) and we want to distribute this to clients for faster ...
1
vote
1answer
65 views

What kind of database should I use?

I'm designing a new system and I'm wondering what kind of DB should I use for my needs. I have a tree structure of nodes, where each node has historic values. For each historic value it should hold 2 ...
5
votes
1answer
634 views

Why is a gin index on a jsonb column slowing down my query and what I can do about it?

Initialize test data: CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE TABLE docs (data JSONB NOT NULL DEFAULT '{}'); -- generate 200k documents, ~half with type: "type1" and another half with type: ...
3
votes
1answer
254 views

How to determine whether a PostgreSQL Hot Standby is fully mirrored?

I have set up a hot standby of a PostgreSQL server. It all seems to be working, but I just want to be sure that I'm not missing something. In /var/lib/pgsql/9.2/data/pg_log/postgresql-Wed.log I have ...
5
votes
1answer
203 views

Managing and speeding up queries on PostgreSQL table with over 3 trillion rows

I have time series data which spans over 10 years and has over 3 trillion rows and 10 columns. At the moment I use a PCIe SSD with 128GB of RAM and I am finding that querying takes a significant ...
2
votes
0answers
113 views

Replication of specific tables using Postgres 9.4.4 and Amazon Web Services

I have currently setup a Postgres 9.4.4 RDS instance on Amazon Web Services. On this instance I hold 20 tables. In addition I also have Postgres 9.4.4 installed on my local server (OS: 64-bit Linux) ...
3
votes
2answers
181 views

Tuning Postgres for Single connection use? Or is postgres wrong tool?

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions? I'm a social ...