Tagged Questions
PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.
0
votes
0answers
4 views
Postgresql EXception:Terminating due to administrator command
I am using Postgresql 9.3 version with jsp.In my app i have to refresh the div tag after every drag and drop and insert into the database.Meanwhile i need to kill the idle connections too.Some times ...
0
votes
0answers
3 views
Shiny-server update database at fixed hour
I've developped a shiny application based on a postgreSQL database.
The RunApp sources a import.R file, reads differents databases (from postgreSQL) and executes a sequential process then the ...
0
votes
0answers
13 views
My Insert queries is blocking my select queries. CPU usage of server goes upto 100% for postgres
I am using select and insert queries on a table. but CPU usage goes upto 100% for postgres. How can I optimize it
Table structure and queries are as follow
CREATE TABLE ad_request
(
request_id ...
0
votes
1answer
16 views
Generating unique combinations in postgres of size two from an array
I have a followup to this question in which I generated arrays of this type of table:
val | fkey | num
------------------
1 | 1 | 10
1 | 2 | 9
1 | 3 | 8
2 | 3 | 1
In ...
0
votes
0answers
5 views
how to looping data in plugin chart automatically without manual
i have a problem, so, here it is, i have data array from database. and i use plugin highchart cakephp. this data i will create to charts, and it's problem is. how if the data is more than 10. and ...
0
votes
0answers
4 views
Hstore array attribute won't update in code, but works in console
This works in the Rails console:
pry(main)> calendar.update({"open_times"=>[{"start"=>"1111-11-11T00:30:00.000Z", "end"=>"1111-11-11T04:00:00.000Z", "title"=>" "}]})
(0.3ms) ...
0
votes
0answers
4 views
JOOQ fails with PostgreSQL Custom Type as an Array: ERROR: malformed record literal
I have the following custom type on Postgres:
CREATE TYPE my_custom_type AS (
field_a VARCHAR,
field_b NUMERIC(10,3)
);
and the following table:
CREATE TABLE my_table
(
COL1 VARCHAR(120) NOT ...
0
votes
1answer
9 views
org.postgresql.Driver not found after install application
When I try to open a connection to a PostgreSQL database, I add this to recognize the driver:
Class.forName("org.postgresql.Driver");
When I run the application for Netbeans/Eclipse, it works fine.
...
0
votes
2answers
15 views
Why am I getting a syntax error on my PostgreSQL SQL?
CREATE TABLE members (
memberID SERIAL,
username VARCHAR(255) NOT NULL,
password VARCHAR(60) NOT NULL,
email VARCHAR(255) NOT NULL,
active VARCHAR(255) NOT NULL,
resetToken ...
0
votes
1answer
19 views
How to decode bit mask in sql
For example 10 = 2+8 = 2^1 + 2^3
In the query, How can I select it when I want the code contains 2^1 or 2^3?
1
vote
2answers
17 views
How to get an array in postgres where the array size is greater than 1
I have a table that looks like this:
val | fkey | num
------------------
1 | 1 | 1
1 | 2 | 1
1 | 3 | 1
2 | 3 | 1
What I would like to do is return a set of rows in which ...
0
votes
0answers
12 views
postgresql column can't be written to via php why?
I am new to PostgreSQL and databases generally. I just modified a form on my webpage to include additional fields: pay and scheduledetails.
While I am able to write/update the other columns of this ...
0
votes
1answer
16 views
Postgres Error when Deploying to Digital Ocean Droplet
I keep getting an error while trying to deploy my app to a Digital Ocean Droplet using Capistrano's cap production deploy. The error message I get says:
rake aborted!
Gem::LoadError: Specified ...
0
votes
1answer
12 views
How to add a user to PostgreSQL from the Ubuntu command line?
I want to add a user and give the createdb permission to it in the PostgreSQL from a Ubuntu command line! I know this can be done by going inside consul and run this:
ALTER USER joe CREATEDB
But I ...
-3
votes
1answer
17 views
How can I download data from a data base from postgres in a shiny app?
I have a shiny app and I would like to download in a csv file data selected by the user, through a query from postgres. These data are stored in a postgresql data base. How can I do it?
0
votes
0answers
8 views
How to plot a point (as linestring) retrieved as a json object using webgl
I have OSM points in a postgres database and my query returns the output points needed as a linestring. I am retrieving this as json objects (using jquery). How do I plot these points using webgl ...
0
votes
1answer
9 views
PostgresSql how to GROUP BY multiple columns and get all other columns aswell?
I want to group by three columns and get the data from all columns.
This worked, but I don't get the data from lap.valid, lap."timestamp" and lap.maxspeed_kmh:
SELECT
players.name,
...
0
votes
1answer
10 views
How to Install Postgresql Gem on Ubuntu
I have Ubuntu 12.04 Precise installed via crouton on my Asus Chromebook. I have a rails application that requires postgres. When I run "bundle" it fails to install the pg gem. I try installing ...
0
votes
3answers
28 views
Select rows as column
name | value | product_id
-------+--------+------------
color | red | 9
style | modern | 9
brand | xxx | 1
color | blue | 4
color | red | ...
0
votes
0answers
8 views
Datanucleus + Postgres 9.3 + Amazon RDS failure
The title almost says it all.
I'm developing a Java web app, hosted in Elastic Beanstalk using Datanucleus 4.0.6 to communicate to a postgresql 9.3 database. The database is hosted in Amazon RDS.
...
0
votes
2answers
32 views
AVG only valid dates
I have a simple search query like this one:
SELECT COUNT(id),
COUNT(CASE WHEN nation = 'german' THEN 1 END),
COUNT(CASE WHEN nation = 'french' THEN 1 END),
...
0
votes
1answer
19 views
PostgreSQL: Find record within distance
For my application, I've an User where each one is geolocated. What I want to achieve, is to retrieve User who are within a distance (e.g. User where distance >= 1000 and distance <= 2000).
My ...
0
votes
1answer
13 views
Does psycopg2's “execute()” offer sufficient SQL injection prevention?
Can I sleep easy knowing that no SQL Injection can get past pycopg2?
Of course assuming that I correctly use it. By this I understand that I have to actually use the parameterisation (sp?) feature ...
0
votes
0answers
13 views
Heroku stalls on CSV import in Heroku Postgres DB
I'm having issues importing a CSV using a rails runner command once my application code is deployed to Heroku.
The CSV gets started from the command line like this:
heroku run rails runner ...
0
votes
1answer
25 views
Error : date/time field value out of range: “30/12/2014 23:58:04” postgresql
I am trying to import csv file into postgresql data base
I already tried set datestyle = mdy
set datestyle = dmy
\copy "Recon".snapdeal_sales (REFERENCES , ORDER_CODE ...
0
votes
0answers
18 views
Get soldats that were send out last week
Lets say I want to know how many soldats were send out during the last week.
I wrote a query that returns me how many soldats were send out today as well as this week, but im wondering how to get the ...
0
votes
1answer
15 views
Simple update set postgres stored procedure
I've a problem trying to make my stored procedure work.
This is my problem:
I have a table with a columns called a, in this column there are telephone numbers.
I have to add 0039 if the number ...
1
vote
0answers
17 views
Restoring plain-text pg_dump with psql and --disable-triggers [migrated]
I had to perform some tests with a short script to update some "legacy" data in one of my tables.
Cautious as I am, using an untested script, I decided to backup the relevant table before doing so. ...
0
votes
0answers
6 views
Can`t config Postgres [duplicate]
I try to config postgresql with:
saasbook@saasbook:~$ sudo apt-get install postgresql postgresql-contrib
I get error like this:
Reading package lists... Done
Building dependency tree
...
0
votes
0answers
17 views
PostgreSQL generic handler for serialization failure
This is a followup question from this one so I know I can use (blocking) LOCKs but I want to use predicate locks and serializable transaction isolation.
What I'd like to have is a generic handler of ...
0
votes
1answer
17 views
How to make my application add column to a PostgreSQL view
I need to find a way for my application to add a column to a PostgreSQL view. This needs to be done dinamically so I cannot hardcode my view, instead I need to get it's actual code through some crazy ...
1
vote
3answers
35 views
Using @ at the beginning of table names in Oracle/SqlServer/PgSql
I'm trying to understand if there is any cons in using table names that starts with the @ sign.
We need a well-identifiable pattern to distinguish these tables from other in the same schema.
We ...
0
votes
1answer
14 views
How to execute PostgreSQL slash methods on ActiveRecord?
I am using Rails 4. I am trying to list all PostgreSQL databases with '\l' using 'execute' method from ActiveRecord. Connection is correctly established.
p = ...
0
votes
2answers
20 views
Get created as well as deleted entries of last week
With this query I can get all entries created during the last week:
SELECT day, COALESCE(ct, 0) AS ct
FROM (SELECT now::date - d AS day FROM generate_series (0, 6) d) d -- 6, not 7
LEFT JOIN (
...
0
votes
1answer
19 views
Postgres Time/Date
I have created a method that lists future dates, the method uses psql's NOW() type. I have been instructed not to use this because the time in the database is different from the time in the system.
...
1
vote
1answer
15 views
Postgres increment SERIAL column per transaction
If I understand SERIAL type correctly, it is auto-incremented if no value is specified for every INSERT statement. I was wondering if I can have auto-increment applied once per group of INSERTs or ...
0
votes
1answer
42 views
Why isn't my join working?
A user has one profile. A profile belongs to a user. The query seems to be working in postgres, but I'm failing at getting the associations to work properly in the view. Here are the query results:
...
0
votes
0answers
21 views
How to execute raw query in JPA?
i'm try to execite a raw query like this :
em.createNativeQuery(
"WITH RECURSIVE recursetree(id, parent_id) AS (\n" +
"SELECT id, parent_g_id FROM group WHERE ...
0
votes
2answers
15 views
SQL (postgres) to join over a variable number of subselects
I have 3 tables:
shop {'ID','NAME'}
product {'ID','NAME'}
orders {'SHOP_ID','PRODUCT_ID','QTY'}
The orders table is referencing to product.id for sold products at shop with shop.id.
I would ...
0
votes
0answers
18 views
Deleting a record in SQLAlchemy at present deletes the whole table's data
without getting too involved in the classes and files I have created I have the following code in a method that I call to delete a product.
prod = models.Product
p = prod.query.filter(prod.id == ...
1
vote
3answers
29 views
Find the lastest qualifying row for each key
I have this table in my database:
I want to get 2 columns: id_chantier and id_chef.
Conditions: date_fin not null and has the last date_deb.
So the rows that I want to get are number 1 and 11.
How ...
2
votes
1answer
28 views
PostgreSQL transaction restart
I'm starting to play with PostgreSQL and noticed that sequences never rollback, even on failed INSERT.
I've read that it is as expected to prevent duplicated sequences on concurrent transactions and I ...
0
votes
0answers
8 views
“Abstract” table in PostgreSQL (inheritance)
There are a lot of PostgreSQL inheritance questions on SO, but I wanted to be clear about my particular case. Suppose I have the following tables:
CREATE TABLE abstract_person (
id bigint PRIMARY ...
-9
votes
0answers
45 views
Should I change to PHP [on hold]
The company i'm taking intership in have an old app created by VB6 and Access database which i should renew , and so i thought about PHP / ZEND and MySQL or PHP / ZEND and PostgreSQL , do you think ...
0
votes
0answers
14 views
Bucardo, replication one source to multi targets
I have dbs called: production, client1, clinet2, ... clientN, all are on the different hosts. I want to create replication from production to all clients. So I have one db:source and many db:targets. ...
0
votes
0answers
26 views
postgresql json type fields data Query : Error
i am having some trouble relating some search in the json type field in psql database ,
here is the case scenario
i have this sample data in the json type field
{"created_at": "2014-11-09 ...
1
vote
3answers
32 views
PostgreSQL multiple tuples selection in WHERE clause
I have this table:
create table myTable (keyword text, category text, result text
, primary key (keyword,category));
insert into myTable values
('foo', 'A', '10'),
...
0
votes
0answers
21 views
Spring to Wildfly Deploy - javax.naming.NameNotFoundException (JNDI)
I am trying to deploy my spring-boot application to WildFly Version 8.1,
but I get an javax.naming.NameNotFoundException, everytime the app tries to boot.
My Configuration:
I added the JDBC as Module ...
0
votes
1answer
10 views
Ansible and postgresql update functionality
Currently i'm working on my ansible playbook for postgresql database and i'd like to add following scenarios in case of installed old db version:
1 - if i have some flag to skip upgrade - ansible ...
2
votes
4answers
58 views
Get count of created entries for each day
Let's say I have a this search query like this:
SELECT COUNT(id), date(created_at)
FROM entries
WHERE date(created_at) >= date(current_date - interval '1 week')
GROUP BY date(created_at)
As you ...
