PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

1
vote
1answer
63 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 ------------- ...
0
votes
1answer
30 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
42 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
31 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
83 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
0answers
44 views

Advice to choose the best tool [closed]

We have some kind of a high-load system. Currently, we're using Postgres on RAM disk (you know all the pros and cons). There are around 10 millions of rows. There's only 1 external key, and these 2 ...
-1
votes
1answer
26 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
34 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 ...
3
votes
0answers
118 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
37 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 ...
1
vote
3answers
330 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
35 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
134 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
42 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 ...
2
votes
1answer
129 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
47 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
97 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 ...
0
votes
0answers
77 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 ...
2
votes
2answers
83 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
62 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
75 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
59 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
23 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
39 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
87 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
25 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
38 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
71 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
32 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 ...
3
votes
0answers
90 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
34 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
109 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 ...
1
vote
0answers
120 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 ...
-1
votes
1answer
37 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 20000 file reading and inserting into db. But the total count is 4 lakhs. When i try with 4 ...
0
votes
0answers
22 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
42 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
19 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
29 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
459 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
63 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". ...
1
vote
0answers
28 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
67 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
49 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
65 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 ...
1
vote
1answer
56 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.
0
votes
0answers
13 views

Creation of a table with foreign keys in postgres is taking longer time [duplicate]

I am wondering if someone can help me understand this. I tried to add an column to a table of 22000 rows which is referencing other tables(one of these tables have 0.5 million records). It didn't ...
1
vote
0answers
120 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 ...
1
vote
0answers
382 views

Mariadb new features vs PostgreSQL? [closed]

Mariadb 10 offer lot of new features. ( https://mariadb.com/products/mariadb/mariadb-vs-mysql) . I want to know, with these new features, will PostgreSQL would be a better solution for newly starting ...
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
90 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: ...