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
6 views
Trying to load a file into a database on virtual machine
I have set up a Postgres db on a linux vm and have been having no issues using a GUI to connect to it. However, I am trying to load a large, 32GB, file onto it and so am skeptical of the old way I was ...
0
votes
1answer
16 views
dm_exec_query_stats in PostgreSQL
I recently started using PostgreSQL after using SQL Server for a while. Is there something like dm_exec_query_stats?
I haven't been able to find anything other than logging every long running query. ...
2
votes
1answer
11 views
postgres: Database replica with some schemas writable
I have two computers. Computer A is dedicated to collecting data and storing it in its local database. Computer B is for analysis. A is running flat out just with the data collection, so I want to ...
0
votes
2answers
18 views
no pg_hba.conf entry for host “10.1.1.91”, user “user” database “testing”, SSL off
I've come across different versions of this same issue on stackoverflow and on this forum. I've tried the recommendations posted on these forums but I am still unable to connect to a database. I've ...
1
vote
0answers
31 views
I am not able to create/reindex a unique index in a table
I have a table that contains user locations (called public.user_location) and is the following:
Column | Type | Modifiers
id | ...
0
votes
2answers
38 views
Calculating average with AVG for a list of numbers
This is probably a newbie question (bear with me) but how can I calculate the average for a list of numbers using SQL and the AVG function? I mean, I can add together numbers in SQL
like:
SELECT ...
1
vote
2answers
84 views
What is faster, one big query or many small queries?
I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then on the application some times, we only ...
1
vote
1answer
24 views
postgresql 9.3: two instances on same port and two different ip adresses don't work
i'm setting up multiple instances of postgres 9.3 on the same machine (centos 7). I have 2 virtual ip addresses, one instance is bound to first address and second is bound to second one. Same ...
2
votes
1answer
26 views
Delay Trigger Execution
Let me explain the scenario first before asking the question.
I have a database with a set of tables. One table, say Table A, contains username details, and other table, say Table B, contains user ...
1
vote
0answers
12 views
pg_basebackup could not get WAL end position from server
I'm trying to pg_basebackup my database. I followed instructions in PG Mauals and set it up properly (check on other server and database), but my backup isn't ending - on end of progress it's ...
0
votes
1answer
18 views
using string concatenation to reference a table
For a database systems project which tracks entities histories, I stumbled acros the following problem.
Assume we have the table "entity"
CREATE TABLE entity (
eID SERIAL PRIMARY KEY,
eName ...
0
votes
1answer
16 views
How to trigger newlines in log messages?
Is it possible for log messages to span multiple lines? I am expecting:
RAISE WARNING 'Line 1\nLine 2';
to result in the following log:
Line 1
Line 2
But instead I get:
Line 1\nLine 2
Any ...
2
votes
2answers
39 views
Trying to set up new data_directory on Postgres 9.3
Ubuntu 14.04, recently installed Postgres 9.3.
Trying to set up a new data_directory to an attached, larger hard drive (/data), and I changed the postgresql.conf file to reflect that.
However, I get ...
0
votes
1answer
26 views
Choosing a RULE instead of a TRIGGER
I my current database schema, there are hundreds of tables that reference another single table (called transaction_entry), that stores generic information about each row (when it was created, last ...
0
votes
1answer
23 views
Can I use pg_restore to restore data to subset of tables?
I am trying to restore data in to select tables of a database (9.3.5). This is a data refresh from production to test, while maintaining some table data in the test database (like application users ...
0
votes
1answer
35 views
Returning value of insert
I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value. This was done fine in ...
0
votes
1answer
26 views
Escaping delimiter in postgresql
I am trying to import data into postgres. Data fields are comma separated, strings may contain commas, in which case those are escaped:
Data structure:
create table v (a varchar);
Data file:
bus
...
1
vote
1answer
22 views
postgresql 9.1 failed insert occupies increasing disk space
I have a postgresql 9.1 installation where the available disk space is very low (20Gb) however there is only one client that inserts data in various tables (typically one entry every few minutes) and ...
2
votes
1answer
18 views
trigger can't access column loop
SSCCE:
The following script:
$ cat test.sql
CREATE TABLE public.foo (
loop INTEGER
);
CREATE OR REPLACE FUNCTION public.foo_fun(loop INTEGER) RETURNS BOOLEAN AS $$
SELECT TRUE;
$$ LANGUAGE SQL;
...
1
vote
1answer
31 views
why is the name `loop` treated differently in PostgreSQL
I issue the following DDL:
CREATE TABLE foo
(
loopa INTEGER,
loop INTEGER
);
Then, examining the DDL in the server (using a client-side tool like DbVisualizer - sorry don't know the query to ...
0
votes
0answers
15 views
produce a view from the output of a with query
I would like to produce a view based on few tables.
So far I did this:
WITH keyword_group AS(
SELECT
g.id AS group_id
,array_agg(pk.keyword_id) AS keyword_ids
FROM group g
...
1
vote
1answer
24 views
Writing a view that caches to another table
I have a table market_trades with columns timestamp, market_id, and amount:
api_production=# \d market_trades;
Table "public.market_trades"
Column | ...
-2
votes
0answers
21 views
Schedule daily snapshot task
I want to create a daily snapshot table based on records from product table.
I want to run the following query every day at certain time:
INSERT INTO ProductSnapshot
SELECT NewSnapshotId(), ...
0
votes
1answer
25 views
Postgres get rows where a field stopps growing
I have a table which has the following structure
id SERIAL PRIMARY KEY,
device_id INT REFERENCES(another_table),
uptime TIMESTAMP
Every device is pinged every N minutes.
The application logic and ...
0
votes
1answer
29 views
PostgreSQL migrate data from one table to another (which inherits the first one)
I have a table "company" and another named "school" ("school" table inherits "company").
Most of the data is community generated and sometimes a cleaning is needed.
I would like to be able to ...
0
votes
1answer
15 views
Postgresql multi-column GIN index [duplicate]
Suppose a postgresql table items like this. (keywords column is of type text[])
name account_id keywords
------------------------------
foo1 1 ['k1', 'k2']
foo2 1 ...
0
votes
1answer
22 views
Backing entire Postgres database server up with pg_basebackup
I back Postgres DB 9.1 up using following command:
pg_basebackup -h 127.0.0.1 -U replicationuser -D /storage/test_backup -Ft -z -P
In postgresql.conf I have this set:
wal_level = hot_standby ...
0
votes
0answers
31 views
updating previous row with current value
select
min(hh.id) AS id,
he.name_related as name,
hh.date_from as date_from,
hh.date_to as date_to,
hh.name as reason,
sum(hh.number_of_days) as no_of_days,
dy.a as remove,
...
0
votes
1answer
6 views
Postgresql warning: type reference X converted to Y (SQL State: 00000 - Error Code: 0)
When I execute:
CREATE VIEW foreign_keys AS
SELECT
tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
...
0
votes
2answers
34 views
Need to group by column while ignoring some text format
I have the following data.
Sport
--------------
Baseball
Football
Baseball [a]
Football [a] [b]
Basketball
How can I group this to ignore the data in the brackets?
0
votes
1answer
13 views
Does PostgreSQL `CLUSTER` command also cluster extended storage?
I'm trying to improve the locality of my data by using the CLUSTER command to periodically cluster records that share the same user_id. My most important data is in extended storage. Will extended ...
1
vote
1answer
30 views
PostgreSQL db volume at 100%. Can I create a tablespace on another volume so I can vacuum full?
I have run out of space, because I am new to Postgres and I have just not been monitoring it properly.
Now I cannot even vacuum the tables:
ERROR: could not extend file "base/20851/11787": No space ...
0
votes
4answers
76 views
Temporary schema per connection?
I am trying to migrate my unit tests from H2 to Postgresql.
Currently, H2 gives me an in-memory schema such that each connection maps to a unique schema, creates the tables, runs the test, and drops ...
0
votes
2answers
44 views
Does Postgresql support different transaction isolation per connection?
I just discovered that H2 doesn't support concurrent connections using different transaction isolation levels. Meaning, changing the transaction isolation of one connection affects all other ...
0
votes
1answer
15 views
Is there a benefit to using FOR SHARE in this PostgreSql simple select?
In the following PgSql stored function:
CREATE OR REPLACE FUNCTION get_offer_from_id(
offer_id bigint)
RETURNS json AS $$
DECLARE
res "entity_data_result";
r "get_offer_data";
...
3
votes
2answers
41 views
idiom to treat empty result set as zero
This is obviously a SSCCE.
I have an inventory table that holds the number of items (numOfItems) in a warehouse at any given date (inventoryDate). Both are integers to keep things simple:
CREATE ...
0
votes
1answer
24 views
FDW, replication and CREATE SERVER - will it work?
We have two servers (master, slave) and we want to use the FDW. The two databases to be connected with the FDW are both on the master (and the slave of course). When we are to CREATE SERVER we would ...
2
votes
1answer
32 views
restoring Postgres database: pg_restore -vs- just using psql
I am dumping my Postgres database using pg_dump (in plain-text format) and then restore it simply by using psql (with the -f option).
Which begs the question: am I missing anything by not using ...
0
votes
2answers
69 views
sum of double precision gives weird results
I've been making a sum of a column containing about 500.000 records of double precision numbers. All numbers in the database should normally be of two ciphers behind the period. However when making ...
0
votes
3answers
90 views
get row count of last select query
I am using a scheduled script which exports the (new) rows of the PostgreSQL database into a textfile. There are also several instances of this script running for several DBs:
COPY (SELECT ... FROM ...
0
votes
1answer
31 views
Run and Save multiple SELECT statements
Within a Postgres function I would like to run a number of PSQL SELECT commands. For each SELECT command I would like to output written to separate text files.
What is the best way to implement ...
0
votes
0answers
32 views
Suppression of statement in Postgresql logging
Given the following SQL function:
CREATE OR REPLACE FUNCTION log_a_notice() RETURNS void AS
$BODY$
BEGIN
RAISE NOTICE 'This message is coming from log_a_notice()';
END;
$BODY$
LANGUAGE plpgsql ...
3
votes
1answer
54 views
Multi-row insert vs multiple single row inserts
In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app.
However, I was curious, is there any other advantages? For example, if ...
1
vote
1answer
29 views
Can a PostgreSQL Trigger be used to ignore an insert?
I've got a situation where there are dozens of various triggers in our database that trigger a record being inserted into another (single) table within the database.
I was hoping that while testing ...
-1
votes
1answer
19 views
Permission denied when importing a database
This is my first time here and i have a question.
I am working with postgresql on windows 8 for the first time.
I am trying to import a database into postgresql with the command line.
Type in:
psql ...
0
votes
1answer
16 views
Using PostgreSQL both with (not only) SQL Workbench - how to apply alternate delimiters
I have created a PostgreSQL database. Now I try to work with it in SQL Workbench, but I have a problem with the way it understands statement delimiters. The documentation on alternate delimiters sais ...
0
votes
1answer
29 views
Why use RDS over EC2 for Postgres?
I have a database currently running on EC2. As I have to move it to a bigger machine the question of using RDS came up.
Pricing:
I can get an EC2 c3.large instance with two SSDs (16GB each), two ...
2
votes
2answers
47 views
Slow fulltext search due to wildly innacurate row estimates
Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB.
The database schema is ...
1
vote
1answer
20 views
Permission for sequence in another schema
Postgres 9.3
Debian 7.0
I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same ...
0
votes
0answers
16 views
fetch datas from PostgreSQL database [migrated]
I have created two tables in a PostgreSQL database named doctor and department. I have used dept_id as foreign key in the doctor table. Now I have listed the departments in Eclipse but when I'm ...
