PostgreSQL 9.3 : 2013 release of PostgreSQL
1
vote
1answer
14 views
Postgres create an insert return rule
I am failing to create a rule which will return the insert's data.
The table is:
CREATE TABLE foo(a int, b text);
My attempt for the rule is:
CREATE RULE return_data AS ON INSERT TO foo DO ...
0
votes
1answer
16 views
Trying to load a file into a database on virtual machine
I have set up a Postgres db on a linux vm and have been having no issues using a GUI to connect to it. However, I am trying to load a large, 32GB, file onto it and so am skeptical of the old way I was ...
0
votes
0answers
5 views
DSN connection and RODBC on windows 8
I wish to run queries on a PostgreSQL database in R using the RODBC package.
Before I had windows 7 and it was working but now trying to make it work on windows 8 I have not yet succeeded to find ...
2
votes
1answer
24 views
postgresql 9.3: two instances on same port and two different ip adresses don't work
i'm setting up multiple instances of postgres 9.3 on the same machine (centos 7). I have 2 virtual ip addresses, one instance is bound to first address and second is bound to second one. Same ...
2
votes
2answers
40 views
Trying to set up new data_directory on Postgres 9.3
Ubuntu 14.04, recently installed Postgres 9.3.
Trying to set up a new data_directory to an attached, larger hard drive (/data), and I changed the postgresql.conf file to reflect that.
However, I get ...
0
votes
0answers
16 views
produce a view from the output of a with query
I would like to produce a view based on few tables.
So far I did this:
WITH keyword_group AS(
SELECT
g.id AS group_id
,array_agg(pk.keyword_id) AS keyword_ids
FROM group g
...
0
votes
1answer
6 views
Postgresql warning: type reference X converted to Y (SQL State: 00000 - Error Code: 0)
When I execute:
CREATE VIEW foreign_keys AS
SELECT
tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
...
0
votes
0answers
26 views
Loop update 1000 rows at a time
For the following table I want to loop update something like 1000 rows at a time. I have accidentally from ruby on rails application changed the updated_at timestamp so now old transactions shows up ...
1
vote
1answer
30 views
PostgreSQL db volume at 100%. Can I create a tablespace on another volume so I can vacuum full?
I have run out of space, because I am new to Postgres and I have just not been monitoring it properly.
Now I cannot even vacuum the tables:
ERROR: could not extend file "base/20851/11787": No space ...
0
votes
1answer
24 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 ...
0
votes
0answers
32 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 ...
1
vote
1answer
20 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 ...
0
votes
1answer
28 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 = ...
3
votes
2answers
58 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 ...
0
votes
0answers
44 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 ...
0
votes
1answer
25 views
Bulk insert in multiple tables
I have two tables on which I perform some bulk inserts:
key: key_id(pk), key_name
related_key: key_id(fk), related_key_id
First Insert:
values_data = "($$key_1$$), ($$key_2$$)"
INSERT INTO key ...
0
votes
0answers
33 views
Storing DB credentials in an application source code?
I am making an app which connects to a DB, and the app will have plenty different userslogins.
Is it a good design choice to have a specific DB login/password for the application, using which the ...
0
votes
0answers
7 views
PostgreSQL: Which connections is blocking ALTER TABLE?
I would like to add some fields to a table in all testing and production DBs, all of which are stored in PostgreSQL 9.3 on Amazon RDS.
ALTER TABLE users DROP COLUMN IF EXISTS device_language;
ALTER ...
0
votes
1answer
26 views
Useful guide & FAQ for Application+SQL [closed]
Of course when you are making your first application for fun it doesn't matter that much how you deal with things. But when you need everything to be up to the current standards you don't always know ...
2
votes
2answers
26 views
Compare multiple field to single IN query
I have a situation where I need to compare 3 different fields of a same table to a single IN query, eg:
select unit from trips where owner1,owner2,owner3 in (select id from login)
Can this be done ...
2
votes
2answers
30 views
How to tell which indexes are not being used
Postgresql 9.3
Debian 7
I have a lot of huge indexes in a legacy database I'm trying to optimize. Thinking about dropping all the useless ones, but how can I tell how often they are used and if they ...
2
votes
3answers
120 views
GROUP BY one column, while sorting by another in PostgreSQL
How can I GROUP BY one column, while sorting only by another.
I'm trying to do the following:
SELECT dbId,retreivalTime
FROM FileItems
WHERE sourceSite='something'
GROUP BY seriesName ...
2
votes
1answer
25 views
pg_upgrade and tablespaces (postgres 9.2 to 9.3)
I have a 9.2 installed with tablespaces sitting at:
/var/lib/postgresql/9.2/main
and
/opt/postgres/9.2/second
And unfortunately they are on different filesystems.
This is a Debian package ...
1
vote
1answer
54 views
PGAdmin III Server Status Error: Could not read directory “pg_log” No such file or directory
Running PostgreSQL 9.3 on Ubuntu 14.04 and pgAdmin III 1.18.1 on Windows 7.
I tried Tools->Server Status and I get an error:
"Could not read directory "pg_log" No such file or directory"
I ...
1
vote
1answer
12 views
Create user to monitor database activity on Postgres
Postgres 9.3
Debian 7
I want to create a monitor user called monitor that the only permission is to run queries to monitor all the database activity.
I created the user and it can select from ...
0
votes
1answer
22 views
remote postgresql 9.3 login using pgadmin3 and a public network server to connect to LAN-only machine
I want to connect to a machine running postgresql 9.3. The machine itself is not reachable via a direct IP but is in a LAN with another machine with a public IP address.
My current approach is:
...
1
vote
1answer
46 views
Update different columns on different clauses
I have a database table in Postgres 9.3 with the following layout:
id SERIAL,
col1 INT,
col2 INT
Whenever a new row is inserted, I will have to update ALL rows col1 and/or col2 in different cases ...
1
vote
1answer
19 views
Grant usage partially on schema to user on Postgres
I granted INSERT in a specific table for one user. The problem is that I need to also grant USAGE in the schema for this same user, but granting USAGE in schema also turn all relations in that schema ...
1
vote
1answer
50 views
PostgreSQL Slave has more files in pg_xlog than /wal_archive
Background: for various reasons I am switching PostgreSQL Slave from a Streaming replication to a warm standby replication.
The streaming replication was setup using this guide: ...
0
votes
2answers
75 views
How can I make this query more efficient?
This is the query:
SELECT
races.*, tmptimers.last_start_time, tmplaps.updated_at AS last_updated_at
FROM
races
LEFT JOIN
(SELECT * FROM timers WHERE timers.user_id = 1) AS tmptimers ON ...
1
vote
1answer
117 views
What does exec(text) do?
I just found the following function in two RDS PostgreSQL 9.3 DBs:
--
-- Name: exec(text); Type: FUNCTION; Schema: public; Owner: adam
--
CREATE FUNCTION exec(text) RETURNS text
LANGUAGE plpgsql
...
3
votes
1answer
42 views
PostgreSQL: Trigger for updating view doesn't work
I'm trying to make the trigger for updating a view work.
If I'm doing and INSERT it works! But when I'm trying to do an UPDATE it just doesn't run.
CREATE OR REPLACE FUNCTION test_trigger_function()
...
0
votes
1answer
24 views
PostgreSQL 9.3: pg_dump fails due to tiger.direction_lookup
I am trying to dump the schema from one of my PostgreSQL 9.3+PostGIS databases:
/usr/lib/postgresql/9.3/bin/pg_dump --schema-only
-h stg-db.....rds.amazonaws.com
...
0
votes
1answer
21 views
Unmount/detach/deactivate PostgreSQL database
I manage a server that serves several web applications that use PostgreSQL as a data store. Each web application has a separate role and database, and each database is stored exclusively on a ...
0
votes
1answer
34 views
PostgreSQL streaming versus file-based replication (In terms of server behavior & configuration)
I am trying to understand best uses of PostgreSQL replication and how it works so I can troubleshoot in a production environment.
I am having a hard time understanding the differences between these 2 ...
0
votes
1answer
19 views
Posgresql 9.3: Show domain details
Showing the columns and constrains of a table in Posgresql is done with:
=> \d+ <table_name>
Which lists the columns, data types and modifiers for a table.
How can I show the details and ...
3
votes
1answer
34 views
Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL?
I'm using PostgreSQL 9.3.5.
Suppose I have the following table:
CREATE TEMPORARY TABLE point_test ("name" varchar(255), "pt" point);
I then insert a row, leaving pt NULL:
INSERT INTO point_test ...
2
votes
2answers
181 views
SELECT UNION from two views of the same table
(Sorry the the vague title, if anyone want to re-edit, be my guest!)
I have these tables and views for a factory inventory module :
+----------------------------+ +--------------------------+
| ...
2
votes
1answer
32 views
What's the data flow between Pgpool, Postgresql and client
I'm new in Pgpool.
I want to ask the question, what is the dataflow between Pgpool, Postgresql when client querying data
1/
Client query data to Pgpool,
PgPool ask Postgresql
Postgresql ...
4
votes
0answers
47 views
How to handle bad query plan caused by exact equality on range type?
I'm performing an update where I require an exact equality on a tstzrange variable. ~1M rows are modified, and the query takes ~13 minutes. The result of EXPLAIN ANALYZE can be seen here, and the ...
1
vote
0answers
32 views
Postgres Performance Over Group by with MAX and MIN
I just want to retrieve the data for specific location of customers with their ever first and last purchases made in the system.
Table details :
CREATE TABLE customer_location ( id UUID, location_id ...
0
votes
0answers
31 views
Bucardo status does not feel if the slave is down
I am using bucardo as third party to postgresql replication between two sites. everything ok except buacrdo status <sync_name>.
I have a Master-slave sync on master server. when I poweroff the ...
3
votes
2answers
88 views
Pagination with PostgreSQL 9.3: counting number of pages
I'm implementing pagination and sorting of rows in, let's say, products table, using multicolumn index on category, score and id.
-- index
create index category_score_id on products(category, score, ...
1
vote
1answer
38 views
Restrict two specific column values from existing at the same time
I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed.
I would appreciate any help creating a constraint that will enforce this.
CREATE TABLE example
(
...
1
vote
1answer
117 views
PostgreSQL 9.3 - Performance Issue: Counting table entries slower with jdbc postgres driver
I'm using PostgreSQL 9.3 and want to ask if it is possible to improve the performance of my queries based on the following database schema:
CREATE TABLE "entities" (
"id" BIGSERIAL,
"type" INT ...
1
vote
1answer
56 views
Problems installing Postgis extension on Ubuntu 14.04
I have Ubuntu 14.04 with Postgres 9.3 and I'm trying to install Postgis extension. One of the most recommended and straightforward ways I found is very simple:
sudo apt-get install postgresql-contrib ...
1
vote
1answer
43 views
What is the impact of building Postgres with --disable-thread-safety option?
I am a newbie in the database field. Recently I have been trying to install Postgres 9.3.4 on AIX 6.1. When I was building the code with the configure command, I ran into this error:
checking thread ...
1
vote
1answer
115 views
Missing the pg_ctl package in Postgres 9.3 installation
I have installed postgresql 9.3 on ubuntu 14.04 using "apt-get install postgresql".
Everything was going well until I discovered that I do not have access to the "pg_ctl" commands. Installing the ...
1
vote
1answer
62 views
Improve performance on concurrent UPDATEs for a timestamp column in Postgres
I am using a timestamp column called updated_at for cache invalidation. More information on this particular technique here.
The queries all have the same format
UPDATE "managers" SET "updated_at" = ...
2
votes
1answer
41 views
Prevent postgres function of running more than 1 instance at the same time
DBMS: Postgres 9.3.4
OS: Debian 7
I have a plpgsql function that will be put in crontab to run every 15 minutes. The function should finish in about 8 minutes, but just in case it takes more than 15 ...
