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)

2
votes
1answer
71 views

PostgreSQL doesn't log

I'm using ArchLinux and PostgreSQL 9.4.4 and have enabled the logging in the config file: $ sudo egrep -v "^[[:blank:]]*($|#|//|/\*| \*|\*/)" /var/lib/postgres/data/postgresql.conf max_connections = ...
2
votes
1answer
41 views

Can column uniqueness be commutative?

I have a table in a Postgres DB where col1 and col2 are foreign keys referring to the same column. I want only unique combinations of (col1, col2), i.e. if (1,2) is entered then (2,1) should be ...
0
votes
0answers
34 views

Database migration stops halfway through

I've had issues trying to use PostgreSQL so changed my application to use MYSQL. But, when I run rake db:migrate I get the following message before the migration stops: -- PostgreSQL database ...
4
votes
2answers
223 views

How to speed up ORDER BY sorting when using GIN index in PostgreSQL?

I have a table like this: CREATE TABLE products ( id serial PRIMARY KEY, category_ids integer[], published boolean NOT NULL, score integer NOT NULL, title varchar NOT NULL); A product ...
1
vote
1answer
63 views

Index on column with data type citext not used

In PostgreSQL 9.4, with following schema: CREATE TABLE people ( id INTEGER PRIMARY KEY, name TEXT, junk CHAR(1000) ); INSERT INTO people(id, name) SELECT generate_series(1,100000), ...
0
votes
1answer
114 views

Kibana-like tool for PosgreSQL [closed]

I'm looking for a tool that could browse and visualize data from db like Kibana does. I'd prefer a native Posgres tool to any that transfers data from a db somewhere else. I know that rivers for ...
1
vote
0answers
31 views

Creating a unique index on a materialized view with no unique columns?

I am using Postgres 9.4. I have created a materialized view from one of my tables and I want to be able to refresh it concurrently. I know from the documentation that in order to do a concurrent ...
1
vote
1answer
29 views

Select Distinct on subset of columns, name different set of columns to return

I am using PostgreSQL and have two tables: Table_A colA | colB | colC | colD | colE | colF | colG | colH | colI | colJ Table_B colA | colB | colC | columnD | colE | columnF | colG | colH | colI | ...
0
votes
1answer
29 views

How to use PostgreSQL bit_or function to aggregate column?

I wish to use PostgreSQL's bit_or function on several rows of a single column of my table, but I can't figure out the proper usage for this. Suppose I have bitwise flags in my column flags, and the ...
0
votes
1answer
38 views

Use PL/pgSQL in PostgreSQL outside of a stored procedure or function?

I'd really like to use an IF, ELSE construct and execute multiple statements within the IF and ELSE blocks, but I don't wish to save my commands to the database as a stored procedure or function. Can ...
1
vote
1answer
56 views

Add a new collation to a Postgres database

I have these collations: postgres=# select * from pg_collation; collname | collnamespace | collowner | collencoding | collcollate | collctype ...
2
votes
1answer
38 views

`chown -R` equivalent for PostgreSQL

We occasionally have the problem that, when upgrading some software and database, inexperienced (or overworked, not excluding myself) sysadmins (we don’t have separate DBAs except for Oracle) run the ...
2
votes
2answers
89 views

How to review Database schema?

I am green been software engineer, my team lead give me a task in PostgreSQL database, I need to determine the relationship of the all 405 tables, but the problem is the database doesn't have any ...
-1
votes
3answers
85 views

Why does connecting to '127.0.0.1' work whilst 'localhost' fails?

I have noticed that although the listening host in postgresql.conf is localhost connecting to a database via psql fails unless the host is changed 127.0.0.1. I have also noticed MySQL display the ...
1
vote
2answers
228 views

PostgreSQL : avoid SUM over same values multiple times

I've a student table and a table of scores in various subjects. I want to sum score1 & score2 for each student across all subjects and then add the bonus for each student. CREATE TABLE student ( ...
2
votes
1answer
30 views

Persistant inserts in a UDF even if the function aborts

I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an ...
0
votes
0answers
38 views

Is there an equivalent in postgresql to MySQL's “safe update mode” or “safe delete mode”?

Is there a configuration or flag in PostgreSQL I can turn on to disable bulk updates and bulk deletes, similar to MySQL's "safe update mode" or "safe delete mode"?
5
votes
2answers
111 views

Avoid unique violation in atomic transaction

Is possible to create atomic transaction in PostgreSQL? Consider I have table category with these rows: id|name --|--------- 1 |'tablets' 2 |'phones' And column name has unique constraint. If I ...
1
vote
1answer
112 views

stored procedure vs. function in postgresql and which is better for executing dml statements

I'm using postgresql 9.3. If I want to add the following steps and call these steps as a routine, would it be better to use a function or stored procedure? My basic steps are: RENAME TABLE ...
0
votes
1answer
31 views

osm2pgsql: select relation member by role

I have a standard postgresql OpenStreetMap database that was filled with data via osm2pgsql. There are different tables for different types (points, lines, polygons, relations) that need to get ...
0
votes
1answer
28 views

PostgreSQL 8.4 replication of more than 100 tables

I am facing a problem with PostgreSQL version 8.4. I need to create an slave machine but when I look to all tutorials of slony or londsite its saying I will need to specify the tables that I want to ...
0
votes
4answers
66 views

Insert from Another Table + Removing Duplicate Entries in Postgres

I'm trying to insert data from one PostgreSQL table to another while removing duplicate entries but I'm having some trouble with the code. I have two tables I'm working with: mytesttable, which ...
0
votes
0answers
35 views

Postgres: Arrays instead of intermediate tables for many-to-many relations

I have a PostgreSQL 9.3.7 database scheme representing a directed graph of vertices and edges with some many-to-many relations. I'm only ever interested in getting all vertices that a given vertex ...
3
votes
1answer
175 views

Estimating the size (of the records) and the overhead in Postgres

Consider the following table in Postgres 9.4: CREATE TABLE t ( a1 bigserial, a2 bigint NOT NULL, a3 bigint NOT NULL, a4 integer, a5 timestamp with time zone NOT NULL, a6 timestamp with ...
1
vote
1answer
35 views

Syntax error in postgresql query [closed]

I am trying to migrate a sql query into PostgreSQL. After running the query I get this error: ERROR: syntax error at or near "(" LINE 20: ..._date is null or fcm.effect_end_date>=current_date()) ...
1
vote
1answer
25 views

Accurate PG database size for comparing representations

I have a Java/JDBC program that takes a sample file and imports the data into the database, shredding it across multiple relations. The program does this multiple times for several different ...
1
vote
2answers
29 views

Migrating PostgreSQL files to new server

We have a Win 2008 server that went down because of cpu failure. We were running parts of the Atlassian stack (JIRA, Confluence, Stash, and Bamboo) on it using PostgreSQL as the database. I now have ...
1
vote
1answer
124 views

How to reinstall PostgreSQL over an existing installation

I am trying to upgrade an installer created with NSIS that installs postgresql. It used to install postgresql 8.3 but we want to upgrade the DB to 9.4.4. The old version on of the DB used to be an ...
1
vote
1answer
48 views

Streaming replication and lag when performing VACUUM on master

My streaming replication standby server is showing a delay of a few minutes when vacuuming a large table on the master. What is the reason for the lag, and is there a way to reduce it? Both servers ...
7
votes
0answers
127 views

Why is query thousands of times slower with DESC?

I have a PostgreSQL database (AWS RDS) and ran the following queries in psql: livenet=> EXPLAIN ANALYZE SELECT * FROM outputs ORDER BY height LIMIT 10; Limit (cost=0.57..36.82 rows=10 ...
0
votes
0answers
34 views

Compare two databases

I want to compare two PostgreSQL database dumps. The output of pg_dump is not sorted. This makes tools like diff useless. Background: We update the way we set up a custom database. After our ...
1
vote
1answer
41 views

Pattern matching in PostgreSQL? [closed]

I have a string in which need to extract the date, Example: S01_XXX001_000_min_01_20150526T123440583.arc , S01_XXX001_000_20150526T123440583_min_01.arc based on the pattern like 'Numeric T ...
1
vote
1answer
70 views

Update staging data with production data in Postgresql

I'm using PostgreSQL (version 9.3) on my server and I want to update the data in my staging instance database with the data in my production instance database. Each database is controlled by a ...
1
vote
1answer
60 views

Does VACUUM FULL need ANALYZE

I'd be grateful for some insight into the VACUUM functionality within PostgreSQL 9.3. I read through the documentation and searched around a bit but could not find a definitive answer to this. I'm ...
1
vote
1answer
50 views

To find data in Multiple tables and delete it?

I am using a PostgreSQL server. Suppose I have 200 values in a master table that need to be deleted. There are 9 other tables referencing this master table. The other 9 tables not only reference the ...
2
votes
1answer
55 views

postgresql - how and why indexes are bigger than their tables

I'm using postgresql 9.3 and trying to understand how and why indexes are bigger than their tables. Sample output: database_name | database_size | table_name ...
0
votes
0answers
15 views

How can I create a copy of postgis db from one windows machine to another

I have postgres 9.3 database with postgis extension. I want to create a new database on different computer and import all the tables from the source database. In mssql it would be simple , just ...
0
votes
1answer
28 views

Backup-strategy for redundant data in postgresql-db.

I have a (postgresql-9.3)-table that contains images as a bytea-column as well as several other columns that contain extracted information from these images. (Some feature points, descriptors). These ...
1
vote
1answer
56 views

Is there a way to add a column to a table without affecting performance?

How do you add a column to a table in production without affecting its performance? Or is this impossible? I'm asking about Postgres.
2
votes
2answers
59 views

Calculate number of consecutive years (not gapless)

I have a request that wants to look at numbers of consecutive year buyers. For example, I have a table like this: Order_year, Contact 2015, 123 2014, 123 2013, 123 2011, 123 2010, 123 2015, 234 2014, ...
5
votes
1answer
74 views

Bulk insert M:N relation in PostgreSQL

I need to import data from an old database to a new one, with slightly different structure. For example, in the old database, there is a table recording employees and their supervisors: CREATE TABLE ...
2
votes
3answers
55 views

What schema for different relation from two tables to another?

There are three tables instructors, meeting_points and bookings. Here are the relation I want: 1 instructor has 0, 1 or n meeting_points 1 booking has 1 meeting_point 1 meeting_point can be linked ...
0
votes
2answers
47 views

Approach to take regarding a field that is frequently re-computed

I have a few fields with text data in a main table which is processed in a certain way to produce a processed version of that piece of text. Now, the way in which this text is processed is expected ...
0
votes
0answers
27 views

Replicating table level PostgreSQL 64bit windows

I need to replicate some tables on PostgreSQL 64bit for Windows (master -> slaves replication model) and I can't find any tool that can achieve that. Slony-I work very well for PostgreSQL 32bit for ...
2
votes
1answer
46 views

How do I prevent my query from needlessly generating rows it'll throw away later?

I have the following query: SELECT stream_points.time AS point_time, stream_points.value AS point_value, stream_predictions.value AS prediction_value, ...
2
votes
1answer
31 views

Querying on multiple joined rows

I have a table called visits that is structured like so: +----+--------------+-------------+ | id | name | visitor_id | |----+--------------+-------------+ | 1 | event1 | 1 ...
-1
votes
1answer
34 views

How to setup path to access psql from command line in Mac [closed]

psql does not work even path was set .bash_profile. Path was set export PATH=/Library/PostgreSQL/9.4/bin:$PATH in .bash_profile.
0
votes
1answer
35 views

Postgres lose xlog

I have 2 instances master-salve postgres run on AWS, the xlog folder is on ephemeral disk. The master instance was down, so I lose the xlog folder, Now, i'm trying to start up the master again and ...
3
votes
3answers
46 views

PostgreSQL GRANT ALL on mutliple tables using LIKE

I have more than 100 tables named public.test_* How can I easily GRANT ALL access to the user test to all that tables at once? I tried with: GRANT ALL ON TABLE public.test_* TO test; But it is ...
0
votes
1answer
28 views

Postgres INNER JOIN same table failing on CartoDB

I have a table with data which looks like this +----+------+-------+ | cp | year | count | +----+------+-------+ | 1 | 2000 | 10000 | | 1 | 2001 | 9000 | | 1 | 2002 | 9500 | | 2 | 2000 | 8000 ...