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
5 views
SymmetricDS: real time synchronisation of MySQL with PostgreSQL
I need to move a huge system from MySQL to PostgreSQL. This cannot be done in one go, which is why I need a robust (real time or near real time) data bi-directional synchronisation solution between ...
0
votes
1answer
21 views
PostgreSQL slow sql update command on Amazon EC2
I am running a PostgreSQL 9.1 server on a Amazon EC2 medium instance (with an EBS storage). I am trying to run a simple UPDATE statement on about 3 millions row. It seems to take forever.
This is ...
0
votes
0answers
31 views
Ways to scale a multi tenant application with per schema per tenant approach
We are working on database architecture of a multi tenant application, and our application context/domain is best fit for per schema per tenant approach, but we concern about scaling on this approach, ...
0
votes
1answer
12 views
How to display sql statements of existing table like the one seen in SQL Pane
is there any easier way about SQL statements that shows the query seen in SQL pane of a certain table?
Thank you very much.
0
votes
0answers
11 views
Dump a table without sequence table in postgres
I have a table named test with id column. And id column has updated by sequence called "test_id_seq". This test table was created by the user "A" and another user "B" have the read permission. While ...
0
votes
0answers
12 views
Rails in production mode does not connect to second database
I am using rails 3.2.15. I have two postgresql databases one of them main and the other postgis. From model Postgis I make connection to postgis database like this:
class Postgis < ...
0
votes
2answers
49 views
Union All Query
I have two tables pricelist and stock. Those tables have following columns
Pircelist Columns itemno,batchno,mrp,rate. Stock table have itemo,mrp,batchno,npr,stock.
Now i want select ...
0
votes
1answer
23 views
postgres treat null as empty string when concatenated with string
When I fire below query on one of our servers
select null || 'query' ,'|',version
the result is null nothing is displayed
when i fire the same query on another server which has same version ...
0
votes
1answer
11 views
how to create dump for specific schema in postgres DB
I have a Postgres database "rafiu" with many schemas namely test1, test2, test3. In this I want to dump the test2 schema and its data. I tried with the following query
pg_dump -U postgres -n test2 ...
-1
votes
1answer
17 views
different query execution plans (the same server, the same query, the same script starting it)
any ideas, why on the same query (the same server, the same script starting it, all tables analyzed) sometimes postgresql selects one execution plan and other times - another?
Namely, how it makes a ...
1
vote
0answers
13 views
Hardware sizing an application which runs fine on a laptop?
If an application already runs well on a laptop with a local webserver and db, how does it impact hardware sizing for when it is deployed into product?
We're piloting this application for the first ...
0
votes
1answer
17 views
Changing Days Results Row Into Weeks SQL
I have the current query
select (current_timestamp - dstat.updated_at) as days_since_activation
from trips
which returns
days_since_activation
77 days, 9:41:23.764788
How would I turn this ...
0
votes
1answer
29 views
Add number of days in given date
In PostgreSQL:
Let say:
SELECT DATE(NOW())+interval '1 day';
It will show date tomorrow.
Problem:
While I having a column duration in table tbl, following SQL does not work :
SELECT ...
1
vote
0answers
9 views
How to write an event trigger which send alerts to a JMS Queue
Is there any example where, we can trigger an event to send messages to JMS Queue when a table is updated/inserted ect for MYSQL/Postgre?
0
votes
1answer
27 views
get all unique records and their corresponding column
Is it possible to get all unique records as well as their corresponding column in a database?
something like:
SELECT DISTINCT *
FROM table_name
?where?
I want to get all unique records with their ...
0
votes
3answers
17 views
Find most common key, value pairs with Hstore in postgres
I am collecting a list of items and version numbers in an hstore column in postgres. I'm interested in seeing 100 most common key value pairs. For example if this was my data set:
"foo"=> "22",
...
0
votes
0answers
9 views
pgpool postgresql ec2 instance
I am trying to setup pgpool2 with postgresql. I have both on different ec2 instances. I am going through the tutorial and I am trying to create a database with
createdb -U <username> -p ...
0
votes
1answer
16 views
Node-postgres date not saving accurately
I'm using Node.js, Postgres, and the node-postgres library. When I try to insert a new record with the current date with code like this:
client.query('INSERT INTO ideas(date) VALUES($1)', [new ...
0
votes
0answers
10 views
Missing libintl.8.dylib in shp2pgsql
I am a little nervous to mess with anything in the terminal because of my small amount of experience of getting anything right on the first try. Which is why I'm asking.
I'm trying to run shp2pgsql ...
0
votes
0answers
25 views
postgres: ERROR: permission denied to create database even though user has permission
List of roles
Role name | Attributes | Member of | Description
-------------+--------------------+------------------+----------------
my_role | ...
-2
votes
0answers
35 views
How to retrieve data from database in java? [on hold]
I have an Excel.java class which has database connections and my task is that i need to retrieve data from database to whatever query i write !! So i created an jlabel.java class in the same package ...
2
votes
2answers
19 views
Postgres User Authentication on web app through LoginRoles Vs Table
I have a postgres database and a web app.
The web app allows the existance of users and they do some stuff on the webapp.
I am new to postgres but what we used to do in SQL Server was ...
0
votes
1answer
29 views
SQL Filter by count of records
I have a table of the form: (Answer must work for PostGreSQL 9.2
StateName, ContryName, Pop, etc. // btw state may also be province, I use them interchangeabley
I would like to remove any country ...
0
votes
0answers
12 views
Slow Query with Limit and HashAggregates
Postgres 9.3
In the following example query, why does the HashAggregate process 10 million rows (in 5s) instead of stopping as soon as it has collected 200 rows (which should take less than 1ms)?
I ...
0
votes
0answers
25 views
How to create in SQLAlchemy model row with random string when Postgresql is under?
How to create in SQLAlchemy model row with random string when Postgresql is under?
I need to convert from MySQL where I can do like this.
Column(uuid_generator.GUID(), default=uuid.uuid4, ...
0
votes
2answers
32 views
timestamp on postgreSQL to unix timestamp
I am trying to use FLOT chart to plot values that i have stored in PostgreSQL. One of the axes will have timestamps.
The format that i have stored it the database is: 2013-11-01 00:05:57 ...
1
vote
1answer
20 views
Convert escaped Unicode character back to actual character in PostgreSQL
Is there a way how I can convert the following string back to the human-readable value? I have some external data where all non-ascii characters are escaped.
Example strings:
16 ...
1
vote
2answers
30 views
Active Record object based on group by count
Hi I am ROR developer and using rails 3.2.13 with Postgres database.
I have two models:
Question
attr_accessible :category, :is_active, :question_text, :question_type_id, :survey_id,
...
1
vote
1answer
56 views
Why does Drop Index require commit?
In my multi-threaded program, one thread drops indexes on a table (this happens first), and other threads insert records in the same table. It so happened that when dropping index is attempted, the ...
1
vote
0answers
17 views
Optimistic and pessimistic locking in Node/Postgres? [on hold]
I'm planning to use Node for my next project, because it seems to be a good fit for the problem I'm solving.
One thing I need to figure out is optimistic and pessimistic locking. ...
0
votes
0answers
21 views
Django tests don't find the postgres extensions
I am running python tests like this.
python manage.py test
.....
[snip]
.....
LINE 1: SELECT (idx([snip])...
^
HINT: No function matches the given name and argument types. You ...
0
votes
0answers
12 views
View performance and orthogonal
I am trying to upgrade many views performance for some work of mine, and i am doing things now like removing subquerys, calls to other functions in select (functions that make selects inside it), and ...
1
vote
1answer
19 views
How to update table using correlated query in postgresql..?
How to update using correlated query I am using postgresql?
Here is my example
CREATE TABLE t("no" integer,"name" character varying)
CREATE TABLE t1(no1 integer,name1 character varying)
INSERT ...
0
votes
2answers
37 views
PHP: Parsing postgre's default string time-stamp format
I have problem to parse the following two time-stamp strings in a generic way?
$timestamp1 = "2013-10-15 19:05:18.756932+03";
$timestamp2 = "2013-10-15 19:05:18.756932+03:00";
I have this so far ...
0
votes
2answers
20 views
Postgresql foreign key — no unique constraint
I have the following case:
I have a table that contains text entries in various languages. It is defined as follows:
CREATE TABLE text
(
textid character varying(70) NOT NULL,
...
0
votes
3answers
31 views
Equivalent of strftime in Postgres
After migrating my SQLite database to Postgres on the development server, I get the
PG::UndefinedFunction: ERROR: function strftime(unknown, date) does not exist
Is there a function I could use ...
-2
votes
0answers
24 views
Visual bug when displaying my data
when i rendering my data from a postgres database sometimes i discover weird records in my list of cities. When i add record to my list, this bush move totaly random to another record from cities ...
0
votes
1answer
13 views
SQL query column does not exist error
I'm totally new in this area please tell me how to fix my problem.
when I write this query "SELECT * FROM places" in my database everything is okay.
However when I change it to "SELECT * FROM places ...
0
votes
1answer
28 views
From Oracle to Postgres [duplicate]
We tried to migrate from Oracle to Postgres. We use ora2pg , but we have an error with this code:
SELECT DISTINCT UPU.USUA_C_USUARIO
FROM GN_USUARIOS U,TR_USUARIOS_X_PERFILES_USUARIO ...
0
votes
0answers
22 views
Error while running rails app on console
I'm landing up in some error when I try to run the application on the console, FYI this error is showing up on the client server, the error is
root@host [path]# RAILS_ENV=development ruby ...
0
votes
1answer
22 views
Referencing colum from JOIN in another JOINS subquery
i'm trying to write a little complicated POSTGRES query with joins, subqueries and agregate functions. All works fine but one thing. Here is the code:
SELECT
table1.id AS id1,
table1.name AS name,
...
1
vote
1answer
25 views
Postgres pg_try_advisory_lock blocks all records
I'm using pg_try_advisory_lock() in Postgres.
Next two queries lock more than one records in table1:
1)
SELECT a.id
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
WHERE
table2.id = 1
AND
...
2
votes
0answers
18 views
Trying to set prices to extended decimal/BTC in Rails on Heroku
So I'm relatively new to Rails, but have been trying to build a little store to sell some things for bitcoin. When I run my storefront on my local environment my pricing looks great (i.e. a book for ...
2
votes
0answers
27 views
Database Migration from Oracle to PostgreSQL [on hold]
I am new in the PostgreSQL Database and I am trying my all database data, stored procedure, view, everything migrate in the PostgreSQL Database. I am many search in Google and try many available tools ...
2
votes
1answer
19 views
Prepared statements in ruby/rails with postgres
I want to execute a rather nasty recursive update query in rails. This means I want to write some raw postgres sql, with parameters, and execute it inside a rails controller.
How do I do that? I ...
0
votes
3answers
29 views
Loop through table and return a specific column
Here is my function:
CREATE OR REPLACE FUNCTION getAllFoo()
RETURNS SETOF "TraderMonthOutstanding" AS
$BODY$
DECLARE
r record;
BEGIN
FOR r IN (select * from "TraderMonthOutstanding"
...
0
votes
0answers
12 views
Order issue in rails4 with postgresql
has_many :posts, :class_name => "Spree::Post", :dependent => :destroy, :order => "posted_at DESC"
Getting below error always from rails4 but works with rails3
...
3
votes
1answer
37 views
Restore deleted records in PostgreSQL
I accidentally deleted all the records from 3 tables in PostgreSQL. How can i restore the data?
0
votes
2answers
37 views
Sorting values obtained from a query in postgresql
select SUM (Account_Invoice.amount_untaxed),
right (Res_Partner.vat,length(Res_Partner.vat)-2) as RFC
from Account_Invoice inner join Res_Partner on Account_Invoice.partner_id = Res_Partner.id ...
0
votes
1answer
35 views
Oracle: Having join or simple from/where clause has no affect on performance?
My manger just told me that having joins or where clause in oracle query doesn't affect performance even when you have million records in each table. And I am just not satisfied with this and want to ...
Related Tags
sql × 5884ruby-on-rails × 2129
database × 1889
php × 1106
java × 1060
mysql × 1009
python × 1003
django × 846
heroku × 799plpgsql × 787
ruby-on-rails-3 × 686
ruby × 595
postgresql-9.1 × 577
hibernate × 524
postgis × 496
activerecord × 475
jdbc × 417
performance × 383
postgresql-9.2 × 336
database-design × 326
triggers × 315
stored-procedures × 273
psycopg2 × 271
c# × 271
arrays × 256
