Tagged Questions
PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for many platforms including Linux, UNIX, MS Windows and Mac OS X. Please mention your PostgreSQL version when asking questions. Greenplum Database, Amazon Redshift, ParAccel, Postgres-XC, ...
-1
votes
0answers
9 views
Run python scripts that belong to multiple users without using root access
I am writing a python script for an application that runs on a tomcat server and uses postgreSQL on the backend to store data. The entire application is on an Ubuntu 12.04 server. The python script ...
0
votes
0answers
17 views
When to use JSON data type? [on hold]
In what case does it make sense to use the JSON data type in postgres? I'm still confused about storing all the values flat in a table vs. a single column in serialized JSON format.
is there a ...
0
votes
0answers
14 views
“unmigrated” tables with Django and PostgreSQL
I have a Django app using MySQL with a medium-sized database and now I'm looking at migrating it to PostgreSQL. I've created a table and loaded the data, so now I can perform basic SQL queries.
From ...
1
vote
0answers
7 views
Sharing a txpostgres connection pool
We have a RESTful(-ish) twisted application that uses txpostgres to access a postgres db. Currently, we generate new txpostgres.Connection instances every time a client pings the server for a db call. ...
0
votes
1answer
9 views
POSTGRES: Select query on a refcursor returned from a function
I have a POSTGRES function that returns a refcursor as output. Printing the refcursor shows an output similar to this:
A B
==========
1 dog
2 cat
3 tiger
My question is:
How can I ...
-1
votes
1answer
17 views
How to easily clone a production postgresql database to staging [duplicate]
I have data in a production database but want to test out some new features so I've set up a staging environment on the same server and want to use the data from the production database with my ...
0
votes
0answers
18 views
column “st_srid” does not exist
Hi I'm executing this query in my java code and it tells me
org.postgresql.util.PSQLException: ERROR: column "st_srid" does not exist
However when I run it directly in pgAdmin it shows me the ...
0
votes
1answer
13 views
last hour count and last received
There are two tables A and B with the following columns and sample data
Table A
Id Code Name Active
---------------------------------
1 A1 Apple t
2 B2 ...
0
votes
1answer
17 views
“null value in column … violates not-null constraint” with INSERT INTO … SELECT
I have two tables: the first table, customer_campaign_import, has 7 columns. The second table, customer_campaign, has more columns than the first table with id(uuid type) as the primary key. Since id ...
0
votes
0answers
8 views
OSX postgis Incompatible library version: libspatialite.7.dylib requires version 12.0.0 or later, but libxml2.2.dylib provides version 10.0.0
I've just installed postgis in a brand new MacBook pro which comes with OSX 10.9.5 by using brew with the brew install postgis command which installs postgres and all other dependencies it has.
I'm ...
0
votes
2answers
16 views
PostgreSQL deadlock while trying to obtain records for update
I have a multiple processes doing some work on accounts in a single table. I have implemented a query which each process uses to lock 100 random unlocked records by setting a value in lock column and ...
0
votes
0answers
11 views
Querying from routine in jOOQ
I have a PostgreSQL function that returns a set of UDTs (i.e. its function signature includes RETURNS SETOF <UDT name>). I'm trying to run the following query in jOOQ:
SELECT col1, col2
FROM ...
2
votes
1answer
20 views
Update From Table - not using index (pg 9.1)
UPDATE
npi_bn
SET
count = npi_bn_count.count
FROM
npi_bn_count
WHERE
npi_bn.bn = npi_bn_count.bn
PROBLEM: This update query is not using my indexes (incredibly slow on 20 mil rows). Its ...
1
vote
1answer
17 views
PG::ConnectionBad Error when trying to run rake db:migrate after creating model, using postgresql
Trying to get my rails up and running but am having a problem. When creating my new rails app on the command line I ran the usual
rails new PhotoApp -d postgresql
Generated my core scaffold. Then ...
1
vote
0answers
20 views
How find greatest tuple before given 2-column tuple in postgres fast
How to increase select statement speed in Postgres 9.0 ?
Table has required index present. Desired result can obtained using index (kuupaev,kellaaeg) immediately.
However Postgres scans all rows:
...
0
votes
0answers
8 views
ActiveRecord: how to reconnect to PostgreSQL automatically when connection drops?
I am using ActiveRecord with Sinatra and PostgreSQL. When the database connection drops (due to temporary network failure or postgres server restarting), my app doesn't re-acquire connection ...
0
votes
1answer
9 views
How to import CSV to potsgresql table if not superuser
I'm trying to copy csv file to postgresql table. As far as I am not superuser (and cannot get superuser password) I cannot do this, unless I use stdout or stdin. But here is a little problem, I don't ...
2
votes
3answers
29 views
Java prepared statement without result set?
I'm using a Java prepared statement to execute a stored procedure in PostgreSQL database. Like this:
String sql = "select testFkt(?,?,?,?,?)";
try (PreparedStatement preparedStatement = ...
1
vote
1answer
25 views
JDBC inserting now() different to the server timestamp
For this PostgreSQL test table:
CREATE TABLE public.test (
id BIGSERIAL,
"datetime" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
value INTEGER,
CONSTRAINT test_pkey PRIMARY KEY(id)
)
I am ...
0
votes
2answers
23 views
sql select query from a single table, results separated by intervals
I have a table where data is being written every 10 seconds. There is a field in the table that holds the timestamp when the data was written. I need to construct a query, where I can specify a ...
1
vote
0answers
9 views
sails postgresql many-to-many association not working
I'm trying to create a many to many association between two models, Operator and Group.
Two two models are:
-Operator.js
var Operator = {
connection:'postgresql',
tableName: 'operator',
...
0
votes
1answer
16 views
Query to PostgreSQL returns a result with 1 column and 0 rows?
I am accessing the PostgreSQL server with libpq. My query looks like this ('Africa' is any string, column_2 and column_3 are both valid names and both queries return fine when executed from psql ...
0
votes
1answer
17 views
Update time only if not a minute passed
I have created the next table.
-- TABLE user_time
user_id integer PRIMARY KEY,
prev_time TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
total_time INTERVAL DEFAULT interval '0 second'
I have to add an ...
1
vote
1answer
21 views
How to create composite index in Postgres 9
I tried
create temp table test (kuupaev date, kellaaeg char(5) ) on commit drop;
create index on test ((kuupaev||kellaaeg));
but got error
ERROR: functions in index expression must be ...
0
votes
1answer
8 views
postgres, substring a subselect
I have a query that uses a subselect like this
SELECT "columnA","columnB", (SELECT column1 FROM tableB WHERE id=1 LIMIT 1) as text
FROM tableA WHERE id=1
Now i would like to only get the last 3 ...
2
votes
0answers
8 views
How to implement an Append-only model in SQLAlchemy
I would like to re-implement some of my existing SQLAlchemy models in an append-only datastore; append-only meaning that object are only updated with INSERT statements, not using UPDATE or DELETE ...
0
votes
1answer
12 views
Postgres COPY command with literal delimiter
I was trying to import a CSV file into a PostgreSQL table using the COPY command. The delimiter of the CSV file is comma (,). However, there's also a text field with a comma in the value. For example:
...
0
votes
0answers
12 views
pg_dump out of shared memory depends on ram?
I am in process of upgrading a postgres database form 8.4 to 9.3. The database is 200Gb in size and has 765,000 tables. For the upgrade i am using pg_upgrade, which is currently in process of a ...
0
votes
0answers
21 views
i cant add values using pythonpsycopg connecting to the database postgresql
i cant add values using python psycopg connecting to the database postgresql. But when i input some values directing to the Postgresql i can now see in the python what i add manually from postgresql.
...
0
votes
1answer
28 views
Doing SELECT from a VIEW is very slow
I have a table say tbl_test. In this table I have 2.4 million records. I run the following three queries which are very very slow and frustrating.
select count(*) from tbl_test;
-- 2.4 mil records in ...
0
votes
0answers
19 views
ODBC escape convert error
I have configured connection to pgsql in excel (using ODBC driver).
When i execute sql query then i get an error "ODBC escape convert error"
Below is query (pseudocode)
SELECT
data1,
data2
...
1
vote
2answers
37 views
Tuple to List - Python / PostgreSQL return type of SETOF Record
so from this code:
from dosql import *
import cgi
import simplejson as json
def index(req, userID):
userID = cgi.escape(userID)
get = doSql()
rec = get.execqry("select ...
0
votes
1answer
29 views
How to verify that data in a field is of a certain DATA_TYPE with SQL?
I need to pull a really big chunk of data from a Postgres server. Before doing so I want to make sure that the data I pull has the right type (it can be text or binary).
So I need to somehow peek at ...
-1
votes
1answer
25 views
To establish sql-connection from jUnit test [on hold]
I'm using PostgreSQL 9.3. PostgreSQL driver is postgresql-8.3-603.jdbc4.jar.
I need to write jUnit test which establishes sql-connection and aggregating data from a database. The following @Test ...
0
votes
2answers
29 views
postgreSQL.app : create database
Hello I'm new in postgreSQL,Please guide me a bit
I have a django project
here is settings.py :
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql_psycopg2",
"NAME": ...
0
votes
0answers
16 views
Postgres ActiveRecord limit table size to one row
I am creating a custom CMS using Sinatra and Postgres with sinatra-activerecord enabled. I am creating a model called SiteInfo which will store information such as the about_description, about_photo, ...
0
votes
0answers
14 views
Admin queryset distinct by field
This is a part of bigger question, but maybe the answer can help in other causes.
The Model:
class Person(models.Model):
names = models.ManyToManyField('Name')
class Name:
first_name = ...
1
vote
1answer
14 views
How to delete a PGPing result from PostgreSQL's libpq?
I am using the c interface to PostgreSQL, libpq.
If I do PQping() on the database, how do I free/delete the returned PGPing instance (to avoid memory leaks)?
Is there something like PQclear() but ...
0
votes
0answers
13 views
postgres: access to schema objects within a function
Here's my problem:
I have Schema A, owned by User 1, that owns all objects.
I've created schema B, owned by User 2.
I have granted User 2 all privileges to the objects owned by User 1.
When logged ...
0
votes
1answer
17 views
PostgreSQL setting pg_hba.config file
I am pretty new to databases and also using the command prompt. I've been trying to create a database but first I believe I need to set the pg_hba.config file. When I open SQL shell it says"
Server ...
0
votes
1answer
18 views
postgresql: select records where ALL associated records match condition
I have:
class A < ActiveRecord::Base
has_many :abs
has_many :bs, through: :abs
end
class AB
belongs_to :a
belongs_to :b
end
class B < ActiveRecord::Base
has_many :abs
has_many ...
0
votes
0answers
5 views
Django DateField after save return DataError
i cant save very simple object in postgresql database through django admin panel... DO you ever see that?
class Aktualnosc(models.Model):
dodano = models.DateField()
tresc = ...
0
votes
2answers
28 views
Suppressing “Notice: Relation exists” when using “CREATE … IF NOT EXISTS”
I have a function that creates a temporary table to store and process data. Problem is I want to run this function on the order of 1M times within a single transaction, without having:
NOTICE: ...
0
votes
1answer
32 views
Stuck on Sql Query with time interval
I want to get best 3 day of users between "2014-07-01" and "2014-08-01"
Could someone help me? I've been stuck here for 3 days.
In real score table entries are 10:00 to 22:00 and 1 entries for each ...
1
vote
0answers
30 views
Storing byte[] PBEKeySpec to a openshift gear behave differently?
If any one could giveme some pointers:
I have a KeySpec generated by PBEKeySpec:
KeySpec spec = new PBEKeySpec(password.toCharArray(), "SALTSALT".getBytes(), 20000, 160);
SecretKeyFactory f = ...
1
vote
2answers
31 views
Debian package postgresql-9.3-postgis-2.1 suddenly stopped installing [on hold]
Literally overnight, the postgresql-9.3-postgis-2.1 package stopped successfully installing on Debian 7.6. I am not seeing much online and the postgresql IRC seems to be unaware of the issue.
The ...
0
votes
1answer
10 views
Posgresql : How to get all points in certain radius
I'm using Postgresql 9.3 and iv'e installed cube and earthdistance extensions.
I'm trying to follow this tutorial, so i have a simple events table with 4 fields: id, name, lat, lng.
Now i'm trying ...
0
votes
1answer
21 views
POSTGRES: Appending where clause while using in a loop
I am trying to write a function in Postgres that entails a loop like this:
for r in (SELECT a, b FROM table_abc)
LOOP
return next r;
END LOOP;
What I am trying to do to this is append a ...
0
votes
0answers
10 views
NetBeans unable to find org.postgresql.ds.PGSimpleDataSource driver
I have glass fish installed and have set up a JDBC connection pool and resource like so (with the password hidden for obvious reasons!):
<jdbc-connection-pool ...
1
vote
1answer
21 views
Unable to connect with psycopg2, but can via command line
I'm attempting to connect to a remote Postgres database (in this case a Heroku Postgres instance). I have a Fabric command that does some work against the DB using psycopg2.
My Postgres connection ...
