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)

0
votes
0answers
5 views

Pgsql Function to create multiple child tables inherited from parent tables

I need help with pgsql function to create multiple child tables concatenated with date. Inherited from parent tables. I am using the following queries -- Setting date range SELECT CAST( ...
1
vote
1answer
26 views

Postgres knowing which where column returned positive result

Assuming we have a query like: select * from table where a=1 or b=2 or c=3 and the where conditions met are a=1 and c=3 Would it be possible to know in the query (in the output) that a and c are ...
0
votes
1answer
28 views

Transfer data to New Laptop [on hold]

I am completely new to PostgreSQL My data is in the directory c:\postgres.......data and the database name is GXPGRAN. I need to copy this to a new laptop. Can somebody please give me all the steps ...
0
votes
0answers
13 views

In Green Plum(GP )how to add a column with same data for all the rows described in select query? [on hold]

Eg, Oracle: select p.prem_num AS premise_number, 'METER INTERRUPTIONS' AS outage_type, 'NA' AS fdr_num, '100' AS met_percent from fpludw.premise p; Here data is presenr for ...
1
vote
1answer
51 views

Group by maximum consecutive row

I have data like the following: id | src_ip | dst_ip | port | ----|----------------|--------------|------| 256 | 192.168.61.200 | 10.75.64.222 | 80 | 257 | 192.168.61.200 | ...
2
votes
1answer
49 views

Slow PostgreSQL query

I'm building a search interface for a web site that currently searches through approximately 2 million pieces of real estate with approximately 60 million attributes (address, city, state, ZIP, beds, ...
1
vote
0answers
27 views

Make a backup file or save a DB in PostgreSQL

I have deployed for my data base in production a new groups of users; An admin user with full access to the DB ; A read only user ; A super admin user to manage all the DBs ; The matter is for ...
0
votes
1answer
20 views

how do I configure postgresql to find the pg_controldata command [on hold]

I am trying to execute the pg_controldata command, I got the error command not found. Then I checked: ls -l /usr/lib/postgresql/9.4/bin/ | grep "control" -rwxr-xr-x 1 root root 25960 feb 5 18:24 ...
0
votes
0answers
17 views

Why postgresql-9.4 puts logs into syslog and server log [on hold]

I am tracking the postgresql-9.4 logs. Well, I check that postgresql is putting the logs in two places: /var/log/syslog /var/log/postgresql/postgresql-9.4-main.log Then, I suppose that it is a ...
1
vote
2answers
90 views

How to know how long my server is started

I am working with postgresql-9.4 and for support and monitoring tasks I need to know how long my server is started? Is there any function or way to know this? Is there an sql way to know it? I ...
1
vote
1answer
16 views

postmaster.opts in postgresql 9.3?

I am using PostgreSQL 9.3, but the book I am reading "Beginning Databases with PostgreSQL From Novice to Professional" is using PostgreSQL 8. It mentions there is a postmaster.opts file which passes ...
0
votes
0answers
27 views

FOR LOOP in postgres [on hold]

I am not able to write a syntactically correct for loop in postgres that returns any data. DECLARE zipcode TEXT; BEGIN FOR cursorzip in (SELECT zip FROM myziptable) LOOP zipcode := cursorzip SELECT ...
1
vote
0answers
8 views

Can I export a PostgreSQL database from Heroku and Import as a PostGIS database in Digital Ocean?

Disclaimer: This may be a dumb question. This is honestly way over my head... one of the pitfalls of using Heroku for too long. I have a PostgreSQL database on Heroku. Now I'm migrating to Digital ...
0
votes
0answers
28 views

Compare timestamps [on hold]

I am using psql 9.4.1 amd am looking for a way to select from a table if a timestamp1 is smaller than another timestamp2. I've tried select * from timestamps where timestamp1 < timestamp2; but ...
2
votes
1answer
15 views

Postgresql assign mutliple row query result to variable and continue execution

I'm trying to create a script that calls several stored procedures in PostgreSQL and print the result of some of them: CREATE OR REPLACE FUNCTION script( vtoken character varying) RETURNS void AS ...
3
votes
1answer
41 views

PostgreSQL partial index unused when created on a table with existing data

In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: ...
0
votes
0answers
12 views

Storing table history - is this a good use case for Postgres's table partitioning / inheritance?

I currently have a products table which stores product information (title, description, tags, manufacturer, etc.) for a website. I would like to store historical information of changes to this table ...
0
votes
0answers
12 views

Postgres: Migrate data with FKs to multi-database

I currently have a single DB with data from a number of clients. I have been tasked with writing a script that splits the data up into separate DBs per client (each DB will have the same schema). For ...
0
votes
0answers
15 views

Heroku Postgres Follower

I'm currently looking at creating a follower db of a live production database. The follower will be used for analytics purposes only. And will not be inserting any data - if it did then it would be by ...
1
vote
0answers
14 views

PostgreSQL 9.0 live migration/replication with native/bucardo between multiple architectures

I'm writing here because I'm in desperate need of a stable and good solution for resolving a issue we are facing. We have an old PostgreSQL server running on RHEL 4 i386 with PostgreSQL 9.0 ...
0
votes
1answer
20 views

PostgreSQL column for year

I want to save the year in a PostgreSQL database - just the year. How should I create this field? There is a lot of information out there how I can extract the year from a date field, but not on how I ...
0
votes
1answer
31 views

How to load 100k + Records into progres database from a CSV file?

Our project needs to load 100k+ rows into a Postgres table. We have a huge .CSV file with all these 100K+ rows and we need to load this data into a table effectively and quicker (may be in the form of ...
1
vote
2answers
49 views

Many columns vs few tables - performance wise

Yes, I am aware that data normalization should be my priority (as it is). I've got a table with 65 columns storing vehicle data with columns: used_vehicle, color, doors, mileage, price and so forth, ...
1
vote
2answers
61 views

Normalization of library book table with multiple authors

I have a database structure with two tables: The first one is called Author, has two columns (AuthID, AuthName) and its Primary Key is (PK_AuthID). The second one is called Book and has three ...
0
votes
0answers
32 views

How to retrieve data from database related to the authenticated user (after login) [closed]

I have an authentication form in my jquery mobile app and I wanna be able to authenticate users using a wcf webservice that'll connect to my postgresql database and check their credentials. Now my ...
0
votes
0answers
16 views

ETL options for mapping SQLite to PostgreSQL using python [closed]

I am just learning about database manipulation. I need to map an SQLite database to PostgresSQL, they have different data models. I have studied them and generated a diagram to map one model to the ...
1
vote
0answers
38 views

How to create index on a postgres table as fast as possible?

I have a large database import (100GB) on a postgres table without indexes. After the import I want to create the index for the queries as fast as possible. No data is accessed on the table as long as ...
2
votes
2answers
42 views

Postgres Slow Queries - Autovacuum frequency

We've noticed the performance of our platform drop in recent weeks so I've run the following: select relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables ...
3
votes
0answers
17 views

Restoring Postgres fixtures into a different database with different premissions

I've got an application called BigSense that uses two database users/rolls. One has full permissions and is used by my application to create/adjust table definitions. Then the application starts with ...
4
votes
2answers
57 views

Optimizing queries on a range of timestamps (one column)

I am using Postgres 9.3 through Heroku. I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ...
1
vote
0answers
16 views

How to improve performance when joining against coalesce

In an effort to remove a caching layer from our application, I'm building a number of views to encapsulate the different ways a user can have access to a piece of content - one per "rule" of access. ...
0
votes
0answers
13 views

Column Datatype Conversion Question [migrated]

I have a varchar column with text formatted like so "Sun Aug 07 17:43:55 +0000 2011" How would I convert that column in PSQL to a date datatype?
0
votes
0answers
24 views

To split tables is a good idea?

I have a table with more than 40 million rows and I want to know what is the best way I can optimize it for searches. The search will happen by the person's document number, and these document numbers ...
2
votes
1answer
40 views

how to use index to speed up sorting in postgres

I am using postgres 9.4. The messages has the following schema: messages belongs to feed_id, and has posted_at, also messages can have a parent message (in case of replies). ...
1
vote
0answers
18 views

postgresql + pgbouncer, idle server and high cl_waiting

I have such a setup: OpenSUSE 13.1 64-bit Linux kernel 3.11.10 PostgreSQL 9.0 + PgBouncer 1.5.4 (libevent-2.0) 16 core AMD, 128 GB RAM PostgreSQL config: max_connections = 1500 shared_buffers = ...
1
vote
2answers
54 views

Does MySQL support querying for the current running transaction id

PostgreSQL supports retrieving the current running transaction id using a query like this: select txid_current(); Does MySQL have any such equivalent?
4
votes
1answer
35 views

Minimal grants for readonly single-table access on PostgreSQL

Following is a list of commands that seems to work to create new user (login) and grant readonly access for one specified table on PostgreSQL. Let's assume that these commands are executed on login ...
0
votes
1answer
25 views

postgresql rename a foreignkey after renaming a table

I renamed a table, and apparently a foreignkey in it doesn't get changed, and causing problems, Although when I \d error I get: "error_test_id_fkey" FOREIGN KEY (test_id) REFERENCES test(id) ON ...
0
votes
0answers
20 views

Login Roles not showing on privileges tab [on hold]

I'm migrating to a new server, created my login roles using plain SQL queries on PgAdmin. Now when trying to grant privileges on database to the created login roles, they don't show up as selectable ...
1
vote
0answers
37 views

Most efficient way to bulk insert or upsert data into multiple related tables

Given a PostgreSQL 9.4 schema with 3 related tables: CREATE TABLE a ( id BIGSERIAL PRIMARY KEY, a_name VARCHAR(80), a_field INTEGER ); CREATE TABLE b ( id BIGSERIAL PRIMARY KEY, b_name ...
0
votes
0answers
25 views
+50

Log large queries with PGbouncer

Is there a way to ask pgbouncer to log any query that generates a result set larger than, say, 5MB?
-1
votes
2answers
32 views

How to use aggregate function and group by with inner join? (Postgres 9.3) [closed]

I would like to select "extra" payments made in association with normal appointments for a one week period and SUM them up by contractor when calculating payroll. Here's what I attempted: SELECT ...
0
votes
0answers
26 views

How to catch code of error which occured inside plv8 function?

In plv8 function I am executing (plv8.execute(...)) INSERT INTO ... query which violates foreign key constraint. However pg client receives following (internal) error instead: { [error: Error: ...
2
votes
1answer
125 views

PostgreSQL function definition “syntax error at or near +” [on hold]

Suppose a simple query: (SELECT MAX(timestamp) FROM events e WHERE e.id < some_id) + ((SELECT MIN(timestamp) FROM events e WHERE e.id > some_id) - (SELECT MAX(timestamp) FROM events e ...
0
votes
0answers
18 views

How to move a specific Postgres database to different harddrive?

I have my postgres db running on harddrive d:\, and also all the tables are placed there by default. Now I want a specific table to be save to a different drive (say f:\). The table is not yet ...
1
vote
1answer
20 views

missing WAL file on standby server following a backup

I seem to have an intermittent problem with my postgres backup/restore to standby Postgres, is version 9.2 running on red hat 6 enterprise i have a production server (PROD1) and a hot standby ...
1
vote
1answer
29 views

Is it possible for a client to kill a long-running Postgres query?

A client makes a request to a Postgres database for data. The database is too slow to respond. Is it possible for the client to send a CANCEL request or something to tell Postgres to stop working on ...
1
vote
2answers
26 views

What to do with WAL files for Postgres Slave reset

So last night our PG Slave ran out of space after a lot of reconfiguring of disk space, new drives etc its now reporting the following error: FATAL: could not receive data from WAL stream: FATAL: ...
0
votes
1answer
15 views

Postgres 9.3: How to update row that has max value of a timestamp in addition to other selection criteria?

I want to allow users to cancel recurring subscriptions. The easiest way to do this given the other infrastructure I've got is to set the recurring field = 0 for the user's latest appointment ...
1
vote
2answers
26 views

Can I select data inserted in the same uncommited transaction?

Maybe this is a dumb beginner question, but I cannot find an answer anywhere. Everywhere I read about the Transaction Isolation which solves the visibility of data within the concurrent transactions. ...