Tagged Questions
0
votes
1answer
44 views
group by clause without aggregate function
I've always assumed GROUP BY was designed specifically for aggregate functions and in all other circumstances you should use ORDER BY. For example, we have three tables: orders, shippers, and ...
0
votes
1answer
442 views
multi-master to single-slave replication at table level with PostgreSQL or MySQL
Here is my scenario
Master1 hosting DB1
Master2 hosting DB2
...
MasterN hosting DBN
replicate to:
Slave1 hosting DB1,DB2... DBN
I've read similar questions and they recommend to start different ...
0
votes
2answers
60 views
Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases
We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL.
In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
0
votes
1answer
48 views
Database design Problem for the web-app
Hello I am in a problem of designing a web-app database schema.
Suppose I have 10 shops and every shop gives deals on every different day Sunday, Monday and so on and for limited time period like ...
2
votes
1answer
393 views
Partitioning MySQL vs PostgreSQL
For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which ...
1
vote
3answers
279 views
What's the most efficient UUID column type
For storing a 128 bits UUID there are multiple storage options:
a byte[16] column
two bigint/long(64 bits) columns
a CHAR(36) column - 32 hex digits + 4 dashes.
a UUID database specific column, if ...
2
votes
2answers
4k views
How to convert PostgreSQL to MySQL
How can I convert database from PostgreSQL to MySQL?
I have the postgresql dump file already and I try to use pg2mysql script, it converted but when uploading the file it produces this error:
ERROR ...
1
vote
0answers
126 views
Mariadb new features vs PostgreSQL? [closed]
Mariadb 10 offer lot of new features. ( https://mariadb.com/products/mariadb/mariadb-vs-mysql) . I want to know, with these new features, will PostgreSQL would be a better solution for newly starting ...
9
votes
3answers
4k views
How to convert mysql to postgresql?
I am desperately looking for a conversion tool to convert a big mysql database to postgresql. I need the tool/script to be:
Free
Works under Linux
Simple to use and not buggy
You actually tried and ...
1
vote
2answers
68 views
how should i do this trigger?
I am a newbie to the sql world :)
Right now i'm trying to create my first mysql trigger that would either update a row or insert a new one. Could you help me with that ?
Here is the code :
DELIMITER ...
22
votes
3answers
18k views
Find highest level of a hierarchical field: with vs without CTEs
note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database.
I ...
18
votes
6answers
654 views
Count where two or more columns in a row are over a certain value [basketball, double double, triple double]
I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating ...
1
vote
3answers
98 views
Mass data upload from Excel - technology
I am a Developer seeking help in terms of DB technology for a specific requirement. I have done some research but can not conclude with list.
Requirement:
There will be a web application which will ...
6
votes
4answers
4k views
No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00
I've spent the last 8 hours trying to import the output of 'mysqldump --compatible=postgresql' into PostgreSQL 8.4.9, and I've read at least 20 different threads here and elesewhere already about this ...
6
votes
1answer
1k views
Is PostgreSQL replication production ready?
How does PostgreSQL native replication compare to MySQL?
I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects?
...
2
votes
2answers
133 views
Problem translating MySQL to PostgreSQL
We're using Trac for issue tracking. I migrated it from MySQL to PostgreSQL, but some of the ticket queries no longer work.
In the below, all I did this far is replace IFNULL() with COALESCE() and ...
-4
votes
1answer
220 views
What are the disadvantages of PostgreSQL compared to other DBMS? [closed]
I am a student studying PostgreSQL.
I want to know the advantages and disadvantages of PostgreSQL in comparison to other DBMS.
2
votes
0answers
72 views
How to distribute a given database between multiple DBMS on a LAN? [closed]
Given a Database D, i need to distribute it over different DBMS in a LAN.
D will be split into tables, and each DBMS will store a given table.
Here is a sketch of what i need to do :
I also need to ...
1
vote
1answer
96 views
Vacuum in postgresql and Purge in mysql
Do the vacuum operation in postgresql and purging operation in mysql perform the same tasks?
0
votes
0answers
65 views
Converting a Ruby on Rails site from MySQL to PostgreSQL? [closed]
Whats the best way to convert a MySQL database to PostgreSQL so that it does not create errors, and safely transferred?
My developer is redeveloping my site and he suggested it use PostgreSQL ...
1
vote
1answer
73 views
Best approach on storing/displaying sanitized data
Surfing the web I find two approaches on storing data in databases.
Approach 1:
Store data in the database with the html-specialcharacters sanitized
$userdata = $_POST['field'];
...
2
votes
1answer
203 views
What is the difference between MySQL and PostgreSQL in terms of writing SQL queries? [closed]
Other than performance and security is there really any difference between MySQL and Postgres like SQL queries, built-in functions, procedures and so on?
I'm asking this because I'm familiar with PHP ...
0
votes
0answers
127 views
Postgres vs MySQL Database Maintenance
I have been using Postgres and MySQL for a long time now. There are a few routine maintenance chores that must be performed on a regular basis to keep a PostgreSQL server running smoothly. As as ...
4
votes
1answer
948 views
MySQL failing and very slow on importing a large file
I have a 47 GB MySQL dump of a single table:
http://dumps.wikimedia.org/commonswiki/latest/commonswiki-latest-image.sql.gz
I ultimately want it into PostgreSQL, but since I didn't figure out an easy ...
-1
votes
2answers
139 views
What database should I use to create a webmail service like gmail? [closed]
I planned to use python for my frontend with django framework.
But for backend, I have no idea which database I should use.
Since it involves plenty of reads and writes can someone point me in the ...
1
vote
0answers
81 views
How can I ensure a valid MySQL -> Postgres migration? [closed]
Our projects are migrating from MySQL to Postgres. We have a Ruby-on-Rails app that handles the schema (source code is on github). We have rewritten all queries to be ANSI-compliant.
We have tried ...
9
votes
3answers
2k views
How is LIKE implemented?
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?
The naive approach would be to inspect ...
0
votes
0answers
69 views
What is the postgresql equivalent of mysql grant require x509?
We're migrating from MySQL to PostgreSQL. We have to support SSL.
MySQL supports creating users which require SSL certificate validity using "GRANT ... REQUIRE X509" [1]. Does PostgreSQL have ...
0
votes
3answers
380 views
Performance of primary key IDs with gaps (but in sequential order)
I know that having non-sequential IDs is bad for index performance. But assuming all my IDs are created in correct order, but with large gaps: i.e:
154300000
283700000
351300000
464200000
...will ...
2
votes
1answer
154 views
Scaling of PostGIS vs MySQL cos/sin calculations [closed]
I need to sort database rows according to GPS coordinates (or any other geographical representation).
The way I've understood it, MySQL Spatial Indexes are too buggy at the time of writing (at least ...
1
vote
1answer
4k views
Performance difference between MySQL and PostgreSQL for the same schema/queries [closed]
I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS.
I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests ...
0
votes
1answer
148 views
Measuring SQL execution time in PostgreSQL?
MySQL has a performance_schema database that allows one to capture SQL statement execution time data in a table (e.g. performance_schema.events_statements_history_long ; useful MySQL link).
I was ...
0
votes
2answers
254 views
Which schema is better for a shopping project? [closed]
I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized ...
1
vote
1answer
1k views
Postgresql vs MySQL - Which is better for join queries & writing data(inserts) [closed]
I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing ...
0
votes
4answers
1k views
Daily and Differential Backups [closed]
Is there a program with which I can create daily MySQL, PostgreSQL and MariaDB backups with these requirements:
The first time, make a full backup.
The second time, only the changes since the last ...
0
votes
1answer
130 views
How does MySQL or PostgreSQL deal with multi-column indexes in ActiveRecord?
I'm creating indexes for my models right now and I want to know how MySQL and PostgreSQL deal with an index with more than 1 column like:
add_index :users, [:username, :created_at]
That should ...
3
votes
2answers
298 views
Equivalents to `ora_rowscn` in other databases?
Do other databases other than Oracle have ora_rowscn (http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm) equivalents, or do you have to implement them yourself in your fields, ...
-1
votes
1answer
338 views
how to export postgresql database table into mysql
I am trying to get data from a Postgres database, but I am totally new to using Postgres.
I am here to ask my question to get some ideas from the best answer on how can I can do this. If possible, I'd ...
0
votes
2answers
123 views
Recommendation for RDBMS for large table [closed]
I have been asked for recommendation on the optimal RDBMS for a large amount of data. The largest table would contain 2TB of data. My own largest table is only 400GB (mysql, performs very well).
The ...
0
votes
1answer
378 views
“relation does not exist” trying to import mysql dump into postgres
environment:
ubuntu 10.04
mysql server 5.1.69
postgres 9.2
Here's the sequence of steps:
created a new postgres database, myDatabase
executed this command: mysqldump -u root -p ...
2
votes
4answers
1k views
Database design - People and Organisations
Databases are not my primary skill and I am finding I need some assistance please.
The software we are building has "Customers".
The Customer could either be a Person or an Organisation.
I really ...
8
votes
2answers
4k views
For absolute performance, is SUM faster or COUNT?
This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100.
I tend to prefer
COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)
However, this is ...
1
vote
1answer
133 views
Equivalent of MRG_MYISAM in databases other than MySQL? [closed]
Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)?
I know about fragmenting, but this is not quite the same AFAIK. We're using ...
8
votes
5answers
6k views
Alter table on live production databases
How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)?
I know the correct way is to ...
0
votes
1answer
210 views
Object-oriented programming CRUD design pattern
At first I have posted this question at http://cstheory.stackexchange.com/questions/17170/object-oriented-programming-crud-design-pattern - that's because I think it's a subect question, and it can ...
5
votes
4answers
2k views
Why are so many MPP solutions based on PostgreSQL instead of MySQL?
Astor Data, Greenplum and GridSQL all allow Massive Parallel Processing of SQL queries. They are also all built around PostgreSQL technology. Is this just because of licensing issues or are there ...
1
vote
0answers
1k views
Achieve PostgreSQL hstore functionality in MySQL
Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table.
I don't know if this feature is available out of the box in MySQL like ...
1
vote
1answer
215 views
Efficient query plan for selecting all data duplicated on several columns
The flavour in question is PostgreSQL, but it is a generic enough query pattern that your techniques for optimising against other RDBMs should help as well.
The purpose of the query is to show all ...
1
vote
1answer
260 views
Which Database to choose: MySQL or Oracle
I have an application generating time series data (32 channels, 22KHz, 6 secs) once a minute. These have to be stored in a database. At the moment I'm using MySQL with InnoDB tables ...
0
votes
1answer
225 views
Storing 5TB web server access log vs OLAP DB
We have over 5TB compressed web server log in a HDFS and we often analyse using Hadoop.
It is painful to run map reduce on 5TB of data and most importantly, not many developer are familiar with it.
...
