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

learn more… | top users | synonyms (1)

0
votes
1answer
9 views

Postgres 9.5 import to MSSQL Server 2012 r2 verification issue

I have a postgresql table with 8 million rows. I need to copy it over to MSSQL. I do not have access to the console so I am unable to use PG_Dump. I have the ODBC driver, and have successfully linked ...
0
votes
2answers
330 views

Postgresql Chained Slaves and Slave Promotion

So I have a setup like Master -> Slave1 -> Slave2. Slave1 replicates from Master and Slave2 replicates from Slave1. If I promote Slave1 to master is it possible for Slave2 to continue replicating from ...
1
vote
1answer
17 views

Reset search_path to the global, cluster default

Say I permanently alter my database's search_path like this: ALTER DATABASE my_db SET search_path TO "$user",public,other_schema; How can I reset it back to whatever the default that a pristine, ...
0
votes
0answers
10 views

Update the current session's default parameter value to reflect a new default

Say I ALTER the database default search_path to include a new schema: ALTER DATABASE my_db SET search_path TO "$user",public,other_schema; Then I also update the current search_path to match: SET ...
0
votes
1answer
10 views

PostgreSQL invalid frontend message type 74

Error invalid frontend message type 74 I'm getting this error and I have no idea where it's coming from or what it means. I can't find any documentation on frontend message types in the PostgreSQL ...
0
votes
0answers
18 views

PostgreSQL on Desktop vs. Server

How does running PostgreSQL on a Windows desktop with pgAdmin III compare to running it on a dedicated server? I am trying to develop a database with very large tables (~60 million rows) with the ...
8
votes
2answers
10k views

Export Postgres table as json

Is there a way to export postgres table data as json to a file? I need the output to be line by line, like: {'id':1,'name':'David'} {'id':2,'name':'James'} ... EDIT: postgres version: 9.3.4
3
votes
2answers
5k views

How much time will a vacuum/autovacuum operation take?

I manage a big (some hundreds of gigs) database containing tables with various roles, some of them holding millions of records. Some tables only receive large number of inserts and deletes, some other ...
0
votes
0answers
21 views

pgAdmin III hangs a lot!

I've just installed PostgreSQL 9.5.3 into my Windows 10 system(x86). While, it didn't show any errors while installing. But, after opening pgAdmin III, when I try to connect using my password, it ...
0
votes
1answer
28 views

Crosstab column without merging data

I'm going to try to explain myself. I need to generate a crosstab in Postgresql, I was able to generate it but it shows no results because it is trying to merge the data. I mean, I have multiple data (...
0
votes
1answer
26 views

PostgreSQL: suggestions for speeding up string cleanup function?

I need to cleanup a string, so that certain ASCII code characters are left out of the string, and others are replaced. New to PG SQL. My function ufn_cie_easy performs way too slow. Grateful to anyone ...
1
vote
1answer
159 views

How to measure or estimate the streaming replication lag in Postgres 9.2

I've been asked to reverse engineer an app that uses postgresql in the back end. I can see that there is some database replica tion going on... based on my reading, I *think it's called streaming ...
1
vote
1answer
28 views

Impact of CLUSTER on performance

I'm trying to optimize my Postgres 9.2 database to speed up queries with date restrictions. I have a timestamp column, but mostly I'm asking for some day, so I have created an index with timestamp to ...
3
votes
2answers
274 views

Can PostgreSQL support integration test with some kind of throwaway overlay?

It's a common problem to write integration tests that include a database. If the test changes the database then it could effect other tests or the next run of itself. I know that I could wrap my test ...
0
votes
1answer
19 views

Postgres - fast way to enforce AND across multiple rows

I've got a 4 column table with ~25M rows: tweetifai=> \d core_tagusage; Table "public.core_tagusage" Column | Type | ...
0
votes
1answer
12 views

Is it possible to migrate a postgresql database without downtime?

I have a Postgresql Database on my Centos, I use barman to backup my database, my question is that is it possible to migrate my database to another server without downtime? I know some solutions with ...
0
votes
1answer
13 views

How to find what tablespace a table/index is in on PostgreSQL?

How do I find out what tablespace a table (or index) is in with PostgreSQL (9.3)?
0
votes
1answer
11 views

Postgresql: replica and HA

I've postgresql 9.4. I'm aware that there are many answer on DBA and SO, I'm aware of the wiki and the documentation. But anyway, I can't understand in a simple way what's the right way to have a ...
0
votes
1answer
20 views

how to force postgresql to filll work_mem?

i'm currently investigating a situation where my PostgreSQL 9.3 instance was killed by the Linux OOM Killer and went to recovery mode. The machine is EC2 Server with 16 GB of RAM and has rather few ...
0
votes
2answers
22 views

Postgres - Multiple commands on table at the same time

How robust is Postgres when it comes to running multiple commands on a table at the same time? Can I for instance, set a primary key and create a couple of indexes using multiple separate commands at ...
0
votes
2answers
21 views

Pressing Ctrl+C during UPDATE query in Postgres, was the query partially run?

I accidentally launched unwanted UPDATE query inside psql (9.4) and killed it with CTRL+C a few seconds later. Were there any records modified, or it's all or nothing?
0
votes
2answers
37 views

How to join to the same table multiple times?

I have two tables, "hierarchy_table" and "name_table". The hierarchy table contains an object which has multiple parents and children. Each parent and child is referenced by id. | object_id | ...
0
votes
0answers
12 views

Counting entites based on status in rolling interval

Given the following table: CREATE TABLE status_log ( entity_id bigint NOT NULL, user_id bigint NOT NULL, status character varying(128), "timestamp" timestamp(6) without time zone ) I want ...
-5
votes
0answers
44 views

Retrieving the data based on id from table contains 2 million rows [closed]

I have one table/collection contains 2 million rows/document (id(20 alphanumerical digits), name, ... etc). From that table I want to retrieve data based on the id within 1 sec Which database ...
1
vote
1answer
216 views

PostgreSQL: Installing a contrib extension on windows

I am trying to install this Temporal Tables extension into my localhost installation of PostgreSQL. (pg9.4, windows 7) I have figured out I needed to put the files into the 9.4/share/contrib folder (...
0
votes
1answer
58 views

Improve query speed for large, wide table

I have created a medical claims database using PostgreSQL 9.4.1 and pgAdmin III. There is one table with ~61 million rows and 103 columns. The table has the structure given below. It is static and ...
0
votes
0answers
15 views

Finding the most commonly used non-stop words in a column

Simply put, I have a postgres column containing ordinary prose and would like to determine the x most commonly used words ("word" being a group of characters deliminated by a space, but not a stop ...
5
votes
1answer
161 views

Problems while building plpgsql_check on Windows

I try to build plpgsql_check extension by @PavelStehule (Erwin Brandstetter recommended it to me in this answer) on Windows XP 32bit with Visual Studio 2010 (I used this article as a manual). However, ...
0
votes
0answers
17 views

Trigger not working error column “G:\Work Orders\Work Orders” does not exist [on hold]

PSQL 9.5, OS Win7 Using information I gained from; Set a column's default value to the concatenation of two other columns' values? I built a trigger that is suppose to create a hyperlink in ...
3
votes
3answers
25 views

CONCAT used in INDEX causes ERROR: functions in index expression must be marked IMMUTABLE

I am facing the following error: ERROR: functions in index expression must be marked IMMUTABLE When trying to create index like this: CREATE INDEX full_phone_number ON orders_clientphone (...
0
votes
0answers
17 views

Why did the data type of my DB change?

I had PostgreSQL installation in an EC2 instance. I wanted to migrate to RDS and thus used the DMS service. However, I later came to realize that the data type of the columns have been changed. The ...
1
vote
1answer
118 views

PostgreSQL streaming replication lag for synchronous standbys

We use PostgreSQL 9.5 in a master + synchronous hot-standby setup, i.e., using synchronous_commit = on and using synchronous_standby_names='*'. Consequently, the standby has sync_state = sync in the ...
2
votes
0answers
14 views

Converting a looped INSERT.. ON CONFLICT to a wCTE in postgres

I have an events table: Column | Type | Modifiers ---------------------+-----------------------------+--------------- ts | timestamp without ...
0
votes
0answers
18 views

Postgres row level security policy [on hold]

How do I list names of all the available policies in the database? Something like: SELECT oid,relname FROM pg_class WHERE relrowsecurity = 'true' But I need to know the policy name
0
votes
0answers
21 views

PostgreSQL 8.1 Corruption Issue

I have an old Postgres server that some major corruption problems on one of the databases. We first noticed the issue when the drives started to fill up on a database that should have plenty of space. ...
2
votes
1answer
1k views

postgresql - a local replication entry doesn't work

Maybe it's just too early in the morning, but I can't seem to get pg_basebackup working for a local user. This is part of testing for an hourly backup automated script. I added a line to pg_hba.conf ...
-1
votes
0answers
17 views

How to use count and groupby to create many columns [on hold]

I have a simple table named A with 3 columns: product_id first_date second_date. If first_date==second_date then up_to_date. if first_date<second_date then nearly_over_date, else over_date After ...
0
votes
0answers
17 views

psql multiline commands: How to edit previous lines?

When writing a query across several lines in psql, sometimes I realize I've made a mistake in one of the lines previously written. In the following example, select * form ( etc, etc... there is a ...
1
vote
1answer
37 views

How to set column DEFAULT using value of a variable

In a script for setting up a database which uses random UUIDs for primary keys, I have: CREATE TABLE alpha (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), value INT); CREATE TABLE beta (aref UUID ...
1
vote
0answers
33 views

How to select random groups of similar records?

I have following table: create table users ( id serial primary key, gender text not null, age integer not null ); insert into users (gender, age) select (case when i % 2 = 0 then 'male' else '...
0
votes
2answers
18 views

How to transfer DDL changes between different stages?

What technical solutions are available to track DDL changes and transfer them between different stages (e.g. DEV -> QA -> PROD) on postgreSQL databases? Is there a common mechanism to do that?
1
vote
1answer
8k views

Error: Could not read Block X of relation base/Y/Z

We have some serious issues with our PostgreSQL-Server (Version 8.4). Our webapplication uses jdbc to connect to the PostgreSQL Server. Suddenly our webapplication cant connect to the PostgreSQL ...
0
votes
2answers
224 views

SQLAlchemy connection error with Postgres

I am trying to write a basic python script that uses SQLAlchemy to connect to my Postgres database. In the connection details, if I use "LOCALHOST", it connects fine. If I reference the server by name,...
0
votes
0answers
12 views

Understanding PostgreSQL autovacuum cost and duration from logs

I've configured autovacuum with cost-delay based parameters and have entries such as this one in the logs: Jul 24 03:31:24 srv postgres[23181]: [6-1] 2016-07-24 03:31:24 EDT [23181]: [2-1] user=,db= ...
0
votes
0answers
8 views

Where has postgresql plv8_95 gone?

I just upgraded to Fedora 24. Before that, there used to be a plv8_95 package in the postgresql main repo. This made adding plv8 very easy by just running "dnf install plv8_95". However, that's ...
2
votes
2answers
5k views

How to completely remove EnterpriseDB Installation of Postgresql on Ubuntu?

All, I'm trying to install/run PostgreSQL 9.3, but am receiving a multitude of errors which seem to point to a failed installation of postgresql from the enterpriseDB package. For example, trying to ...
1
vote
1answer
602 views

Postgres function to merge two json objects with overlapping keys into one object

I've got the following json object: { "a" : { "0" : 2, "1" : 4, "3" : 6, } "b" : { "2" : 8, "1" : 10, /*note this key exists in "a" too*/ "...
1
vote
0answers
13 views

Is it possible to change pgAdmin SQL completion shortcut?

In pgAdmin SQL query editor, the default SQL code completion shortcut is ctrl-space; however, my native language is not English, we use ctrl-space to switch input method. Is it possible to change ...
0
votes
1answer
25 views

Sending an async notification to another database in PostgreSQL

Is there any way I can send a NOTIFY to a database other than the one that my connection has specified? [CLARIFICATION] Consider we have two databases within one instance of PostgreSQL. If you are ...
0
votes
1answer
26 views

Cleanup after deleting a large table

I have a database that once held a large table: ~6 billion rows, ~6TB of disk space, ~10 rows inserted/second. The rest of the tables, all told, comes to ~500MB and have infrequent writes. So the big ...