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.
0
votes
0answers
2 views
Rank based on sequence of dates in postgresql
I am having data as below
Heading Date
A 2009-02-01
B 2009-02-03
c 2009-02-05
d 2009-02-06
e 2009-02-08
I need rank as below
Heading ...
0
votes
0answers
3 views
COPY FROM error in cqlsh Cassandra newline character seen in unquoted field
I am attempting to import a tab delimited file (or a csv for that matter) using cqlsh into a Cassandra keyspace. In Postgres I would simply use:
copy alaska_chinook (year, count_date, fish_count, ...
0
votes
0answers
8 views
Returning a “computed” column from a query in SQLAlchemy
Say I have the following classes:
class Basket(db.Model):
id = Column('id', Integer, primary_key=True)
class Fruit(db.Model):
id = Column('id', Integer, primary_key=True)
basket_id = ...
0
votes
1answer
6 views
getting the name of the column in which a value was change (postgresql)
is it posible to get the name of the column in which a value was change after an insert or update?, i know that with TG_TABLE_NAME you get the table name in which the value was change, but i want to ...
0
votes
1answer
12 views
Rails Query Includes acts_as_votable
I'm trying to Query for top casts (kind of like posts) based on how many votes they got.
Acts_as_votable gem, and currently my casts are votable. I can vote on them and retrieve their upvotes - ...
0
votes
0answers
7 views
django & sql - how can I efficiently store and update sort order information associated with records in my database table?
So I have a Django app and one of the tables is basically a list of items. The user may choose to rearrange the order of this list. When they do this, I want to preserve this information so that the ...
0
votes
0answers
13 views
cannot get my slave - replication server to start
I have no idea if this is the right place to figure out why the replication server will not start.
I have followed the isntructions here:
...
0
votes
1answer
12 views
Postgres error when trying to display all items through a relationship
This works on SQLite3, but not on PostgreSQL.
The error I'm getting is PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
I'm trying to display ...
1
vote
1answer
7 views
Restore PG local database from SQLite DB - Rails
I just switched my whole app's (dev and test) databases to Postgres and got it working well. I did have a ton of information (users, posts, etc) on my local sqlite database that I would love to ...
0
votes
2answers
18 views
PHP postgreSql cannot display results
Please can someone help me with this I cant find very much information on this problem.
I am connecting fine to PostgreSql database but when I look through the array and display nothing is being ...
0
votes
0answers
20 views
trying to create a simple flask service to connect to postgresql database
newbie to flask & compute engine
I've created a database (myDB) with table user and store procedure insert_user.
now i wish to create a simple python/ flask service that will connect to the ...
google-compute-engine
0
votes
0answers
11 views
Doctrine Undefined index: user when joining with view
I have the following models set up with Doctrine in ZF 1.12. The first is from my users table:
/**
*
* @Table(name="users")
* @Entity
*/
class My_Model_User extends My_Model
{
/**
*
* @var ...
0
votes
1answer
20 views
Postgres DB can't connect to R with RJDBC
I've been trying to query data from a PostgreSQL DB via R. I've tried skinning the cat with a few different packages (RODBC, RJDBC, DBI, RPostgres, etc), but I seem to keep getting driver errors. ...
0
votes
1answer
20 views
What's the cleanest way to get the current local time in Postgres?
Is there a more succinct way to write the following in Postgres:
DATE(NOW() AT TIME ZONE time_zone)
i.e. to get the local time for a record with time zone time_zone?
Ideally I'd like to do ...
0
votes
1answer
28 views
What is the fastest way to compare the minimum right most characters between two fields
I have a phone number field in two different tables. I want to compare the right most characters of both for the minimum length. Is one way more efficient than another to do this in PostgreSQL?
...
1
vote
1answer
25 views
Is there a way to execute SQL functions directly using rails dbconsole
When you fire up rails dbconsole you can easily execute commands such as:
select * from users limit 1;
However some commands require using Postgres functions such as now() or timezone(zone, ...
1
vote
1answer
28 views
Getting random rows based on column index
As of right now the table setup is like so:
CREATE TABLE testing (
id INT NOT NULL,
text TEXT NOT NULL
);
CREATE TABLE users (
id INT NOT NULL,
name VARCHAR(32) NOT NULL
) UNIQUE KEY ...
2
votes
1answer
31 views
Slick query very slow
I'm trying to learn Slick and have got a Postgres database up and running. I made a tiny program to test it like this:
import scala.slick.driver.PostgresDriver.simple._
import ...
0
votes
0answers
4 views
Could Mahout be deployed on JBoss, and read data from datasource using postgres?
I know that Mahout can be used without Hadoop, can we deploy it on JBoss? Can it read data from database using postgres? I cannot find any Mahout example using JBoss and postgres. Could anyone know ...
0
votes
0answers
17 views
Postgres hangs during delete query
I am using psql 8.4.7. I am trying to delete from a log table with over 4 million rows. Every time the process starts, it hangs and I stop it.
I tried deleting 1 row, using the id to select it, but ...
0
votes
1answer
20 views
How can I return top 4 results by user using Postgres sql
Here is my problem: I have a table that stores the results of surveys taken by users. A single user can take multiple surveys. The key columns are: user_id, the user identifier and survey_id, the ...
-1
votes
0answers
12 views
should I use elastic search for non-free-text searches
I use Postgres as a data warehouse. I need to do free text search on many of the fields. My DBA recommends not to use Postgres for free text searches. I am now considering elastic search. The question ...
0
votes
1answer
19 views
Access postgres in matlab without database toolbox
I followed the steps listed in the previous answer to this question here
How can I access a postgresql database from matlab with without matlabs database toolbox?
However, on the step
driver = ...
0
votes
0answers
7 views
BoneCP Connection Never Closed
After seeing some odd performance and eventual timeouts, I used the connection watch with BoneCP and see the following:
14:41:38,637 ERROR [com.jolbox.bonecp.CloseThreadMonitor]
...
3
votes
1answer
32 views
Writing an SQL trigger to find if number appears in column more than X times?
I want to write a Postgres SQL trigger that will basically find if a number appears in a column 5 or more times. If it appears a 5th time, I want to throw an exception. Here is how the table looks:
...
0
votes
1answer
7 views
shp2pgsql creates a table but psql reports “relation…does not exist”
Using shp2pgsql to import a shapefile into a PostGIS database, all works fine:
Connection: host=localhost port=5432 user=aperrin dbname=tweets password='******'
Destination: ...
0
votes
1answer
26 views
Rails 4 Hstore, array escaped quotes
I'm trying to store a Hash of arrays using Rails 4 and Hstore (Postgres 9.2) for when users submit a form with a bunch of checkboxes. The problem I'm having is that once they're stored, all the ...
0
votes
1answer
14 views
build link using postgresql and php
)
I have a question:
I have a databse with the following information:
Name of report [name]
Link to report [link]
The various reports are displayed in tables in my page (using db_tables filed with ...
0
votes
1answer
11 views
How to return as multiple columns instead of an array of matched regexp_matches results in psql?
Currently I have to manually extract nth paranthesized subexpression matches, and duplicate regex for the number of parantheses,
SELECT regexp_matches(col2, '(\d)(\d)(\d)')[1],
...
0
votes
1answer
13 views
How to Conditionally Rollback a Transaction Using the PG Ruby Gem
I am currently taking a databases course and one of the lab questions has me stumped as to how to implement the above and indeed, if it was even possible. I have tried searching the docs but the ...
0
votes
0answers
29 views
DB Restore from directory with pgAdmin
I want to restore/import a postgreSQL DB, that i have stored several months ago.
I stored the DB in directory and not in a *.backup file those days.
The directory has lots of files like xxxx.dat.gz, ...
0
votes
0answers
18 views
asp.net mvc 4 application + postgreSQL database
I need your support
I´m facing now my first asp.net mvc application.
WHAT I TRIED:I tried to use connection string into my web.config but without result at all.
WHAT I NEED:i need to connect to a ...
0
votes
0answers
18 views
Design Tips For Database Structure That Takes Sharding Into Consideration [on hold]
I am about to embark on a project that the size of the dataset will increase exponentially and sharding will be required. What are some good database design tips and difficulties should I take in ...
mongodb
0
votes
1answer
19 views
EF DataContext, set schema name programmatically
I am currently using EF DataContext with class like this :
[Table(Name = "schema.tablename")]
public class Table
{
[Column(Name = "id", DbType = "serial", IsPrimaryKey = true, IsDbGenerated = ...
0
votes
0answers
9 views
PostreSQL: export column2 to be the contents of a new file, where the filename is column2
I need to pull out some data from a PostgreSQL database - namely some old "lost" functions.
I've got a select statement which pulls up the function name as column1, and the function definition as ...
0
votes
0answers
22 views
ECPG: sizeof() in EXEC SQL DECLARE SECTION
I have Sybase ACE CPRE Embedded SQL С code:
void MyFunc( MyClass*unit_address )
{
EXEC SQL BEGIN DECLARE SECTION;
CS_BINARY var1[sizeof(MyClass)];
EXEC SQL END DECLARE SECTION;
...
0
votes
1answer
26 views
Combine similar data from multiple schemas
I have a table in a common schema which contain details about all enterprises of the application like id, name, logo, web_site_address, schema_name etc. Then there are schemas for each enterprise ...
0
votes
0answers
19 views
copy table data from file to postgresql table
I have exported my local database table to a file and wish to load it to my cloud db.
when using the command: \copy <table name> from '<path>/<file name>.txt' delimiter ',' csv; the ...
0
votes
1answer
23 views
Timing issue (?) between python, bash and psql
I use Pythons os.system, pg_dump and psql to transfer data between Postgresql databases. The command is execute like this:
os.system('set some variables ; pg_dump ... | psql ...')
... process copied ...
0
votes
1answer
12 views
postgresql immutable read workload tuning
I have a table where the non-primary key columns are deterministic given the primary key.
I think this could be pretty common, for example a table representing memoization/caching of an expensive ...
0
votes
0answers
18 views
PostgresSQL - dump file and database rules
I have problem with dump files generated by pg_dump for PostgresSQL database. I have table with database rules (these rules have references to other tables). When I create dump file using pg_dump, ...
0
votes
0answers
15 views
Django postgresql on Heroku DatabaseError value too long for type character varying(1000)
I'm getting this error when saving a Django Model Object:
DatabaseError: value too long for type character varying(1000)
Inside the model there is a textfield:
models.TextField()
I'm saving ...
0
votes
1answer
16 views
what is visibility check in index scan
I am looking up query optimization in Postgres.
I don't understand this statement:
Index scans involve random disk access and still have to read the underlying data blocks for visibility checks.
...
0
votes
0answers
11 views
npgsql function that returns “create table” “query string”
instead of copying manually, Is there an npgsql or c# method/function that returns "create table" "query string" of an existing table like the one that displays on sql pane of pgAdmin when you ...
1
vote
1answer
19 views
How can I avoid duplication in a join query using Sequel with Postgres on Sinatra?
I want to do a simple join. I have two tables: "candidates" and "notes".
Not all candidates have notes written about them, some candidates have more than one note written about them. The linking ...
0
votes
1answer
31 views
Vagrant & Chef - Postgresql not starting on reboot [on hold]
I decided to create my own chef script to install Postgres. The installation works perfectly fine, but postgres doesn't start on boot when I vagrant reload
Here's my recipes/default.rb:
...
-1
votes
1answer
37 views
left join table without effecting calculation left column table?
I have a problem finding a query for my tables, I am using postgresql for my DB
I have 2 tables table A and table B as follow
table A
---------------------------------------------------------------
...
1
vote
2answers
21 views
Postgres geolocation points Distance
I'm trying to make a query to determinate the distances between geolocation points in a Postgres database this is my query
SELECT latitude, longitude, SQRT(
POW(69.1 * (latitude - 31.8679), 2) +
...
0
votes
0answers
7 views
How To Turn Off Postgres Network Services From PgAdmin GUI? [on hold]
I cannot for the life of me find where to stop the server or stop postgres in general (if they are distinctly different that is -- I don't know).
I am learning about databases and don't really know ...
0
votes
0answers
29 views
Rails how to implement hierarchical associations
Is there a Rails way to implement hierarchical associations?
I'm currently trying to map below layout fields in database:
Product -> Product URL -> No Link
-> ...
Related Tags
sql × 5835ruby-on-rails × 2123
database × 1878
php × 1105
java × 1058
mysql × 1004
python × 998
django × 842
heroku × 796plpgsql × 784
ruby-on-rails-3 × 687
ruby × 592
postgresql-9.1 × 574
hibernate × 522
postgis × 492
activerecord × 478
jdbc × 417
performance × 382
postgresql-9.2 × 331
database-design × 323
triggers × 315
stored-procedures × 273
psycopg2 × 271
c# × 271
arrays × 255
