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.
1
vote
0answers
25 views
Keeping postgres entirely in memory
I am running various tests that spend a lot of time in the database.
I'd like to keep it all in memory and have it not touch the db, hopefully that would speed things up. Like using sqlite3's ...
0
votes
0answers
9 views
Diferent calculations between ST_Transform and CoordTransform
I am working with GeoDjango and Postgis, and doing transformation on a point from 4326 to 28477, when I have checked the result, it is not the same
My values are:
Latitude -16.42238172128686
...
0
votes
0answers
8 views
Deleting id that is a class in Hibernate
I have the following query to delete an item in a table when a user presses a button
String hqery = "DELETE FROM Person WHERE automobile = " selected_id;
Query q = hbsession.createQuery(hquery);
...
0
votes
2answers
12 views
pattern matching using “LIKE” in query ,postgres
How to use "LIKE" to find rows that have for instance "R011","R012","R021","R022" in their string?I dont want "R01" nor "R02" to be selected.
SELECT 'Code'
FROM "public"."Test"
WHERE 'Code' LIKE ...
0
votes
0answers
11 views
Delete all rows containing a specific number across multiple tables in a postgresql database?
I'm fairly new to SQL and I have a large database that needs some cleanup. In most of the tables, there is a column called "ID number" and I want to go through all of these tables, check each ID ...
0
votes
1answer
7 views
How do I write my ddl in a single file for quickly creating database, schema, table, role objects in postgres?
I'm definitely new to postgres. I'm using version 9.2. I'm trying to write a script to create a few roles, a database, a schema and a basic table. I tried using the sql editor in pgAdminIII but ...
0
votes
1answer
9 views
Dynamic table partitioning in postgres
I was looking up ways to have postgres partition data into tables based on timestamp for example, but without having to add the relevant child tables manually. I saw this blog post that does just that
...
0
votes
1answer
26 views
how to select only partial result but get total number of result
All:
I got stuck on SQL subquery selection
Right now, I have a table products:
id | name | description
----+-------+----------------------
6 | 123 | this is a +
| ...
2
votes
1answer
21 views
How can I use an 'or' statement to define conditions for selection from a postgresql table?
I'm new to SQL and I want to select all entries from a table when, say, one or more of its columns are empty ie.
SELECT * FROM my_table WHERE column1 IS NULL (or) column2 IS NULL;
Can anyone tell ...
0
votes
0answers
17 views
Display data from a Select Count in Postgres with function
I'm trying to do a select here but we're not displaying any data, is as follows, I need to do a select count on a table in postgre me where it displays how many records to select this field that are ...
1
vote
2answers
21 views
How to group by events by year and month in django ORM?
I tried multiple approach, but could not come to solution.
I have event model like this:
class Event(models.Model):
start_date = models.DateTimeField()
end_date = models.DateTimeField()
...
1
vote
1answer
19 views
Array field not being recognized as attribute
I have the following model I created,
class CreateUsers < ActiveRecord::Migration
def change
create_table :users do |t|
t.string :name
t.string :email
t.string ...
1
vote
1answer
12 views
Adding columns of two relations in postgresql
I have two relations such as relation1 and relation2. relation1 has columns of A,B,C and relation2 has columns of D,E,F.
I want to add A of relation1 with D of relation2 where C = F. For the C values ...
3
votes
3answers
20 views
How to use PostgreSQL upper() function with a different locale?
I have a PostgreSQL database on a shared host and the result of using the upper function is different in my local database because of the locale setting.
Here is what I want, and have in my local ...
1
vote
0answers
18 views
Greenplum database is not expanding dynamically to our Linux system
The main difficultly in this situation is a out of disk space error from the Greenplum database. We are running a Greenplum Database 4.2.0.0 on a Amazon Linux box.
The investigation has presented the ...
0
votes
0answers
12 views
China to AWS Multi-Master Replication
I am currently supporting a system that sits inside a data center in China, but has terrible performance outside of China due to the firewall. We are in the process of setting up a data center in ...
1
vote
2answers
26 views
Get order number that makes the total sum of orders be 1000
I have a table of Orders, and each row of those have a column called price. Each of those orders also has a column called created_at that will say when that order was created.
What would be a good ...
0
votes
2answers
39 views
PostgresSQL update query
I need to update table in my database. For sake of simplicity lets assume that table's name is tab and it has 2 columns: id (PRIMARY KEY, NOT NULL) and col (UNIQUE VARCHAR(300)). I need to update ...
0
votes
1answer
14 views
Import CSV table in PostgreSQL
I want import in PostgreSQL csv table with next structure:
1,qw,sdf,ty
2,efw,sd,hj,gh,hj
3,sfd,sd,gf,gh,h
4,fg,sd,dff
I use sql query:
CREATE TABLE test("nom" integer, "f1" text, "f2" text, "f3" ...
0
votes
0answers
7 views
Executing query in chunks on Greenplum
I am trying to creating a way to convert bulk date queries into incremental query. For example, if a query has where condition specified as
WHERE date > now()::date - interval '365 days' and date ...
0
votes
1answer
30 views
JOIN'ing by 3 tables and retrieving field based on content of those tables
Kind of hard to explain, so I'll map it out. Given these four tables: houses, landlord_houses, company and tenant, I need to find all the houses that have signed up in the last 14 days and get some ...
0
votes
1answer
13 views
DB Cleaner not cleaning database after each test after switching from sqlite to PostgreSQL
This config worked fine when I was using sqlite:
config.before(:suite) do
DatabaseCleaner.strategy = :truncation
end
config.before(:each) do
...
1
vote
2answers
13 views
Get a timestamp from concatenating day and time columns
I am having day and time fields in database. I want to get the time-stamp by concatenating the day and time. How to do this in PostgreSQL?
I have done this:
SELECT EXTRACT(EPOCH FROM TIMESTAMP ...
1
vote
1answer
20 views
Issue connecting to postgresql using pdo on localhost (08006)
I have set up a test server and installed PHP 5.4 (zend Server with Nginx) and also postgresql 9.1.
I am trying to connect to the postgresql database using the following code:
define('DB_TYPE', ...
0
votes
1answer
25 views
Implementing visitors statistics for many users
I'm facing a challenge and I need your opinion, let me explain:
I have a database of around 300 000 users, which all have a profile page, and I would like to store the amounts of visitors that visit ...
0
votes
1answer
29 views
Tools for migrating database from sqlserver to postgres
I am looking for some good tools for migrating database. Migrating from sqlserver to postgres database.
Any Help :)
0
votes
0answers
23 views
Postgres - Changing user access creates a “password authentification fail” for postgres user
I've been having a strange issue for sometime.
Whenever, I modify the user access in pgAdmin3, allowing only user 'postgres' for instance, trying to create a new connection with a different user ends ...
0
votes
1answer
36 views
DatabaseError: column “pub_date” of relation “courses_courses” does not exist
I'm getting this error when I run python manage.py migrate courses.
(edu-venv)vagrant@precise32:/vagrant/projects/kodex$ python manage.py migrate courses
Running migrations for courses:
- Migrating ...
1
vote
1answer
21 views
Joins in postgresql
How do you change this Oracle sql to postgresql
SELECT prd.name, pkg.name, si.name
FROM service_items si, service_link sl, products prd, packages pkg, product_package_link ppl
WHERE ppl.id='3'
AND ...
-2
votes
3answers
19 views
Porting from Oracle DB. MySQL or PostgreSQL?
Client has an old system based on Oracle DB. With the current (2013) versions of MySQL or PostGreSQL (consideration also of stored procedures, triggers, etc), which one is easier (would take less man ...
1
vote
0answers
18 views
Closest match in text fields between two tables: how to improve plpgsql function
I am trying to geocode data in one table (A) with address data contained in another table (B). As street names can be written differently, I would like to first go through the data in A and for each ...
0
votes
1answer
17 views
Namespacing versions of an PostgreSQL schema for backward-compatibility?
Say we have an SQL database with a table Person and several applications accessing it. For some reason we would like to modify the Person table in a backward-incompatible way.
One potential solution ...
0
votes
1answer
15 views
vertical database partitioning in postgres 9.2
i have a table with 60 columns and want to vertically partition it,
more or like dividing it in further small tables divided in columns.
how to do vertical partitioning in a database using postgres ...
0
votes
1answer
15 views
Zend framework - connection to postgresql
I encountered troubles when trying to connect to postgresql database within Zend framework application. In my application.ini I use
resources.db.adapter = PDO_PGSQL
resources.db.params.host = ...
0
votes
1answer
25 views
PostgreSQL - Column name of a table is the name of a function
There is a table in my database where on of the column names is called "end". End is a timestamp.
When I try to run a simple query such as:
select end
from driver_shift_log
I get a syntax error ...
2
votes
1answer
32 views
Postgresql, days from date interval
I need to find all rows from database table, for which the difference between columns date_start and date_end is exactly 3 days. I looked through postgresql documentation like ...
0
votes
0answers
15 views
Issue with function in Python - Openerp - PostgreSQL query
I made this function, to execute a query into PostgreSQL, and subtract a quantity from a field to another, when a purchase order is done in OpenErp, the subtraction is made between the two fields, ...
0
votes
1answer
28 views
transaction in function of postgtresql
From the research I have done so far. what I have found is that in postgrsql functions can not be transactionized from within. They had to be called by a piece of code which can be transactionized. We ...
0
votes
1answer
16 views
Postgres failed to install, 'unexpected character “;”'
So this morning I couldn't install Postgres 9.1 from the Ubuntu repo. I tried installing 9.2 from postgres repo, but if failed with the same error. The error trace is really uninformative (I don't ...
1
vote
0answers
10 views
Rails migration is always inserting whitespaces or changing the ordering of columns in the schema
There is a frustrating issue where my rails migrations update the schema with whitespaces and the position of the table's columns.
So most times when I run bundle exec rake db:migrate it will do one ...
0
votes
0answers
5 views
Sequelize: Changing model schema on production
We're using the orm sequelize.js and have defined a model as such:
module.exports = function(sequelize, DataTypes) {
var Source = sequelize.define('Source', {
name: {
type: ...
0
votes
2answers
37 views
Best way to search in postgres by a group of keyword
right now I have a keyword array like:
['key1', 'key2', 'key3'.......] , the keyword can be number or character.
If I want to search in my table (postgres database), and find out all record contain ...
-1
votes
4answers
50 views
How Do I Omit Repeating Results
Let’s say you are an employee at a business where they put employees in a database, with the columns ID, FIRSTNAME, LASTNAME column. Now, if you have multiple last names, what SQL technique do you use ...
0
votes
1answer
27 views
PostgreSQL: Create schema in specific database [NOT psql]
Im trying to create a SQL file to import my database/schema/tables with PHP.
If I use the PgAdmin, it inserts a meta-command \connect after the CREATE TABLE
and this generates an error if I run the ...
0
votes
1answer
15 views
PostgreSQL HSTORE GIN query
I can't figure out how to re-write this query using arrays for the test cases:
--explain
SELECT COUNT(id)
FROM (
SELECT T.id
FROM product2 AS T
WHERE (ext @> 'p01=>1' OR ...
0
votes
0answers
15 views
How to use Postgres inet data type with OpenJPA?
I need to record IP addresses in a Postgres (9.0) table with OpenJPA (2.2.2).
I've got it working using a native query:
EntityManager entityManager = entityManagerFactory.createEntityManager();
...
2
votes
3answers
56 views
Is Hadoop Suitable For This?
We have some Postgres queries that take 6 - 12 hours to complete and are wondering if Hadoop is suited to doing it faster. We have (2) 64 core servers with 256GB of RAM that Hadoop could use.
We're ...
0
votes
1answer
24 views
How to select a database in Postgres with PHP?
I have the following string connection:
$string_connection = "host=localhost port=5432 user=postgres password=postgres";
$conn = pg_connect($string_connection );
Later in my code, I need to select ...
0
votes
3answers
23 views
postgresql select minimum from a value pair of two columns
I have a table that looks like this:
id | group | title | description | added | modified
---------------------------------------------------
1 | 1 | ... | ... | ... | ...
2 | 1 ...
1
vote
1answer
25 views
PostgreSQL Performance: keep seldomly used small database in memory while server busy with big database
I have a server with 64GB RAM and PostgreSQL 9.2. On it is one small database "A" with only 4GB which is only queried once an hour or so and one big database "B" with about 60GB which gets queried ...
Related Tags
sql × 4824ruby-on-rails × 1770
database × 1687
php × 975
mysql × 912
java × 895
python × 860
django × 734
heroku × 695plpgsql × 671
ruby-on-rails-3 × 640
postgresql-9.1 × 490
ruby × 473
hibernate × 456
postgis × 421
activerecord × 392
jdbc × 353
performance × 347
database-design × 288
triggers × 275
stored-procedures × 243
postgresql-9.2 × 237
c# × 236
psycopg2 × 236
sql-server × 217
