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
0answers
51 views
How I can copy from local file to remote DB in PostgreSQL?
I am a novice in psql and need some help.
How can I load a local CSV to a remote DB?
I am using the following command
\COPY test(user_id, product_id, value)
FROM '/Users/testuser/test.tsv' ...
0
votes
1answer
24 views
Comparing two tables for a UUID change and fix it
I have two tables which I'm trying to reconcile the differences of in postgresql.
Table A is old and needs updating.
Table B is an updated, schema identical version of Table A which I have the data ...
1
vote
1answer
73 views
Postgresql querying trends
Firstly apologies if this is a duplicate, I am fairly new to SQL and so Im not sure what the correct terminology to use in my searches
So I have a database which records motor races, with the ...
0
votes
1answer
41 views
Set field values to newly imported rows in PostgreSQL table with existing data
I have a PostgreSQL table with existing data, and needs to import all the rows in a CSV file into that table. I am using pgadmin3's Import tool to do the import.
Question: For the newly imported ...
4
votes
2answers
129 views
Inner join using an array column
Having trouble indexing and executing a query in O (log n) time.
The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
2
votes
0answers
57 views
How to execute a non-table-locking update operation on PostgreSQL? [closed]
Looking for a good way to update the value on a column for all the rows in a database (not huge, but big enough - about 10M records), without locking the whole table, so operations can continue while ...
-4
votes
1answer
28 views
Postgresql output doubt [closed]
I am getting output and I have to press spacebar after one page of output, I want to get rid of that, how can I and which option I have to enable.
By automatically, how can I automatically execute ...
3
votes
1answer
99 views
Most efficient to prevent duplicates
I'm building an application that will fetch messages with JSON every once in a while and store these in a postgres db. Now I want to prevent duplicates, so I calculate the sha-512 of the message and ...
1
vote
1answer
16 views
Big jump in search time for Postgres Index query for results with high selectivity
I am doing some performance comparison of databases and lucene for full-text searching.
So I use Postgres to create an Index for the data to search:
CREATE INDEX bodies_index
ON bodies
USING gin
...
2
votes
1answer
76 views
Limiting number of results in a Partition using OVER(PARTITION BY)
In the following query, why is it that we have to limit the results returned from each Partition by using the clause WHERE foo.row_num < 3 outside of the subquery foo but not from within the ...
0
votes
1answer
44 views
Very fast replication
I used this guide to make replication between my two db servers : http://wiki.postgresql.org/wiki/Streaming_Replication
But, when I updated a row on master server it will be available in slave ...
0
votes
1answer
136 views
which postgres do i have installed? what directory needs to be in my path to run initdb?
I need to use postgresql with ruby on rails.
I have these directories
/Library/PostgreSQL
and
/usr/local/Cellar/postgresql/usr:local:Cellar:postgresql:9.2.2
This 2nd directory name is ...
0
votes
1answer
66 views
Getting error while importing large data through csv
I am new Postgres. I am getting constraint violation errors while importing a
large CSV file.
I want to disable all of the database constraints temporarily - I do not know how to do this.
0
votes
1answer
81 views
How to use array variable in query in PostgreSQL
Create table t1 ( xcheck varchar[], name text );
CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])
RETURNS record AS
DECLARE xrc as record;
execute 'select name from t1 where xcheck @> ...
1
vote
1answer
31 views
Can pg_hba.conf trust processes?
Is it possible to configure PostgreSQL such that it can trust a single process, instead of a specific user?
2
votes
2answers
78 views
Postgres: Use result of group by as column names
I have a messages table in a database, which include a sender id and a message type (and of course many more columns not relevant for this question). I try to create a query which counts how many ...
2
votes
1answer
36 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
378 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
68 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
59 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
27 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
81 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
36 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
56 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
101 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
58 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
65 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
44 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
52 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
119 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
111 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
66 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
76 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
106 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
113 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
84 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
131 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
142 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
63 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
86 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
126 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
101 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
124 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 ...
