Tagged Questions

PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

0
votes
1answer
35 views

FDW, replication and CREATE SERVER - will it work?

We have two servers (master, slave) and we want to use the FDW. The two databases to be connected with the FDW are both on the master (and the slave of course). When we are to CREATE SERVER we would ...
2
votes
1answer
96 views

restoring Postgres database: pg_restore -vs- just using psql

I am dumping my Postgres database using pg_dump (in plain-text format) and then restore it simply by using psql (with the -f option). Which begs the question: am I missing anything by not using ...
0
votes
2answers
87 views

sum of double precision gives weird results

I've been making a sum of a column containing about 500.000 records of double precision numbers. All numbers in the database should normally be of two ciphers behind the period. However when making ...
0
votes
3answers
133 views

get row count of last select query

I am using a scheduled script which exports the (new) rows of the PostgreSQL database into a textfile. There are also several instances of this script running for several DBs: COPY (SELECT ... FROM ...
0
votes
1answer
38 views

Run and Save multiple SELECT statements

Within a Postgres function I would like to run a number of PSQL SELECT commands. For each SELECT command I would like to output written to separate text files. What is the best way to implement ...
0
votes
0answers
36 views

Suppression of statement in Postgresql logging

Given the following SQL function: CREATE OR REPLACE FUNCTION log_a_notice() RETURNS void AS $BODY$ BEGIN RAISE NOTICE 'This message is coming from log_a_notice()'; END; $BODY$ LANGUAGE plpgsql ...
3
votes
1answer
68 views

Multi-row insert vs multiple single row inserts

In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app. However, I was curious, is there any other advantages? For example, if ...
1
vote
1answer
43 views

Can a PostgreSQL Trigger be used to ignore an insert?

I've got a situation where there are dozens of various triggers in our database that trigger a record being inserted into another (single) table within the database. I was hoping that while testing ...
-1
votes
1answer
20 views

Permission denied when importing a database

This is my first time here and i have a question. I am working with postgresql on windows 8 for the first time. I am trying to import a database into postgresql with the command line. Type in: psql ...
0
votes
1answer
20 views

Using PostgreSQL both with (not only) SQL Workbench - how to apply alternate delimiters

I have created a PostgreSQL database. Now I try to work with it in SQL Workbench, but I have a problem with the way it understands statement delimiters. The documentation on alternate delimiters sais ...
0
votes
1answer
69 views

Why use RDS over EC2 for Postgres?

I have a database currently running on EC2. As I have to move it to a bigger machine the question of using RDS came up. Pricing: I can get an EC2 c3.large instance with two SSDs (16GB each), two ...
2
votes
2answers
60 views

Slow fulltext search due to wildly innacurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...
1
vote
1answer
37 views

Permission for sequence in another schema

Postgres 9.3 Debian 7.0 I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same ...
2
votes
2answers
45 views

Does the system process slow down my Database access?

I have a Fairly large DB containing mostly html documents. I wrote a python script that fetches always 1000 html documents at once from my database cur.execute("SELECT id,url,html_file FROM html ORDER ...
3
votes
4answers
84 views

postgres: index to SELECT strings like '%foo%';

I have an expensive, periodic PostgreSQL 9.3 query of the form: SELECT * from mytable where name NOT LIKE '%foo%'; foo is actually a constant here that never changes. The query is expensive ...
0
votes
1answer
26 views

Solutions to migrate databases

I'm working in redesign an application. This application has a MySQL database and has approximately 50 tables (there are no relations , there are not standars, etc). We want to migrate to Postgresql ...
1
vote
1answer
30 views

Delete rows while updating references

Suppose I have a person table with an id and some other columns. Other tables reference the person table using ON UPDATE CASCADE. for instance: CREATE TABLE person (id int PRIMARY KEY); CREATE TABLE ...
3
votes
1answer
261 views

Optimizing large database query (25+ million rows, using max() and GROUP BY)

I'm using Postgres 9.3.5 and I have a large table in the database, currently it has over 25 million rows and it tends to get even larger rapidly. I'm trying to select specific rows (all unit_ids with ...
1
vote
1answer
60 views

Handling user logins while using a multiple schema design to support multi-tenancy

I'm creating a web application (in PHP and PostreSQL) that will be used by organisations and their staff and I want to use multiple schemas to support multi-tenancy. There will be one database and I ...
0
votes
1answer
60 views

Delete returns nothing in Psycopg2?

I have a fairly simple delete query in a PostgreSQL database that I'm interfacing with via psycopg2. Take the following minimal example: def testDelete(): db = DbInterface() cur = ...
0
votes
0answers
18 views

Cascading pgbouncer setup?

I am wondering if anyone has tried a cascading pgbouncer setup, where pgbouncer A connects to pgbouncer B and that in turn connects to postgres. From 1Km away this seems a possible setup, but I am ...
2
votes
2answers
74 views

Postgresql - Do Foreign Key Constraints Automatically Create Indexes?

Per the PGSQL 9.3 docs: Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on ...
1
vote
3answers
53 views

How do I keep a database physically secure? [closed]

This is more of a conceptual question. I am learning how to integrate Java and databases (using PostgreSQL at the moment) and my initial questions is: What are the guarantees that the data I ...
2
votes
2answers
101 views

SQL order by query results in any arbitrary (but reproducible) manner

For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is ...
0
votes
1answer
42 views

repmgr: Can I replicate databases from two different masters to a third slave?

If I have a master node 1 (with database A) and a master node 2 (with database B) can I use repmgr and have both A and B replicated to a hot standby node 3 ? [ EDIT: They would all be running ...
2
votes
0answers
23 views

Can you force pgpool to require SSL connections?

Can you force pgpool to drop non-ssl connections? I know you can with postgresql (Force PostgreSQL clients to use SSL?), but dropping that same syntax (hostssl instead of host) results in a ...
0
votes
1answer
26 views

accessing fields of returned record object

I need to use a function which exists on another database and was able to figure out how to access it via dblink. The function returns the name of an encryption algorithm and the key used for it, as ...
1
vote
1answer
22 views

Handling exceptions from inside a function

I need to add a trigger after insert or update on a table so that, where some row is updated and a certain condition is met, then some other table needs to be updated. However, that last update ...
1
vote
0answers
41 views

Debugging Postgres 9.3 Triggers

Right now I have a trigger in my database that is giving me some issues. I've set up this line so I can check what is being fetched from a query RAISE NOTICE 'hstore_key = %',hstore_key; as well I ...
3
votes
0answers
63 views

Ways to process XML into a relational structure in Postgres

I have a database that is comprised of ~10 tables, most of which have many-to-many relationships with each other (and an associated join table). I am inserting of the order 100k XML files into a raw ...
2
votes
1answer
70 views

Most efficient way to do many UPDATES in Postgresql

I have to update cca 2.5 milion rows in a table with cca 20 milion rows. The updated column value is based on value of another column, but another select is required to determine it's value. The ...
2
votes
1answer
57 views

PostgreSQL Division In Query Not Working

So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this: (tier1 / ...
2
votes
2answers
197 views

Disable all constraints and table checks while restoring a dump

I've obtained a dump of my PostgreSQL database with: pg_dump -U user-name -d db-name -f dumpfile which I then proceed to restore in another database with: psql X -U postgres -d db-name-b -f ...
3
votes
2answers
65 views

Removing all columns with given name

I am converting a MSSQL schema to PostgreSQL and in that schema most tables have a column called Timestamp that are of MSSQL timestamp datatype which is effectively rowversion. When inserting records ...
1
vote
1answer
61 views

PostgreSQL: Another seq scan where an index scan is expected

Here's the query: SELECT DISTINCT ON(points.track_id) participants.contest_number, points.track_id, points.lat, points.lon, points.alt, points.timestamp, points.h_speed, ...
1
vote
1answer
110 views

Returning rows in PostgreSQL with a table called “user” [duplicate]

I have a table called user I have some SQL that is select User.* from User This fails because User is a reserved word however, I was under the impression that Postgresql didn't care about the case ...
3
votes
2answers
596 views

Is Data Warehousing possible in MySQL and PostgresSQL [closed]

I am fairly new to DB design and development. My requirement is simple drilldown/slicing based on time and language of words (in a language on a particular day). However, my db is mysql. But so far I ...
0
votes
1answer
39 views

Change permissions of postgres copy to exported file

I have asked this question on SO but perhaps that was the wrong location. Apologies for double posting but the question may have a better audience here. The original question is: ...
2
votes
1answer
43 views

Diagnosing the error in a Postgresql to Redshift migration through psycopg2

I am running a job class that contains the following: A Postgresql connection that can issue SQL statements A Redshift connection that can do the same an S3 connection to function as an intermediary ...
0
votes
0answers
25 views

Postgresql Full Text Search sequence of strings

What I'm trying to do is that i have a bunch of html documents in which I need to search for certain groups of strings. I did build a ts_vector for all htmls but I seem not be able to find out if it ...
0
votes
0answers
26 views

Best way to setup postgresql load balancing and failover?

Ok so I know that my db is going to be postgres and I want to ensure that it is able to handle read/write queries in the future (instant messaging application so lots of small writes and a lot more ...
1
vote
0answers
79 views

Implementing a functionality for processing heavy insertion

I use PostgreSQL 9.1. The scenario is: I receive heavy insertion into Table1 (about 100 rows per sec). For each new row, I have to check the previous and next row of the source table if those are ...
5
votes
1answer
64 views

In Postgres, how to get a list of current defined savepoint?

I'm using postgres SAVEPOINT which create a new savepoint within the current transaction, and would like to display the list of currently defined savepoint in a connection. To be more precise: I'd ...
0
votes
0answers
53 views

Reset password for 'postgres' on v9.3 (Win Srv)

I'm trying to reset the password of the user 'postgres' on a postgres 9.3 installation, but have been unable to do so... I've tried every combination in pg_hba.conf I could find online (ex. 'host all ...
1
vote
1answer
78 views

Best way to add Foreign Key to massive table?

Say I have 4 tables: arrival (~100 million rows), departure (~100 million rows), airport (~30 million rows), airplane (small). arrival and departure each have 2 ForeignKeys: one to airport, one to ...
0
votes
1answer
32 views

PostgreSQL replication with periodic log shipping

I'm using Windows Server 2008 R2 with 64-bit PostgreSQL 9.2 on both test machines. What I wish to achieve is that one server will be a master and other will be a slave. This is just for tests, finally ...
5
votes
1answer
194 views

Rounding issues?

Try this: create table test (f float); insert into test values (330.0); commit; select (8 + 330.0/60)::int; --14 select (8 + f/60)::int from test; --14 select (9 + 330.0/60)::int; --15 select (9 + ...
0
votes
0answers
28 views

What to do if pg_cancel_backend and pg_terminate_backend return False?

I cannot end/terminate the queries shown in the screenshot. The functions are not running anymore (usually they cause a high load on the server and the server was fine). I have tried both ...
2
votes
1answer
38 views

Are transactions in PostgreSQL via `psycopg2` per-cursor or per-connection?

I'm doing some work with PostgreSQL 9.3 using the psycopg2 database API. I have the DB API set in the minimum isolation level ("autocommit" mode), and am managing my own transactions directly via ...
1
vote
1answer
47 views

What's the best solution for offline db syncing

I'm building a system which requires a master db (BIG master) that contains things like accounts, balances, actions, etc. This db then needs to be accessible at a physical place, like a fairground ...