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)

0
votes
1answer
44 views

How to relate to information_schema

Is it possible to create a foreign key that relates to information_schema? For an application implementation, I would like to store extra column information in another table, but I want to ensure ...
2
votes
1answer
65 views

Understanding the simple execution plan in PostgreSQL

I'm new to execution plans still trying to learn. My problem is for below query select * from item limit 3; I got following execution plan, "Limit (cost=0.00..0.07 rows=3 width=75)" ...
0
votes
1answer
61 views

Error when building documentation of Postgres [closed]

I am trying to build the documentation of Postgres on OSX After getting the source, installing node and jade, I do: cd /doc/src/sgml make html This results in the error: fs.js:684 return ...
2
votes
1answer
479 views

Fetch nested results as JSON PostgreSQL 9.2

Consider the following tables Device -------- id name type -------- components -------- id INT type VARCHAR -------- Manufacturers ------------- id INT name VARCHAR country VARCHAR ------------- ...
1
vote
1answer
222 views

PostgreSQL partition pruning?

In postgres partition is done by child tables. My problem are, when we issue a query to master table , does the query optimizer do partition pruning ? i.e select appropriate child table rather than ...
0
votes
1answer
95 views

Can PostgreSQL index calculations be multithreaded?

During a single query write of approximately 300,000 rows with 30 columns all indexed, I noticed that only one core was maxed at a time. Is there any way to calculate indexes multithreaded for single ...
-1
votes
1answer
88 views

Inserting null value without quotes postgresql

I'm creating a php application which relies on postgresql. Now i have this query: $query= 'INSERT INTO "users" ("name", "username", "email", "phone") VALUES (\''. $name .'\', \''. $username .'\', ...
0
votes
1answer
88 views

PostgreSQL: After restoring data, unable to use Views (permission denied)

I'm attempting to get a PostgreSQL environment running locally. I'm using 9.2.8, and have restored data from a backup made by the DBA of the production environment. The restoration worked fine ...
2
votes
1answer
26 views

Splitting metadata inside Postgresql

I have metadata in a postgresql table that I need to split. So far I have been attempting to use Select col_name from "tbl_name" split_part('QGC|WGPLNW|IR|BBC|FCPC|WGPLNW|ARME|QPC', ", 1 | 2 | 3 | ...
0
votes
0answers
433 views

huge size of /var/lib/postgresql/9.3/main/base/1 directory (postgresql)

i'm using postgresql 9.3.4 and real size of database is about 5.5 GB but POSTGRESQL fill disk about 256 GB !! is this POSTGRESQL Cache and what is method to clean thing issue ?
1
vote
3answers
1k views

What's the most efficient UUID column type

For storing a 128 bits UUID there are multiple storage options: a byte[16] column two bigint/long(64 bits) columns a CHAR(36) column - 32 hex digits + 4 dashes. a UUID database specific column, if ...
1
vote
2answers
497 views

What is a good solution for reliably transferring data between SQL Server and PostgreSQL? [closed]

In analyzing some of our business data, we're realizing that we need to join data across disparate data sources. For example, our application data is warehoused in Postgres (ported from MongoDB via ...
-1
votes
1answer
38 views

how to stop the execution after n-th continues query execution

Is there any way to limit the continues query execution in postgres? I have a program to run the queries (10000+). But I need know is there any way to stop (break) the execution after exactly 10000 ...
1
vote
0answers
128 views

Amazon Redshift VACUUM error

I'm trying to run vacuum delete only on a table with big amount of columns (800+) and 4 million rows. I know it's recommended to use deep copy instead, but still am wondering if running vacuum is ...
1
vote
1answer
50 views

Get database creation timestamp from PostgreSQL?

Is there a way to get a date and time of when a particular database was created in Postgresql? The \list command does not provide a timestamp.
2
votes
2answers
6k views

Most efficient way to add a serial column to a huge table

What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)? EDIT: I want the column to be incremented values for existing rows (NOT NULL). I didn't set a fillfactor ...
3
votes
1answer
2k views

Cannot get my slave - replication server to start

My replication server will not start. I have followed the instructions here: http://opensourcedbms.com/dbms/setup-replication-with-postgres-9-2-on-centos-6redhat-el6fedora/ As well as several other ...
0
votes
0answers
52 views

Why is PostgreSQL doing a seq scan on column != column?

Why is PostgreSQL doing a table scan on column != column? Isn't this query guaranteed to return no results? Isn't anything != anything guaranteed to be always false for any record on a not null ...
6
votes
2answers
852 views

Is this temp table behaviour documented?

Query Language (SQL) and PL/pgSQL functions treat temp tables differently: begin; create table foo(id) as values (1); select * from foo; /* id ---- 1 */ savepoint s; create function f() returns ...
0
votes
1answer
62 views

Keeping bank transactions in sync

I'm counting on your expertise in this one, I'm developing a scrapper for banking transactions history. My problem is, scrapping should be done regularly and the bank doesn't supply any unique ...
0
votes
0answers
94 views

Inner joins take too long

We have a query that takes about 8 minutes to execute if I want one day's scans, and we're going to need months'. It retrieves some columns which are used to determine to which bracket a scanned ...
4
votes
1answer
228 views

Optimal hash technique to index large text

Erwin Brandstetter thankfully saved me from myself when I tried to create unique indexes on textual columns with large lengths. The upper limit for the insertion rate is tens of billions of rows per ...
2
votes
1answer
337 views

Character varying index overhead & length limit

I have a unique constraint on a character varying column that will mostly have lengths of approximately 600 but could get to 10 million. I have read about B-Trees, but I can't determine how much disk ...
3
votes
2answers
1k views

PostgreSQL using count() to determine percentages (cast issues)

I'm trying to run the following query to provide the % of records in my patients table that have a value entered in the refinst field. I keep getting a result of 0. select (count (refinst) / (select ...
0
votes
1answer
84 views

What configuration to use for PGbouncer over a WAN link

We have two datacenters, one in America and one in Europe. For various reasons, the Europe datacenter has the database server, while the workers and web apps are in America. PGbouncer is in the ...
1
vote
0answers
94 views

Can I add a primary key automatically for all tables?

I have a database (postgresql) with 10 schema and every schema has 10 tables. For some reasons, some tables do not have a primary key. Is it possible to create a function/trigger that can look for ...
2
votes
1answer
107 views

File database vs application server database for concurrent performance & bulk data

I asked a question about Ripple's database implementation, and received this response: The ripple server uses SQLite for structured data and a configurable "back end" for unstructured "bulk" ...
1
vote
1answer
722 views

Secure postgresql database encryption

According to this answer, it's not possible to have a fully secure database encryption with pgcrypto. Now, 2 years later (in postgresql 9.3) is there any new extension or new method to have a ...
2
votes
1answer
658 views

Postgres database level encryption

I want to have the whole database to be encrypted, instead of encrypting the data before storing into database. Is it possible to encrypt the whole postgres database and what best method to use? ...
0
votes
1answer
46 views

PostgreSQL: How does the implicit index work when using “COPY” for loading bulk rows into a newly created table?

What I know for fast loading bulk rows into a new table is: Create the table(without creating the index) Use "COPY" to load data from a file into the table Create the index What if I added a ...
2
votes
0answers
45 views

Efficiently joining two irregular series representing changes

(This is a PostgreSQL-specific question, using version 9.1, but could be upgraded to 9.3.) I have a number of tables that represent a series of changes. These tables all have the same structure: ...
0
votes
1answer
352 views

Return a default value in group by if selected column is null

I am trying to run a group by statement which works fine however, one of the "group" is null. How do I return "Unknown" for that row? For example: 10000 A 9999 B 9990 C 4 etc. First ...
0
votes
1answer
768 views

changed password for postgres login in pgAdmin, now can't connect to server

I was trying to use pg_dump to script out a database and in the process I used pgAdmin to change the password for the postgres user. I realize (now) that changing that password was a bad idea. But now ...
0
votes
0answers
87 views

Performance issue in Postgresql9.3

I have very good configuration server dedicated only for PostgresSql. Server is 16 Core 122 GB RAM on Linux machine. select version(); **PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc ...
6
votes
1answer
223 views

How to dive into the PostgreSQL's optimizer and trace each step it takes to optimize the query?

Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Does PostgreSQL have the similar structure which records steps the ...
0
votes
0answers
28 views

use domains in multiple schemas

The PostgreSQL CREATE DOMAIN command appears to create the domain at the schema level. Is there a best approach I should be aware of that would allow me to reuse domains across the multiple schemas ...
1
vote
0answers
63 views

Guidelines on best indexing strategy for varying searches on 20+ columns

I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation. Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search. The problem: ...
3
votes
0answers
311 views

PostgreSQL custom operator for equality

I want to build a custom equality operator in PostgreSQL, which can be used in GROUP BY, UNION and DISTINCT [ON] for the json type (just for the sake of curiosity, not for real-world implementation -- ...
0
votes
0answers
56 views

Slow search times with functions despite index?

I'm aware that this question might be under the guidelines of being closed, but I'm out of ideas at the moment on how to optemize this table any further. Given a have a few billion records with ...
1
vote
0answers
523 views

How to improve query performance in PostgreSQL? [closed]

I have the following query to be execute within a second but getting time for execute 2.8 minutes: Query: Using Common Table Expression. explain analyze with temp_cte as ( Select ...
0
votes
1answer
173 views

How to get pg_archivecleanup on Amazon Linux 2014.03?

I have an Amazon EC2 instance based on Amazon Linux AMI 2014.03. I installed PostresSQL from their repos: yum install postgresql-libs postgresql postgresql-server postgresql-devel I've read about ...
0
votes
1answer
76 views

I need to copy data from remote Postgres server that has limited disk space and limited connectivity to a server

I have a remote Postgres server recording some data every second. the server has very less space like 6-7 GB, currently I write data to a remote VM server but due to poor connectivity the applications ...
2
votes
0answers
371 views

Postgresql trigger - get all changed rows after transaction

I have invoice_item table and invoice table. I need to check total price of Order after each transaction. Since I still want to be able to change things, I need trigger after each transaction. CREATE ...
0
votes
1answer
143 views

Issue working with Postgres on Windows

I am running PostgreSQL 9.3 on my machine. I have PhpPgAdmin running on a cloud, using which I exported the database I am supposed to be working on, selecting "Structure and Data" and "Download". ...
0
votes
0answers
33 views

General tuning in PostgreSQL [closed]

I need to do general tuning for PostgreSQL 9.3 version. I have postgresql configuration file with the following details: max_connections = 100 shared_buffers = 128MB effective_cache_size = 128MB ...
2
votes
0answers
116 views

How to make PostgreSQL default_tablespace work properly?

My question is, "Why isn't database 'screwy' created in tablespace 'screwy'? I made the following script, with it's output following, to show the problem I am having : #!/bin/bash # export ...
1
vote
0answers
24 views

Optimize finding the next batch of data from A to be crunched and inserted into B

I'm using PostgreSQL to store data produced by a large distributed computation. There are several stages to this computation; the idiom we've settled on is that there's a table containing the results ...
2
votes
2answers
97 views

Get count of processed queries

I have been searching a while to find a way to retrieve the amount of queries my postgres database is processing and responding to. I found various statistics about disk I/O, insert/update statistics ...
1
vote
1answer
44 views

Why changing the database owner doesn't change the owner in PostgreSQL dump?

I was exporting a database using pg_dump command when I realized that the owner of the database had to be changed. So I change it, and make extra sure that the newuser is correct. Then try the command ...
2
votes
1answer
474 views

Restoring a postgres8.3 backup with postgres8.4

I'm trying to restore a backup dumped from a postgres 8.3 server in a server with postgres 8.4 with this command: pg_restore --host host --port 5432 --username username --dbname my_db ...