Skip to main content

Questions tagged [postgresql-9.3]

PostgreSQL version 9.3

Filter by
Sorted by
Tagged with
8 votes
2 answers
7k views

Going out of disk space in the course of querying a postgres database

Do SELECT queries (w/ table JOINs) on a database affect disk space whilst they're running? Background: I have a Django app with a Postgresql backend (9.3.10). My DB resides in a VM that was running ...
Hassan Baig's user avatar
  • 1,989
21 votes
2 answers
41k views

Dealing with disk space full in postgresql

I have a Django web application with postgresql 9.3.10 backend (sitting in a Linux OS). I ran into disk full error, such that even if I try to truncate a table, I get errors of the sort: ERROR: ...
Hassan Baig's user avatar
  • 1,989
2 votes
2 answers
3k views

External size of table still not released (as disk space) after issuing truncate command

In PostgreSQL 9.3.10 (Ubuntu 14.04 OS), I have a table called groupseen whose size is as follows: Table | Size | External Size ----------------------------------+------...
Hassan Baig's user avatar
  • 1,989
4 votes
4 answers
632 views

What is the better way to handle a different types of measurement in grocery store items database

While designing a DB for grocery shop, I came across a requirement of "same product available in different measurements" - e.g., Lets say, Green Peas available in 250gms, 500gms, 1Kg and so. My ...
Itzdsp's user avatar
  • 141
0 votes
1 answer
360 views

postgres 9.3. resets log_min_duration_statement after receiving SIGHUP

I'm using PostgreSQL-server 9.3.5. After setting log_min_duration_statement to 1000ms I'm sending SIGHUP and see that server resets value to '-1': -bash-4.1$ psql psql (9.3.5) Type "help" for help. ...
user avatar
4 votes
1 answer
4k views

Connection Timeouts and Failed Database Connections on AWS RDS

Environment details - Amazon RDS running PostgreSQL 9.3.14, db.r3.xlarge, IOPS 2000, 500 GB Issue - Numerous java.sql.SQLTimeoutException: Timeout after 30000ms of waiting for a connection and java....
gvatreya's user avatar
  • 203
8 votes
4 answers
5k views

How to run pg_dump so that it doesn't slow down other connections?

I am running a daily pg_dump on PostgreSQL 9.3.14 database of approx. 50 GB of size: pg_dump -Fc dbname > file The problem I am having is that while pg_dump runs, which takes several hours, all ...
zabojnik's user avatar
3 votes
0 answers
44 views

Upgrade PostgreSQL 9.3 without moving the DB and messing PostGIS on OSX OpenGeo Suite original installation

I looked for the answer, but could not find a specifically fitting one, so would appreciate an answer or a pointer to one... Q: How do I upgrade PostgreSQL 9.3 to the current (now 9.6 ?) without ...
Lizardie's user avatar
  • 773
2 votes
1 answer
2k views

Query Optimization - CTE Is Slow With OPENQUERY

I am running the below query that utilizes a CTE - the full recordset is roughly 10K records. When I do a Select Top 1000 the query takes roughly 45 seconds to return the results. I am querying a ...
BellHopByDayAmetuerCoderByNigh's user avatar
5 votes
2 answers
516 views

Speed up query calculation. Where can I add indexes or optimize the query or server?

I'm looking to speed up some calculations on a single table. Here is the table, which I believe has over 93 million rows and it grows every day: CREATE TABLE daily_data ( id serial NOT NULL, ...
mountainclimber11's user avatar
64 votes
3 answers
92k views

How to install the additional module pg_trgm

I just want to know how to install the module pg_tgrm as used in the trigram indexing scheme that allows you to do un-anchored search patterns on an index. WHERE foo LIKE '%bar%';
Lizardie's user avatar
  • 773
6 votes
1 answer
7k views

Deleting records from tables containing FK pointing to each other

Background: Bit of an accidental DBA here. I have this reddit-like website where users submit links pointing to various internet content, and can then leave comments under each posting. This app - let'...
Hassan Baig's user avatar
  • 1,989
1 vote
0 answers
666 views

Avoid changing table reference in view after renaming the main table

My table name 'Transaction'. I renamed the table name as 'Transaction_backup' and I have created a new Table 'Transaction'. When I renamed the old table, the table reference in view also changed ...
Vidhya's user avatar
  • 11
4 votes
3 answers
6k views

pl/pgsql: Dynamicly get a column name from a record

Greeting, I want dynamicly get a column name from a record. As showing in the code below that I created a cursor and I used a loop to process each record in that cursor by fetching each row to a ...
Eyla's user avatar
  • 219
2 votes
1 answer
3k views

Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5

How do I calculate the percentile of the nth element in a field relative to the ones previous to it in a time series for each subset (partition) of my data? For example, this is exactly what I want ...
mountainclimber11's user avatar
14 votes
2 answers
58k views

Postgresql To Return 0 when Null

I am attempting to run a query using MAX() but my issue is that if the field contains a NULL value the data returns nothing. Updating the table is not an option, so how should this query be re-...
Michael Mormon's user avatar
2 votes
1 answer
2k views

pg_upgrade from 9.3.5 to 9.6 with PostGIS (Windows)

Versions current: PostgreSQL: 9.3.5 64-bit PostGIS: 2.2.3 notes: 2.2.3 is the latest available on StackBuilder for 9.3.5; upgrade PostgreSQL: 9.6 64-bit PostGIS: 2.3.1 notes: 2.3.1 is the only ...
GT.'s user avatar
  • 215
7 votes
1 answer
7k views

ERROR: invalid input syntax for type money

While importing I get this error: pg_restore: [archiver (db)] COPY failed for table "transaction_details": ERROR: invalid input syntax for type money: "$0.00" restore completed, but the ...
Akash Kumar's user avatar
1 vote
0 answers
1k views

How check server resources in postgresql

In my local pc/db, I have a C# app calling Postgres user functions using multiple threads (BackgroundWorkers) using NpgSQL. But after 1h hour something happen and process stop. My guess is something ...
Juan Carlos Oropeza's user avatar
0 votes
1 answer
252 views

Migrating PostgreSQL 9.1 database to Ubuntu 14.04

I want to migrate a database from PostgreSQL 9.1 installed on an old Debian system to Ubuntu 14.04. The database is making use of triggers and it is a requirement that the triggers be migrated as well,...
kasperd's user avatar
  • 241
4 votes
1 answer
4k views

Filter is applied after DISTINCT ON

There's a script at the end that will fully create the schema and populate it with sample data. Schema Consider these two tables: Table of polygons: CREATE TABLE my_polygon ( my_polygon_id ...
jpmc26's user avatar
  • 1,602
3 votes
1 answer
97 views

Role, whose name doesn't match database name, can't access the database

Role app can access database app. But role app2 can't. I don't see any specific privileges for role app, and it's not an owner according to \l: app2=> \l Name | Owner | Encoding | ...
x-yuri's user avatar
  • 367
2 votes
0 answers
767 views

Amount by which to increase checkpoint_segments (postgresql)

In my postgresql log, I see statements such as checkpoints are occurring too frequently (26 seconds apart). Currently in the conf, checkpoint_segments are 32, checkpoint_timeout is 15 mins and ...
Hassan Baig's user avatar
  • 1,989
2 votes
0 answers
818 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 ...
Phillip Stack's user avatar
4 votes
2 answers
2k 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 ...
Hassan Baig's user avatar
  • 1,989
18 votes
2 answers
69k 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 ...
Dave's user avatar
  • 713
1 vote
0 answers
201 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 ...
Jerome B's user avatar
3 votes
2 answers
8k 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 ...
user107542's user avatar
1 vote
1 answer
276 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 ...
kontextify's user avatar
0 votes
1 answer
3k 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 ...
Chandrakumar M's user avatar
6 votes
2 answers
20k views

Export image file from bytea column

I am trying to export an image file from a PostgreSQL database.The users_data_circulation table has a photo (bytea) column. My command: copy (select encode(photo,'hex') from users_data_circulation ...
Melih's user avatar
  • 284
1 vote
1 answer
278 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 ...
E.E's user avatar
  • 111
0 votes
1 answer
208 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 ...
Nirmani Malee's user avatar
0 votes
0 answers
54 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 ...
user2676140's user avatar
1 vote
2 answers
548 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 ...
Adam Matan's user avatar
  • 11.7k
5 votes
2 answers
2k 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 ...
aydow's user avatar
  • 197
2 votes
1 answer
10k 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,...
terse's user avatar
  • 123
4 votes
2 answers
8k 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?
Midwire's user avatar
  • 183
0 votes
1 answer
164 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?
kunal kumar's user avatar
5 votes
2 answers
2k 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 ...
Miles Erickson's user avatar
7 votes
1 answer
22k 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)?
Amandasaurus's user avatar
3 votes
1 answer
1k 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 ...
sudokai's user avatar
  • 133
4 votes
2 answers
7k 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. ...
subdavis's user avatar
  • 181
2 votes
1 answer
4k 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 ...
user2709587's user avatar
1 vote
2 answers
4k 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 ...
Hassan Baig's user avatar
  • 1,989
2 votes
1 answer
291 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 ...
vabada's user avatar
  • 123
1 vote
0 answers
2k 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 ...
Siddharth Trikha's user avatar
1 vote
0 answers
874 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 ...
lev's user avatar
  • 111
3 votes
1 answer
1k 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 ...
Jim's user avatar
  • 133
3 votes
1 answer
268 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 ...
Upendra01's user avatar
  • 151

1 2
3
4 5
12