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

Postgresql unique history

I am running several commands, some repeatedly, from the postgresql command line tool. In Bash, the history function keeps one command for each set of sequential identical commands, it seems that ...
1
vote
0answers
18 views

Create a grouping based on chains of pairs

Say I have two tables: CREATE TABLE datum ( datum_id SERIAL PRIMARY KEY, datum_text TEXT NOT NULL ); CREATE TABLE datum_pair ( datum_id1 INTEGER NOT NULL REFERENCES datum (datum_id), ...
2
votes
3answers
94 views

Unknown returned type in PostgreSQL query

The following query works: SELECT a, b FROM unnest(ARRAY[(1,2), (3,4)]) AS t(a integer, b integer); a b _ _ 1 2 3 2 However, I wasn't able to use a different column type such as varchar(255): ...
3
votes
1answer
26 views

Circular dependency in schema or boolean with CHECK?

Let's say I have the following tables: create table repositories ( id serial primary key, name varchar(255) not null ); create table remotes ( id serial primary key, url varchar(255) ...
0
votes
0answers
20 views

Alternative when IN clause is inputed A LOT of values (postgreSQL) [migrated]

I'm using the IN clause to retrieve places that contains certain tags. For that I simply use select .. FROM table WHERE tags IN (...) For now the number of tags I provide in the IN clause is around ...
1
vote
0answers
19 views

how to get a sum total of each column and row - postgresql [Crosstab or Case]

I have to count the records according to the criterion, they are much more filters but I have summarized. I could use either of two ways. 1# SELECT * FROM crosstab('SELECT dim_espacio.desc_estab, ...
2
votes
0answers
24 views

Postgres Streaming Replication: How to re-sync data in master (with standby) after failover

I am absolutely new to postgres 9.1 replication mechanisms. I have managed to set up streaming replication between a master and standby postgres (Both have x number of records). At one point when ...
3
votes
2answers
42 views

Is copy/rename a good way to improve temp table performance?

An application I am working with contains this sequence of queries at the end of constructing a large temp table; I believe it is an attempt to improve performance by pruning dead tuples - create ...
1
vote
1answer
20 views

pg_dump does not honor -n

I'm using pg_dump (9.2 database), to extract one schema and load this into another database (9.4). well, here's the command: pg_dump -f file.sql -F plain --inserts -n schema database Ok. My ...
2
votes
0answers
10 views

Postgres to Postgres foreign data wrapper, for tables based on queries

I have a db with sensitive data in a secure server, and a set of anonymization queries that I'd like to create foreign tables based on in a less secure server. The Postgres native wrapper only ...
0
votes
0answers
13 views

can't connect to remote postgresql database

I'm trying to connect to a remote psql database. Before I added the pg_hba.conf entry with the client's IP address, I was getting an error message : xdev@xdevbox:~$ psql -U postgres testdb -h ...
1
vote
2answers
55 views

Can I configure the PostgreSQL locale so that it ignores umlauts in ILIKE queries?

I'm using PostgreSQL to look for city names matching a user-entered string. I'd like to be able to match a row with the name "München" (German for "Munich") if the user types "Munchen". In other ...
0
votes
0answers
23 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
29 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
14 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
58 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
62 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
30 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
18 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
99 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
22 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
28 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
11 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
29 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
43 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
13 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
15 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
32 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
50 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
62 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
42 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
45 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
58 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
17 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
41 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
21 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
55 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
36 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
27 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 ...