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 ...
2
votes
1answer
34 views
What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?
Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this?
SELECT id FROM table
WHERE table.int_col > 1;
I'm specifically interested in ...
5
votes
1answer
334 views
Using MongoDB and PostgreSQL together
My current project is essentially a run of the mill document management system.
That said, there are some wrinkles (surprise, surprise). While some of
the wrinkles are fairly specific to the ...
4
votes
1answer
65 views
How does PostgreSQL physically order new records on disk (after a cluster on primary key)?
Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
-1
votes
1answer
61 views
Passing argument in trigger dynamically
CREATE TRIGGER audit_proc_tr
AFTER INSERT OR UPDATE OR DELETE
ON "log".hi
FOR EACH ROW
EXECUTE PROCEDURE "log".audit_proc(argument);
CREATE OR REPLACE FUNCTION ...
2
votes
0answers
32 views
Postgres caching and bytea
I have a DB which, among other things, stores images (as bytea, if that's interesting). It also stores users' metadata, activity logs, etc., but images currently take 500MB out of the total 600MB ...
0
votes
0answers
58 views
REST webservice as a Foreign Data Wrapper in postgresql
I know it's possible to embed data from a foreign resource in a postgresql database as a foreign table and have seen examples including redis, another postgres database, etc but I can't really find ...
1
vote
0answers
25 views
MySQL Federated acces to PostgreSQL table
For example 2 DBases:
DBMS Postgresql
DBMS MySQL
In each DB include one DB and one table in it. Can I work with table in PostgreSQL through MySQL?
Is it possible to link as federated postgresql ...
2
votes
1answer
72 views
How to make postgresql SUM to be more accurate on large amount of floating point data?
I'm trying to SUM 8 million floating point (REAL) values with simple query like this:
SELECT SUM(metric) FROM metrics;
However, it returns very inaccurate result.
It should return 137,586.77, but ...
0
votes
0answers
33 views
PostgreSQL : Londiste issue
I am new to Londiste， And I want to install Londiste, accroding the
Londiste Tutorial after I intall skytool (http://pgfoundry.org/frs/?group_id=1000206) , I can not find the pgqadm.py script ， ...
1
vote
0answers
35 views
Inheritance and its alternatives in PostgreSQL
I make an archaeological database in PostgreSQL 9.2. The description of archaeological context has a subdivision between different types of context. I'm not sure how to implement it:
1) to violate ...
2
votes
2answers
52 views
Postgres RIGHT JOIN with custom array
I'm using Postgres 9.1 and want to get a result with some blanks where there is no data. My query looks like the following:
SELECT institution_id FROM ... WHERE institution_id IN (1, 3, 4, 5, 7, 9)
...
0
votes
1answer
87 views
Error: set_valued function called in context that cannot accept a set. What is it about?
I use Postgresql 9.1, with ubuntu 12.04.
Inspired by Craig's answer to my question Concatenation of setof type or setof record I thought I would go well with using return query, setof record, and a ...
0
votes
1answer
53 views
How to turn the foreign key constraints on and off while db restore in postgres?
After using below the backup cmd in a db with 250+ tables:
pg_dump -h localhost -p portNum -U userName -f fileName -o dbName
I tried restoring it with the following command:
psql -h localhost -p ...
1
vote
1answer
60 views
Concatenation of setof type or setof record
I use Postgresql 9.1 with Ubuntu 12.04.
In a plpgsql function I try to concatenate setof type returned from another function.
the type pair_id_value in question is created with create type ...
1
vote
1answer
41 views
Postgres continuous backups - should WAL numbers be sequential after pg_start_backup call?
I'm using PostgreSQL WAL archiving and pg_start_backup as outlined in the Continuous Archiving section of the Postgres manual to backup a large database. Twice a month we do a full backup and every ...
2
votes
1answer
51 views
How to access the number of rows accumulated by RETURNs in PL/pgSQL
When performing
RETURN QUERY ...
in a PL/pgSQL function, is it possible afterwards to directly access the number of rows accumulated into the pile of records, which are returned when the function ...
1
vote
1answer
109 views
Monitoring real-time statistics on PostgreSQL
I need to monitor the DB performance on a distributed system with multiple databases. Some of the database servers use pgbouncer to pool connections and in those servers I can connect to pgbouncer and ...
4
votes
2answers
104 views
Custom unique column constraint, only enforced if one column has a specific value
Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter).
If type is "true", then ...
0
votes
1answer
25 views
Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?
In this SO question about stored procedures in plpgsql, the stored procedure look like sth.name(). I don't know what is the meaning of the prefix sth.
For example:
create or replace function ...
1
vote
1answer
63 views
Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure
While looking at several examples of pl/python and pl/pgsql, I have seen many - but not all - using volatile cost.
ie:
CREATE OR REPLACE FUNCTION my_function()
RETURNS setof record AS
$BODY$
-- ...
2
votes
1answer
72 views
PostgreSQL CREATE EXTENSION file not found error on Mac OS
On PostgreSQL 9.2.3 when trying this:
CREATE EXTENSION btree_gist;
I get this error:
ERROR: could not open extension control file "/opt/local/share/postgresql92/extension/btree_gist.control": ...
1
vote
1answer
103 views
Postgresql query speed
This is my first time seriously attempting to get into the guts of the speed of a query on Postgres, and I was hoping for some more explanation of exactly what is going on. I am using Postgres v 9.1 ...
2
votes
1answer
107 views
PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
In PostgreSQL 9.2.3 I am trying to create this simplified table:
CREATE TABLE test (
user_id INTEGER,
startend TSTZRANGE,
EXCLUDE USING gist (user_id WITH =, startend WITH &&)
);
...
0
votes
1answer
75 views
Cannot `create function` in plpython3u, permission denied
As postgres user, I have create extension plpython3u; in my database
then I have set the plpython3u to trusted: select lanpltrusted from pg_language where lanname like 'plpython3u'; returns true
but ...
0
votes
0answers
119 views
Achieve PostgreSQL hstore functionality in MySQL
Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table.
I don't know if this feature is available out of the box in MySQL like ...
2
votes
2answers
135 views
postgres backup / restore: restored database much smaller?
I am worried that my restored database is very different from the original:
#check size of postgres database
postgres@db1:/tmp$ psql -c "select pg_size_pretty(pg_database_size('test_db'));"
...
0
votes
1answer
58 views
Cannot use python3 as stored procedure language in posgresql database
I want to have python3 in my postgresql database for writing stored procedures.
Being in the psql client, when I enter the command create extension plpython3u I get the error:
couldn't open ...
3
votes
0answers
23 views
Does avg_query for PgBouncer include update and inserts?
Trying to tune our application and would like to know if the stats data that pgBouncer shows as avg_query, according to the PgBouncer docs on usage:
avg_query
Average query duration in ...
1
vote
2answers
83 views
Recursive query using plpgsql
I'm trying to write a plpgsql function that recursively returns a set of columns from records in a tree structure.
I have a data table and a table to link the data together:
DATATABLE
-----------
id ...
3
votes
2answers
125 views
pessimistic locking and client death
We are considering pessimistic locking for our project (SELECT ... FOR UPDATE); as we are used to optimistic locking, we are afraid about the operation of the server being blocked on such a lock (lock ...
1
vote
1answer
32 views
Log statements from within triggers in Postgresql
I have several sql statements running in some of my plpgsql triggers. I've enabled logging with the log_statement = 'all'. It logs the 'triggering statement', but not the statements within the query ...
2
votes
1answer
96 views
Will table inheritance in PostgreSql suit this situation?
Let's start off by saying, I'm new to PSQL and coming from a MSSQL background. I'm starting to design a DB in PSQL.
So my main aim is to have a Main schema in this DB, which I am going to use to ...
3
votes
1answer
38 views
PostgresSQL: Get single attribute of UDT in SELECT statement
I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work.
Creation of type ...
0
votes
2answers
114 views
A stored procedure to bulk load in Postgres or Oracle given a URL
Is there a stored procedure available in Postgres or Oracle that does a bulk load
given a URL, as in:
url_bulkload csvFile.ctl
Where csvFile.ctl is
#
# csvFile.ctl -- Control file to load CSV ...
3
votes
1answer
67 views
Avoiding repetition without creating a view
Suppose that I have a query Q1 and I need to run a query like the following:
Q1
union
select *
from (some query that uses Q1 outcome)
I would like to do that:
Without creating any view
Without ...
0
votes
1answer
53 views
Create log file only for my executed queries
I have been using the following to set up the log file:
log_statement='all',
log_collector='on',
log_destination='on',
log_filename='filename'
After that, for testing I just executed a query ...
2
votes
1answer
67 views
How to Model / Quickly test for set membership in SQL?
Each row in a table needs to be protected a list of application specific roles lets say something that looks like this example below
Desired Table Structure
pkey | data | roles
...
1
vote
1answer
44 views
clusterdb stopped working for PostgreSQL database in Ubuntu 12.04 server
After a fresh PostgreSQL install (version 9.2) on my Ubuntu server 12.04, clusterdb utility stopped working. I used the same config files as I used with version 9.1.
Below is the actual command:
...
1
vote
1answer
145 views
Very slow DELETE in PostgreSQL, workaround?
I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
2
votes
1answer
111 views
PostgreSQL 9.2: vacuum returning disk space to operating system
Vacuum usually does not return disk space to operating system, except some special cases.
From the docs:
The standard form of VACUUM removes dead row versions in tables and indexes and marks the ...
1
vote
1answer
66 views
Create database on new partition
I use postgresql 9.1, on ubuntu 12.04. I had installed the depot package.
I have added a new partition to my system, and I would like to create a postgresql database on this partition ( which will be ...
0
votes
0answers
31 views
Create Log file for insert,update and delete records and retrive the log details
How to create/update log file for each modifications(insert,update,delete) in my database. i cant find any feasible methods to achieve so. can anybody know how to implement this.? if so then please ...
0
votes
1answer
49 views
Retrieving all PK and FK with pgadmin
I have a big database that I need to extract all primary keys and foreign keys from each table.
I have pgAdmin III.
Is there a way to do this automatically and not go over each table manually?
2
votes
3answers
68 views
get account activity in postgresql
I have a old DB with bunch of accounts. One of them is used for sure but for 5 of them or so there is no information.
Is there any info in the system table in Postgresql when was the last login or ...
1
vote
4answers
61 views
How to determine if a postgres database needs to be vaccumed?
Given a PostgreSQL database what command can be used to determine if the database needs to vacuumed?
UPDATE I am working with client who is evaluating postgres but I am not a DBA, I just happen to ...
6
votes
1answer
84 views
What is a good way to determine the performance I should expect from a PostgreSQL server?
Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
0
votes
1answer
109 views
Postgresql: Difference between pg_dump and backup/restore?
Don't they essentially do the same thing by storing all the data in a file and dumping it into another database? Is it that pg_dump just adds the data on to whatever is already there and restore ...
6
votes
6answers
390 views
Find “n” consecutive free numbers from table
I have some table with numbers like this (status is either FREE or ASSIGNED)
id_set number status
-----------------------
1 000001 ASSIGNED
1 000002 FREE
1 000003 ...
1
vote
0answers
76 views
SOLVED: Postgres 8.4.5: Bad query plan vs Good query plan when changing date range on large data table
SOLVED.
I was able to resolve this issue by altering the time.date column to allow 1000 stats: ALTER TABLE time ALTER COLUMN date SET STATISTICS 1000;
Now the queries are running very fast, down to ...
2
votes
2answers
93 views
Permission denied in Postgres
I added the user myuserto Postgres.
Then, through the PgAdminIII GUI I added the database mydatabase and I restored from a backup file. So, the owner of mydatabase is the "administator" user ...
