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
30 views

Database design. And get last modified time with each column in PostgreSQL

My database/table each endpoint/scope have multiple table, e.g user: user, user_information, user_role ... I was wondering should I separate table like below, or not? This is my first time to ...
1
vote
2answers
47 views

How to find out if rows are refenced from other tables?

I have a table named contacts (primary key id) which is referenced by a number of other tables via foreign keys. When a list of contacts is displayed in the application, I get their primary keys in an ...
0
votes
0answers
17 views

Backup and Restore Hive Metastore in Postgres

How to backup and restore hive metatstore in embeded postgresql for CDH5.7.0 cluster? If the table is drop, then how to restore the metadata for that particular table in embeded postgresql.
0
votes
1answer
25 views

how to check list of users present in all postgres database

I have around 50 of postgres database. I need to check all users with their respective database name. I know \du gives the list of all users like, List of roles ...
0
votes
1answer
26 views

PostgreSQL - cannot create RULE from stored procedure

I'm trying to automate boring database work. I would like to have one function which creates views, rules, etc so I don't need to run corresponding SQL queries from file. Currently I faced an issue ...
-1
votes
0answers
17 views

PostgreSQL Table Function: return all table fields

I have already created a view which has my derived columns with appropriate aliases. I would like to create a table function which uses this view and returns all the columns from that view. I have ...
0
votes
0answers
17 views

Is Schema Level Replication possible in PostgreSQL?

I am very new to the world of databases and wanted to know how one would/could accomplish Schema level replication in PostgreSQL. I've read about Slony, but it does not appear to state anything about ...
0
votes
1answer
15 views

Postgres: SELECTing bytea data partially with offset and length

I have a table containing a bytea column, with an average entry size of 5MB. Is it possible to query from random points in the bytea data (given an offset and length of data required) instead of ...
0
votes
0answers
26 views

pg_controldata 'Time of latest checkpoint' on standby doesn't change

About 2 weeks ago, pg 9.3 master space filled up, didn't die, continued operation after space was freed up, and it's standby has kept up with it ever since. However the standby pg_xlog keeps filling ...
0
votes
2answers
45 views

Are two logically equal indices physically the same index?

I have a table with this definition: CREATE TABLE public.delete_operation ( id bigint NOT NULL, parent_id bigint, pdu character varying, progress smallint NOT NULL DEFAULT 0, description ...
0
votes
1answer
21 views

plpgsql function to replace ' ' with null after insert using a trigger

I have a table in PostgreSQL 9.5 on which insert statements are run frequently. After an insert statement was ran on the table there are ' ' (single quote, space, single quote) values when no data was ...
0
votes
0answers
10 views

how to enable shp2psql-gui in pgadmin3 with postgresql-9.5 on centos

I have installed postgresql-9.5 on centos with postgis2_95 extension. Now I want the shp2psql-gui plugin tobe enable in my pgadmin3. Please help on this as I am unable to enable it though I have ...
0
votes
1answer
27 views

postgresql reindex frequency and monitoring

What's up guys. I have a couple of tables where i inserting much of data (and almost never deleting). Periodically i see that insert gains ExclusiveLock on index of table. So i guess that sometimes ...
0
votes
2answers
20 views

PITR restore in postgresql

I need to perform incremental backups and restore those backups in postgresql. So in order to achieve this i setup wal-archiving as follows. wal_level = archive archive_mode = on archive_command = ...
1
vote
2answers
51 views

Full Join Query

I am doing a full join query from two tables. SELECT i.idinasistencia, i.fecha, i.valor, i.justificado, h.id, p.nombres, p.apellido_paterno, p.apellido_materno, p.rut_persona FROM historialalumno ...
3
votes
1answer
60 views

How to LEFT JOIN a date table onto each customer (GROUP BY) in a customer purchase table? Can a JOIN be done for dates after initial purchase?

I am stuck on implementing a left join on dates for each individual customer. I have a table which stores customer purchases: SELECT purchase_date, customer FROM purchases I would like to create a ...
3
votes
1answer
77 views

Optimise a LATERAL JOIN query on a big table

I'm using Postgres 9.5. I have a table that records page hits from several web sites. This table contains about 32 million rows spanning from Jan 1, 2016 to June 30, 2016. CREATE TABLE event_pg ( ...
0
votes
0answers
19 views

Restore Postgres Database with Old Data Directory

I have not made a backup from my old data directory, but I still have the directory in tact. In my current database I have the same Schema, etc... When I try to restore the database (using pgadmin III)...
0
votes
0answers
23 views

Traditional SELECT with Multiple Joins or SELECT JSONB then multiple SELECT

I am writing a simple recipe book app based on node.js with sequilize as ORM and Postgresql 9.4 as DB. Recipe has multiple ingredients that can be measured with different units. I store recipe steps ...
1
vote
1answer
33 views

Conditional WHERE in plpgsql function

I'm writing function in plpgsql and stuck with following question: my function has parameter service_id_input and query. I want to create additional clause in WHERE section if this variable is not ...
0
votes
0answers
43 views

Deadlock during delete in same transaction?

pid basename oid user tx locktype acquired date time query 7592 base 1595014 postgres 363/197025 RowExclusiveLock Yes 2016-06-27 18:19:54+...
1
vote
1answer
41 views

Impact of OS timezone change on Postgres Database

I want change OS time zone to UTC. This will shift back time by 2h. Can this possibly damage data in any way (for example overwrite data)? What should I do to safely change the OS time zone?
0
votes
1answer
75 views

CTE Query copy value if exists into an existing table [closed]

I got the following query: WITH table_data AS ( SELECT UNNEST(array['Small Bus', 'Window', 'Test 1']) AS description, UNNEST(array['1dfsdf4', 'QSF-312', 'EXD-TUH']) ...
0
votes
1answer
28 views

Incremental backup in postgresql in windows

I am developing an application with postgresql as backend. So my requirement is to take the incremental backup either hourly or daily basis. I have gone through many related articles (like https://...
0
votes
1answer
21 views

Can I pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE?

I have a function which returns: TABLE (id TEXT, collected TIMESTAMPTZ) In another function, I would like to be able to pass the result of that first function as a parameter. For example: CREATE ...
0
votes
0answers
25 views

Cannot delete Postgresql constraints that were former unique keys

I have modified unique constraints. I am adding a field to every unique constraint. To do that, I need to drop the existing unique constraints and create the new ones. I am trying to delete foreign ...
1
vote
1answer
28 views

Postgresql log rotation

In postgresql, I'm using the following configuration to maintain my weekly logs: log_truncate_on_rotation = on log_rotation_age = 1d log_filename = 'postgresql-%a.log' log_rotation_size = 0 #just ...
0
votes
1answer
20 views

postgresql statement taking ages to cancel in pgAdmin III

I was running a query in pgAdmin III and it was taking a long time. After 9 minutes I decided to cancel it using the stop button. The UI stated that it was cancelling and stayed in that state for ...
1
vote
0answers
25 views

Windows Domain User with Admin privileges cannot access a drive with full permissions in psql

I have installed the standard PostgreSQL 9.5 from Enterprise DB with a domain-level postgres user who has domain-level admin group membership. I have explicitly added full permissions for this user to ...
5
votes
2answers
140 views

Optimizing “WHERE x BETWEEN a AND b GROUP BY y” query

CREATE TABLE test_table ( id uuid NOT NULL, "RefId" uuid NOT NULL, "timestampCol" timestamp without time zone NOT NULL, "bigint1" bigint NOT NULL, "bigint2" bigint NOT NULL, "int1" integer ...
0
votes
0answers
24 views

Create custom JSON response from PostgreSQL table

Consider the following table: CREATE TABLE IF NOT EXISTS trips ( trip_id SERIAL PRIMARY KEY, start_location TEXT REFERENCES locations(label), end_location TEXT REFERENCES locations(label), ...
0
votes
0answers
21 views

BDR Syncing Logs

I am making use of 2ndquadrant's BDR. Syncing always worked smoothly except for one day when I would make data changes on node 1. The data was confirmed to be on the node and committed successfully ...
1
vote
1answer
32 views

Median absolute deviation for time series outlier detection in Amazon Redshift

Context I am tasked with trying to detect outliers in time series data in an Amazon Redshift (PostgreSQL) system. Also known as a public holiday detector around the office. The method I have been ...
1
vote
1answer
22 views

Postgres latency issues during memory compaction on EC2 (Ubuntu 14.04)

We've recently upgraded our EC2 instance that hosts our Postgres database to an i2.8xlarge with 244GB of memory (this is to utilise the large amounts of ephemeral storage it comes with). Since ...
0
votes
1answer
35 views

Database extensions vs migrations [closed]

When using of database specific features (instead of 3rd party solutions) is justified, especially on the beginning stage of the project? Example: Postgres has full-text search, which do not support ...
3
votes
1answer
18 views

What can I do to monitor “out of shared memory” issues?

I'm new to database administration and I would really like some advice on what tools I could use or what I could do to monitor "out of shared memory" issues. I've been seeing these "out of shared ...
1
vote
1answer
47 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 ...
2
votes
1answer
80 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) ...
2
votes
0answers
30 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
46 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
25 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 ...
0
votes
1answer
22 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 ...
-1
votes
2answers
83 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
67 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 ...
1
vote
0answers
71 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 ...
1
vote
2answers
54 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
1answer
50 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 ...
0
votes
1answer
26 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
vote
2answers
32 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 ...
1
vote
0answers
21 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 ...