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, ...
0
votes
1answer
9 views
Ruby on Rails - order by updated_at by ignoring seconds and milliseconds
Want to display all Users order by updated_at column.
But I should ignore seconds and milliseconds.
Using Postgres database.
Currently updated_at is having values like "2014-09-08 12:59:32.61052"
...
2
votes
0answers
6 views
Connecting Tableau to Heroku Follower Database
Have created a follower database for a Heroku Postgres DB. I am able to connect to the follower DB by using pgAdmin, no problem.
When I try to connect with Tableau 8.2 Postgres connector using the ...
0
votes
1answer
11 views
Use Ruby to query a Postgres DB using a db url
In a Ruby script, I want to connect to a Postgres DB via URL, and do a simple SELECT * FROM users hello world query.
What is a gem to do this? I haven't found any gem that lets me connect through ...
0
votes
0answers
13 views
Postgresql and multiple steps
I am new to both postgresql and using a text editor. Sorry if this is a stupid question.
Postgres version ; 9.3.5.0
Text editor; sublime
I have the following table;
I have approximately 1 million ...
0
votes
1answer
11 views
R or QGIS direction for distance
Looking for either a R or QGIS solution: I have file with lat long, and group name. I'd like to find either a) distance from a starting point to an end in a group, b) average distance between ...
2
votes
1answer
12 views
How do I store an unsigned int in postgres sql?
How do I store an unsigned int (uint32) in postgres? I noticed that numeric(10,0) would fit the number of digits, but is this the best way?
On further research another similar problem is storing a ...
0
votes
2answers
26 views
SQL left outer join but detect what row were “filled” with nulls
I have a SQL LEFT OUTER JOIN with a one to many relationship. Here is an example
Table A
+------+------+------+
| Col1 | Col2 | Col3 |
+------+------+------+
| a1 | a2 | a3 |
| b1 | b2 | ...
0
votes
0answers
12 views
Solutions to migrate databases [migrated]
I'm working in redesign an application. This application has a MySQL database and has approximately 50 tables (there are no relations , there are not standars, etc). We want to migrate to Postgresql ...
0
votes
0answers
13 views
Rails States I do not have permission to access the table?
I am trying to run bundle exec rspec and I run into:
`async_exec': PG::InsufficientPrivilege: ERROR: permission denied for
relation schema_migrations (ActiveRecord::StatementInvalid) : SELECT
...
2
votes
2answers
23 views
PostgreSQL query to detect overlapping time ranges
I have a table in PostgreSQL 9.2 that looks like this (simplified):
CREATE TABLE my_features
(
id integer NOT NULL,
feature_id integer NOT NULL,
begin_time timestamp NOT NULL,
end_time ...
0
votes
0answers
19 views
return nested hours of operation in a postgresql query
I have a table of garages. I am allowing for an API query that should also be able to return the hours of operation for the garages. The hours of operation are related to the garage in a TimeBlock ...
0
votes
1answer
14 views
Postgresql function from a SQL Server stored procedure
I have a procedure in SQL Server as follows:
ALTER procedure [EI].[GetAdmByYear]
@YR int ='2012',
@ReportId nchar(7)='ECCSUR1'
as
begin
Declare @TestTypeID int
select top 1 @TestTypeID = ...
0
votes
2answers
17 views
Generating a ordered sequence rank on complex query
I am trying to create an implicit rank in the output from the query. The problem seems to be that row_number() is running before worth is calculated.
SELECT
firstname,
lastname,
...
0
votes
3answers
25 views
PostgreSQL - Syntax Error When Creating Function With If ELSIF Statements
Could someone help explain what's wrong with syntax for the following codes:
CREATE OR REPLACE FUNCTION linearly_decrement_offset(location_in text)
RETURNS void AS
$BODY$BEGIN
IF ...
1
vote
0answers
9 views
How to format a SQLAlchemy ORM Postgresql CIDR contains (>>) query
I have a model defined with:
from app import db
from sqlalchemy.dialects import postgresql
class TableIpAddress(db.Model):
__tablename__ = 'ipaddress'
idipaddress = db.Column( postgresql.UUID, ...
0
votes
2answers
21 views
Can you export a Postgres table into multiple CSV files based on a column value?
I have a table in Postgres with ~6M rows and want to split and export them as multiple CSV files. Is there a way to automatically generate a different CSV file based on the value of a row's column?
0
votes
3answers
25 views
WITH… UPDATE in PostgresQL
I'm trying to update a table (in pgsql) with a complex expression that needs to occur several times in the UPDATE statement. WITH seems perfect for this:
WITH newtz AS (SELECT timezone FROM ...
0
votes
1answer
29 views
How to solve bad sql grammar error with Spring JDBC?
I'm using PostgreSQL with Spring JDBC. Everythig building with Gradle:
dependencies {
compile("org.springframework.boot:spring-boot-starter-web")
compile("org.springframework:spring-jdbc")
...
1
vote
1answer
22 views
Use (insert into … returning id) as a value - Postgresql
I think this is yet another twist on an old query, I've searched for a solution w/o success.
I have a table of, say, two values:
create table source (
a varchar(10) not null,
b varchar(10) not null
...
1
vote
1answer
18 views
SQL: Return all records ignoring duplicates based on a field in postgresql
I have a database looking like:
Incremental_ID | First Name | Last Name | Email | Data1 | Data2 | Concat
This database contains one million records about approximately 200,000 customers that has ...
0
votes
1answer
37 views
How to store a secretKey in database postgresql and retrieve it
I tried convert it to byte[] and store it on a bytea but it didn't work, i don't receive the same secretkey that i stored. Using type String didn't work as well
if you're interested in more details, ...
0
votes
1answer
20 views
Could someone explain the anatomy of a database.yml file
I've consulted the Ruby on Rails guide and stack overflow regarding setting up database.yml files and how they are set up but I'm running into a few snags with a particular database.yml file and how ...
-1
votes
1answer
9 views
MySQL to Postgres syntax
I am working on a maven project and for that I am using Postgres as database. Unfortunately the database I know more is MySQL. I have the basic details of SQL file which I want to include in the ...
1
vote
1answer
25 views
SQL multiple rows into one
It seems like an easy task, but i can't seem to wrap my head around it.
I just need to find out, users, that used mobiles, users that used desktop and users that used both, on day to day basis. Every ...
1
vote
0answers
10 views
RDS Postgres Backup / Restore of a Rails DB using pg_dump / psql?
I apologize, this is a cross-post from the AWS forums (I couldn't get an answer there, https://forums.aws.amazon.com/thread.jspa?messageID=567255򊟗).
To start this thread I want to say that ...
0
votes
1answer
22 views
Could someone explain what postgresql-client is and how it interacts with postgresql core package?
I am having a database related issue illustrated by a previous question found here. Could'nt drop staging database. PG::CONNECTION Fatal. no pg_hba.conf. What it comes down to is that I need to figure ...
0
votes
0answers
21 views
What is the difference between := and =? [duplicate]
As title says, what is the difference between those two operators?
I have made this test function:
CREATE OR REPLACE FUNCTION test_operators(p_param1 integer, p_param2 integer)
RETURNS void AS
...
0
votes
0answers
19 views
Efficient PostgreSQL Query for Mins and Maxis withing equal intervals in a time period
I am using Postgres v9.2.6.
Have a system with lots of devices that take measurements. These measurements are stored in
table with three fields.
device_id
measurement (Indexed)
time (Indexed)
...
-2
votes
0answers
13 views
how install postgreSQL on os x server [on hold]
i buy OS X Server but i dont find any database inside the server, how to install postgreSQL on OS X Mavericks Server? mySQL is fine too. thanks
-1
votes
0answers
16 views
How to create a new User with password and new database in Postgres? [on hold]
I need to create a new user with a password, superuser rights and a new database from a linux command line.
I want to run this command(s) without any user input (prompt).
How can I do that?
1
vote
2answers
36 views
Complex aggregate query
create temp table tokens (
id serial not null,
name text not null,
locale text not null,
CONSTRAINT tokens_pkey PRIMARY KEY (id)
);
insert into tokens(name, locale) values
...
0
votes
1answer
14 views
How to insert a java ArrayList in postgresql Array (integer[] ) type column using @InsertProvider
I Have a mapper method which is defined as following
@InsertProvider(type = ActivityMapperSQLBuilder.class, method = "insertIntoActivityComment")
public int ...
0
votes
1answer
31 views
How do I import the content from a SQL file into my current Rails app on Heroku?
I was given a SQL file, with an existing db schema that I don't want in its entirety.
I only want some columns from 1 table (and some of the relations thereof).
This SQL file is 500MB.
So my first ...
0
votes
0answers
6 views
How to update row column and also attribute of each json array element in another column
My table looks like this:
...
textCol: 'sometext'
jsonCol: [{ param: 'value1', ... },{ param: 'value2', ... },{ param: 'value3', ... }]
...
I want to update textCol and param in each element of ...
0
votes
0answers
17 views
Trigger in postgresql (different column values will upadate one column values in postgresl
Im having two tables - registration and patient_header. In registration tables there are columns ( custom_field1, custom_field2, custom_field3) and so on..in the patient_header table, the column ...
0
votes
1answer
23 views
Best way to avoid data loss in a high-load Django app?
Imagine a quite complex Django application with both frontend and backend parts. Some users modify some data on the frontend part. Some scripts modify the same data periodically on the backend part.
...
0
votes
1answer
21 views
Postgres Slow and delayed insertions
I have and issue with database inserting slowly and delayed manner.
What the situation is, I have a table mobiledata which has a high insertion in it. It has a primary key as id (bigserial). The id ...
-1
votes
0answers
5 views
issue in jboss7 datasource config
I'm currently migrating from jboss 4.3 to jboss 7.1.1 (Final) and i'm trying to configure an postgres datasource but it's not working!.can anybody tell me how to configure standalone.xml?
Tohid ...
0
votes
0answers
36 views
Django error: Relation does not exist
I have this user profiles model:
class UserProfile(models.Model):
user = models.OneToOneField(User, related_name='profile')
age = models.IntegerField(null = True)
location = ...
1
vote
1answer
36 views
autogenerated keys for grails 2.4.3
The problem has been solved. The problem was that I used jdbc3. Using jdbc4 solves the problem!
I have upgraded from grails 2.3.6 to grails 2.4.3.
This meant changing from hibernate 3.6.10.3 to ...
0
votes
1answer
9 views
RedShift: How to cast integer with year into a date?
Assume a user tries to cast a number (year of birth) into a date.
select year_of_birth, cast(year_of_birth as date) from visit_occurrence
And gets this error :
cannot cast type smallint to date
...
0
votes
2answers
23 views
postgresql outputting pg_query img url data in href
i want to printf pg_guery returning data which is actually image url, in href quotation.. but i cannot do that. i will make a fancybox by these results
here is my codes
<?php
...
0
votes
1answer
20 views
Postgres Decrypt in sql
I am having a problem creating a libraries on codeigniter and postgres. My problem is that on my query on postgres I've encrypted the that is.
$sql = "INSERT table ('text') ...
1
vote
2answers
27 views
How to access the procedure that return setof refcursor from PostgreSQL in Java?
Need to access a procedure that return setof refcursor from PostgreSQL.
I am able to access the first object but not rest of object not rest of objects.
con.setAutoCommit(false);
try ...
0
votes
0answers
8 views
proftpd + mod_sql_postgres + mod_sql_conf - Bad file descriptor
Please advice. I use proftp + mod_sql + mod_sql_postgres + mod_conf_sql. I have created db in postgresql, all needed tables and add string in my proftpd.conf:
<IfModule mod_conf_sql.c>
Include ...
0
votes
2answers
21 views
Deployed Rails 4 to Heroku , received error
I have finished a rails 4 project, it runs perfect locally , but after i pushed app to Heroku, it didn't work .
An error occurred in the application and your page could not be served. Please try ...
0
votes
1answer
10 views
postgresql date_trunc to arbitrary precision?
postgresql has date_trunc that can truncate the time stamp value to a specific unit, like hour or minute. I want to know if there's any build-in function that would allow me to truncate to 10 minutes?
...
1
vote
0answers
36 views
Transferring a system built in php(with cakephp) to the other server
I want to transfer a system to the other server with the deterioration of the server that a certain system works.
After transferring a program file and a database to a new server, an error occurs.
...
1
vote
0answers
17 views
Postgresql - converting text to ts_vector
Sorry for the basic question.
I have a table with the following columns.
Column | Type | Modifiers
--------+---------+-----------
id | integer |
doc_id | bigint |
...
1
vote
0answers
22 views
Why am I getting an error when I run the migrate command on Jython-Django 1.7? How do I resolve it?
I hoping that you can help me out. I currently have Django1.7 running on windows7/Java7/Jython2.7/Postgresql9.3/postgresql-9.3-1102.jdbc41.
I learned today that on Django 1.7 there is no reason to ...
