PostgreSQL version 9.3
0
votes
0answers
9 views
Why I cant create a new conection? Server doesn't Listen
I already have two connection to my localhost db, with two users postgres and pgAgent. You can see I can browse both connection ok here. I only have this postgres server, but also have MSSQL.
I try ...
1
vote
0answers
21 views
Import Data From csv Into Postgresql WITHOUT COPY
I am writing a function for a python environment which needs to import data from a csv file into an existing table. The scope in which the function can execute does not allow for a db SUPERUSER to run ...
0
votes
0answers
8 views
Postgresql connection failed at client end for only one machine
We have a postgresql server and have created the entry like all the clients should be able to access the client.
When I am trying to connect the server using one machine, its giving me Connection ...
0
votes
1answer
38 views
Should PgBouncer be installed on web application server or database server?
I have a django website where the application and database server are different. I need to install pgbouncer to enable database pooling, however, I'm confused about where to install it: web ...
2
votes
0answers
99 views
How do I install/enable the uuid-ossp extension on Postgres 9.3?
I’m using Postgres 9.3 on Ubuntu Linux 14.04. How do I install/enable the uuid-ossp extension? I first verified the contrib package was installed …
root@prodbox:/home/rails/myproject# apt-get ...
1
vote
0answers
14 views
SET NULL fails with consecutive cascade operations
I have tables for country/province/district/commune/village. Each have a foreign key on its parent with a cascade delete.
I then have a location table with foreign keys to each tables above with a ...
1
vote
2answers
37 views
Replacing last 3 characters of values
I presumably appended .png to all entries in the column image like this:
update genres set image=image||'.png'.
I then realised that the images I'm referencing will be jpegs; and I'm too lazy to go ...
1
vote
1answer
17 views
PostgreSQL server integrity after losing a tablespace
I'm running a PostgreSQL server with 2 tablespaces: 'ssd' and 'hdd'. The first contains databases that need to be queried quickly, and is located on a single SSD that is not redundant in any way (no ...
0
votes
1answer
63 views
PostgreSQL corrupted after running pg_resetxlog
We are using PostgreSQL version 9.3 on Ubuntu 14.04. This PostgreSQL server shared among all our application servers (Odoo), so we made it run on the separate environment.
On Saturday we found disk ...
3
votes
0answers
46 views
how to export image file from bytea column of postgresql
I try to export image file from postgresql database.
In database, users_data_circulation table has photo (bytea) column.
My command;
copy (select encode(photo,'hex') from users_data_circulation limit ...
1
vote
1answer
24 views
Calculating new date with date/time operators fails with variables
I have two fields, one timestamp (calldate), and one bigint (duration - no idea why it's this big).
Through a query I would like to construct a 3rd field that is the timestamp plus the bigint as ...
0
votes
1answer
31 views
Ubuntu postgresql 2 databas in two hard disks
I have two postgresql database in single 1tb hard disk. Each data base sizes more than 300gb. So now I faced a disk space problem and I bought another 1tb hard disk recently. So I need to move one ...
0
votes
1answer
36 views
PostgreSQL Query With Sub-Query
On my production server, this query returns no results, so I took it to SQLFiddle to test the results with test data to see if maybe it was a conversion issue. Well SQLFiddle throws an error anytime ...
1
vote
2answers
51 views
Uninstalling / upgrading PostgreSQL on OSX
The problem
I have PostgreSQL 9.3 installed on my MacBook Pro. I don't remember how I installed it (there are numerous ways), and now I want to upgrade to PostgreSQL 9.5.
Foresnsics
The running ...
0
votes
1answer
23 views
Limit of psql_history
I can't find (using ctrl-r within psql and less ~/.psql_history) a particular query that I executed a couple months back. I also recently upgraded from 9.3 to 9.5 so I'm not sure if there is a limit ...
1
vote
1answer
54 views
Selecting only overlapping elements from array of ranges
The table I need to search contains an array of numrange values illustrated by:
CREATE TABLE data ( sensor varchar(25), ranges numrange[] );
INSERT INTO data VALUES
('sensor0','{"[872985609.0,...
1
vote
2answers
66 views
Does changing `wal_keep_segments` require a restart?
I have a need to increase wal_keep_segments on our master server. Can I do that on the fly or does it require a restart?
0
votes
1answer
20 views
Postgresql boolean [closed]
Postgresql stores boolean value TRUE or FALSE, while I am passing 1 or 0. And I want to store as 1 or 0 instead of TRUE or FALSE.
Can anyone help me on this please?
2
votes
1answer
53 views
Why does PostgreSQL perform a seq scan when comparing a numeric value with a bigint column?
Given a table vp with column timestamp type bigint, and a btree index on timestamp, why would Postgres ignore the index and run a seq scan on comparison of timestamp with a floating point value, when ...
0
votes
0answers
5 views
postgres: How to skip the fist and the last row of a queryset in a for-loop?
In a function I'm looping over the all rows a select statement returns. I want to skip the fist and the last row. How could I do this?
...
FOR arow IN
SELECT (dp).path[1] As index,
...
0
votes
1answer
26 views
How to find what tablespace a table/index is in on PostgreSQL?
How do I find out what tablespace a table (or index) is in with PostgreSQL (9.3)?
3
votes
1answer
58 views
Slow query on primary server runs fast on hot standby
I have a primary PostgreSQL 9.3.4 server with 64GB of RAM that is replicated using streaming replication to a hot standby server with 32GB of RAM. My problem is as follows: I've detected a query that ...
0
votes
2answers
82 views
Postgresql pg_hba.conf changes are ignored or not loaded
Using Postgresql 9.3 on Ubuntu 14.04.4 LTS
I've been changing the /etc/postgresql/9.3/main/pg_hba.conf config file madly trying to make a change that will allow me to log in with any user remotely.
...
0
votes
0answers
14 views
Is it possible to convert enums to text in posgresql
Is is possible to convert an enum declared in a postgresql schema to text so that I could use like clause for the enum column?
0
votes
1answer
42 views
Use system timestamp comparison in SELECT query
I have to backup some tables on a daily basis using the condition that the backup will be only for one previous day. I am planning to use copy to dump the records in to a csv file. I need help in ...
0
votes
0answers
59 views
pg_controldata 'Time of latest checkpoint' on standby doesn't change
About 2 weeks ago, pg 9.3 master space filled up, didn't die, continued operation after space was freed up, and it's standby has kept up with it ever since.
However the standby pg_xlog keeps filling ...
1
vote
2answers
41 views
Routinely deleting aged postgresql rows via cron
I have a Django website with a postgresql 9.3.10 backend. The website contains dynamic user-generated content (think of it as a form of 9gag). There are some tables from which I routinely delete aged ...
3
votes
1answer
43 views
Postgresql tables seem duplicated (with suffixes such us pkey, hashes, etc)
due to some problems I came to check the state of my current database in production and I found that some tables are "duplicated". By duplicated I mean that there appear to be some clones of the ...
1
vote
0answers
99 views
Check Postgres Listener is up/down?
I am using PostgreSQL LISTEN/NOTIFY functionality in Java. So I have made a ListenerBean which is run at Application startup.
LISTENER class in java:
@Singleton
@Startup
...
0
votes
0answers
42 views
Force Postgres to write log to a new file
I've updated the value of log_line_prefix, and I wanted that the log file will contain only logs with the new prefix.
I deleted the log file (postgresql-9.3-main.log), assuming that once the file is ...
0
votes
0answers
36 views
Pgpool2 connection pooling
I have installed both PGPOOL2 3.3 and Postgresql 9.3 db(master) on the same server and in the pgpool.conf file have given the connection parameters as:
num_init_children = 100 max_pool = 2
Therefore ...
3
votes
1answer
56 views
PostgreSQL CAST() giving unexpected results
I can't seem to figure out how this is rounding overflow digits. It is most definitely not any of:
Round up if last digit is 5 or higher.
Round to even if last digit is 5.
Randomly round up or down ...
1
vote
1answer
33 views
password confusion in postgres sql
I have logged into postgresql server using
sudo -u postgres psql
Then I also created a database from within /usr/local/bin directory using the command.
createdb -h localhost -p 5432 -U postgres ...
1
vote
1answer
29 views
How do I restrict PostGIS database with user specific access?
I am using open source tools and working on QGIS 2.8 and Postgres/PostGIS 9.3 .
Working on urban application so we have single database and many users .I want to create department wise user group so ...
2
votes
1answer
72 views
Placing a uniqueness constraint on a date range
I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate).
How do I place a constraint on the table such that bookings are not ...
0
votes
0answers
34 views
Considerations to take into account for moving a big table into another database
In our application, we a have 67 GB table ( very big compared to the rest of our tables ) and it acts almost as an archive table since its record are not modified and the ratio of read operations is ...
1
vote
1answer
18 views
What run-time configuration parameters are used for concurrent index creation
Suppose I were to run the following. The first line shows the server's default maintenance_work_mem value.
mhildreth=# show maintenance_work_mem;
maintenance_work_mem
----------------------
16MB
(1 ...
0
votes
0answers
217 views
Postgresql restore database from file encoding error
I have problem while i import database from backup I gives many output and many "invalid command" messages. In the end it gives an encoding error. what can the reason be?
my command:
sudo -u ...
1
vote
0answers
34 views
How can I upgrade PostgreSQL while using PgQ queue system?
I'm using PostgreSQL 9.3 and PgQ 3.2.6 for about two years and now it's time to upgrade. I found out that upgrading PostgreSQL is fairy simple. Just install new Postgres version and then:
sudo ...
2
votes
2answers
196 views
best way to avoid redundant aggregate functions and/or group by columns
Suppose I have two tables:
Foo:
id
baz
Bar:
id
foo_id
boom
So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set of ...
1
vote
0answers
38 views
PostgreSQL 9.3 Query Design Optimisation - Improving speed
I am looking to optimise a query that is currently taking ~2500ms to run in PostgreSQL 9.3. The reason I need to optimise it is that may have to be run frequently and it directly impacts user ...
0
votes
0answers
12 views
SQL query for setting value
I found this example for configuration databse tables:
http://stackoverflow.com/questions/10204902/database-design-for-user-settings
Can you show me some example for SQL query for getting ...
5
votes
1answer
76 views
What permissions are required to return rows from information_schema.schemata?
In postgres (I'm on 9.3.4) what permissions are required to return rows for
select * from information_schema.schemata
The 9.3 documentation says "are owned by a currently enabled role". Does that ...
0
votes
1answer
63 views
Postgresql maintainenace queries fail with foreignkey violation
I maintain a Django app with a postgresql backend, where users post interesting URLs (links) and then either leave replies under these or upvote/downvote them. All this is done via 4 postgresql tables ...
1
vote
0answers
46 views
My simple FK constraints appear to have been ignored. How?
I have two tables, device and platform where device.platform_id enforces the following constraint:
Foreign-key constraints:
"device_platform_id_fkey" FOREIGN KEY (platform_id) REFERENCES platform(...
1
vote
1answer
149 views
PostgreSQL 9.3: Dump a database excluding the data of some tables
Consider a production database with a few dozens of normal-size tables, and a handful of huge tables.
I am looking for a convenient way to pg_dump the database content, excluding the few huge tables, ...
0
votes
0answers
24 views
How to upgrade PostgreSQL 7.3 to 9.3?
I want to upgrade the PostgreSQL database from 7.3 to 9.3. Please help me how to perform the upgrade.
2
votes
0answers
41 views
In PostgreSQL, can you read from an index during a ALTER INDEX … SET TABLESPACE operation?
I have a PostgreSQL 9.3 server, and there's a large index that I want to move to another tablespace on another disk, because I want to free up space on the original disk. However this is a production ...
1
vote
1answer
42 views
Tricky condition for PostgreSQL UPDATE query
I need help with a PostgreSQL query.
I have a Django app + pg backend, where users post interesting URLs (or links) and can then comment under such links (called replies). There are two pg tables to ...
1
vote
1answer
65 views
PostgreSQL 9.3: Aggregate collection of JSON arrays
I have a table inventory with a value column that contains JSON strings, with the following data structure:
{
"category": {
"item": ["price", "quantity"]
},
"widgets": {
"foo": [300, 15]...
