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)

0
votes
1answer
22 views

How do I select the value with SERIAL data type in postgres

I wanna ask how to select coloum that has SERIAL data type. I already try to do that but the output is like this 'Resource id #3', and not just '3' I use SERIAL so I dont have to input ID like when ...
0
votes
1answer
27 views

Selecting 3MB (or more/less) data from database server

I installed PostgreSQL Server on my Windows 7 machine. I created database and a simple table, and populated the table. Now I want to connect through the network with my Postgres server and select a ...
0
votes
0answers
34 views

Will Amazon RDS for Postgres be as convenient as using Heroku Postgres?

I'm currently running a Heroku Rails app and our Postgres DB has grown to a data size of 50GB, and our DB cache hit rate is at 93%. We're on the legacy Ronin ($200/month) plan (1.7GB) and probably ...
0
votes
1answer
35 views

Will removing a foreign key constraint affect performance?

I have a units table and a reports table. A unit has many reports. So reports table has a foreign key unit_id which corresponds to the id of the unit. Hence, there is a foreign key constraint in ...
0
votes
1answer
37 views

Postgres : Executing SELECT within a transaction does not return most recent rows

Given a table layout that looks like this inv_items ========= * id bigserial sku character varying(22) name character varying(32) ... inv_items_stock =============== item_id bigint (inv_items) qty ...
3
votes
0answers
34 views

Postgres multidisk master replicating to single disk slave

I have an odd situation. I have a postgreSQL database master with multiple large tablespaces divided across 3 physical disks. I am setting up a slave that will be used to offload long, ...
0
votes
1answer
44 views

PostgreSQL: Creating view of multiple tables in function

I want to create view of multiple table in function using PostgreSQL 9.3 version. Example: I have following tables in the table name "AllTables": table1, table2,table3 And I have following ...
1
vote
1answer
34 views

How to insert into view through GUI in pgAdmin?

I have some updateable views for inserting the data. The reason is not just "security layer", inserting into some 10 tables directly would be a pain without the view with INSTEAD OF trigger. In ...
1
vote
0answers
31 views

How many Maximum Number of database can be created in single instance of PostgreSQL? [closed]

How many Maximum Number of database can be created in single instance of PostgreSQL 9.x?
2
votes
1answer
33 views

Create a new table (multiple columns) by selecting random values from an existing table

I am stuck on the following task (PostgreSQL 9.3). Let's say we have the following table1 (which has 10k rows): table1: id 754 800 330 4 59 My goal is to create another table2 with two columns ...
1
vote
1answer
32 views

What will GRANT <privilege> ON ALL TABLES IN SCHEMA <scheme> TO <role> do?

It seems obvious that PostgreSQL will GRANT some privileges ON ALL TABLES IN some schema TO some role when I'll command. But, will it make it permanent for the schema that on all tables within it, ...
2
votes
1answer
28 views

PostgreSQL Automatic Failover (Trigger File Creation)?

Is there a way to detect if the master PostgreSQL (9.3) server becomes unresponsive, and if so, create the trigger file (that is specified in recovery.conf) that initiates the failover process? Or ...
0
votes
1answer
30 views

Can PostgreSQL results include the query?

I run the following command to run 2 queries in a sql file. Could be 1, could be 50 queries. Depends on the deployment. psql -U <username> -h <servername> -d <databasename> -f ...
1
vote
1answer
24 views

Postgres Update, limit 1

I have a postgres database which contains details on clusters of servers, such as server status (active, standby, etc). Active servers at any time may need to fail over to a standby, and I don't care ...
0
votes
1answer
44 views

Postgres table growing on massive updates

I have an issue with my postgres database, I'm running massive update queries (1000 per second) to a single table (with 3000 entries) and I can see that the size of that table is growing and growing ...
0
votes
1answer
28 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
27 views

How to grant drop table/function privileges to a role in PostgreSQL?

I want to grant drop privileges on all tables and functions (not only those owned by the user) in certain schema of a specific database to a specific role. However, GRANT ALL PRIVILEGES is not enough ...
2
votes
1answer
45 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)" ...
1
vote
1answer
75 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 ...
1
vote
1answer
38 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
22 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
28 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
22 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
42 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
43 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
22 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
23 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
82 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
34 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
245 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
33 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
45 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
38 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 ...
1
vote
0answers
29 views

PostgreSQL clarification [migrated]

I have written a function inside PostgreSQL which has the following code: for (i = 0; i < 4; i++) { Datum dat_value = CStringGetDatum(inp->str[0][i]); values[i] = datumCopy(dat_value, ...
2
votes
1answer
109 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
40 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
60 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
72 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
36 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
49 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
65 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
21 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
16 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
37 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
21 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
33 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
60 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
27 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
57 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 -- ...