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
1answer
52 views

What's an “inline query”?

According to the currently accepted answer to this question a partial solution to an efficiency problem was to "inline" the query. I can't tell what that is, I can't ask in comments (too little rep) ...
0
votes
1answer
11 views

PostgreSQL to HDFS to Spark, or PostgreSQL directly to Spark SQL?

If the end goal is to perform computations with Spark, what are the reasons one would first transfer Postgres data to an HDFS (with Sqoop) vs. just using Spark SQL with Postgres directly (with the ...
0
votes
2answers
385 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.
2
votes
0answers
15 views

export data from postgresql

I have some text like xml -code in a field of table. I want to export the data from my field into file without any changes. I've tried to use a lot of variants but all of them changes the text. The ...
0
votes
1answer
116 views

PostgreSQL : Master-Slave replication strategy and solution for 3 servers.

We have currently 3 servers(X,Y,Z) running Debian X64, two of them host projects(Y,Z), which don't require that much database, as they serve more static content, but the third server(X) is largely ...
2
votes
0answers
18 views

In Postgresql comparing timestamps gives no result at all?

I am having two tables a, b. Table a is a FDW table from a mysql database. Table b is a normal pgsql table. b.entry_time is of type timestamp without time zone. table a is having some data that I ...
0
votes
1answer
20 views
+50

Get the Replication/Lag time of Redshift

I'm currently running RedShift with pushes of all data from our production Postgresql databases every 10 minutes or so. Periodically, the ETL process from Postgresql to Redshift gets delayed or backed ...
1
vote
1answer
274 views

PostgreSQL on Windows fails to start - 'could not create shared memory segment'

PostgreSQL 9.4 & 9.5 (beta) on my Windows 7 laptop fails to start. In the Windows event log I see the following error: 2015-11-10 13:53:49 IST FATAL: could not create shared memory segment "...
0
votes
0answers
19 views

Repmgr standby does not ask witness before promote

we have PostgreSQL 9.5 cluster with 3 servers (master,standby, witness) and repmgr 3.1.3. Master and standby server are in different data centers. Set automatic failover. All working good except one ...
1
vote
1answer
306 views

pg_ctl promote is not working

I'm working on setting up a two-node PostgreSQL 9.4 environment. Right now I have test instances running on port 5532 on two identical Centos servers. node1 is correctly sending logs to a shared ...
1
vote
2answers
26 views

Routinely deleting aged postgresql rows via cron

I have a Django website with a postgresql 9.3.10 backend. The website contains dynamic user-generated content (think of it as a form of 9gag). There are some tables from which I routinely delete aged ...
5
votes
2answers
5k views

Have Postgresql accept 1 and 0 as true and false for boolean

I have a large file of dumped sql data to load in to postgresl (600mb+). In that dump, fields that are boolean true and false are expessed as 1 and 0 without quotes. Trying to load them in to ...
9
votes
2answers
266 views

Find rows where integer sequence contains a given subsequence

Problem Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL. I have a table representing sequences of integers. The definition is: CREATE TABLE sequences ( id ...
3
votes
1answer
184 views

PostGIS: Cutting Holes in Self Geometry

I have a table of geometries in PostGIS, some of which overlap. When I display the geometries, this has the unfortunate consequence of blending the two stacked geometries together and producing an ...
0
votes
1answer
28 views

Using postgres database from another computer

I have PostgreSQL version 9.5 installed at my machine (Windows 7 Enterprise x64 bit version). I have several databases in schema 'public'. I would like to use/access one of these databases from ...
0
votes
0answers
24 views

PostgreSQL Numeric field: allow comma or dot in decimal separator [on hold]

Some users insert a comma (1,2) instead of dot (1.2) in a numeric field. This raises a postgresql error: Invalid input syntax for type numeric I tried creating a trigger that replaces the comma ...
-2
votes
2answers
65 views

SQL Text data type deprecated?

I have read that Microsoft SQL Server has deprecated the text data type in favour of varchar(max). PostGreSQL already allows varchar (without a length) as an alternative for text. The question is, ...
1
vote
0answers
43 views

PostgreSQL Partitioned table update trigger

I have a date-partitioned table (inheritance partitioninng just like the one pg_partman does) which is referenced by quite a few tables in my database. To resolve the FKs checking for referenced field ...
0
votes
1answer
18 views

How to add PK on every id column in Postgres

I have an issue. I have database backup for existing DB let's call it dev_db. I recovered it but under a different name like master_db and backup how I understood has only tables, columns types and ...
6
votes
2answers
11k views

How to upgrade postgresql 8.4 to 9.4

I want to upgrade my postgresql 8.4 to 9.4. the documentations are not clear to me. Will I lose my old databases if I do the upgrade? How can I backup my old databases if I am to lose them after ...
1
vote
2answers
50 views

pgAdmin ||| paging?

I'm wondering if there is no paging feature for the pgAdmin III 1.22.1 Query tool. So if I try to query a table with 500k+ rows without constraints, the query takes like 30s. Is there a paging ...
0
votes
0answers
35 views

DBMS comparison [on hold]

I am interested in getting to know differences in some of the DBM systems. I am mainly thinking of MS SQL, PostgreSQL and mySQL, but am also willing to dig into some other DBM systems, like Oracle. ...
0
votes
1answer
30 views

Repopulating PostgreSQL Database with Data Directory from Another DataBase Instance

I have had to delete PostgreSQL for an issue and reinstall it, however before uninstalling I did not execute the command pg_dump. I still have the data directory from this instance. After reinstalling ...
1
vote
0answers
23 views

Postgres “could not receive data from client: Connection reset by peer.”

I have a cakephproject got a error when the project reload and it seem the error Database connection "Postgres" is missing, or could not be created. I have checked the error it will show error could ...
0
votes
1answer
151 views

Is it possible to set a timer for autovacuum in PostgreSQL

I am running PostgreSQL 9.4.1. I am currently cleaning up data, which requires me to run expensive queries. It seems like my tables get "locked" up every day at the same time in the afternoon. My ...
6
votes
2answers
256 views

How to examine PostgreSQL server's SSL certificate?

Suppose there is a PostgreSQL server running and it has SSL enabled. Using "standard" Linux and PostgreSQL tools, how can I examine its SSL certificate? I'm hoping for output similar to what you ...
0
votes
2answers
303 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 ...
3
votes
0answers
51 views

Protect a table against concurrent data changes while allowing to vacuum it

I am running PostgreSQL 9.5. In a transaction, I check if data in the table satisfy some conditions: IF condition1 THEN -- do this ELSE IF condition2 THEN -- do that ELSE ... END IF; ...
0
votes
1answer
340 views

sorting by partial key name in json column

I have column document with data type json. I save here data for logging. So document has: {blocks: { participant-information_primary__Primary__39011__2288960: first_name: Firsst name, ......
1
vote
1answer
117 views

Querying JSONB in PostgreSQL

I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support). ...
0
votes
2answers
198 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,...
7
votes
1answer
63 views

Who is “focker” and why does he own my schema?

I have a Postgres DB deployment in Compose.io, and I am using the standard "compose" db for my app. I would like to wipe out the current DB contents so that I can recreate the DB with a new schema. ...
1
vote
1answer
33 views

PostgreSQL merging identical tables from separate databases into single database

I've started working on a project where the application data is split across multiple databases that all have the same schema (basically there's a database per "organisation"). After some discussion ...
0
votes
1answer
23 views

Postgres: turn off formating for xml export?

I want to export some table to xml file: \o output.xml SELECT table_to_xml('modified_records', TRUE, TRUE, ''); However, I'm struggling with psql formatting. The best I managed to get is: \t on \x ...
-1
votes
0answers
14 views

How can i get created function dates in postgresql? [closed]

I want to know about newly function created date in postgresql.
1
vote
0answers
15 views

Add Shapefile loader plugin to PgAdminPortable

I'm using PgAdminPortable and I'd like to enable « PostGIS Shapefile and DBF loader » plugin. The issue is that the files are not structured as in a PostgreSQL complete install (see below). I have ...
1
vote
1answer
153 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 ...
0
votes
0answers
38 views

Remove header (column names) from query result [migrated]

I am using a Java based program and I am writing a simple select query inside that program to retrieve data from the PostgreSQL database. The data come with the header which is an error for the rest ...
3
votes
1answer
30 views

Customize jsonb key sort order involving arrays

I have a table in PostgreSQL with some data: create table t2 ( key jsonb, value jsonb ); INSERT INTO t2(key, value) VALUES ('1', '"test 1"') ,('2', '"test 2"') ,('3', '"test 3"') ,('[]', '"...
0
votes
0answers
12 views

RedShift Roles For Superuser-like Role with Read Only Access

I'm managing a RedShift cluster and our engineers need more access than is generally provided to them with their roles. Essentially they would require the right to read EVERYTHING in all tables but ...
3
votes
1answer
38 views

Postgresql tables seem duplicated (with suffixes such us pkey, hashes, etc)

due to some problems I came to check the state of my current database in production and I found that some tables are "duplicated". By duplicated I mean that there appear to be some clones of the ...
0
votes
0answers
22 views

Restore PostgreSQL database with same locale settings as source database

I have a PostreSQL 9.x database on an Ubuntu 14.04 LTS production machine. My development machine is Windows 7-based, providing PostreSQL 9.y. I want to restore the Ubuntu PostreSQL database on my ...
-2
votes
0answers
38 views

How can I replicate some tables from one PostgreSQL database to another? [closed]

How can I replicate some tables from one PostgreSQL database to another? I know I can replicate a database but I want to replicate 2 or 3 tables from one database to another.
1
vote
1answer
495 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*/ "...
0
votes
1answer
160 views

Tuning postgresql.conf when benchmark

Description: We build a new database server Enterprisedb v9.3 (PostgreSQL commercial), so we use JMX to benchmark it with 1000 users. Here our detailed information: Operating System: Centos 6 x64 ...
3
votes
1answer
22 views

UPDATE a column in one table according to a column in another

In Postgres, I have two tables that have become de-normalized. We're in the process of migrating to a normalized form, but in the meantime the tables have become out of sync. I'd like to copy the ...
1
vote
4answers
100 views

Find IDs from a list that don't exist in a table

Say I have the following schema and data: create table images( id int not null ); insert into images values(1), (2), (3), (4), (6), (8); I want to perform a query like: select id from images ...
0
votes
1answer
20 views

Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table

I have a query similar to this below. As you can see, there are multiple parts which are very similar. SELECT id, aaaa, bbbb, tags ,( SELECT COUNT(*) > 0 ...
1
vote
1answer
47 views

SELECT count(*) from a materialized view with unique index is slow

I have a materialized view with a unique index (on the id column) and both of these queries are slow (they scan the whole table): SELECT count(DISTINCT a.id) from the_view a; SELECT count(*) from ...
1
vote
1answer
49 views

How can CREATE OR REPLACE fail with already exists?

DETAIL: Key (proname, proargtypes, pronamespace)=(rand, , 2200) already exists.: CREATE OR REPLACE FUNCTION "rand"() RETURNS float AS How on earth is that even possible? How can CREATE OR REPLACE ...