All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

1
vote
2answers
69 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
30 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/ ...
4
votes
1answer
41 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
37 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
89 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 ...
3
votes
0answers
84 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 ...
1
vote
1answer
227 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
38 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
48 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 ...
1
vote
0answers
29 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
44 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
39 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
60 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
27 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
84 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
58 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
53 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
17 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
28 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
35 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
41 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
25 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
23 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
55 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
91 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
31 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
85 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
46 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
18 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 ...
1
vote
0answers
31 views

Create a trigger in Postgres that will insert a record to another table with added column

I have these 2 tables created in postgres, CREATE TABLE EMPLOYEE( ID INT PRIMARY KEY NOT NULL, NAME VARCHAR(100) NOT NULL, ADDRESS VARCHAR (250) NOT ...
1
vote
0answers
8 views

xmlforest() omits any fields that are null entirely, instead of creating an “empty” element

I'm know I can use an empty string, but I don't want to lose the null functionality in the database. In some cases null is appropriate and I can't use empty strings. Other than a empty string, how ...
3
votes
2answers
37 views

Restore PostgreSQL database (or db names) to new version, from files?

Scenario in short: a development laptop broke old HDD is still readable new laptop has PostgreSQL 9.4 instead of 9.1 both laptops use a flavor of Ubuntu Linux Question 1: Is my understanding ...
4
votes
1answer
67 views

I Need Help Setting Up Roles and Passwords in PostgreSQL

I'm using the postgres.app on my Mac Mini Server running version 9.4.0 using El Capitan. I have not done any customization in pg-hba.conf. I do not have a .pgpass file. I believe that .pgpass is used ...
1
vote
0answers
22 views

Slony with Amazon's RDS service?

Is it actually possible to somehow use Slony with Amazon’s RDS service? I’m aware that there’s no such thing as a host that you can log into to make Slony’s configurations, but perhaps it can be done ...
0
votes
1answer
24 views

PostgreSQL - duplicate data show and count

This is table. CREATE TABLE cameras ( id integer NOT NULL DEFAULT nextval('sq_streams'::regclass), created_at timestamp with time zone NOT NULL DEFAULT now(), updated_at timestamp with time ...
3
votes
1answer
101 views

pg_stat_statements not found even with "shared_preload_libraries = 'pg_stat_statements'

I have followed the instructions given below: http://www.postgresql.org/docs/9.3/static/pgstatstatements.html ... to the effect of adding the following lines: # postgresql.conf ...
3
votes
0answers
28 views

Understanding row estimation for timestamp in postgresql

PostgreSQL 9.4 I have a table (call it tbl) that's got the column registration_date timestamp. Now, for simplicity I set the following statistic parameter: ALTER TABLE tbl ALTER COLUMN ...
0
votes
0answers
40 views

I'm sure there's a better way than this INSERT

I have 2 tables: logs and relations. Logs' field have something like this: id (serial), mid, rid, value, insert_date Sample logs: 1001, 21, 1, "-94.420307", "2015-12-10 18:00:01" 1002, 21, 2, ...
0
votes
0answers
25 views

autovacuum launcher process, but no autovacuum workers

Debian Squeeze (yeah, I know), postgresql 9.1. Two similar db servers, one master one shard. postgresql.conf identical between the two besides small memory footprint config differences - all ...
1
vote
0answers
42 views

Constraint across multiple tables

I'm modeling a system where with vendors, each of which sells some overlapping subset of products available in the universe. Products (T-shirt) come in various variants (Small, Medium, Large) with ...
1
vote
1answer
40 views

Postgres auto analyze performance

On postgresql 9.4 I have a table that never has any updates or deletes, only inserts and selects. It therefore never gets auto vacuumed, but it does still get auto analyzed and when it does it can ...
0
votes
0answers
9 views

How to load a .dmp into PostgreSQL using pgadminIII [duplicate]

I have a dump file from oracle that I wish to load into pgSQL database. I can use psycopg2 or pgAdminIII to accomplish this. Any tutorials or references would be much appreciated as I cant seem to ...
8
votes
3answers
139 views

How to avoid a cyclic dependency (circular reference) between 3 tables?

I have 3 tables: People Post Likes When I design the ER model it has a cyclic dependency: 1:N People --------< Post 1:N Post ----------< Likes 1:N People ...
2
votes
0answers
34 views

Debug PostgreSQL lock count with check_postgres.pl

We use check_postgres.pl to monitor our database. We use this to check the count of the locks: https://bucardo.org/check_postgres/check_postgres.pl.html#locks We often see more than 150 locks. The ...
2
votes
0answers
46 views

Is PostgreSQL text really that much faster than Oracle CLOB? [closed]

I have an oracle 10g table with 1 clob column. The clobs aren't very large mostly around 2-8 kb. However reading many rows takes an excruciating amount of time. Even with storage in row enabled. I ...
7
votes
1answer
53 views

Postgresql join removal

I just watched the very interesting video from Rob Farley "Designing for simplification" and I was wondering if the 4 rules given in the video for join removal also hold on PostgreSQL. If a view is ...
3
votes
2answers
73 views

PostgreSQL requires “GROUP BY” when aliasing a table?

Here's something strange: there is a large table named "products" which is partitioned with inheritance such that there are two children: products_active and products_inactive, and the constraint is ...