PostgreSQL 9.3 : 2013 release of PostgreSQL

learn more… | top users | synonyms (1)

0
votes
1answer
8 views

How to use a value from insert in conditional insert in postgres

insert into addresses(street, city, user_id) select 'street1','LA', 2 where exists (select * from users where users.id = $$$$$ AND users.storeaddress='true')) I want to insert address for a ...
-6
votes
1answer
37 views

Postgres Dump and Restore on Linux Terminal

I need to take a dump in postgres db and to be restored on some other server. please tell me the command in Linux with compressed and encrypted format.
0
votes
1answer
18 views

Convert two columns to json

I have a json column with a json array in the form of [{key : "key", value : "value"}, {key : "key2", value : "value2"}]  that I wish to convert to a new form {"key" : "value", "key2" : ...
1
vote
1answer
23 views

Create one row for every user in a Postgres table

I have a bunch of rows of users in a users table, for example: | id | name | |----|-------| | 1 | Chris | | 2 | Max | | 3 | Steve | For each one of these users, I'd like to create a row in ...
1
vote
0answers
27 views

Query planner slow to use newly created index on database under heavy load

We have some heavily loaded databases with more than 1k requests / sec and have started to have problem with index maintenance. Sometimes we need to update/recreate one of the indexes we have, for ...
2
votes
1answer
29 views

Adding new null column to table caused lock

Yesterday we ran a schema migration on our production database that we thought would be safe, but ran into an issue. It seems as though adding the new column caused a lock and our application could ...
2
votes
1answer
40 views

Switching replication master in cascading replication scenario

Imagine I have 3 PostgreSQL 9.3 databases (A, B, and C) set up in a cascading, streaming replication scenario (i.e. am NOT manually shipping WAL files, am having Postgres ship them over TCP ...
1
vote
1answer
26 views

Can't create user in postgres

When I run CREATE USER username WITH PASSWORD 'test' in sudo -u postgres psql and run \du only postgres user appears... I'm not prompted anything when running commands like ALTER USER and I know USER ...
1
vote
1answer
44 views

postgres Poor performance on ORDER BY “id” DESC LIMIT 1

I have table items with following schema (in postgres v9.3.5): Column | Type | Modifiers | Storage ...
2
votes
2answers
23 views

Use PostgreSQL builtin operator <@ after including extension intarray

I added the intarray extension to my PostgreSQL database in order to make use of the - operator, but I still want to use the builtin <@ operator, not intarray's <@ operator. Is there a way to do ...
0
votes
1answer
32 views

PostgreSQL full-text search retrival from id gap

I have a full-text search column on one of my tables in PostgreSQL 9.3 implemented based on this tutorial (without the South part). There is a postgres function where I SELECT concat_ws(' ', ...
1
vote
1answer
22 views

PostgeSQL 9.3: Is it safe to stop VACUUM FULL?

I'm using PostgreSQL 9.3 on RDS. Once in a while, I run a VACUUM FULL operation on the database. However, such operation can take quite a while and it blocks other tables, so the need to stop the ...
0
votes
0answers
15 views

requested WAL segment ***** has already been removed

Yesterday, I tried to run 'vacuum freeze' a table on Master instance. After that, I keep getting this error message "requested WAL segment ****** has already been removed" in the postgres log. I ...
0
votes
1answer
35 views

PostgreSQL bug(?) - logging with a minimum log level

I am using the log_min_error_statement - Setting in the PostgreSQL configuration file, but the logger does not react on the setting, either if I turn it on, or off, or set it to another level, the ...
3
votes
0answers
39 views

Efficiently computing aggregate functions over subqueries with incremental data

I have a PostgreSQL database (version 9.3.6) containing a large number of orders. As the orders are processed, scan_events are triggered and stored, with multiple events per order. Scan events have a ...
5
votes
1answer
69 views

What do the shared buffers metrics for various types of index scans represent?

TL;DR: When an "EXPLAIN (BUFFERS)" shows an "Index Scan", it does not show the number of pages needed to be read from the index. Is it just omitting that number, or does it actually not read pages ...
0
votes
1answer
29 views

Create postgres schema in db with multi user in AUTHORIZATION

Is it possible to create a schema that is owned or accessible by more than one user in the same database? I was reading postgres create schema documentation and postgres alter schema documentation ...
0
votes
0answers
17 views

Tuning Postgresql autovacuum [duplicate]

Setup I have a problem similar to the one described and not solved here , with some differences... I am using Postgresql 9.3 on Ubuntu 14.04 where I have a Django project running with celery. I ...
0
votes
0answers
25 views

PostgreSQL 9.3: Minimize locking when updating a large table with primary key

I am building a DB solution to handle a fast stream of online events. Each event UPSERTs a row in a location_update table, using its car_id primary key. Since there are very few new car_ids, the ...
0
votes
0answers
25 views

UPSERT-ing large batches in PostgreSQL 9.3 on AWS RDS

Consider the following location updates table , which keeps the most recent location update of each car_id. The table has the following columns: car_id - a textual primary key geom - a PostGIS ...
5
votes
0answers
50 views

Complete deadlock on PostgreSQL 9.3 after removing a numeric id

The problem - complete DB lock after a schema change Architecture I use a PostgreSQL 9.3 with PostGIS to store location updates. The updates are written to two tables - location_updates and ...
1
vote
1answer
37 views

Strange Intermittent Postgres Performance Issue (docker?)

I am running a local Postgres 9.3 in a docker container with a temporary database that is used for some file import operations. The basic scenario is: Load files into input tables INSERT from a ...
-2
votes
2answers
35 views

How can i get list of postgresql database through php or using just sql query? [duplicate]

What i want to do is to make a drop down list of all the databases of my postgresql 9.3 server using php . if i can get just the sql script that can do the trick then i can handle the php part. ...
1
vote
1answer
28 views

Unexpected results with soundex() function when combined st_distance()

select soundex(A.name), A.gid, A.name, soundex(B.name), B.gid, B.name, st_distance(B.the_geom,A.the_geom) * 111.325 as dist from (select name, ...
2
votes
0answers
24 views

PostgreSQL/PostGIS: Query seems inordinately time-consuming

I'm doing what I would consider a pretty straightforward query on two tables that aren't huge (~626k records in one; ~47k records in the other). Both tables have GIST indices on their spatial ...
4
votes
2answers
73 views

Grouping by sequential relationships with Postgres

This is quite similar to this question about continuous ranges, and this question about grouping by sequential numbers, but differs in that the sequences are not numeric. Given the following ...
3
votes
2answers
128 views

Filter and append data to row

My query and my data set (copied from sqlfiddle): CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, ...
3
votes
1answer
88 views

psql breaks if I specify host localhost

I am aware that this or similar has been asked in other places. None of the other answers have worked for me. Machine is a Macbook Pro 2014 / OS X Yosemite 10.10 with Postgres 9.3.5 installed via ...
0
votes
1answer
44 views

How to find similar (but different) names with the same soundex value?

I got the output with the same name and same soundex value, but I want the result which has similar name and same soundex value. Can anyone help me to do this?
1
vote
1answer
46 views

How can I improve this query in PostgreSQL?

I've been using PostgreSQL for a little over a year. I am continually whacked by queries that have counts in them, such as: SELECT COUNT(*) AS count_all, listings.id AS listings_id FROM "listings" ...
3
votes
1answer
30 views

Single table or multiple table design when records have a clear distinction but are queried together?

Let's say I have the following types of recurring data (all types periodically come in). Post (Title, Summary, Text, Timestamp) Image (URL, Caption, Timestamp) Event (StartDate, EndDate, Title, ...
0
votes
1answer
34 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
67 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
109 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
29 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
70 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
59 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
55 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
31 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
28 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
46 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
76 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
122 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
59 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
51 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
19 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
40 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
74 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
77 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
113 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 ...