PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.
2
votes
1answer
22 views
Creating crosstab() pivot table in PostgreSQL 9.0
I have a rather complicated issue for myself which I hope someone can help me with.
I want to use PostgreSQL to enable me to create pivot tables from a geometry-less table.
To keep it simple I will ...
3
votes
1answer
19 views
How to properly implement compound greatest-n filtering
Yep, more greatest-n-per-group questions.
Given the a table releases with the following columns:
id | primary key |
volume | double precision |
chapter ...
2
votes
3answers
136 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 ...
2
votes
1answer
62 views
PostgreSQL query very slow when subquery added
I have a relatively simple query on a table with 1.5M rows:
SELECT mtid FROM publication
WHERE mtid IN (9762715) OR last_modifier=21321
LIMIT 5000;
EXPLAIN ANALYZE output:
Limit ...
1
vote
1answer
592 views
Connect to PostgreSQL via a ssh tunnel using Cygwin
I would like to connect to a Postgres server installed on a remote server using an ssh tunnel.
I have Windows so I use cygwin for SSH.
I do the following on cygwin:
# Create the tunnel
ssh -f ...
2
votes
1answer
182 views
Unsupported languages in Postgres Full text search
My web application supports 3 languages, English, Traditional Chinese, Vietnamese. I understand that Traditional Chinese and Vietnamese is not supported in Postgres' Full Text Search.
If I were to go ...
3
votes
0answers
15 views
Preventing Infinite Trigger Loops in PostgreSQL
I am working with two PostgreSQL databases (one internal and one on our company Web server) where I am attempting to share data between the two instances through the use of triggers. The databases ...
2
votes
1answer
4k views
Efficient way to insert/update/delete table records from complex query in Postgres 9.x
I have this function which returns a set a records and I need to persist those records into a table. I have to do it hundred times a day.
My initial approach was just clear data from my table and ...
2
votes
0answers
19 views
Slow fulltext search for terms with high occurence
I have a table that contains data that is extracted from text documents. The data is stored in a column called CONTENT for which I have created this index using GIN:
CREATE INDEX "File_contentIndex"
...
0
votes
0answers
17 views
EXCEPT it exits in 2 other tables [on hold]
I need to get all values from table product , EXCEPT they exits in 2 other table products. Is this query acceptable using 2 EXCEPTS ? Should this be done differently .
CREATE table missing_values
AS
...
0
votes
1answer
837 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
0answers
10 views
write_location and flush_location of pg_stat_replication view
I'm confused about the write_location and flush_location of pg_stat_replication view . The document say, write_location stands for last transaction log position written to disk by this standby server ...
0
votes
1answer
22 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 ...
1
vote
1answer
31 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 ...
14
votes
2answers
11k 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 ...
2
votes
1answer
140 views
Should database be denormalized if I need an index across multiple tables
I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like:
SELECT * FROM song s
JOIN album a ON a.id = s.album_id
WHERE LOWER(CONCAT_WS(' ', ...
2
votes
1answer
43 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 ...
0
votes
1answer
109 views
How to set weights on a Postgres TSVECTOR column?
Currently, I'm using Postgres 9.3.5 for full-text search but I don't want to set weight or convert to tsvector on the fly (a lot of articles suggest that, and that just seems like a terrible idea to ...
1
vote
1answer
168 views
Replication Lag on Postgres AWS RDS Service
We have a single master/single streaming replica Postgres 9.3 db on AWS. The load is not terribly high - this is a development/staging environment. (The production shows similar metrics). Point is the ...
5
votes
3answers
73 views
Conditional Foreign Key Relationship
I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables. Here's the hierachy of tables, this is done via FK ...
7
votes
1answer
165 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 ...
1
vote
0answers
29 views
Form builder schema for SQL database?
There are many ways one can envision the solution, from the top of my head:
Two table approach:
Field table with (name, field_name, type, description, meta) attributes
Form table with (name, ...
2
votes
1answer
37 views
How to create and populate a PostgreSQL table from CSV automatically?
I am new to database management and we are using PostgreSQL. All I need to do is to migrate CSV files (corresponding to around 200 tables) to our database. Manually creating tables for every CSVfile ...
0
votes
2answers
23 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
22 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 ...
1
vote
1answer
1k views
How to make pg_dump skip extension?
This is on 9.3 but I can remember simillar things happening since 7.x. So I create database and install plpgsql extension into it. Later on I create a pg_dump and before restoring it into databse I ...
-2
votes
0answers
28 views
Store items with statistic data with ability to select daterange and sorting [on hold]
So I have DB with items tables. Let say it is domains and pages. It grows over time like 5000 objects a day now for each table. Each item has some stats numbers: a1, a2, a3.
I need to aggregate stats ...
3
votes
2answers
48 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"
...
2
votes
1answer
40 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 ...
5
votes
1answer
89 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: ...
2
votes
1answer
385 views
REST webservice as a Foreign Data Wrapper in postgresql
I know it's possible to embed data from a foreign resource in a postgresql database as a foreign table and have seen examples including redis, another postgres database, etc but I can't really find ...
5
votes
1answer
66 views
ANALYZE strategy for big tables in PostgreSQL
In our PostgreSQL 9.4.4 database we have a table that receives around 600k new records each day. Each day, nightly, we are performing some ETL exports from the table. If it has not been analyzed ...
1
vote
0answers
18 views
PostgreSQL WAL recovery not working due to missing WAL
I am using pgBarman for PITR recovery. Now it's a bit of a crisis, because the Barman backup and the WAL archive seems like it cannot be recovered. We're running PostgreSQL 9.3.6 and latest stable ...
4
votes
1answer
44 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
34 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 ...
2
votes
1answer
201 views
Postgres query plans and tuning parameters
I have a query for which the explain plan indicates that the cost for using a seq scan is lower than the cost for using an index that I have set up. However the actual time for the query is 20 times ...
3
votes
1answer
35 views
Do fixed-width rows improve PostgreSQL read performance?
I have a table articles:
Table "articles"
Column | Type | Modifiers | ...
2
votes
1answer
2k views
PgPool 2 with HA Proxy
So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for.
E.g.:
PostgreSQL ...
3
votes
0answers
2k 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
10 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 ...
6
votes
1answer
5k views
PostgreSQL High Availability/Scalability using HAProxy and PGBouncer
I have multiple PostgreSQL servers for a web application.
Typically one master and multiple slaves in hot standby mode (asynchronous streaming replication).
I use PGBouncer for connection pooling: ...
1
vote
0answers
12 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
5 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
15 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
69 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
126 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
36 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
154 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
34 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 ...
3
votes
2answers
81 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
...
