Tagged Questions
0
votes
1answer
28 views
is it possible to connect two dbms MySQLand postgres SQL in codeigniter?
I am developing like fair size project which is in developing stage using MySQL as a primary database. Now, client has some data, which is in postgres SQL, which can not be converted into MySQL. So, ...
0
votes
0answers
17 views
Did anyone get pgloader to work for migration from Mysql to Postgres
I have installed pgloader using yum install on centos 6.5. I cannot get pgloader to load the following file. My command on the shell is pgloader mysql.load
LOAD DATABASE
from ...
-1
votes
2answers
39 views
Why is it a vacuum not needed with Mysql compared to the PostgreSQL?
I am more familiar with PostgreSQL than MySQL. Have encountered wraparound Id failure once with the PostgreSQL db and then understood the importance of vacuuming in the db. Actually, that was such a ...
0
votes
3answers
34 views
Storing Zip file in Postgres
I've been storing the contents of zip files in a LONGBLOB in a MySQL database, but I'm moving to Postgres. From what I've read, the equivalent to LONGBLOB in Postgres is bytea. Unfortunately, I ...
0
votes
4answers
57 views
Join two tables and remove duplicates
I'm trying to join two tables. Where table2 has duplates.
The tables look something like
CREATE TABLE ta
(
id int,
cno varchar(30),
d1 varchar(30),
d2 int
);
CREATE ...
0
votes
0answers
25 views
how to store the xml output of a request at the planet-file
i need some hints for a storing task; i ve done a requst on the planetfile of openstreetmap.
have done a request to overpass-api.de - regarding the planet-file. (note: i have done the requests
i ...
0
votes
2answers
31 views
Multiple queries in database depending on parameter without loops
I have a PostgreSQL database and a Perl script like this
sub reassign_minions {
my $self = shift;
my @users = $self->get_userids_of_minions();
my $sth = ...
-1
votes
0answers
24 views
Changing data in the database related on time
I would like to discuss an approach how to implement following workflow in the database.(Mysql or Postgresql.. )
I am running some simulation, which is a couple of objects changing their values in ...
-3
votes
1answer
54 views
Randomly select fields where few condition met
Here is the table-
+----+---------+--------+
| id | letters | status |
+----+---------+--------+
| 1 | A | 0 |
| 2 | B | 1 |
| 3 | C | 0 |
| 4 | D | 0 ...
-2
votes
0answers
29 views
C# Project using MySQL - pros and cons? [duplicate]
I want to start a project. To be more specific, I want to develop an ERP using C# language.
I am now, trying to select with DBMS to use. Since I am more familiarized with MySQL, I am thinking to use ...
0
votes
3answers
36 views
PostgreSQL grouping error in a query after moving from MySQL
I've moved to PostgreSQL from MySQL, but got strange problem. My query worked perfect in MySQL.
SELECT MIN("events"."schedule") AS schedule
FROM "events"
WHERE ("events"."state" IN (1)) AND ...
-1
votes
0answers
37 views
How do I make multiple instances of a method run simaltaneosly in ruby?
I am trying to migrate to discourse. I have a mysql dump and I am running phpbb3.rb migrating script for doing it. There are more than 3 lakhs user records in it which will take few days to complete. ...
1
vote
3answers
63 views
Using %s commands in execute statements (python) in mySQL vs postgreSQL?
I've been writing code recently in python that works with postgreSQL databases, and it looks like the following:
def insert(table, ID, date, timestamp):
cur.execute("""INSERT INTO %s (ID, date, ...
0
votes
1answer
64 views
Count the total number of records in the database but return only X records
We are shifiting one of our applications from PostGreSQL to MySQL. We have the following query in PostGres -
SELECT
idrte
, left_name
, gacodemun
, clsrte
, speed
, ...
0
votes
1answer
39 views
Matching two tables with SQL
I have a table named 'companies' where I store general information about the companies in Lithuania that i am listing, therefore the columnns are named name, phonenumber, town, zipcode, etc.
But ...
2
votes
3answers
104 views
Grant privileges for a particular database in PostgreSQL
I'm moving from MySQL to PostgreSQL and have hit a wall with user privileges. I am used to assigning a user all privileges to all tables of a database with the following command:
# MySQL
grant all ...
0
votes
0answers
34 views
Do I need to switch from MySQL to PostgreSQL to get support for GIS queries?
I have a Django application that sits atop a MySQL database. Actually the database is MariaDB. But Django doesn't know that. As far as it is concerned, it's just MySQL.
My application needs to keep ...
0
votes
0answers
46 views
Very Large MySQL Table Design
We want to store a very large table in MySQL. The workload's spec is like this:
Around 1 billion rows.
Each row has just two fields: a long int as the key and a blob with average size of 20KB as the ...
0
votes
2answers
29 views
Change query into mysql?
I have query like this
$where = $where." CAST({$field} AS TEXT) = '{$value_i}' AND ";
$a[] = "CAST({$field} AS TEXT) ~* '{$value_i}'";
if I excecute on postgres it's ok nevermind and if i excecute ...
0
votes
3answers
44 views
Joining a table of properties as columns and values?
CREATE TABLE person_properties (
person_id INT,
key TEXT,
value TEXT,
PRIMARY KEY (person_id, key)
);
CREATE TABLE persons (
id SERIAL PRIMARY KEY
);
I've done this:
INSERT INTO ...
1
vote
1answer
21 views
How can I import data from ASCII (ISO/IEC 8859-1) to my Rails/PGSQL database?
I am trying to use data from the USDA found here: http://www.ars.usda.gov/Services/docs.htm?docid=23634
ASCII (8.6Mb) - This file contains the SR26 data in ASCII (ISO/IEC 8859-1), delimited files. ...
0
votes
1answer
24 views
How can I update some rows using an inner join in SQL in a way which will work on both mysql and postgres?
I have this SQL which updates rows on MySQL, but I need to rewrite it so that it will work on PostgreSQL as well. I'm aware that I can make it work for PostgreSQL using a different syntax, but I need ...
0
votes
3answers
37 views
converting mysql scripts to postgresql script
I have the following line in a .sql file from a mysql db:
ALTER TABLE lcr_gw ALTER COLUMN ip_addr TYPE VARCHAR(50) DEFAULT NULL;
I would like to convert it into syntax that postgresql would ...
0
votes
3answers
56 views
How do databases maintain results if another query edits those results
I was just wondering how most relational databases handled maintaining your set of results if another query has edited those rows that you were working on. For instance if I do a select of like 100k ...
1
vote
1answer
33 views
Converting MySQL “get top y for each n” GROUP BY query to PostgreSQL
Ok so I'm going from mysql to postgresql because of Heroku. I have this statement that works perfect in mysql, but I can't seem to figure out the best way / any way for PG.
m = C.select("m.derp, ...
0
votes
0answers
21 views
Promote staging database to be new production database in a Rails application
How can I create a feature where data changes in a staging environment, after QA, could be "promoted" to production? My expectation is that the code base would be the same in both environments and ...
0
votes
3answers
53 views
SQL, build a query using data provided in the query itself
For experimental purposes only.
I would like to build a query but not querying data extracted for any table but querying data provided in the query it self. Like:
select numbers.* from (1, 2, 3) as ...
0
votes
1answer
27 views
Geolocation queries in Doctrine2
I am using Doctrine2 and CodeIgniter2 for my test application. I have a table in my database that stores all the geographic locations have fields
Name
Latitude
Longitude
Created(Timestamp)
I see ...
0
votes
1answer
15 views
Django Postgres Aggregate Functions
I want to use aggregate functions on a column in my model.
Following is my model:-
class ShipmentWeightMapping(models.Model):
weight = models.CharField(max_length = 255)
status = ...
0
votes
3answers
57 views
Use table alias in another query to traverse a tree
I have the following query:
select * from (
select p1.c as child, p1.p as parent, 1 as height
from parent as p1
where (p1.c=3 or p1.c=8);
union
select p2.c as child, p2.c as parent, ...
2
votes
1answer
45 views
Database connection pooling for R parallel process [duplicate]
I have a database connection to PostgreSQL using the package RPostgreSQL. Currently I do the following:
retrieve a list from my database
run the list through a for loop, doing a calculation and ...
2
votes
2answers
44 views
SQL query using outer join and limiting child records for each parent
I'm having trouble figuring out how to structure a SQL query. Let's say we have a User table and a Pet table. Each user can have many pets and Pet has a breed column.
User:
id | name
...
0
votes
0answers
14 views
Rails: Query and joins on postgres schemas
Using postgresql schema's pretty heavily and want to convert many of my previously sql written queries to ActiveRecord query.
Firstly, how would I convert a query like this to AR:
def language
...
2
votes
1answer
34 views
Find polygons that fall within a latitude/longitude combination with a N margin in degrees in MySQL/PostgreSQL
Normally to find polygons that compasses a certain GPS point you can use a query like:
SELECT * FROM `polygons` WHERE ST_CONTAINS(`polygon`, POINT(-33.874087, 151.207865)));
However how you go with ...
2
votes
2answers
92 views
how does oracle makes 1 million insertion in 1 second
Few years ago, I found oracle can achieve 1 million insertion in 1 second, first let me explain this, I remember in SQL/PLUS interactive command line
set timing on
create table tbl as
select ROWNUM ...
1
vote
1answer
23 views
Rails + Postgres Relation Issue With Custom Foreign Key
I'm in the middle of a migration to Postgres from MySQL and i'v hit an issue that I just can't solve. I'm sure there is simple answer, but i'm stuck.
The Problem
I have a step model that belongs to ...
0
votes
1answer
17 views
How could one use Python's DBAPI to portably validate a database schema?
I'm writing a utility which I intend to be useable with at least three different database server backends (SQLite3, PostgreSQL, and MySQL). I'm not using an ORM (though I try a fork using SQLAlchemy ...
0
votes
2answers
39 views
Database for saving date span
I have a question generally about database design.
I need to store price for different room types for different hotels for different dates. The date could be a interval or it could be specific ...
1
vote
1answer
40 views
Using sequelize, how do you use an OR operator with the where criteria?
From a previous SO discussion I was able to update multiple sequelize instances with two conditions.
Can anyone explain how I can modify the sequelize .update method using a logical OR operator ...
0
votes
1answer
51 views
Updating multiple records using sequelize and nodejs?
Using node.js (v0.10.12) and sequelize (2.0.0-dev9), I am wanting to update attributes within an existing database table. From anther SO discussion I am able to update only one record at a time. I ...
0
votes
2answers
24 views
Get a subset of rows grouping by one of them
I am working on a ecommerce site with products, products variants, etc.
I have made a complex query that throws the following results.
id | product_id | options
----+------------+---------
1 | ...
0
votes
1answer
28 views
Sorting order behaviour between Postgres and Mysql
I have faced some strange sort order behaviour between Postgres & mysql.
For example, i have created simple table with varchar column and inserted two records as below in both Postgres and Mysql.
...
0
votes
2answers
45 views
For each / query doesn't give the right value
I have a table in my database. I want to get ltotal value from leave table, and then count all of ltotal. Here are my query and code I use:
$annual_query = pg_query("select ltotal from leave where ...
1
vote
1answer
39 views
Saving check boxes choices with Perl and PostgreSQL
I'm trying to create a user's preference page that has a bunch of check boxes letting the user decide how they want to receive their notifications. Here is my current HTML form for preferences:
...
0
votes
1answer
36 views
Selecting Positive/Negative Values of a computation SQL
I have a User model and a Workplace model. User has a field gender(m/f) and each workplace has many users. I want to select the count of total number of users in a workplace and also the count of ...
2
votes
2answers
29 views
Selecting Multiple Counts from the database
I have a User model and a Workplace model.
User has a field gender(m/f) and each workplace has many users.
I want to select the count of total number of users in a workplace and also the count of ...
0
votes
2answers
37 views
MySQL - Postgres: Wrapping multiple rows into one
Here is an example of the current rows as is:
id sdate subject exam1 assgn1 overallmark result credits
...
1
vote
1answer
38 views
Yii Rights module error after converting to postgres
I have changed my applicaiton database from mysql to postgres since this the rights module is giving exception:
`CDbCommand failed to execute the SQL statement: SQLSTATE[42P01]: Undefined table: 7
...
2
votes
4answers
54 views
I would like to find goes up by one and falls by one. Use SQL
I think about that problem for one week.
Anyway.I would like to find goes up by one and falls by one. Use SQL.
For example
This table exists.
name: ValTable
No val
1 → 2
2 → 3 ...
-1
votes
1answer
39 views
Postgres not allowing “>=” but mysql does, how to overcome?
My local rails database is mysql but my server host (heroku) is Postgres.
Probably a fairly common combination.
I have an advanced search form that work locally in development mode but not in ...