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 ...