current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0

Tagged Questions

204
votes
15answers
30k views

Would you recommend PostgreSQL over MySQL? [closed]

We are currently working with JavaEE and MySQL 5 in our company, but we have some queries, especially delete queries that take > 10 min to execute. We are considering a switch to PostgreSQL. What are ...
193
votes
7answers
70k views

Comparison of full text search engine - Lucene, Sphinx, Postgresql, MySQL?

I'm building a Django site and I am looking for a search engine. A few candidates: Lucene/Lucene with Compass/Solr Sphinx Postgresql built-in full text search MySQl built-in full text search ...
173
votes
27answers
8k views

What resources exist for Database performance-tuning? [closed]

What good resources exist for understanding database tuning on the major engines and advancing your knowledge in that area? The idea of this question is to collect the shed load of resources that ...
95
votes
11answers
52k views

MySQL vs PostgreSQL for Web Applications [closed]

I am working on a web application using Python (Django) and would like to know whether MySQL or PostgreSQL would be better when deploying for production. In one podcast Joel said that he had some ...
87
votes
1answer
12k views

How to handle too long index names in a Rails migration with MySQL?

I am trying to add an unique index that gets created from the foreign keys of 4 associated tables (users, universities, subject_names, subject_types). add_index :studies, ["user_id", ...
56
votes
6answers
89k views

Online SQL syntax checker conforming to multiple databases [closed]

Is there any site available online for verifying the syntax which conforms to multiple databases? For example: If I have a SQL statement with a 'usage' keyword, then the site should throw me an error ...
50
votes
11answers
34k views

How do you write a case insensitive query for both MySQL and Postgres?

I'm running a MySQL database locally for development, but deploying to Heroku which uses Postgres. Heroku handles almost everything, but my case-insensitive Like statements become case sensitive. I ...
50
votes
5answers
20k views

Postgres 9.1 vs Mysql 5.6 InnoDB?

Simple question - what would better for a medium/big size database with requirement for compatibility with ACID in 2012. I have read it all (well most) about mySQL vs pgSQL but most of those posts ...
48
votes
7answers
19k views

Pros/Cons of MySQL vs Postgresql for production Ruby on Rails environment?

I will soon be switching from sqlite3 to either postgres or mysql. What should I consider when making this decision? Is mysql more suited for Rails than postgres in some areas and/or vice versa? Or, ...
47
votes
13answers
24k views

Is there a free tool to convert mysql dump to postgres

I am trying to move my site from mysql to postgres, I don't know how to do it. Already dumped my mysql database with compatible option. But it's impossible to import the data directly. I wonder if ...
46
votes
10answers
42k views

How do I UPDATE a row in a table or INSERT it if it doesn't exist?

I have the following table of counters: CREATE TABLE cache ( key text PRIMARY KEY, generation int ); I would like to increment one of the counters, or set it to zero if the corresponding ...
42
votes
9answers
17k views

postgreSQL group by different from mysql?

I've been migrating some of my mySQL queries to postgreSQL to use Heroku... most of my queries work fine, but I keep having a similar recurring error when I use group by: ERROR: column "XYZ" must ...
40
votes
10answers
11k views

MySQL vs PostgreSQL? Which should I choose for my Django project?

My Django project is going to be backed by a large database with several hundred thousand entries, and will need to support searching (I'll probably end up using djangosearch or a similar project.) ...
39
votes
5answers
23k views

Best practices for SQL varchar column length

Every time is set up a new SQL table or add a new varchar column to an existing table, I am wondering one thing: what is the best value for the length. So, lets say, you have a column called name of ...
38
votes
7answers
31k views

Show which columns an index is on in PostgreSQL

I would like to get the columns that an index is on in PostgreSQL. In MySQL you can use SHOW INDEXES FOR table and look at the Column_name column. mysql> show indexes from foos; ...
33
votes
13answers
4k views

How to filter SQL results in a has-many-through relation

Assuming I have the tables student, club, and student_club: student { id name } club { id name } student_club { student_id club_id } I want to know how to find all students ...
33
votes
5answers
19k views

GIS: PostGIS/PostgreSQL vs. MySql vs. SQL Server? [closed]

EDIT: I have been using Postgres with PostGIS for a few months now, and I am satisfied. I need to analyze a few million geocoded records, each of which will have latitude and longitude. These records ...
24
votes
5answers
7k views

Why does Heroku use Postgresql?

I'm busy porting some MySQL specific code to Postgresql in order to use it with Heroku. Just wondering if there is any specific reason why Heroku went with Postgresql over MySQL? Performance, ...
24
votes
1answer
14k views

Comparison of database column types in MySQL, PostgreSQL, and SQLite? (Cross-Mapping)

I am trying to find some way to relate column types across the the most used Databases: MySQL, PostgreSQL, and SQLite. Here is what I have so far, but I'm afraid it's not done and I need some people ...
22
votes
3answers
14k views

Migrating from MySQL to PostgreSQL [closed]

We are currently using MySQL for a product we are building, and are keen to move to PostgreSQL as soon as possible, primarily for licensing reasons. Has anyone else done such a move? Our database is ...
21
votes
6answers
12k views

Switching from MySQL to PostgreSQL - tips, tricks and gotchas?

I am contemplating a switch from MySQL to PostgreSQL. What are your tips, tricks and gotchas for working with PostgreSQL? What should a MySQLer look out for? See also: How different is ...
21
votes
2answers
7k views

Why is PHP PDO DSN a different format for MySQL versus PostgreSQL?

When I connect to a MySQL database using PDO, the way I need to connect is: $pdoConnection = new PDO("mysql:host=hostname;dbname=databasename",user,password); But, for PostgreSQL, the DSN is more ...
20
votes
6answers
5k views

For a beginner, is there much difference between MySQL and PostgreSQL

IS there much difference between MySQL and PostgreSQL for a beginner like me, using basic select statements and the like, or are the main differences with using more advanced queries?
20
votes
6answers
16k views

Simple Random Samples from a Sql database

How do I take an efficient simple random sample in SQL? The database in question is running MySQL; my table is at least 200,000 rows, and I want a simple random sample of about 10,000. The "obvious" ...
19
votes
3answers
11k views

Relationship between catalog, schema, user, and database instance

To compare databases of different vendors (Oracle, SQL Server, DB2, MySQL, and PostgreSQL) how can I identify any object uniquely and do I need a catalog? For instance, In Java's DatabaseMetadata I ...
19
votes
3answers
16k views

Why are batch inserts/updates faster? How do batch updates work?

Why are batch inserts faster? Is it because the connection and setup overhead for inserting a single row is the same for a set of rows? What other factors make batch inserts faster? How do batch ...
17
votes
3answers
1k views

how does one _model_ data from relational databases in clojure?

I have asked this question on twitter as well the #clojure IRC channel, yet got no responses. There have been several articles about Clojure-for-Ruby-programmers, Clojure-for-lisp-programmers.. but ...
16
votes
13answers
19k views

SQL ORDER BY the 'IN' value list

I have a simple SQL query (in PostgreSQL 8.3) that grabs a bunch of comments. I've composed a bunch of ids before-hand and that gets fed into the WHERE IN clause like so... SELECT * FROM "comments" ...
16
votes
8answers
2k views

Is a 'blackhole' table evil?

Reading to this question i've just learned the existence of the blackhole table trick: basically consist in using a single table to insert data, and then a trigger that split the data in many other ...
16
votes
3answers
4k views

Migrate from MySQL to PostgreSQL on Linux (Kubuntu)

A long time ago on a system far, far away... Trying to migrate a database from MySQL to PostgreSQL. All the documentation I have read covers, in great detail, how to migrate the structure. I have ...
15
votes
7answers
7k views

How different is PostgreSQL to MySQL?

I've been asked to support and take on a PostgreSQL app, but am a MySQL guy - is this a realistic task?
14
votes
1answer
12k views

Postgresql: Update timestamp when row is updated

In Mysql, we can execute this where it updates the changetimestamp every time the row is changed. create table ab ( id int, changeTimestamp timestamp NOT NULL default CURRENT_TIMESTAMP ...
14
votes
14answers
942 views

SQL hidden techniques? [duplicate]

Possible Duplicate: Hidden Features of SQL Server What are those pro/subtle techniques that SQL provides and not many know about which also cut code and improve performance? eg: I have ...
14
votes
5answers
21k views

CURRENT_TIMESTAMP in milliseconds

Is there any way to get milliseconds out of a timestamp in MySql or PostgreSql (or others just out of curiosity)? SELECT CURRENT_TIMESTAMP --> 2012-03-08 20:12:06.032572 Is there anything like ...
14
votes
3answers
7k views

Postgres ENUM data type or CHECK CONSTRAINT?

I have been migrating a MySQL db to Pg (9.1), and have been emulating MySQL ENUM data types by creating a new data type in Pg, and then using that as the column definition. My question -- could I, and ...
13
votes
6answers
8k views

SQL: Repeat a result row multiple times, and number the rows

I have a SQL query with a result like this: value | count ------+------ foo | 1 bar | 3 baz | 2 Now I want to expand this so that each row with a count larger than 1 occurs ...
13
votes
7answers
7k views

Simulating MySQL's ORDER BY FIELD() in Postgresql

Just trying out Postgresql for the first time, coming from MySQL. In our Rails application we have a couple of locations with SQL like so: SELECT * FROM `currency_codes` ORDER BY FIELD(code, 'GBP', ...
13
votes
16answers
5k views

`active' flag or not?

OK, so practically every database based application has to deal with "non-active" records. Either, soft-deletions or marking something as "to be ignored". I'm curious as to whether there are any ...
12
votes
3answers
8k views

Converting MySQL select to PostgreSQL

I have this query which works correctly in MySQL. More background on it here. SELECT c.*, SUM(ABS(v.vote)) AS score FROM categories c,items i, votes v WHERE c.id = i.category_id AND i.id = ...
12
votes
3answers
4k views

What is the MySQL equivalent of PostgreSQL's EXPLAIN ANALYZE

I'd like to get a detailed query plan in MySQL similar to EXPLAIN ANALYZE shows in PostgreSQL. Is there an equivalent?
12
votes
3answers
9k views

Is there any array data type in MySQL like in PostgreSQL?

I need to store arrays of integers in a MySQL database. In there something equivalent to this in MySQL? CREATE TABLE tictactoe ( squares integer[3][3] ); I want to store matrices with ...
12
votes
4answers
18k views

Application to reverse engineer MySQL & PostgreSQL DB?

Is there an application to reverse engineer an existing database in mysql and/or postgre? I'm interested in obtaining the DB diagram from an existing one, similar as it can be done in MSSQL Server
11
votes
9answers
2k views

Choosing the right database: MySQL vs. Everything else

It would seem these days that everyone just goes with MySQL because that's just what everyone goes with. I'm working on a web application that will be handling a large quantity of incoming data and am ...
11
votes
3answers
7k views

How to import a big database from Heroku to local mysql or sqlite3?

As per title I need to import, but PG backups is giving me strict Postgres SQL that doesn't work with MySQL, also with a non-specified encoding that I guess is UTF-16. Using db:pull takes ages and ...
11
votes
9answers
649 views

Oracle Gotchas for An Experienced Newb

What are some Oracle gotchas for someone new to the platform, but not new to relational databases (MySQL, MS SQL Server, Postgres, etc.) in general. Two examples of the kind of things I'm looking for ...
11
votes
6answers
16k views

How can I pass an “array” of values to my stored procedure?

I want to be able to pass an "array" of values to my stored procedure, instead of calling "Add value" procedure serially. Can anyone suggest a way to do it? am I missing something here? Edit: I will ...
11
votes
3answers
8k views

Postgresql Current timestamp on Update

What is the postgres equivalent of the below mysql code CREATE TABLE t1 ( created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ...
11
votes
4answers
754 views

How many of you have gone from MySQL to Postgresql? Was it worth it?

I'm thinking about moving from MySQL to Postgres for Rails development and I just want to hear what other developers that made the move have to say about it. I'm looking for personal experiences, ...
10
votes
10answers
3k views

Which database has the best support for replication

I have a fairly good feel for what MySQL replication can do. I'm wondering what other databases support replication, and how they compare to MySQL and others? Some questions I would have are: Is ...
10
votes
8answers
418 views

Is it conceptually right to do a SELECT MAX(id) etc. for finding the last inserted row?

I was reviewing a Drupal module when I found this pattern for getting the id of the row last inserted: SELECT MAX(id) FROM ... WHERE ... where the id is a field working in a usual autoincrement ...

1 2 3 4 5 … 25