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

How can I combine two event tables into a single timeline, in PostgreSQL?

Given two tables: CREATE TABLE foo (ts timestamp, foo text); CREATE TABLE bar (ts timestamp, bar text); I wish to write a query that returns values for ts, foo, and bar that represents a unified ...
1
vote
1answer
12 views

Postgresql Function error syntax error at or near “SELECT”

I never created a function in Postgres, I followed some tutorials and made this code, but I don´t know why is wrong, the error in the console is "syntax error at or near "SELECT" I'm using the ...
1
vote
0answers
13 views

Use trigger to synchronize columns with fields in json column on insert or update

I'm a bit of a database/postgres beginner,so bear with me. If I have a table, something like this. CREATE TABLE testy ( id INTEGER REFERENCES other_table, name varchar(128) PRIMARY KEY, ...
0
votes
0answers
8 views

how to convert a master cluster to a slave cluster?

I am learning about Postgresql streaming replication and so far I have learned how to create a slave cluster using : pg_basebackup -x -R -D main -h host -p port also I have learned that I can ...
0
votes
0answers
18 views

When does pg_dump not export CREATE EXTENSION?

I have a production database that I am trying to dump then load (in plain text format) locally. The problem is, the database being dumped uses the hstore extension in several tables, and contrary to ...
0
votes
0answers
15 views

Getting TEXT data from postgres throught linked server to MSSQL

I'm using MSSQL 2008 R2 with linked server to postgres 9.4. My problem is: when I select column with data type text it shows me only XXX first chars and adds (...) on the end. select description from ...
0
votes
0answers
18 views

PostgreSQL becomes very slow after heavy utilization

I have built a Python web application and I am hosting it in a 2GB RAM Debian Server. My application uses PostgeSQL 9.1 with PostGIS. One of my tasks involves importing data from big csv files. ...
1
vote
0answers
22 views

Is there a way to determine if a postgres function depends on a user-defined type?

I am writing a Ruby script to handle functions and user-defined types in Postgres. I want to make sure, that before a function is created in the database, if it uses a user-defined type, than this ...
2
votes
2answers
36 views

Locking issues under transaction on Postgres

I recently got a "Out of shared memory - You might need to increase max_locks_per_transaction" error. This is a long running process where I import a bunch of CSV files after proper processing. I ...
0
votes
1answer
24 views

Copying CSV file to temp table with dynamic number of columns?

I am wondering if there is a way to copy a csv file into a temp table where the number of columns is unknown in the csv file. The DB software I'm using is PgAdmin III. I figured out that if I do know ...
5
votes
3answers
141 views

Postgres is performing sequential scan instead of index scan

I have a table with about 10 million rows in it and an index on a date field. When I try and extract the unique values of the indexed field Postgres runs a sequential scan even though the result set ...
2
votes
1answer
33 views

Postgres 9.4+: JSONB instead of EAV

I'm figuring out a way to structure a new postgresql database, and I'm curious if I could use a JSONB column instead of the EAV model for storing properties of an entity. This seens like a flexible ...
1
vote
0answers
36 views

Connection refused when inserting

Description: Machine 1: Linux, PostgreSQL 9.3, on local, 10.12.10.123 Machine 2: Linux, PostgreSQL 9.3, on cloud, 104.10.124.123 Configuration (on both): + postgresql.conf : listen_addresses ="*" + ...
3
votes
0answers
77 views

Leap second in Database system (Postgresql and SQL Server)

This night will be longer because of leap second: https://en.wikipedia.org/wiki/Leap_second My impact will be minimal in my case, I don't even know if I will recive some weird data like 20150630 ...
0
votes
0answers
27 views

Column doesn't exist in PostgreSQL View [on hold]

I have the below situation but I don't get what is the problem with my code. I receive this error: column vx1 doesn't exist. vx1 double precision; vy1 double precision; vz1 double ...
0
votes
0answers
28 views

What order do foreign key constraints run in? Why does my foreign key not work after import?

I have two tables tags and tag_memberships in a PostgreSQL database. On the tag_memberships table, I have two constraints. The first is this: ALTER TABLE tag_memberships ADD CONSTRAINT ...
2
votes
1answer
77 views

Subquery magic in PostgreSQL

I have a query: update product_product set (write_date, default_code) = (LOCALTIMESTAMP, 'update') where product_tmpl_id in ( select distinct product_tmpl_id from product_template ...
0
votes
2answers
50 views

Minimizing SELECT latency in PostgreSQL

I am evaluating Postgres 9.4 performance on a machine with Intel i7 quad-core 3.6 GHz CPU, 8 GB ram, and 7400 rpm HDD (no RAID) running Linux Mint. The DB schema has the following table: ...
0
votes
2answers
56 views

Would like to know why this query is behaving this way?

I have an emp table, with schema and some initial values like below: emp_id | name | last_name | role_id --------+------+-----------+--------- 1 | hell | bell | 1 2 | well | ...
2
votes
1answer
38 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
37 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
30 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
177 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
32 views

Index on colum 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
22 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
19 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
18 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
13 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
27 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
24 views

Add a new collation to a Postgres database

I have these collations: postgres=# select * from pg_collation; collname | collnamespace | collowner | collencoding | collcollate | collctype ...
0
votes
0answers
12 views

I am installing postgres-xl. While initializing the coordinator, i am getting the error below [closed]

loading PL/pgSQL server-side language ... FATAL: could not open extension control file "/usr/local/share/postgresql/extension/plpgsql.control": No such file or directory STATEMENT: CREATE ...
2
votes
1answer
35 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
83 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
67 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 ...
0
votes
0answers
21 views

PostgreSQL: pgbouncer cannot connect to server

I have PG 9.4 running on a given CentOS host: host1, and pgbouncer 1.5.5 running on another CentOS host: host2. Once an hour, some CSV files are downloaded automatically on host2 and loaded into PG ...
1
vote
2answers
184 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
23 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
24 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"?
4
votes
2answers
80 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
43 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
30 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
15 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
44 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
17 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
121 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
28 views

Syntax error in postgresql query

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
16 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
20 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
48 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
34 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 ...