for PostgreSQL questions specific to version 9.3.
1
vote
0answers
18 views
Upload of big files to data directory of postgresql 9.3
After 2 weeks of huge efforts to solve one postgresql problem I'm coming here as last hope.
What I'm trying to do:
I need to store and retrieve huge files (sizes between 1 MB to 2000 MB) to ...
0
votes
1answer
22 views
How to COALESCE a timestamp column?
In PostgreSQL 9.3 I have the following table with 2 timestamps:
create table pref_users (
id varchar(32) primary key,
first_name varchar(64) not null,
last_name varchar(64),
...
1
vote
0answers
19 views
PostgreSQL error: query string argument of EXECUTE is null
I have a table called evidence with a trigger which calls a stored procedure which basically does table partitioning by month. However I get an obscure error when I start inserting lots of rows under ...
0
votes
0answers
18 views
Install and use PostgresSql 9.3 on Amazon EC2
. Hi, everyone
I'm having a problem with an Amazon Linux (CentOs based) instance when trying to install the latest stable Postgres (version 9.3).
That version doesn't ships in the main repositories ...
3
votes
1answer
28 views
How to check if deletion is caused by CASCADE in PostgreSQL trigger
In PL/pgSQL trigger function, is there a way to know that the deletion was invoked by cascading delete action?
I have a number of checks in a trigger function to see if deletion is allowed, which I ...
-2
votes
0answers
13 views
Performance tuning in PostgreSQL [on hold]
Question: How to improve performance of query in PostgreSQL 9.3 version?
I have a tables and views of huge data in it. And I have added indexes for tables and required views, and using INNER JOIN ...
0
votes
1answer
32 views
Rails+Postgres - hstore automatic conversion of strings
I'm using hstore on one of my models to store a hash with float values. I need to periodically access and perform calculations the values in the store.
My current solution for working with these ...
3
votes
0answers
28 views
Postgresql permissions keep failing
I am running PostgreSQL 9.3.2 on Linux.
I have a group role 'data_scientist' and permissions to a particular (already populated) schema like so
grant usage on schema schemaname to data_scientist;
...
1
vote
1answer
15 views
Postgres 9.3, hstore 1.1, unknown operator '?'
I have a log table that collects table changes using hstore. Something similar to:
CREATE TABLE "gt_wells"."log_edits" (
"well_gid" SERIAL
, "modified_by" TEXT
, "edit" HSTORE
...
0
votes
2answers
23 views
Get sum of integers for UNIQUE ids
In a game using PostgreSQL 9.3 as backend I am trying to limit the number of games played by a user per week.
I have prepared an SQL Fiddle, but unfortunately it doesn't work.
My (test, not ...
0
votes
0answers
13 views
django south, creating a table with foreignkey to another south created table
I have created a table using django south, the code is.
MODEL
class Task(models.Model):
t_project_id = models.ForeignKey(Project,related_name="task")
t_name = models.CharField(max_length=200)
...
-1
votes
1answer
20 views
Postgres Output not cosntant, Unix command
Hello I'm using a unix command to execute an postgres psql script, which creates a DB, in theory it could be more complex, after this command i am using ">>" to save the output on a file, the thing ...
0
votes
0answers
13 views
Upsert on PostgreSql Json Array Field
I have a table with a json field. I plan on storing a json array of tags in that field e.g. ["beach","car","phone"]. How do I perform actions like, add a tag to the array, delete a tag from the array ...
0
votes
1answer
31 views
How to install PL/Python on PostgreSQL 9.3 x64 Windows 7?
I have tried to install the PL/Python v2.x language inside PostgreSQL on my database running the query:
CREATE EXTENSION plpythonu;
(I got this from ...
-4
votes
2answers
32 views
Mysql vs Postgresql which one should I Choose? [closed]
I am going to build a website for posting of projects and biding for projects online
my web site may have 1000 of connections at the same time
also the data is very important, I am developing my ...
0
votes
0answers
7 views
How to upload/restore sample database to PostgreSQL 9.3?
I use pgAdmin 3 to upload sample database (the "dvdrental" db in the psql site) to
PostgreSQl 9.3. I downloaded dvd.zip file and then converted it to dvdrental.tar. I created dvdrental database but ...
0
votes
0answers
35 views
Why is Postgres sending data somewhere?
I installed PostgreSql 9.3.4 on my Mac Book using the graphical installer from EnterpriseDB.
I’m just using PostgreSql for development and I’m only using it from localhost, however I found that one ...
2
votes
1answer
25 views
Inserting valid json with copy into postgres table
Valid JSON can naturally have the backslash character: \. When you insert data in a SQL statement like so:
sidharth=# create temp table foo(data json);
CREATE TABLE
sidharth=# insert into foo values( ...
0
votes
1answer
7 views
cluster and partion on postgresql
I have a table with 200.000.000 rows and I'd like to reduce the size of this table. I want to use partition and cluster. So my question is:
Can I use cluster on a partitioned table in postgresql
...
0
votes
1answer
39 views
Efficient update statement in PostgreSQL
I have a large table (around 10M records) in a PostgreSQL 9.3 database, and I'm trying to run a simple update statement:
UPDATE mytable SET fresh = null WHERE fresh = true;
and it's been running ...
1
vote
1answer
60 views
PostGIS: Merge Multipolygons and keeping boundaries
We are trying to merge two Multipolygons which are stored in our PostGIS 2.1 database without losing the boundaries that are contained in each Multipolygon.
Our spatial data matches the following ...
0
votes
0answers
20 views
Postgres 9.3 and deleting large object
Setup a Postgres DB, where I access them from an Spring Application. All works fine, with one exception: The deleting of the file (time of great files).
Is there a way (with a parameter setting) to ...
1
vote
1answer
21 views
How to add data file to PostgreSQL database tablespace?
I am using PostgreSQL and I want to add a new data files to existing tablespace ( like in oracle )
I could not found any description on this on PostgreSQL documentation.
How to do this ?
3
votes
4answers
43 views
Postgresql LEFT JOIN json_agg() ignore/remove NULL
SELECT C.id, C.name, json_agg(E) AS emails FROM contacts C
LEFT JOIN emails E ON C.id = E.user_id
GROUP BY C.id;
Postgres 9.3 creates output for example
id | name | emails
...
0
votes
1answer
17 views
Extract an array from a postgres json column and map it
create table test(a json);
insert into test(a)
values('{"orders":[{"orderId":1}, {"orderId":2, "status":"done"}, {"orderId":3}]}');
Given the structure above, can I get an array or set of orderIds ...
2
votes
1answer
36 views
Postgresql Trigger function for setting value in column based on most recent timestamp
I'm having troubles with creating a trigger function in postgresql which should do the following:
I have a table with category(varchar), edit_date(timestamp) and actual(boolean).
I already have a ...
0
votes
1answer
24 views
Laravel 4.x migrations on production environment
I'm an experienced DBA who's not very experienced with Laravel. My primary developer is moderately experienced in Laravel, however, has a tendency to gloss over Database details. The issue at hand is ...
4
votes
2answers
58 views
Postgres: Best way to move data from public schema of one DB to new schema of another DB
I am new to Postgres and just discovered that I cannot access data of different databases in one SQL query. And also learned the concept of schema in Postgres.
Now, I have two databases
db1 and db2
...
0
votes
0answers
15 views
Postgresql count(*) very slow on big table
SELECT
T . ID,
T . DATE,
T . TIME,
T .i_want,
T .i_want_date,
T .i_want_date_to,
T . ACTION,
T .country,
T .url,
T .user_agent,
(
SELECT
client. NAME
FROM
client client
...
0
votes
1answer
11 views
Connecting new PostgreSQL 9.3 installation to a disconnected database
I do not know what happened...I think lightening had something to do with it... In either case, a once happy and running PostgreSQL installation no longer exists on my windows 7 machine. Not even the ...
1
vote
0answers
23 views
nested views much slower in Pg 9.3.4 than 8.4.8
We recently upgraded from Pg 8.4 to 9.3 and soon noticed that nested views which ran in a few seconds in 8.4 take 100 times longer or never complete w/in a reasonable (10 min) amount of time in 9.3.
...
0
votes
2answers
25 views
Call a Postgres function that returns a record, passing in the result of a query
The problem is that I need to run a query (only known at run-time) to determine the input parameter to my function, which needs to return a result. For example, I want to write:
CREATE FUNCTION ...
1
vote
2answers
42 views
postgresql using json sub-element in where clause
This might be a very basic question but I am not able to find anything on this online.
If I create a sample table :
create table dummy ( id int not null, data json );
Then, if I query the table ...
0
votes
0answers
28 views
CartoDB “Error loading rows, check your SQL query” & “There is a pb with your connection” [migrated]
I have been trying to install my own CartoDB server for a few weeks.
I succeeded to install each part with no error (after few corrections) with this tutorial.
Make check with CartoDB-SQL-API and ...
1
vote
1answer
43 views
Return row from upsert method
I have an upsert function that I modified from the documentation. However I have been trying to return the updated or inserted row. I'm calling this function from a node application and I need to ...
0
votes
0answers
39 views
How to ignore an INSERT failed because of DUPLICATE KEY?
In a web game with PostgreSQL 9.3 backend I sometimes have to ban users, by putting them into the following table:
create table pref_ban (
id varchar(32) primary key,
first_name varchar(64),
...
-1
votes
0answers
25 views
COALESCE functionality in postgres
Below is sql server function with coalesce pre-defined function. could please help me to convert this function in to postgreSQL.
{
CREATE FUNCTION [dbo].[FN_MEDICALADD_INFOVALUES](@TYPE_LKUP_NBR ...
0
votes
1answer
23 views
Disabling WAL archiving during pg_restore?
Let's say I made a quick backup dump of my Postgres 9.3 DB through pg_dump before doing a large destructive migration and I discovered I want to undo it. No writes were performed against the DB in the ...
4
votes
1answer
41 views
How to use column default in the where clause to identify default value
I am trying to write a query that tells if a column named s in table a.t has its default value (which is a very big base64 string). So I tried:
SELECT 1 FROM a.t WHERE s = (
SELECT column_default ...
0
votes
1answer
14 views
Postgres setting bit size dynamically
The goal is to fast extract bit at position N;
So far i've found only this way to do that:
CREATE OR REPLACE FUNCTION test(x int, size int)
RETURNS int AS
$BODY$
DECLARE
y int;
BEGIN
y = ...
0
votes
2answers
39 views
How to trim whitespaces in JSON datatype of Postgres?
When I do:
SELECT cast('{"viewport" : { "northeast" : { "lat" : 40.7155809802915, "lng" : -73.9599399197085 }, "southwest" : ...
1
vote
1answer
41 views
Delete duplicates in postgres
I want to delete all but one row for a given duplicate "external_id". The query below takes about two minutes to run for my table of 5,000,000 rows, and I feel like there's got to be a quicker way of ...
1
vote
0answers
36 views
ipython notebook and psycopg2 don't work together
I am new with using mac os, maybe thats why i got such troubles.
So firstly I found out problem with running ipython notebook and successfully solved it with this two commands in shell (thx to ...
0
votes
0answers
10 views
pgadminIII database location
I have using postgresql9.3 with pgadminIII. I have successfully imported database from WindowsMSSql to Ubuntu. How do find the database location?
2
votes
2answers
51 views
Fill in NULL column values with lag(N) values
I have a simple table that is missing values for the "somebody" column. I want to fill in the NULL values with previous values in ascending order from the id field, but not the descending values ...
2
votes
1answer
112 views
Refresh a materialized view automatically using a rule or notify
I have a materialized view on a PostgreSQL 9.3 database which seldom changes (about twice a day). But when it does, I'd like to update its data promptly.
Here is what I was thinking about so far:
...
0
votes
0answers
29 views
Decrease WAL file size for backup [migrated]
I'm setting up a backup strategy based on WAL files archiving and transmission via rsync.
As
DB activity will propably be very low (less than 100 inserts/updates/deletes per day), at least in the ...
0
votes
1answer
36 views
postgresql: Peer authentication failure with md5
In setting up my postgresql installation, I determined that a user attempting to log in with a password, when not on its respective OS user, is failing authentication with the following message:
$ ...
0
votes
1answer
27 views
Hstore index row size maximum error
Using Rails 4 and trying to do an index on my hstore column.
My migration file:
class AddIndexToProfiles < ActiveRecord::Migration
def up
execute "CREATE INDEX profiles_metadata_gin_data ...
1
vote
2answers
53 views
Parallel unnest() and sort order in PostgreSQL
I understand that using
SELECT unnest(ARRAY[5,3,9]) as id
without an ORDER BY clause, the order of the result set is not guaranteed. I could for example get:
id
--
3
5
9
But what about the ...
