PostgreSQL 9.3 : 2013 release of PostgreSQL

learn more… | top users | synonyms (1)

0
votes
0answers
19 views

When does pg_dump not export CREATE EXTENSION?

I have a production database that I am trying to dump then load (in plain text format) locally. The problem is, the database being dumped uses the hstore extension in several tables, and contrary to ...
2
votes
2answers
42 views

Locking issues under transaction on Postgres

I recently got a "Out of shared memory - You might need to increase max_locks_per_transaction" error. This is a long running process where I import a bunch of CSV files after proper processing. I ...
1
vote
1answer
45 views

stored procedure vs. function in postgresql and which is better for executing dml statements

I'm using postgresql 9.3. If I want to add the following steps and call these steps as a routine, would it be better to use a function or stored procedure? My basic steps are: RENAME TABLE ...
1
vote
2answers
20 views

Migrating PostgreSQL files to new server

We have a Win 2008 server that went down because of cpu failure. We were running parts of the Atlassian stack (JIRA, Confluence, Stash, and Bamboo) on it using PostgreSQL as the database. I now have ...
1
vote
1answer
34 views

Update staging data with production data in Postgresql

I'm using PostgreSQL (version 9.3) on my server and I want to update the data in my staging instance database with the data in my production instance database. Each database is controlled by a ...
1
vote
1answer
38 views

Does VACUUM FULL need ANALYZE

I'd be grateful for some insight into the VACUUM functionality within PostgreSQL 9.3. I read through the documentation and searched around a bit but could not find a definitive answer to this. I'm ...
2
votes
1answer
47 views

postgresql - how and why indexes are bigger than their tables

I'm using postgresql 9.3 and trying to understand how and why indexes are bigger than their tables. Sample output: database_name | database_size | table_name ...
0
votes
1answer
30 views

What happens when a CTE query is interrupted

I have the following query. WITH d_rows AS (DELETE FROM t_a RETURNING * ) INSERT INTO t_b SELECT * FROM d_rows; What will happen if i cancel this query (Ctrl+c in psql)? Will the delete run? Does ...
0
votes
1answer
26 views

Backup-strategy for redundant data in postgresql-db.

I have a (postgresql-9.3)-table that contains images as a bytea-column as well as several other columns that contain extracted information from these images. (Some feature points, descriptors). These ...
3
votes
3answers
37 views

PostgreSQL GRANT ALL on mutliple tables using LIKE

I have more than 100 tables named public.test_* How can I easily GRANT ALL access to the user test to all that tables at once? I tried with: GRANT ALL ON TABLE public.test_* TO test; But it is ...
2
votes
1answer
63 views

Best index for similarity function

So I have this table with 6.2 millions records and I have to perform search queries with similarity for one for the column. The queries can be: SELECT "lca_test".* FROM "lca_test" WHERE ...
2
votes
3answers
101 views

How to prevent a PostgreSQL trigger from being fired by another trigger?

I have 2 triggers on one table; one works for INSERTs : CREATE TRIGGER "get_user_name" AFTER INSERT ON "field_data" FOR EACH ROW EXECUTE PROCEDURE "add_info"(); This updates some values in the ...
0
votes
0answers
28 views

Check if key exists in a JSON with PL/pgSQL? [migrated]

I'm trying to check if a key exists in a JSON sent as parameter in a PL/pgSQL function. Here is the function. CREATE FUNCTION sp_update_user(user_info json) RETURNS json LANGUAGE plpgsql AS ...
0
votes
0answers
14 views

Sync offline database with online

I have a rails application which is currently running on cloud. We want to serve our application to rural locations where Internet is not available. We will be deploying our rails application on local ...
1
vote
0answers
38 views

What is the best way to clear couple of tables each night at specific hour in Postgres SQL?

I need to clear couple of tables every night on my db so in the morning they will be empty and ready to use for the end users. I already wrote the query to truncate them but I'm not sure what is the ...
0
votes
1answer
18 views

Select row if column data was not selected

How can I selected rows from a table when a column data is different. Table: id(pk), name, title, book_id, date I want to get all the rows where book_id is uniq. Data example: test1, test2, 1, ...
-1
votes
1answer
32 views

how to aggregate time and data correspondingly using SQL?

I want to aggregate the time series and the data under each time series, the table I have now is: 00:00,00:15,location_1,300 00:00,00:15,location_2,240 00:15,00:30,location_1,100 ...
2
votes
0answers
50 views

Postgresql upgrade issue

I'm trying to test the Postgresql 9.3 -> 9.4 upgrade process on the development environment on windows and I get weird issue during the pg_upgrade invocation: PS D:\bin\postgres-9.4> ...
3
votes
1answer
36 views

Local Backup from Amazon RDS PostgreSQL database

I have a PostgreSQL database on an Amazon RDS instance. I want to have a local dump of this instance. So far, I've tried with this: PGPASSWORD="Password" pg_dump -h ...
0
votes
1answer
36 views

Adding elements into PostgreSQL json array field

I have table where one of the fields is JSON array. I need to append received JSON array into that field without overriding existing values. Something like that: CREATE OR REPLACE FUNCTION ...
0
votes
2answers
26 views

Table constraint, number of row taken by a fk key

Is it posible to add a table constraint, to prevent an insert if there are already 3 row of the same fk key My table: ProjectTitles (id(pk), project_id(fk), title_id(fk)) Check if project_id has ...
0
votes
0answers
20 views

doing a insert while selecting an specific user

I'm importing and legacy database to a new database, and changing data format. In this legacy database, I have data as following: Interaction id: 5458d717cd07870859000004 user_id: ...
0
votes
0answers
14 views

Why postgresql pg_dump exports a view as a table?

I am using postgresql 9.3 pg_dump tool for extracting only the public schema definition using: pg_dump -s -n public -h host -U postgres --dbname=db > ./schema.sql but when I check schema.sql one ...
1
vote
0answers
42 views

Master/Slave - streaming replication restrict function to master

How can I run a function a on master only? I don't want to connect directly to my master server, because the master server might change. Or can I change my function to work on this setup? Because ...
0
votes
0answers
21 views

PostgreSQL updates hang when two servers involved

I have an update statement that works fine when only a single server is involved, but never completes when it comes from two servers. I'm connecting with JDBC 9.1-902 to a 9.3 database on CentOS 6.6. ...
-1
votes
3answers
74 views

psql: could not connect to server and socket /var/run/postgresql/.s.PGSQL.5432

I have installed postgresql 9.4 and I removed postgresql 9.3 from my computer. Now when I try to run: foo@bar:~$ psql I get the error psql: could not connect to server: No such file or directory ...
0
votes
0answers
14 views

Unable to import database through osm2pgrouting

I am very frustrated by the following errors while trying to import NewDelhi by osm2pgrouting tool: osm2pgrouting -file "/home/user/Downloads/new-delhi_india.osm" -conf ...
1
vote
1answer
53 views

Reduce the number of queries to the same table

I need to build some reports using two tables: AuthorTitles: id(pk), author_id, title_id AuthorReports: id(pk), author_id, title_id, items_sold, date My report needs to output the fallowing: ...
1
vote
1answer
64 views

Implementing json_object_agg() in Postgres 9.3

I feel like I need the json_object_agg() function of Postgres 9.4 but I will not be able to upgrade from 9.3 right now. Is there a way to do what I want in 9.3? Here's my scenario. I have a table ...
4
votes
2answers
98 views

Optimizing queries on a range of timestamps (one column)

I am using Postgres 9.3 through Heroku. I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ...
0
votes
0answers
20 views

PostgreSQL - Calculating opening and closing balances

I am having the following scenario some time right after midnight I'd like to calculate was the closing balances were for the day before and the opening balances for the current day. Those two values ...
2
votes
2answers
27 views

Aggregate all joined rows with where clause

Three nested tables exist: user_groups, users, projects. Each user_group has many users and, in turn, each user has many projects. Each project has an ordinal assigned, 1-5 (step_number). I would ...
0
votes
2answers
68 views

Automatically set value on child rows when deleting parent row?

Description We have a table and data below : Version: PostgreSQL 9.3 Table name: tree_data (id int, code text, name text, parent_id int) Primary key: id Foreign key: parent_id (refer to id) and ...
1
vote
1answer
93 views

Postgresql query history hacking

I believe that PostgreSQL can log slow or unsuccessful queries. Probably I can set also PostgreSQL to log all queries executed. Contrarily, I am interested to know if there is a way that a malicious ...
0
votes
0answers
20 views

A PostgreSQL user with SUPERUSER and all permissions to particular database can't read a specific column [closed]

I am trying to edit a website. I created a user for myself and granted all permissions and set up that user as SUPERUSER, using the user postgres. I still can't read a specific column (which is ...
1
vote
0answers
59 views

Query for a table with paging and filtering vs. CTE (common table expression)

Intro In PostgreSQL 9.3: I am building a query that fetches data for a table that supports sorting, filtering and paging. Think Customers for example, you want to show name, surname, some detailed ...
1
vote
1answer
84 views

Replication Lag on Postgres AWS RDS Service

We have a single master/single streaming replica Postgres 9.3 db on AWS. The load is not terribly high - this is a development/staging environment. (The production shows similar metrics). Point is the ...
1
vote
1answer
27 views

purchasing item by client - how to ensure that user has enough funds?

I have to tables: users (id serial, coins integer not null default 0) purchases (id serial, price integer not null, user_id) When new row is inserted into purchases table I want to subtract amount ...
1
vote
1answer
129 views

Foreign Data Wrappers & Windows Authentication

I been trying to get data from a SQL Server to use it on a PostgreSQL database. After a previous question I got into use FOREIGN DATA WRAPPERS, first I try with tds_fdw but after a successfull install ...
3
votes
2answers
298 views

How to get exception context for a manually raised exception in PL/pgSQL?

In Postgres, we get the "stack trace" of exceptions using this code: EXCEPTION WHEN others THEN GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT; This works fine for "natural" ...
0
votes
1answer
50 views

What will be best (for performance) transaction isolation level?

There, are two types of queries that are executed in my app: DELETE FROM tbl1 WHERE user_id = 1; INSERT INTO tbl1 VALUES (...), (...), ...; and UPDATE tbl2 WHERE id = 1 SET ...; DELETE FROM tbl3 ...
3
votes
2answers
163 views

Have: Postgres database files. Need: Functioning database

I am running Ubuntu 14.04 with a postgres/postgis setup and in a weak moment removed PostgreSQL 9.1 (seemingly a legacy install from 12.04 from before I upgraded to 14.04), having erroneously ...
1
vote
1answer
25 views

Search results mismatch after postgresql update

Today we had to update the PostgreSQL version on our database servers. Before update the version was 9.3.4 and the new one is 9.3.6. Despite the small difference in the database version and the ...
2
votes
1answer
70 views

Linking SQL Server tables to a PostgreSQL Server

I have a SQL Server 2005 instance and also a PostgreSQL 9.3 running on the same network, I would like to know if its possible to somehow create or have 'views' on a database within the PostgreSQL ...
2
votes
0answers
70 views

Unique Index much bigger than the table itself

Pg 9.3 on debian 7. I have a very huge table in rows, about 178 million (will turn 250 million in a week and then grow 500 thousand a day), but not that huge in size, wich is 20GB, it's a small ...
1
vote
1answer
42 views

Return type of JOINing stored procedure - PostgreSQL

Is it possible to express return type of stored procedure that returns joined tables using table type of one of joined tables? My stored procedure looks like this: CREATE or REPLACE FUNCTION ...
1
vote
1answer
180 views

How to return values from “upsert” query in case when UPDATE has been performed?

WITH upsert AS (UPDATE tbl SET a = 2 WHERE a = 1 RETURNING tbl.*) INSERT INTO tbl (a) SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert ) RETURNING * This "upsert" statement works however I would like ...
3
votes
1answer
56 views

GRANT USAGE on all schemas in a database? postgres

I want to GRANT USAGE to a user/role for a given database. The database has many schemas. I know there's an ON ALL TABLES IN SCHEMA, but I want "all schemas". I tried GRANT USAGE .. ON DATABASE, but ...
4
votes
3answers
249 views

Custom sorting of a query

The application I am working on displays some tasks based on status, and date (in that order). However, for a particular status, the sort condition should be inverted. For example, a table which looks ...
1
vote
0answers
34 views

How to return updated data from UPDATE … FROM query?

UPDATE "tbl1" SET "col" = 'val', FROM "tbl2" WHERE "tbl2".id = 1 RETURNING * This query returns values from tbl2 instead of updated data from tbl1 - how can I get updated data? (PG 9.3).