PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

1
vote
0answers
14 views

MATLAB® export double numbers into PostgreSQL by `datainsert` (of Database Toolbox)

code I'm trying to pass results into postgreSQL row by row; they have been calculated in MATLAB. Here it follows the list of matlab data type: nomi 197x1 cell eps 197x1 double Re 197x2 ...
0
votes
0answers
10 views

Postgresql FDW oracle out of memory

I have a linux box with centos 6.7 and postgres 9.3.9. I have a foreign data wrapper throught an oracle 11.2 box and a jdbc driver. It's working fine since several months, but yesterday I had to ...
3
votes
1answer
35 views

GROUP BY uninterrupted sequence of logs for same location

I'm having a hard time describing my problem or finding it via Google. I want to be able to group rows based on location and employee, but the group should be split if a row with the same location ...
0
votes
0answers
7 views

Multiple Tables as source and Target in Talend Open Studio

Loading data from mysql to postgres through Talend is working for single table loading. How to load whole db loading(multiple tables). can anyone tell me?
2
votes
2answers
53 views

Extremely slow query on indexed column

I'm getting a extremely slow query on an indexed column. Given the query SELECT * FROM orders WHERE shop_id = 3828 ORDER BY updated_at desc LIMIT 1 explain analyze returned: QUERY PLAN ...
2
votes
1answer
75 views

Postgres Count with different condition on the same query

EDIT Postgres 9.3 I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2 The current query is working fine which looks like this: Basically it is a 3 table ...
1
vote
0answers
18 views

Connection Failure for postgres at Talend Open Studio

I have tried to connect postgresql database in Talend open studio. After giving login, password, port and server, i have issued check. Its saying "Connection Failure. You must change the DB Setting". ...
0
votes
0answers
12 views

RDS raising unwanted PostGIS NOTICEs

I have an Amazon RDS instance that is producing unwanted NOTICEs. I have never seen this behaviour before. mydb=> select current_setting('client_min_messages'); current_setting ...
0
votes
0answers
11 views

Deleted data from pg_authid

This is my first post on this site. I've recently by mistake deleted all data from pg_authid. As a result i couldn't log into database cluster (no user 'postgres'). I've managed to restore database ...
1
vote
0answers
7 views

postgresql: deleting from a view

In Postgresql 9.4, we're doing a view as a UNION of to tables A and B, and we'd like to have a single DELETED N ROWS message when we 'delete' the records in the underlying tables (via a a trigger on ...
0
votes
1answer
17 views

Restore Postgres Database on New Machine

Trying to restore a postgres database on a new Macbook Pro. I recreated the database using createdb dbname then tried to restore the old database using psql dbname < dbname_dump.bin but ended up ...
0
votes
2answers
29 views

Can I make postgres more verbosely tell me what it's doing?

I'm trying to use ALTER TABLE on a live db (not in production, just poking around at a dev box and trying to learn some things) Here is the command I'm trying: psql -h $IP -p 5432 -U $DBUSER -d ...
3
votes
1answer
26 views

PostgreSQL table partitioning problem

I partitioned my table day by day. The child tables are look like points_20150830. Every day, I run a cron job to create table and I insert records without using any trigger function. I insert ...
5
votes
0answers
44 views

ANALYZE strategy for big tables in PostgreSQL

In our PostgreSQL 9.4.4 database we have a table that receives around 600k new records each day. Each day, nightly, we are performing some ETL exports from the table. If it has not been analyzed ...
1
vote
0answers
18 views

Postgres 9.4 on kubuntu 14.04 won't start

I've had postgres9.4 running without incident for around 5 months. Today after a restart I can't get postgres running. When I open pgAdminIII and try to connect to my server I get "Server doesn't ...
1
vote
1answer
45 views

What kind of database should I use?

I'm designing a new system and I'm wondering what kind of DB should I use for my needs. I have a tree structure of nodes, where each node has historic values. For each historic value it should hold 2 ...
1
vote
1answer
29 views

Can I do VACUUM FULL to pg_largeobject table?

I have two tables (table1, table2) in a Postgres 9.1 database. Both having oid type. Each table 1 million records. And pg_largeobject table size is around 40GB. I have removed 0.9 million records from ...
4
votes
1answer
63 views

Why is a gin index on a jsonb column slowing down my query and what I can do about it?

Initialize test data: CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE TABLE docs (data JSONB NOT NULL DEFAULT '{}'); -- generate 200k documents, ~half with type: "type1" and another half with type: ...
1
vote
0answers
22 views

Replication of specific tables using Postgres 9.4.4 and Amazon Web Services

I have currently setup a Postgres 9.4.4 RDS instance on Amazon Web Services. On this instance I hold 20 tables. In addition I also have Postgres 9.4.4 installed on my local server (OS: 64-bit Linux) ...
1
vote
1answer
28 views

PostgreSQL 9.3 is not returning existing rows with Ruby on Rails or Squirrel

I dumped an existing PostgrSQL database (generating "Insert" statements), and imported it on another computer. The source runs Debian (UK) OS, the target Mint (FR) OS. For some tables, when I try to ...
1
vote
0answers
28 views

PostgreSQL 9.4.4 Crashing

We are having 9.4.4 version running on linux( PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit) which is crashing unexpectedly and coming ...
-1
votes
0answers
24 views

I want load the Jason File in my local desktop , that i want to load in postgres [on hold]

I have .jason file in my mac book, that I want to load in postgres server, Kindly navigate me how to do it in simple way, I'm not jason script user, please describe me each and every step very ...
0
votes
0answers
34 views

Error: File '' couldn't be removed (error 2: No such file or directory)

I get the following pgAdmin III error every time I restore a database, using pgAdmin III: Error: File '' couldn't be removed (error 2: No such file or directory) See the pgadmin log. My ...
0
votes
1answer
40 views

PostgreSQL failed on Select

I am trying to use a result from a select for another select: DECLARE _port int; BEGIN select alter_port into _port from database where ....; select data inot data_list from database where ...
0
votes
0answers
21 views

Postgres New trigger being blocked.

Running Postgresql 9.3 We have a trigger that was being blocked by select for update statements, so it couldn't be added. We took the DB down and kicked everyone off (changed network port). When we ...
4
votes
2answers
15 views

Improve performance for order by with columns from many tables

Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two ...
0
votes
1answer
13 views

How to use a value from insert in conditional insert in postgres

insert into addresses(street, city, user_id) select 'street1','LA', 2 where exists (select * from users where users.id = $$$$$ AND users.storeaddress='true')) I want to insert address for a ...
2
votes
2answers
36 views

Calculating the percentage change from the previous row

I just entered the world of window functions. I have a similar problem to solve as in this question. I have this table: The last column (%Grown) is given by: -(1 - price of row 2 / price of row ...
0
votes
1answer
28 views

How to return multiple columns (each a composite type) in plpythonu

I have created a plpythonu function that should return a table with multiple columns. At present, it returns a single column with multiple components. I came across this answer which is A) a little ...
1
vote
1answer
69 views

Matching data in two tables

I'm teaching myself SQL and I ran into a question that may be related to joining tables. I'm just a bit unsure. Say, you have two tables, one listing the customer's creditcard_number, and one listing ...
0
votes
1answer
17 views

Looks like a postgres backup/dump structure (A sort of a forensic/data archeology question)

I was given a compressed folder that contains number of subdirectories, and, among others, directories named .pgdir with number of numerically sequenced files in a <9999>.dat.gz format and one in a ...
2
votes
1answer
61 views

Trigger function using current row and current table name as variables (final part)

Like detailed in my first question I have a Postgres 9.1 database with multiple tables that have the exact same column names, they only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... ...
0
votes
1answer
52 views

Trigger function using current row and current table name as variables

Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB. They only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... 26 tables (from a to ...
0
votes
1answer
23 views

Store Procedure for adding cascade

I have a DB with 180 tables, and I was doing a logical deletion, now I need to do some testing with physical deletion. Is there a way to alter all my tables and to drop their fk's and add then add ...
-6
votes
1answer
46 views

Postgres Dump and Restore on Linux Terminal

I need to take a dump in postgres db and to be restored on some other server. please tell me the command in Linux with compressed and encrypted format.
1
vote
3answers
58 views

PostgreSQL trigger function that selects from %current% table

I have multiple tables that have the same column names, they only vary in their column values, like: tbl_log_a tbl_log_b tbl_log_c ... 26 tables from a to z. Each table has a trigger that calls a ...
2
votes
2answers
31 views

Grouping in long tables

My application is running over Postgresql 9.4 and I found a problem and I don't know how can I fix it. I'm runing VoIp application, and we have a few tables that are too long (>60M of rows) with all ...
1
vote
0answers
30 views

PostgreSQL trigram GIST vs. GIN indexes

I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes. Initially, I started to use GIN ...
5
votes
3answers
70 views

Slow query performance due to temporary file?

Here is the query: SELECT "products".* FROM "products" WHERE (status > 100) AND "products"."above_revenue_average" = 't' AND ("products"."category_id" NOT IN (5)) ORDER BY ...
8
votes
3answers
134 views

Constraint to enforce “at least one” or “exactly one” in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | ...
0
votes
0answers
17 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 ...
0
votes
1answer
20 views

Convert two columns to json

I have a json column with a json array in the form of [{key : "key", value : "value"}, {key : "key2", value : "value2"}]  that I wish to convert to a new form {"key" : "value", "key2" : ...
1
vote
1answer
21 views

Passing array from one plpgsql function to another

I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new ...
0
votes
0answers
17 views

Using pipelined order by while fetching only first ten rows

PostgreSQL 9.2 I have the table table: id user_id amount PK integer integer and I need to fetch the first 10 rows as follows: SELECT user_id, SUM(amount) s FROM table GROUP BY ...
2
votes
1answer
29 views

What exactly is Psycopg2?

This question is for a report I'm writing for school. Technically speaking, what is Psycopg2? In this tutorial they refer to it both as an "adapter" and "driver" Psycopg2 is a DB API 2.0 compliant ...
3
votes
0answers
39 views

How to deal with large offsets in select?

Table jtest with 200k rows, each row contains jsonb { id: "<uuid>", key: <index> } (<index> is integer 1-200k incremented per each row). There is also btree index on data->'key'. ...
2
votes
1answer
31 views

PostgreSQL: ERROR: cannot execute CREATE DATABASE in a read-only transaction

I installed PostgreSQL and and its GUI, pgAdmin III. Setup already had a default database postgres. When I am trying to create a new table or database, it is showing an error: ERROR: cannot ...
1
vote
1answer
32 views

Calculate column running total

Given the following schema and dummy data: CREATE TABLE transactions ( id SERIAL PRIMARY KEY, memo VARCHAR(255) NOT NULL, amount INT NOT NULL, tx_date DATE NOT NULL ); INSERT INTO ...
1
vote
1answer
42 views

pg_restore: [archiver] did not find magic string in file header

I'm using PostgreSQL 9.1 and want to restore backup files generated with pg_dump: sudo pg_dump -h 127.0.0.1 -U postgres --clean --inserts -E UTF8 -f out.sql database_name This command generates a ...
0
votes
1answer
39 views

Postgresql pg_basebackup from slave didnt save WAL's

Good day please tell me whether it is possible to shoot a backup with utility pg_basebackup from the slave server (where of streaming replication with the help of this utility pg_basebackup) I have ...