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
1answer
185 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 ...
0
votes
0answers
1k 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 ?
-2
votes
1answer
49 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
244 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 ...
4
votes
0answers
7k views

psql: FATAL: sorry, too many clients already

I am suddenly getting this error when either trying to access the website that uses the postgresql database, or even when using the psql utility or pgadmin3. My database is set to handle 150 maximum ...
0
votes
1answer
85 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 ...
3
votes
3answers
3k 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
1answer
97 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.
1
vote
1answer
2k views

How to set bytea_output to hex?

I cannot seem to change bytea_output to hex. I have tried this method: ALTER DATABASE dbname SET bytea_output TO 'hex'; I have tried to edit postgresql.conf bytea_output = 'hex' and restarted ...
0
votes
0answers
63 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 ...
4
votes
1answer
464 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 ...
0
votes
1answer
102 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 ...
2
votes
1answer
706 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 ...
1
vote
0answers
33 views

periodic data move from local server to remote server

I have a local PostgreSQL server that is recording data on a solid state drive of less than 8 GB free space on a Windows 7 embedded 32 bit machine. In order to make a backup and store large data I ...
0
votes
0answers
118 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 ...
3
votes
2answers
4k 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 ...
2
votes
1answer
227 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
0answers
164 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 ...
0
votes
1answer
950 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
99 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
48 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
2k 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
33 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
114 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: ...
0
votes
0answers
98 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 ...
0
votes
0answers
78 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 ...
4
votes
0answers
516 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
1answer
120 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
552 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 ...
-2
votes
1answer
263 views

Maximum limit for COPY FROM STDIN in postgres

May I know what is the maximum number of records that can be inserted. In my program, it works for around 20,000 file reading and inserting into db. But the total count is 400,000. When I try with ...
0
votes
0answers
36 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 ...
3
votes
1answer
181 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
27 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 ...
1
vote
1answer
69 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 ...
5
votes
4answers
3k views

could not save history to file “/var/lib/postgresql/.psql_history”: No such file or directory

I'm following this tutorial to set up my postgresql db. http://inventtheweb.blogspot.ca/2012/05/install-postgresql-for-django.html After I finish running the CREATE DATABASE command and run \q I ...
0
votes
1answer
229 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". ...
3
votes
1answer
118 views

Problems while building plpgsql_check on Windows

I try to build plpgsql_check extension by @PavelStehule (Erwin Brandstetter recommended it to me in this answer) on Windows XP 32bit with Visual Studio 2010 (I used this article as a manual). However, ...
0
votes
1answer
321 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 ...
1
vote
1answer
116 views

Postgres resource management

I'm consolidating multiple Linux boxes running Postgres into one box. Given the way Postgres handles resource utilization, would one single server service handling all the databases provide a much ...
4
votes
2answers
204 views

Update SQL n:n table with multiple associations

I have done this before, but before I mindlessly repeat something that I consider as a hack, I'm asking here. I have 3 tables (details left out for clarity) : inv_items id bigserial (PK) sku ...
2
votes
1answer
283 views

Does PostgreSQL support multi-threaded replication?

Is PostgreSQL replication single-threaded ? Are there any tools to achieve multi-threaded replication ? I'm asking this since Mariadb support this and currently I'm learning both these dbs.
2
votes
0answers
563 views

PostgreSQL continuous archiving - use snapshot for base backup on AWS?

I'm new to AWS and PostgreSQL admin and have been poring over the docs trying to come up with a good backup strategy for a fairly simple setup. I have a single Amazon Web Services EC2 instance that ...
0
votes
0answers
17 views

setting auto-increment value of primary key to maximum in postgre sql [duplicate]

the sequencing of my auto incremented id value which is also primary key of the table has changed due to some unknown reason. i want to know whether i can reset this value to the maximum value by ...
2
votes
1answer
1k views

Difference between pg_column_size(table.*) and pg_column_size(table.col1) + pg_column_size (table.col2)

From PG DOC pg_column_size(any) : number of bytes used to store a particular value (possibly compressed) pg_column_size shows the space used to store any individual data value Example: ...
1
vote
2answers
940 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 ...
0
votes
1answer
77 views

Postgresql Function Trying to Insert into 1 row instead of 2

Postgresql 9.3, Mac OS X 10.6.7 I created this function and I would like to insert both results into the same row. Obviously the way I have it inserts into 2 rows. First, here is the Table T1: ...
7
votes
4answers
369 views

Store millions of rows of denomalized data or some SQL magic?

My DBA experience doesn't go much further than simple storage + retrieval of CMS style data - so this may be a silly question, I don't know! I have a problem whereby I need to lookup or calculate ...
1
vote
2answers
55 views

Can I find errors without using testing data for all the branches of each function?

In PostgreSQL, syntax errors (except for trivial syntax errors) are reported while running the code in question; unlike Oracle, PostgreSQL can't find errors without running the part of the code. This ...
5
votes
1answer
1k views

Optimizing concurrent updates in Postgres

I'm running concurrent Postgres queries like this: UPDATE foo SET bar = bar + 1 WHERE baz = 1234 Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which ...
0
votes
1answer
295 views

How to start my 9.2 data directory using an 8.4 server?

I have an old version of postgres (8.4.13) that doesn't run for various reasons. I plan to move the database files in there to a new install of postgres 9.3. How would I go about doing this? For one I ...