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 ...
0
votes
1answer
5 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
0answers
11 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
8 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
17 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
0answers
22 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 ...
2
votes
0answers
13 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
30 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
19 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
26 views
How many Maximum Number of database can be created in single instance of PostgreSQL? [on hold]
How many Maximum Number of database can be created in single instance of PostgreSQL 9.x?
1
vote
1answer
10 views
PSQL Create a new table (multiple columns) by selecting random values of 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
22 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
12 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
29 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
15 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
32 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
22 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
21 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
35 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
57 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
0answers
11 views
Is there easy way to connect postgres mysql and neo4j in one hosting database [on hold]
I was Creating a Website where user can host pages blogs and such while perfoming ,this task
i found some difficuties when i was trying to create user id in different task with a single click ...
1
vote
1answer
25 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
15 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 ...
0
votes
1answer
20 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
12 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 ...
-1
votes
0answers
19 views
inserting from one table to another with one column in common in postgres db [on hold]
i have the following table
tab1(sy_id,col1,col2,col3)
tab2(sy_id,c1)
only sy_id is common
now i want to insert the values of tab1 sy_id and c1 in tab2
corresponding to each value of sy_id ...
0
votes
0answers
19 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
35 views
Advice to choose the best tool [on hold]
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 ...
0
votes
0answers
21 views
Amazon RDS, RedShift and Data Warehousing
Is Amazon RDS for Postgresql a viable choice for a data warehouse?
I currently have a data warehouse environment running under Postgres 9.2 and am looking at cloud offerings.
I considered Redshift, ...
-1
votes
1answer
21 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
12 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
35 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
29 views
Error when building documentation of Postgres
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
184 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
27 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
21 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
31 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
95 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
37 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
42 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
67 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
25 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
35 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
46 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
12 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
13 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
36 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
18 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
18 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
29 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:
...
