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.

learn more… | top users | synonyms (1)

1
vote
1answer
100 views

EXECUTE within function not using index?

I have an accounts table with ~200k rows and an index for these columns: account_type_id BIGINT , member_id BIGINT , external_id VARCHAR(64) CREATE INDEX account_full_qualifiers_idx ON ...
0
votes
1answer
49 views

Database Security - Prevent column writing?

I am using a Postgres db. I am concerned if someone gets in my DB, they could change the price of my products to $0 and download my products. Am I able to prevent writing / editing of that column ...
2
votes
4answers
108 views

Trigger or Stored Procedure?

Let's say I have two tables, foo and bar: CREATE TABLE foo( foo_id serial NOT NULL, count integer NULL, PRIMARY KEY(foo_id) ); CREATE TABLE bar( bar_id serial NOT ...
7
votes
2answers
4k views

Should I use the PostgreSQL bit string?

I've been learning about the bit string data type lately, and I am quite curious about: At the bottom of this doc page there is the sentence: ... plus 5 or 8 bytes overhead depending on the ...
3
votes
1answer
122 views

Why postgres is using an odd index with no relation to the query

(Debian 7, Postgres 9.3, dedicated machine with huge cache) I have one big table called process_data (14gb) and another tiny lookup table called process_location. I'm doing a query between these two ...
1
vote
1answer
1k views

Postgresql doesn't accept replication connection

Plain old streaming replication. PostgreSQL: 9.2.7 Windows 8.1 64 bit My primary and secondary clusters are on same windows machine. I have already done pg_start_backup() and everything, so both ...
0
votes
1answer
58 views

Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?

My application has a Product and a Purchased table. The Purchased table includes a full copy of the Product row at time of purchase, so that, even if a product name or description was changed, the ...
2
votes
0answers
91 views

How can I avoid breaking Postgres sync replication when building a large index?

We have pairs of Postgres 9.2 servers using synchronous replication. Sometimes rebuilding an index on a large table will break replication due to WAL segments not being kept around long enough. We are ...
0
votes
1answer
191 views

Postgresql cluster over nfs

Can I create a HA postreges cluster using two different nodes via NFS? According to the postgresql 9.4 official doc this isn't a good idea but I don't find any other info on the web.
0
votes
1answer
93 views

PostgreSQL roles versus users, grant permissions

I'm currently learning the differences between PostgreSQL and MySQL as I've got a new project and I also intend to migrate my existing software from MySQL to PostgreSQL. I've actually started creating ...
7
votes
4answers
4k views

PostgreSQL: Creation date of a table

I've recently finished a project, during which many DB tables were created. Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables. Is there a ...
0
votes
0answers
38 views

Does Postgres shutdown gracefully when I restart my Mac?

When I restart my Mac OS X computer, I assume the daemon controlling Postgres (the "Postmaster" as I recall) is notified by the OS and performs a graceful shutdown of any running databases with ...
1
vote
1answer
120 views

encrypt data without performance issues?

I've googled around for the last 45 minutes or so and it seems like that I can't figure out what kind of options I got when it comes up to encryption in databases. Problem: I got a SaaS product and ...
1
vote
1answer
62 views

Are PostgreSQL dynamic arrays more wasteful than plain text?

Today, someone was helping me to make a function in PostgreSQL. I was wanting the function to return in arrays of 100+ geometries per two columns instead of delimited text to make it easier to ...
1
vote
1answer
610 views

How to migrate postgreSQL on EC2 instance to AWS RDS?

I have a large PostgreSQL DB (around 100GB) which is on EC2. I want to migrate it to RDS. How can I achieve this with minimal downtime?
0
votes
1answer
38 views

multi-column FKs with NULL values

I understand that in PostgreSQL if there is a multi-column FK from table B to table A and one or more of the FK columns in table B are nullable then the FK is still considered as "honored" for the ...
1
vote
1answer
45 views

Matching a value with every element of a set of values - Postgresql

I'm new in Postgres and I can't achieve what I'm trying to do. I have a list of films in a Films table ID_FILM FILM NAME 1 Terminator 2 Forrest Gump 3 Nueve reinas A ...
6
votes
2answers
604 views

PostgreSQL Complete Database Backup

I've been trying to figure out how I can completely backup my PostgreSQL database. I have not seen any resource that explains how to do a complete backup. I am aware of the pg_dump and pg_dumpall ...
0
votes
1answer
603 views

Query optimization for multiple joins postgres

ER Diagram Brief description The database to be used is postgres. The database might contain upto 2 million AccountHolder records. Since the AccountHolder table is accessed and updated frequently ...
2
votes
2answers
146 views

Call UPSERT function multiple times to different inputs

I have a function: merge_vehicles(vid, cid, vname, reg_no, name, name_1st) Can i call it multiple times on inputs like (2335, 55, '246BDH', '246BDH', '811', 1), (2336, 55, '038THX', '038THX', ...
1
vote
1answer
85 views

script for restore of postgres database localy from dump given on stdin - ambiguous redirect

Runing this command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -U foo some_db" runs perfectly fine. It returns on stdandard output the dump of db. Then I would like to restore new_db based on dump ...
2
votes
2answers
2k views

Check whether empty strings are present in character-type columns

I have an application that (as part of its logic) trims strings and replaces empty strings with NULL prior to insertion in the database. I guess one way to ensure that this is enforced would be to ...
-3
votes
1answer
129 views

Best design to handle multiple query keys [closed]

I am designing a postgresql database for an online card game. I want to provide users with the option to access their play history - users can see log of their games. The data I want to save has the ...
0
votes
0answers
74 views

Best way to handle multiple query keys

I am designing a postgresql database for an online card game. I want to provide users with the option to access their play history - users can see log of their games. The data I want to save has the ...
0
votes
0answers
473 views

pgpool-II Master Slave mode / Load Balance Mode / Watchdog On result many primary servers

I installed pgpool-II in Centos6.5, Master Slave mode / Load Balance Mode / Watchdog On but three postgresql servers are all primary server, shows as below: IP Address Port Status Weight ...
0
votes
1answer
365 views

Postgres JSON field for many to many relationships

I'm designing a schema that has a many-to-many relationship as such: A lead can have several stages and each stage can have several leads. I currently have three tables, with one of them as being a ...
0
votes
1answer
68 views

PostgreSQL: Why is the schema owner not the database owner

I've a simple question, yet I am unable to find the answer from the official documentation. When I create a database in PostgreSQL as a specific user, I'm the owner of all the objects under the ...
1
vote
1answer
178 views

Deadlock detected on CREATE TABLE in PostgreSQL

I'm creating multiple tables asynchronously when I get deadlock detected. Both tables (Table A & B) have FK to one table (Table C) and I get deadlock on relation to Table C. Scripts CREATE TABLE ...
2
votes
0answers
89 views

How to optimize update using join in postgresql

I'm using PostgreSQL 9.3.5 on a CentOS system. I have a very large table (twenty million rows, 50+ column) that aggregates data from a few hundred systems. Several times a day each of the systems ...
0
votes
0answers
37 views

Join does not not use index in PostgreSQL?

I have a statement that selects from two tables news and status. It seems that the index on the column status of the table news is not used. This table has about 70.000 rows and the query costs too ...
1
vote
0answers
100 views

Optimizing SELECT queries

I'm not very experienced with database query optimization. I've been reading through similar questions here and Postgres tuning articles online, but unfortunately I haven't had any luck. Here's what I ...
1
vote
1answer
234 views

TRUNCATE in transaction: dead lock

In a unittest we run this: BEGIN; TRUNCATE table1; TRUNCATE table2; ... UPDATE ...; ROLLBACK; Unfortunately this results in a dead lock if a cron job starts and this job operates on table1 and ...
1
vote
1answer
43 views

MySQL data to Postgres every X time

Our team (Uni students) have a project that we are updating. Old project team has a Live MySQL database. We want to make a archive/warehouse from it in PostgreSQL. My question is: How can we import ...
0
votes
1answer
37 views

For each record, count records dated earlier in PostgreSQL

I have a table that contains a column year. I need, for each row, calculate the number of entries where the year is less than that in the row. So that the output would look like: col1 | col2 | ...
0
votes
1answer
68 views

modeling data dimensions as columns - some dimensions are highly variable while others have low variability

Please excuse my question if it is a bad one - I am not a DBA... I would like to model the following data: Let's say that I have one column that is the row id as generated by a sequence. Let's also ...
0
votes
2answers
531 views

Is there data visualisation tool for postgresql which is capable of displaying inter schema relations as well?

Operating system used is linux. I have tried Navicat and SQL Power Architect. They did display relations between tables in the same schema. I have some foreign key constraints which reference tables ...
2
votes
1answer
6k views

PostgreSQL isn't listen on 5432 in Windows 7 [closed]

I have a PostgreSQL 9.3.1 DB Server running in Windows 7 Ultimate. I have checked the file postgresql.conf and this lines are uncommented: listen_addresses="*" port=5432 And this is the ...
2
votes
2answers
1k views

Setting up binary replication between two PostgreSQL instances

I have two virtual machines: dbrepa (-.175) and dbrepb (-.191). Both have PostgreSQL 9.2 installed; once PG was installed (never initialized) on dbrepa, dbrepb was made as a clone. I need to have the ...
1
vote
1answer
28 views

Is it possible to set a timeout for optimization in PostgreSQL?

In PostgreSQL is there a timeout when the optimizer stops the optimization process and returns the least expensive plan it has found so far? Is it possible to change its value?
1
vote
2answers
91 views

Equality in JOIN or WHERE clause? Efficiency and style

Of the two queries below, which is the most efficient for postgresql? And which has better style in terms of readability, etc. The difference is the placement of the statement doctor.type != ...
1
vote
0answers
83 views

Pgpool load-balancing vs. consistency

In what ways is consistency weakened when using pgpool-II load-balancing on Postgresql servers running streaming replication? Obviously with asynchronous replication you can insert a row on the ...
2
votes
1answer
68 views

Is there a way to make an OOo form for PostgreSQL updateable view?

I have a PostgreSQL database, with data from some 8 tables (not counting enums implemented as a table referenced by foreign key) summarized in a view. I made an INSTEAD OF trigger, so the view is in ...
2
votes
2answers
97 views

PostgreSQL and storing configs

The problem is to store and use global configs such as currency/date format etc. Since this functions are commonly called I'm concerned with the performance impact. Lets take currency as an example. ...
4
votes
2answers
2k views

PostgreSQL: schema diff/patch tool [closed]

Consider the following setting: A production DB A dev db, on which schema changes are made to enable new features When the development of a new feature is complete, I have to manually update the ...
2
votes
1answer
506 views

How to index a query `WHERE field IS NULL` (postgres)

So I basically have a table with lots of inserts, setting one of the fields (uploaded_at) to NULL. Then a periodic task selects all the tuples WHERE uploaded_at IS NULL, processes them and updates, ...
3
votes
2answers
796 views

Slow index scans in large table (PostgreSQL 9.2)

I'm having trouble with slow queries on a relatively large table (200+ million rows). I'm trying anything crazy, just add historic values. Below is the query and the query plan output. I sadly expect ...
0
votes
0answers
93 views

PostgreSQL buffer page size limitations - rebuild with larger size?

We just ran into a limit on indexing due to the 'index size cannot be more than 1/3 of page size (2712)' error. This indicates the buffer page size is 8192 (8k). This seems awfully small during the ...
5
votes
2answers
3k views

Postgresql function to create table

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this: CREATE OR ...
0
votes
1answer
29 views

Multiple queries and a report

im fairly new to databases. I have multiple query's that i normally run individually at the end of loading data to my table doing simple integrity checks like length ,char types and so on (see ...
4
votes
2answers
509 views

CTE works as expected, but not when wrapped into a function

--testing table CREATE TABLE public.test_patient_table ( entity_id INTEGER NOT NULL, site_held_at INTEGER NOT NULL, CONSTRAINT entityid_pk PRIMARY KEY ...