Tagged Questions
0
votes
2answers
26 views
PostgreSQL row inserting CTE with extra returning fields
Long time reader first time asker here!
In my database, I have the following (simplified) sample tables with some example data,
create table file_zips (file_id int, zip varchar(5), total int);
...
3
votes
1answer
100 views
Postgres ltree query, count joined items on each level of tree
I have 3 tables:
LOCATION, LOCATION DESCRIPTION which holds languages for each location etc and 1 for STORE.
LOCATION DESCRIPTION table also holds the hierarchy in an ltree path field like below:
...
2
votes
1answer
131 views
PostgreSQL: duplicate key value violates unique constraint on UPDATE command
When doing an UPDATE query, we got the following error message:
ERROR: duplicate key value violates unique constraint "tableA_pkey"
DETAIL: Key (id)=(47470) already exists.
However, our UPDATE ...
1
vote
1answer
51 views
Search entities by weighted keywords and spelling correction
For starters, a little diagram relations-entities
And now, a dataset
Archive
create :
CREATE TABLE archive (
id integer NOT NULL,
parent_id integer,
code character varying(15) NOT ...
1
vote
1answer
19 views
Poor performance on contained by query for tstzrange
I have 2 tables:
account_transaction:
+-------------------------------+--------------------------+------------------------+
| Column | Type | ...
0
votes
1answer
9 views
pgbench for postgresql 9.3 for centos where to find it?
How can I install pgbench for postgresql 9.3?
I have basically set up my postgresql9.3 on centos 64 bit, and it runs fine. No problem at all.
I then installed postgresql93-contrib on my centos ...
0
votes
1answer
12 views
How to get user creation timestamp in RDS Postgres 9.3.1/9.3.2/9.3.3
I am able to get user creation timestamp in Amazon Redshift like below:
redshiftpocdb6=# select username,recordtime,valuntil from stl_userlog where username = 'u002';
username ...
0
votes
1answer
16 views
Execute function returning data in remote PostgreSQL database from local PostgreSQL database
Postgres version: 9.3.4
I have the need to execute a function which resides in a remote database. The function returns a table of statistic data based on the parameters given.
I am in effect only ...
0
votes
1answer
28 views
Postgres multiple database connection
This can fall into the category of MSSQL USE database. I'm building a Postgres data warehouse for an existing transactional Postgres application. One of the main goals is to offload report generation ...
0
votes
1answer
46 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 ...
0
votes
1answer
53 views
Change JSON by trigger in PostgreSQL 9.3
I have some sequence and table:
CREATE SEQUENCE test_uid start 1;
CREATE TABLE test (
some_data JSON
);
How can I make trigger which add id field by sequence and modified field by timestamp ...
0
votes
1answer
105 views
Postgresql collation
is it posible to define collation for connection? I'm connecting to postgresql server 9.3 via php pdo. I know that I can set collation for server, column or define it in select statement but I need it ...
0
votes
1answer
64 views
granting database access privileges in postgres
I have a database currently with the correct permissions. I'm attempting to create a duplicate but i can't get the permissions to be the same.
i created a dump with
pg_dump database1 > dbdump
...
0
votes
1answer
167 views
Postgres jdbc driver problems with encoding
I use 9.3-1100-jdbc4 driver and I have UTF-8 as default encoding in Eclipse.
When I run my application I get stack trace but I can't read it
Error opening session. Cause: ...
0
votes
1answer
51 views
What is the difference between #> and ->> operator in PostgreSQL?
We can access any JSON element in PostgreSQL 9.3 using the -> and ->> operators. Seems to me that the #> along with #>> only provide a shorter form of writing the JSON path. Or is ...
0
votes
1answer
222 views
How to use a GIST or GIN index with an hstore column in Postgresql?
I'm playing around with postgresql 9.3's hstore. I'm trying to use and index for an hstore column just like documentation states. MY problem is that the index appear not to be used. Let me give you an ...
3
votes
0answers
56 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;
...
2
votes
0answers
66 views
Postgresql 9.3 group by overlapping/adjacent ranges
My Data
I have a table that has a few columns of type TSTZRANGE that represent nested timelines.
It looks like this:
CREATE TABLE data (
KeyID int8 NOT NULL,
Part int4 NOT NULL,
Value ...
2
votes
0answers
142 views
Multiple ways to create index on a json field's nested property in PostgreSQL 9.3
In PostgreSQL 9.3, there are multiple ways to build an expression, which points to a json field's nested property:
data->'foo'->>'bar'
data#>>'{foo,bar}'
json_extract_path_text(data, ...
1
vote
0answers
26 views
How to make PostgreSQL use the proper query plan with this JSON
I'm using PostgreSQL 9.3.4. I have a table which is defined as follows:
CREATE TABLE report (id int, keyval json, time timestamp);
The table includes a column (keyval), specified as JSON. It ...
1
vote
0answers
17 views
immutable strict ignored by plv8 function
I thought immutable strict ment the database could not be modified.
The following inserts a new row in 'some_table':
CREATE FUNCTION insert_row() RETURNS void AS
$$
plv8.execute('INSERT INTO ...
1
vote
0answers
24 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 ...
1
vote
0answers
44 views
Nearest non adjacent point
There is a point column in a table. I need to add another point. The new point can not be nearer than a certain distance from the existing points. The distance is given by the <@> operator of ...
0
votes
0answers
20 views
Actual rows value in explain analyze output changed by Sort Node
When I run this query in PostgreSQL9.3.4 on TPC-H database
explain analyze select s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment from part,supplier,partsupp,nation,region where ...
0
votes
0answers
40 views
cannot install Postgres on windows 8.1
I am trying to install postgres so I can use it with my rails app. I downloaded the installer for postgres 9.3. At the end of the installation process the following warning popped up
Problem running ...
0
votes
0answers
21 views
How to update and set unit_id to be NULL, unit id is foreign key from unit table
How to update and set unit_id to be NULL, unit id is foreign key and I need to set to be NULL
CREATE TABLE troops
(
id serial NOT NULL,
unit_id integer,
type integer NOT NULL,
level integer ...
0
votes
0answers
12 views
postgresql to_json() usage, convert to columns as key-values json
I am trying to accomplish the following in postgresql
My table structure is
postgres=# select * from t_test;
var1 | var2 | var3
------+------+------
1 | A | v1
1 | B | v2
2 | A ...
0
votes
0answers
25 views
modify an existing collation postgresql 9.3
I would like to define a new collation for my bd in postgresql. I read this article ( http://www.postgresql.org/docs/9.3/static/sql-createcollation.html ) and I decide to modify an existing collation ...
0
votes
0answers
20 views
JDBC issue reading ande writing Bytes
I am trying to migrate my java application from PostgreSQL 8.4 to latest release.
I am facing an issue with reading the byte data from the db.
I am inserting the IpAddress in bytes in to the table and ...
0
votes
0answers
83 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 ...
0
votes
0answers
38 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 ...
0
votes
0answers
62 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),
...
0
votes
0answers
55 views
getting error function lo_manage() does not exist
Currently i am using PostgreSQL Plus Advance Server 9.3 and i just created a table which has a column of blob type and i am using oid data type to store blob value.
Now i want to create a BEFORE ...
0
votes
0answers
30 views
How to get Pivot table without specifing inner sql columns in outer brackets in PostgreSQL 9.3
Currently i am using PostgreSQL Plus Advance Server 9.3 and i have a table like :-
Section Status ct
A Active 1
A Inactive 2
B Active 4
B ...
0
votes
0answers
82 views
encoding LATIN1 does not match locale “Spanish_Mexico.1252”; the chosen LC_CTYPE setting requires encoding “WIN1252”
I installed PostgreSQL as part of the OpenGeo Suite and now I need to create a new database with LATIN1 encoding since I need to preserve accents. Problem is that when I try to create the new DB using ...
0
votes
0answers
94 views
PostGIS with SFCGAL support from source
I would like to install SFCGAL support in Postgis so I tried to install it from source.
Ubuntu 13.04, PostgreSQL was installed from package.
PostGIS is now configured for i686-pc-linux-gnu
...
0
votes
0answers
963 views
server version mismatch postgresql pg_dump
I'm getting the following error when I'm trying to export my database...
pg_dump: server version: 9.3.1; pg_dump version: 9.1.11
pg_dump: aborting because of server version mismatch
The problem is ...
0
votes
0answers
1k views
Postgresql starting failed
I was connected with remote postgres-9.3 by pgAdmin III. After close pgAdmin i try to connect on another day with the same db.
/etc/init.d/postgresql-9.3 status
dead but pid file exists
service ...
