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 ...
0
votes
1answer
20 views
To normalize or not to normalize for few values
Assuming in a Postgres database, you have a table called 'Party', which can have less than 5 well-defined party_types such as 'Person' or 'Organization'.
Would you store the party_type in the Party ...
2
votes
1answer
59 views
UPDATE table with another table data
I want to update the table car_check field fencing.(total 40 rows).I also use postgis function.
I think it's a little difficult.Any suggestions?
The code is the first carid example.
UPDATE car_check ...
1
vote
1answer
40 views
Confused about horizontal scaling in postgresql [on hold]
Please correct me if I am wrong but I guess handling more requests and load by adding more machines or balancing the load between multiple servers is horizontal scalling. So, if I add more servers, ...
5
votes
1answer
29 views
How to see amount of reusable space for a table
I had a huge table(about 300mil rows). After massive DELETE and VACCUUM table files have stopped to grow. That's exactly what I expected. But is there any way to see how much space became reusable?
0
votes
1answer
15 views
pgpool2 not working on my Ubuntu i686 athlon i386 GNU/Linux
I am trying to implement replication and load balancing on my Ubuntu i686 athlon i386 GNU/Linux.for creating database clustures I have installed postgresql contrib
9.1.11 having initially database ...
3
votes
1answer
32 views
Non-overlapping rectangles constrained to a boundary
I am trying to model placement of parts on a circuit board. Without any
meaningful constraints, my basic schema looks like this:
create table part (
part_id bigserial primary key,
name text ...
2
votes
2answers
41 views
Order of returned record with IN statement postgres
I know that the order of returned records is not guaranteed with the IN statement for Pstgres. For example if I do this:
SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)
...
2
votes
0answers
13 views
Postgresql 9.2.6 ignoring archive_command
I'm attempting to set up a 3-server chain by leveraging WAL-E. I've done this in the past with replication/wal segments and I'm attempting the exact same method, just with using WAL-E in between.
My ...
0
votes
0answers
14 views
postgres database program to save sql templates and search where used it
I have a postgres database with tables and functions build using sql templates. And I would like to use a database program to save these templates and search where I used it.
What i want to do is to ...
1
vote
1answer
18 views
Battery backed write cache and Postgres
I will be getting a SAN with a battery backed write cache for a new Postgres data warehouse and am not sure what settings to apply to make proper use of it. If the cache is enabled in the SAN is that ...
1
vote
1answer
22 views
constraint view CASE values
Below is a sscce to motivate the case.
I keep a table of persons:
CREATE TABLE person (name VARCHAR);
ALTER TABLE person add PRIMARY KEY (name);
INSERT INTO person (name) VALUES ('Bob'), ('Mike');
...
0
votes
0answers
21 views
PostgreSQL Install Error
When I install the server for the first time, when almost finished it reports:
(from log file "%temp%\bitlock_installer1376.txt")
Loading additional SQL modules...
Executing cscript //NoLogo ...
1
vote
1answer
40 views
Selecting distinct records
I have a user relation to address (1 to 1).
So the address has two types which is a type column.
One is the VerifiedAddress and the other one is the SearchAddress.
So the user who had verified his ...
0
votes
0answers
12 views
Unable to exit a Greenplum SQL script
Is there any way in psql (Greenplum flavour) of exiting a script?
I have tried with \q and \quit, e.g.
\echo "Hello";
\q -- or \quit
but I get:
my_database> \i my_script.sql
...
4
votes
3answers
67 views
PostgreSQL: Extending database storage when disk is full
I have a database that takes about 60GB in my disk storage. And it doesn't alow to insert into database anymore.
I have read the documentation about tablespaces link But It's like create space for a ...
1
vote
2answers
225 views
Very slow execution of a simple query
I try to create a report for my data, but it is really slow on a big table.
The table structure is:
CREATE TABLE posts
(
id serial NOT NULL,
project_id integer,
moderation character ...
0
votes
1answer
32 views
PostgreSQL, storing encrypted data or encrypt database with pgcrypto - what is more secure?
I have two machines: client and server (a database server). I want to connect from client to server and manipulate data from database (insert, update, delete, select) using dblink.
I would like to ...
0
votes
1answer
24 views
PostgreSQL dblink password encrypted or not?
I have two PostgreSQL servers:
server 1: 192.168.0.1
server 2: 192.168.0.2
I want to connect to the database on server 2 from server 1. I do it like this:
SELECT ...
0
votes
1answer
39 views
Postgres database migration “zero” downtime
I have a setup with a JBoss EAP server, postgres 9.1 database.
I will soon update so I have two JBoss servers in a cluster. This is mostly to avoid downtime when doing application deployments.
With ...
1
vote
1answer
56 views
Optimizing Postgres query
I've got a one to one relation from users to addresses table.
Where one user can have one search address and one verified address.
I've got two indexes on addresses table :
Index on state field
...
2
votes
1answer
20 views
Understanding postgres reason for picking this query plan
I ran a query:
select * from orderline where orderid = 12345 and status > 0
that gave me the following plan:
Bitmap Heap Scan on orderline (cost=286370.00..531410.78 rows=76372 width=155)
...
2
votes
2answers
54 views
2 B-tree indices OR 1 GiST index on tsrange — which will perform better?
I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the ...
0
votes
0answers
36 views
Architecture of high-perf SQL-backed web services? [on hold]
I'm looking for resources to help me understand how to design and architect a well-performing distributed system with a central SQL database as its core. I'm using Postgres with a Clojure/Ring-based ...
2
votes
2answers
70 views
What is a valid use case for using TIMESTAMP WITHOUT TIMEZONE
There is a long and quite elucidating answer on the differences between
TIMESTAMP WITH TIMEZONE
-vs-
TIMESTAMP WITHOUT TIMEZONE
available in this SO post. What I would like to know is: are there ...
1
vote
1answer
67 views
Most performant way to fetch last record
I have a Postgres table Prices with the columns
price (Decimal)
product_id (Int)
Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the table ...
0
votes
0answers
37 views
PostgreSQL problems on Ubuntu 12.04
I'm having terrible problems with PostgreSQL initialization on my VPS. I followed several tutorials on this topic and nothing made my database server work.
I followed these steps:
sudo apt-get ...
2
votes
1answer
29 views
redundant foreign keys in PostgreSQL - are there any indexing benefits?
Note: this is not the same as this SO question which is about adding redundant information in the tables themselves.
Does the final statement in the below series of statements buy me anything in ...
2
votes
1answer
77 views
Enforcing constraints “two tables away”
I ran into some trouble modeling an electrical schematic in SQL.
The structure I'd like to capture is
part ←────────── pin
↑ ↑
part_inst ←───── pin_inst
where "inst" is short ...
0
votes
2answers
33 views
Configure postgresql recovery again to be slave
I completed a replication setup using PostgreSQL. Everything was working fine, so I proceeded doing a test by bringing down the main database and promoting the slave to be new master (via touch ...
4
votes
0answers
71 views
User locking tables without privileges
This is a restatement of a question asked on the GIS Stack Exchange here. Although it has GIS-specific elements it may be be a more general PostgreSQL effect.
I have a PostgreSQL 8.4.4 database ...
0
votes
1answer
52 views
Circular foreign key deferrable cascade behavior
I am trying to determine what happens for a particular circular foreign key that will cascade delete. Please feel free to address any other possibilities that my inexperience has not listed.
On my ...
1
vote
1answer
93 views
Restore postgres data/tablespace to new tablespace (at new mount point)?
I have a bunch of test/development databases which we currently refresh with production data as-needed using a NetApp filer's snapshot capabilities - we have a production slave with its datadir on a ...
1
vote
0answers
36 views
MATCH FULL vs MATCH SIMPLE
I've noticed a MATCH SIMPLE or MATCH FULL option in phpPgAdmin, but I can't find a description in the docs.
How do they function?
The default is set to MATCH SIMPLE.
0
votes
1answer
22 views
Postgres replication: Do I need to rsync pg_xlog files if I have archiving setup in master?
I am trying to replicate a master to slave, without shutting down my master.
In the tutorial here: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial, they recommend these steps:
...
0
votes
0answers
23 views
PostGres- How to loop in Table to update every record in Table
I am working on complicated problem, but I will simplify it to this problem.
I have two tables
A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]
and i want to update the ...
0
votes
0answers
23 views
PgPool 2 with HA Proxy
So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for.
E.g.:
PostgreSQL ...
1
vote
0answers
85 views
Postgres database zero down time production deployment
I need some idea about how I can deploy a financial application in production environment using PostgreSQL DB with zero down time. I have two servers in Master-Slave design. Currently I am giving ...
1
vote
1answer
54 views
How do I access a old saved tablespace after reinstalling postgres?
So a lot of things might have gone wrong here. I first installed postgres 9.3 on mac 9.1 via homebrew to manage some data. I am using postgres.app. Turns out my harddrive was too small so I decided to ...
0
votes
1answer
25 views
Multiple source CASCADE DELETE behavior
I'm having trouble understanding Postgres's CASCADE DELETE behavior.
If table_A REFERENCES table_B, and the column referenced could have multiple instances of the same value, will the table_A row be ...
0
votes
1answer
62 views
create a batch script on windows that periodically starts a sql function
I need to create a batch script on windows that periodically starts a sql function written in 'plpgsql'.
I can use windows schedule task for creating a cron job but I don't know how to write the ...
1
vote
1answer
25 views
Where is the generic plan stored when using PREPARE statement in PostgreSQL?
According to the document, if a prepared statement is executed enough times, the server may eventually decide to save and re-use a generic plan rather than re-planning each time. My question is where ...
1
vote
1answer
41 views
Use result from table_schema query as <table> in select * from <table>
Is it possible in PostgreSQL to use the result of the query below as table name in a SELECT * FROM .
SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE 'seam_event_%'
ORDER BY ...
3
votes
0answers
48 views
How to properly monitor the number of PostgreSQL database connections?
I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 ...
0
votes
1answer
45 views
Creating a table if it doesn't exist (old versions of PostgreSQL)
In recent versions of postgreSQL one can use the syntax:
create temp table if not exist my_table as
...
to create a temporary table if it doesn't exist yet.
However, the clause if not exist is a ...
1
vote
1answer
44 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 ...
0
votes
1answer
26 views
How to add CYCLE to BIGSERIAL in phpPgAdmin
I'm trying to apply CYCLE to a BIGSERIAL in phpPgAdmin.
I've seen how to do it with a query, but I'd much rather be able to do it from phpPgAdmin.
Can this be done with phpPgAdmin? If so, how? If ...
0
votes
1answer
23 views
Postgres CYCLE and existing values
I love that SERIALs can wrap around with CYCLE.
However, I can't seem to figure out if that there are lingering values, will Postgres skip over them or throw an error.
In other words, I have a table ...
1
vote
1answer
32 views
PostgreSQL - how to rename CamelCase column?
When I tried to rename a CamelCase column like this:
ALTER TABLE mytable RENAME COLUMN camelCaseColumn TO camel_case_column;
I get the following error:
ERROR: column "camelcasecolumn" does not ...
1
vote
1answer
24 views
Postgres: Purpose of archiving in master?
I am reading tutorials on how to setup streaming replication in Postgres from master to slave.
Some tutorials recommend setting up archiving in the master (and restoring in slave) . In the master, ...
2
votes
0answers
25 views
Postgresql | Database with OID doesn't exist
So, how it started, DB started throwing errors about "database with OID 1"
2014-02-07 22:54:46.561 GET >ERROR: database is not accepting commands to avoid wraparound data loss in database with ...
