All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
1
vote
0answers
20 views
Versioning data in a database
I have a database to hold data about projects.
Projects have lots of data associated with them that is segmented into other tables such as project_contacts and project_images. Those are one-to-many ...
0
votes
1answer
18 views
Does ALTER on PostgreSQL, Oracle table (respectively) create intermediate copy of the table?
I've heard MySQL admin claim that ALTER on MySQL table creates an intermediate copy of the table during the operation (don't know if the type of table involved were supposed to be MyISAM or InnoDB), ...
2
votes
1answer
9 views
How can I exclude range overlaps if one range may be empty?
Suppose I want to ensure that, for any expected height in feet, I have a single, unambiguous classification. I create this table:
CREATE TABLE heights_in_feet(
size TEXT NOT NULL, min INTEGER NOT ...
0
votes
0answers
10 views
Mapping between each bit in a bitmap index and tuple-pointers
Bitmap Indexes are described here, but I don't understand some part of it.
A plain indexscan fetches one tuple-pointer at a time from the index,
and immediately visits that tuple in the table. ...
2
votes
0answers
12 views
Postgresql Streaming Replication Error: WAL segment removed
I want to set up PostgreSQL streaming replication, but get the following error:
FATAL: could not receive data from WAL stream:
ERROR: requested WAL segment 00000001000000000000006A has already ...
1
vote
0answers
23 views
How to fix postgres stuck in recovery mode?
I have Jira and Confluence installed in a server. We didn't have any free disk space one day, and these services didn't started correctly, so after deleting some files we now have some free space ...
4
votes
1answer
301 views
What will happen when we add an index to an existing table with a large amount of data?
I've a table which will contain around ~15 Million records. Now I need to add an index to the table.
Adding an index will take some time to update every entry in the table.
I'm quite confused ...
3
votes
0answers
46 views
To partition or not to partition?
Having already read several questions on SO, external blog posts and manual
SO: Foreign Key constraint to partitioned table in Pg
dba.SE: Different ways of handling FK to partitioned table in Pg
...
2
votes
1answer
29 views
Filesystem tuning for PostgreSQL
I want to install PostgreSQL 9.x version in an Ubuntu 14.04 workstation and I'm looking for a recommendation on how to set it up in order to
get a good read-write (50/50-balance) performance
keep a ...
4
votes
1answer
55 views
Improving sort performance in GROUP BY clause
I have two tables in Postgres 9.4.1 events and event_refs with the following schemas:
events table
CREATE TABLE events (
id serial NOT NULL PRIMARY KEY,
event_type text NOT NULL,
event_path ...
3
votes
1answer
22 views
Postgres - estimate end time for a long update
I have currently running a long update (~million rows based on a postgis st_within condition) that was expected to finish in an hour or so based on similar previous queries. However, it's been three ...
0
votes
0answers
12 views
Basic transactional DDL script in PostgreSQL
PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the ...
0
votes
1answer
19 views
How to return a set of rows from this function?
I am new to Postgres functions and have a table with the following structure:
CREATE TABLE options.options (
delta double precision,
gamma double precision,
rho double precision,
theta double ...
0
votes
0answers
9 views
Postgres 9.2 replica can't catchup to master
I have configured a master/replica setup for postgres 9.2 on Amazon Linux. PS output shows that the replica is connected and streaming, but also recovering. This has been going on for about 24 hours.
...
0
votes
0answers
11 views
Where to put snowball files to add a snowball stemmer to PostgreSQL database?
I've downloaded Czech snowball stemmer source files (.c, .h files) and compiled them into .o files and I know the correct SQL syntax for adding the dictionary (it's in the docs).
I keep getting the ...
5
votes
1answer
79 views
Index not being used in SELECT query
I have a table of about 3.25M rows with the follow format in Postgres 9.4.1
CREATE TABLE stats
(
id serial NOT NULL,
type character varying(255) NOT NULL,
"references" jsonb NOT NULL,
...
0
votes
2answers
52 views
Postgresql text column character Limit
Im not even close to a DBA, so plain English please. I recently needed to look into a performance issue on a relatively small PG database, but yet is performing very slow. the server is running PGSql ...
0
votes
1answer
17 views
postgresql.conf file found in both /usr/share/postgresql and /etc/postgresql/
While troubleshooting another issue I noticed that in my Ubuntu 14.04 system there are two postgresql directories containing a postgresql.conf file:
/usr/share/postgresql
/etc/postgresql/9.3/main/
...
3
votes
1answer
36 views
Group and count array elements using intarray
I am working on a Postgres 9.4 project with the intarray extension enabled. We have a table that looks like this:
items
-------------------------------------
id name tag_ids
...
1
vote
1answer
27 views
Migrate Postgres from VM to VM
I have 2 VMs, both are identical in structural terms (came from the same snapshot, nothing installed, upgraded or removed).
One VM is "clean", never used.
The other VMs was heavily used and it has ...
4
votes
2answers
77 views
Optimize a query with small LIMIT, predicate on one column and order by another
I'm using Postgres 9.3.4 and I have 4 queries that have very similar inputs but have vastly different response times:
Query #1
EXPLAIN ANALYZE SELECT posts.* FROM posts
WHERE posts.source_id IN ...
0
votes
0answers
12 views
How to recover a Standby in Postgres Slot Replication [closed]
I am using 4 nodes of Postgres 9.5 server in a Slot replication set up. One node is acting as a Master and three other nodes as Standby nodes. Everything ran fine for few days and suddenly one node ...
3
votes
0answers
73 views
Postgresql Strange Characters
I've found a lot of strange characters on data rows like this
ÃÃÂÃ
The data inserted by perl script which converting to UTF8 ...
0
votes
1answer
19 views
Upgrade PostgreSQL 9.4 to 9.5 : LC_ALL unset
I'm trying to upgrade my PostgreSQL 9.4 database to PostgreSQL 9.5 but everytime while running sudo pg_upgradecluster 9.4 main I get this error:
Stopping old cluster...
Disabling connections to the ...
3
votes
1answer
31 views
How to get 0 as array_length() result when there are no elements
I have a query like this:
select id, array_length(users_who_like_ids,1) as ct
from queryables
order by 2 desc;
But empty arrays (with no elements) sort on top. I'd rather have array_length() return ...
0
votes
1answer
11 views
How Can I Access the postgres database in a Script Executing pg_dumpall?
I want to run the following script using my role which includes the postgres database.
pg_dumpall -c -f ~/Desktop/pgdumpall_proddb_`date +%Y%m%d_%H%M%S`.sql
Here are the settings in pg_hba.conf:
# ...
3
votes
1answer
32 views
PostgreSQL translate special characters?
Description:
PostgreSQL 9.3
String: 'ì ằ ú ề'
Desired Result: 'i a u e'
My code:
select translate ('ì ằ ú ề', 'ìằúề', 'iaue') ; -- it works. Result: i a u e
Question:
If I use it this way, I ...
1
vote
0answers
10 views
how to clear the postgrsql-9.1-main-log
Is there an easy way to clear the logfile of postgresql? the logfile is 6044 MB, can you use cp /dev/null file-name or do you mess up the database?
1
vote
0answers
14 views
remote postgres user logon with certificate
I would like to have each remote user logon using an SSL certificate and use their postgres logon role to better track their use of the system. I can set up a certificate for a given user per the ...
0
votes
0answers
27 views
How can I tell Postgres to make good index decisions with SQL? [closed]
Postgres is making some very poor execution decisions on some very well-written queries against my production database. They execute flawlessly on my laptop. Production server is running 9.3.3. Laptop ...
1
vote
0answers
23 views
postgresql columns are inside quotes - “ ” and uppercase but occurs relation does not exist error when queried in java
I'm using Java and Postgresql and I've created a relation "UserTable", and when I open PgAdmin, this is how I see my database.
But when I query the UserTable, in Spring Tool Suite using Java, I get ...
0
votes
1answer
35 views
Postgres datetime sort slow and not using index
Using Postgres 9.4, I'm trying to order a query by an indexed datetime column.
explain
SELECT g.*, array_agg(gp.platform_id) as platform_list,
array_agg(gm.metacritic_id) as ...
3
votes
1answer
32 views
Join table into different columns
I have two tables (simplified).
table1:
- id (serial)
table2:
- table1_id (integer)
- value (text)
- type (integer)
I want to join these two tables, but depending on the type, I want ...
0
votes
2answers
53 views
Perform join on multiple tables
I have three tables right now in a postgres DB:
Artists
ID Name
Shows
ID Date Venue Price Is_sold Ages Time Multi_day Pit
Artists_shows
ID Show_id ...
1
vote
0answers
19 views
How to make a PostgreSQL trigger thread-safe
I want to make my PostgreSQL trigger function do something like incrementing counters etc (something complex with multiple queries etc) thread-safely (so the same function triggered concurrently won't ...
3
votes
1answer
77 views
Why am I getting old data?
Today I discovered something weird on PostgreSQL 9.5. (I have no idea whether this is because of beta or not.) When I want to fetch data, I get old and deleted data from query. I then do VACUUM FULL ...
2
votes
1answer
39 views
Postgres 9.4 won't accept remote connections
I'm running postgres 9.4 on Ubuntu server 14 which is running on virtualbox. After a reboot no remote connections would work to postgres.
I have other services on server that still take remote ...
3
votes
1answer
46 views
Indices for a join query with datetime range
Given the following query:
SELECT DISTINCT COUNT(DISTINCT "patients"."id")
FROM "patients"
INNER JOIN "transactions" ON "transactions"."patient_data_id" = "patients"."patient_data_id"
WHERE ...
2
votes
0answers
11 views
Promote Postgres standby while materialized view updates in primary
I have one primary Postgres server (serverA) and one standby Postgres server (serverB), both running Postgres 9.4 with Streaming Replication enabled.
Is it possible to update a materialized view on ...
1
vote
0answers
27 views
PostgreSQL long transaction on a table performance
We have a long processing pipeline running on PostgreSQL 9.0.
Is it faster or slower to perform many operations on a single table within one transaction, versus doing auto-commit for each operation?
...
0
votes
1answer
34 views
If else with 5 possible cases PostgreSQL
I have bunch of records in Database and i have been searching for query on NULL json object and i got that right,but now its difficult for me to continue it on 5 possible cases
For example: am ...
4
votes
1answer
34 views
Fastest way to find (rare) rows with a null value among millions of rows?
If I have an addresses table with ~ 4 million rows in a Postgres database. Is there a way to optimize the following query so that I can find the few rows where address_id is NULL? Sometimes I need to ...
1
vote
0answers
23 views
Design options for time series scientific data
I've been scratching my head over the last few days trying to find a solution that best suit my needs. I tried to think that what I want is probably not very different from what many others wanted ...
0
votes
0answers
17 views
How autocommit = 1 impacts statements between BEGIN/COMMIT?
Let's say I have following code:
begin;
set autocommit = 1;
update tbl1 set col1 = 1;
update tbl1 set col1 = 2;
commit;
What would be the difference if autocommit (line 2) was set to 0?
0
votes
1answer
29 views
PostgreSQL force sslmode=verify-full
I have created my CA, Server and Clients certificates and keys. I have tried connections using sslmode flags with psql. I have edited the pg_hba.conf file to force SSL using hostssl only. Now I would ...
-1
votes
2answers
75 views
How to delete millions of records from table
I am using postgree 9.4 database and I have a table snapshots with 700+ million rows and I want to delete about 9 million rows. I have tried different queries to delete records, but no success.
Query ...
3
votes
1answer
29 views
Can atomic counter in trigger lead to “could not serialize access due to concurrent update”?
I have the following code:
create table users (id serial primary key, comments_counter integer default 0);
create table comments (id serial primary key, user_id integer references users(id) not null)
...
3
votes
3answers
82 views
SUM of ever increasing column
I have a table of "transactions" where each transaction has an amount:
http://sqlfiddle.com/#!15/42849/1
Records in the table are never REMOVE'ed or UPDATE'ed. Only new transactions are added.
I ...
0
votes
0answers
35 views
Migrate json file to Postgres database
Is there any easy way to migrate json file to Postgres database?
I googled, but it seems to me that there in no psql command to load json data, like there is for example \copy for csv.
0
votes
1answer
10 views
Check lock count for PostgreSQL
Up to now we used check_postgres.pl to monitor our database.
We used this to check the count of the locks:
https://bucardo.org/check_postgres/check_postgres.pl.html#locks
We often see more than 150 ...
