All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
0
votes
1answer
21 views
Postgis radius search
I have a table setup with the following columns, and it containts about 100K listing of data.
Column: address | Type: Text
Column: geo_point | Type: geometry(Point,4326)
My question is for ...
0
votes
0answers
13 views
PostgreSQL - Error: duplicate key value violates unique constraint [duplicate]
I try to insert a record into following table:
CREATE TABLE web_structure(
id serial NOT NULL,
label character varying(255),
name character varying(255) NOT NULL,
frompage integer,
topage ...
0
votes
1answer
19 views
cross database table references
I try to create table GroupArticle in database CmsGroup, one column ArticleId references another table in another database? is this possible and correct syntax?
CmsArticle
CREATE DATABASE ...
2
votes
2answers
28 views
Unable to force drop Postgres database
I'm trying to forcibly drop a PostgreSQL 9.4.4 database from a Bash script using psql:
#!/bin/bash
db="mydb"
psql -d $db -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = ...
0
votes
1answer
22 views
How can I drop relations in a PostgreSQL database without foreign key check?
I need to create and delete tables in a test database while in development stage, but foreign keys verification bores me every time I need to erase its tables.
Is there any form to bypass this in ...
1
vote
0answers
19 views
Is there a way to execute statements on server startup?
Is there something similar to mysql init-file, where one can put prewarm statements?
0
votes
1answer
21 views
What will happen with cached index or table when it is modified, will it stay in cache?
if PostgreSQL loads something (for example an index) into cache, what happens if it (cached index) gets modified? Is it going to be removed from cache or not?
1
vote
0answers
15 views
PostgreSQL FATAL canceling authentication due to timeout
I have a PostgreSQL 9.4 Database running on my CentOS 7 Server.
This database has only ever been available to users on the LAN,
however recently I needed to make it available to users outside the LAN
...
1
vote
1answer
10 views
enabling fsync in postgresql 9.1
currently fsync option is disabled in postgresql.conf.
If I am enable that,
From:
#fsync = on
To:
fsync = on
(I am enabling fsync to avoid data corruption if any failure since my data ...
1
vote
0answers
13 views
PostGIS radius search performance
In my Postgres database, there is a listing table, inside that table, I have a column called geo_point, which stores latitude and longitude information. The column type is geometry(Point,4326).
I ...
1
vote
2answers
23 views
Select column to be inserted based on input column value
Assume the following two tables (and ignore the quality of the database design):
Table 1 has columns id, sequence_number, value: it contains many rows.
Table 2 has columns id, seq1, seq2, seq3, ...
2
votes
0answers
34 views
Access points of geometric path data in PostgreSQL 8.1
Summary:
I would like to perform basic operations on the points of geometric path data contained in a PostgreSQL 8.1 server which I do not have administrative access to. In particular I would like to ...
0
votes
0answers
23 views
psql “invalid client_encoding” error on OS X, PostgreSQL 9.4.5
I'm currently running a OS X Lion Server system which ships with a built-in and not-upgradable PostgreSQL version.
After years of usage I've finnaly decided to leave the built-in version and install ...
1
vote
0answers
30 views
Can I use one table field value as second table field name in mysql select query?
Scenario:
Field names of Table1 named as testtable
id,name,size,width,height
Field names of Table2 named as errortable
id,desc,field1,field2,operator
Values of errortable
...
0
votes
1answer
34 views
Can postgres make use of both GIN and BTREE indexes in a single query?
Let's say I have GIN index on column A that allows me to filter table fast on that column by appropriate WHERE condition which would be slow without that index. However I would like also to order ...
0
votes
2answers
23 views
PostgreSQL array column, querying and grouping by array field
I have the following table data in a table named queryables in Postgres 9.4:
queryable_id | liked_count | users_who_like_ids
--------------+-------------+--------------------
2376 | ...
1
vote
1answer
34 views
Postgres corrupted index on update
Sometimes, when doing an update on a specific table record, i get this error:
index "myIndexName" contains corrupted page at block 468
Please REINDEX it.
But on the next update it works, without ...
0
votes
0answers
30 views
Client-side encryption in Postgres
We're developing an application that allows our clients to submit jobs to our server. After a job is submitted, the server spins up worker machines to process parts of the job.
Worker machines ...
1
vote
1answer
184 views
Is there a possible reason for having index on (a) and on (a,b) at the same time [duplicate]
Suppose I have a table with several column, among them - columns a and b.
Is there any use case for which having both multi-column index on (a,b) and a single-column index on (a) is beneficial?
...
1
vote
1answer
24 views
Postgresql INSERT triggers “0 row affected” workaround
In a INSERT trigger based table partition, insert always give "0 row affected". I think this problem is well know, it is even in the todo.
Is there any workarounds for this issue in 9.4?
0
votes
0answers
11 views
Will PostgreSQL server work if I install the service in mounted partition
I have created all tablespaces (only tables created in these tablespaces) in mounted partition, But db is available in pg_default tablespace (root partition where postgresql installed).
I am facing ...
0
votes
0answers
13 views
Easy way to provide failover for Postgresql?
We currently have a single postgres server (9.4) that holds data in our production environment (strictly Centos 6.x environment), I'm trying to plan ahead in case it ever crashes. We have a second ...
0
votes
2answers
32 views
Whats the best and fast way to import a huge DBF file to a Postgres in Windows?
I have some huge DBF files (about 35 GB) to import to Postgres.
Whats the best and fast way to do it?
OBS: I have 300 GB of free disk space and 8 GB RAM.
1
vote
0answers
6 views
Getting Error: wrong pg_constraint entry for trigger
I have not been able to find any details on this error at all. Specifically, I'm running a query similar to:
UPDATE "data"."campaigns" SET "name"='New Name' WHERE ("id"='182')
... and I'm getting ...
2
votes
2answers
63 views
Merge two huge tables keeping only the unique rows
I have two tables: table1 with 61 million rows and table2 with 59 millions rows. The columns in both are the same (name and type). Both are imported from backup files.
I want to merge these two ...
0
votes
0answers
19 views
autovacuum: found orphan temp table “(null)”.“myvar”
I get the following message in my PostgreSQL logs:
db=,user= LOG: autovacuum: found orphan temp table "(null)"."myvar" in database "xxx"
What do I need to do to fix this?
0
votes
0answers
19 views
PostgreSQL Connection from outside LAN [closed]
I have PostgreSQL running on a server on our LAN.
192.xx.xx.x:5432.
I have configured it to allow remote connections
postgresql.conf
listen_adresses = '*'
pg_hba.conf
# LAN
host dbname ...
0
votes
1answer
29 views
plpy.execute('…') statements not in logs
How can I get the SQL statements executed by plyp into the postgres log files?
CREATE FUNCTION history_monitor() RETURNS trigger
LANGUAGE plpythonu
AS $_$
...
plpy.execute('...')
$_$;
...
0
votes
2answers
41 views
pgpool load balancing is sending all queries only to master
My two postgresql servers are configured for streaming replication, which is working fine.
Pgpool is configured for Master Slave mode / Load Balance Mode.
pgpool.conf:
listen_addresses = '*'
port = ...
1
vote
1answer
49 views
Optimizing query in PostgreSQL that tries to match a string and matches a timestamp range
I am building a database in PostgreSQL for financial data, where the table looks like this:
create table fin_data(
brokerid text,
stock int,
holding bigint,
stake float,
value ...
0
votes
0answers
19 views
index being used during equality operator but not comparison operator [duplicate]
Using a btree as my index. From what I understand(http://www.postgresql.org/docs/9.0/static/indexes-types.html), btree works for comparison operators. For equality operator it works fine
explain ...
2
votes
4answers
47 views
Copy indexes from one table to another
I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm ...
1
vote
1answer
65 views
Replicating two PostgreSQL databases without using master db
I'm trying to create a simplest possible db cluster for my application and i'm not sure if that's the best way to achieve that so i'm gonna try to explain my situation the best i can.
Im basically ...
0
votes
0answers
49 views
Is there replication by “pushing” instead of “pulling”?
Here is the situation:
pg 9.4
pg instance "A" in intranet which is often loosing connection with
WAN because of satellite problems (primary). This server has dynamic public ip.
pg instance "B" ...
0
votes
0answers
22 views
What is the best recent referential SQL book that covers PostgreSQL & MySQL for an already C programmer? [closed]
I am a C programmer & I am targeting SQL for PostgreSQL & MySQL
I know just the basics about SQL (SELECT, INSERT, CREATE, UPDATE, DELETE).
I need to know all about SQL.
So I am looking for a ...
1
vote
1answer
95 views
return subquery value into main query
I have the following query:
SELECT *
FROM item_ledger
WHERE to_id='7'
AND (SELECT SUM(qty) AS qty_in
FROM item_ledger
WHERE from_item_ledger_id='5') < intransit_qty
the query ...
1
vote
0answers
20 views
Why does SemiJoin node receive so bad statistic even after runnning ANALYZE
PosgtreSQL 9.4
I have two tables:
CREATE TABLE home_transactions( id serial NOT NULL, transaction_id integer );
CREATE TABLE transactions( id serial NOT NULL, user_id, amount numeric(13,4) NOT NULL ...
0
votes
1answer
20 views
Unused Postgres sequence values
Observation: I have a serial index on a table in a PostgreSQL instance, and I execute a function that inserts a new record into that table. The function fails (reason not important), and the ...
1
vote
1answer
25 views
How to verify that a PostgreSQL base + WAL backup has been restored correctly
Coworkers were trying to extract a PostgreSQL database copy from a backup made at a hot standby in version 9.1, but it wasn't reliable - we would run it daily, but usually it would end up with various ...
0
votes
2answers
61 views
PostgreSQL How to optimize a query with ORDER BY and LIMIT 1?
I have the following PostgreSQL schema:
CREATE TABLE User (
ID INTEGER PRIMARY KEY
);
CREATE TABLE BOX (
ID INTEGER PRIMARY KEY
);
CREATE SEQUENCE seq_item;
CREATE TABLE Item (
ID ...
0
votes
1answer
18 views
What are nonremovable row versions and how to remove them?
I did full vacuum on postgresql-9.4 database and I have a lot of tables with nonremovable row versions. I would like to know what are these or where I can read about them.
INFO: vacuuming ...
0
votes
1answer
20 views
Postgres Refresh Materialized View Locks
I have a materialized view in Postgres, and want to know what locks (if any) are taken out when refreshing that view.
CREATE TABLE people ( name VARCHAR(30) );
INSERT INTO people VALUES ('Alice'), ...
3
votes
1answer
41 views
Preserve order of array elements after join
I have a query that returns a CTE looking like
+-----------+-------------+
| node_id | ancestors |
|-----------+-------------|
| 1 | [] |
| 2 | [] |
| 3 ...
0
votes
0answers
17 views
PGBouncer pausing hanging from ltm monitor
Currently I have the following setup:
F5LTM --> PGBx2 --> DBx3
The PGBouncers are set up with priority groups on the F5 so in the event one fails or hangs traffic will still be sent to the database ...
4
votes
2answers
50 views
Best practice regarding concurrency for INSERT into a table with composite primary key?
Say I've got the tables users, teams and a teams_users junction table (team_ID, user_ID / composite PK). If I wanted to add a user to a team, what's the best option when it comes to performance / ...
0
votes
1answer
24 views
How to get first value from list of value
I have data as follows
Id Col1 Col2
================
101 101 abc
101 102 def
102 102 ght
103 101 mgj
103 102 dkf
103 103 dfj
I need output as follows
Id Col1 ...
1
vote
2answers
44 views
Update a table after insertion to another table in PostgreSQL?
Let's say that my database has 2 tables (Person & Car):
Person (personID, number_of_cars)
Car (carID, owner)
where the owner attribute in Car refers to the personID in the Person table. Now, ...
3
votes
1answer
48 views
How to avoid invoking functions twice when using GROUP BY and HAVING?
I have a PostgreSQL database (9.2) with a table of parent-child relations. I have a query that looks for nodes having multiple parents.
The following query works and returns the correct results:
...
0
votes
0answers
18 views
Postgresql configuration for tiny server and append-only data
Currently my database is runing on tiny-sized hardware (digital ocean 5$) and I want to keep this hardware.
It is:
512M memory
1 CPU
My database works with these conditions:
Data is never ...
0
votes
1answer
16 views
PostgreSQL-Backup from windows hard drive to linux
I'm in a bit of a pickle here. I managed to copy the whole postgresql installation directory to a backup drive but how would I retrieve it from Linux with the same architecture and version. Is this ...
