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
2answers
40 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 ...
2
votes
1answer
413 views

Is there a psql equivalent of bash's reverse-search-history?

I am very fond of bash's reverse-search-history (C-r) (command-line feature): Search backward starting at the current line and moving ‘up’ through the history as necessary. This is an incremental ...
1
vote
2answers
95 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
0answers
7 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
0answers
4 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 ...
0
votes
1answer
21 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( ...
2
votes
0answers
8 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
7 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
650 views

How do I use an Ispell dictionary with Postgres text search?

Postgres can use Ispell-compatible dictionaries in text search, but does not provide the required files.
1
vote
1answer
27 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 ...
1
vote
1answer
53 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 | ...
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
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 ...
2
votes
1answer
54 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, ...
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 ...
1
vote
1answer
367 views

How to make pg_dump skip extension?

This is on 9.3 but I can remember simillar things happening since 7.x. So I create database and install plpgsql extension into it. Later on I create a pg_dump and before restoring it into databse I ...
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 ...
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?
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 ...
2
votes
5answers
208 views

How to determine if a postgres database needs to be vaccumed?

Given a PostgreSQL database what command can be used to determine if the database needs to vacuumed? UPDATE I am working with client who is evaluating postgres but I am not a DBA, I just happen to ...
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
1answer
18 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 ...
1
vote
0answers
10 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 ...
3
votes
1answer
42 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
1answer
209 views

Postgres DB - Commit xlog files after server crash

After a server crash, it would seem that all transactions going back a number of months are not showing up in the live Postgres DB. Investigation has found all xlog files that does contain the data ...
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 ...
18
votes
3answers
38k views

Granting access to all tables for a user

I'm a new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, DELETE privileges on a low privileged user and enable those grants to apply to all ...
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
1answer
1k views

How to edit `postgresql.conf` with pgAdmin?

How does one edit the 'postgresql.conf' file with the pgAdmin app? With pgAdmin 1.18.1 on Postgres 9.3.x, when I choose File > Open postgresql.conf I get a file picker dialog. And, yes, I'm connected ...
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
1answer
444 views

Sync offline PostgreSQL server

I am new to PostgreSQL. I am looking for a solution wherein 2 mirror images of a database sit on a local and remote server. All the work is done on the local server and at periodically synced with the ...
16
votes
4answers
10k views

Measure the size of a PostgreSQL table row

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
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 ...
0
votes
1answer
52 views

How do I select the value with SERIAL data type in postgres [on hold]

I want to know how to select values from a column that has SERIAL data type. I already try to do that but the output is like this 'Resource id #3', and not just '3' I use SERIAL so I dont have to ...
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 ...
2
votes
2answers
2k views

pgpool replication not allowing md5 authentication

I have installed both pgpool and postgresql via apt. In pgpool.conf, I have made the changes necessary to use replication mode, as well as my two postgresql databases (currently all running on the ...
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, ...
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 ...
1
vote
1answer
355 views

Designing a High Score/Leaderboard table

I'm looking for input on how to design a highscore table for a game I'm creating. The database I'm using is postgres. That's not really something I can or want to change (unless there's very good ...
3
votes
1answer
555 views

Index for finding combinations of attributes in JSON array

Based on this related question on SO. This works fine if you only want simple matches. Suppose tracks have both Artist and Title field in the JSON data. So we have something like: INSERT INTO tracks ...
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 ...
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
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 ...
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 ...
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 ...
3
votes
2answers
4k views

Configuring PostgreSQL for read performance

Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow. The primary key (constraint) structure is ...
0
votes
2answers
399 views

What is the default size of text field in Postgres?

In the official documentation (http://www.postgresql.org/docs/9.1/static/datatype-character.html), it states text field is variable unlimited length. I wonder what is the default size of this field ...