PHP Code

1) Vacuum frequently

You must do that especially after adding or deleting a large number of rows in a table.

2) || means concatenation in PosgreSql

(as opossed to Mysql where it ment OR)

3) PostgreSQL doesn’t know unsigned.

SMALLINT, INTEGER, BIGINT types doesn’t have an unsigned option, as it is in mysql

4) PostgreSQL is case-sensitive for string comparasions

So if in mysql making:

SELECT * FROM articles WHERE title='postgresql'

would find a record with title field: “PostgreSQL”

In PostgreSQL the same query will not find that record

The solution is to use lower(title)=’postgresql’ if you want to make the query case insensitive

5) There is no autoincrement in PostgreSql.

You have to use Sequences to do what autoincrement did in Mysql.

Example on usage of sequences:

CREATE SEQUENCE article_id START 1 INCREMENT 1;

and then, when you want to use the next value in the sequence you use:

SELECT NEXTVAL('serial');

6) Be carefull when sorting fields containing NULL values.

Mysql considered NULL values to be lower than other values, so the query:

SELECT * FROM articles ORDER BY title DESC

put the NULL rows at the end of the results while in PostgreSQL, the same query:

SELECT * FROM articles ORDER BY title DESC;

put the NULL rows in the begining of the results.

That is because “By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST  otherwise.” (PostgreSQL documentation)

The solution is to add NULLS LAST to the query if you want to behave like mysql:

SELECT * FROM TABLE ORDER BY COLUMN DESC NULLS LAST;

7) Always use unique values in OREDER BY … LIMIT

Let’s assume you have the following table article_visits

id    visits
--------------
1    1
2    9
3    1
4    24
5    1
6    1
7    1
8    1
9    1

Running:

SELECT * FROM article_vists ORDER BY visits DESC LIMIT 4 OFFSET 0

will produce:

id    visits
--------------
4     24
2     9
1     1
3     1

All seems OK, however, if you now execute:

SELECT * FROM article_vists ORDER BY visits DESC LIMIT 4 OFFSET 4

you will sometimes get

id    visits
--------------
5     24
3     9
6     1
7     1

Note how row containing id:3 is repeating? (This does not happen all the time, in fact it is very hard to reproduce)

This  inconsistency is explained in PostgreSQL Sql limit documentation
“When using LIMIT, it is a good idea to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable
subset of the query’s rows — you might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? You don’t know what
ordering unless you specify ORDER BY.
The query planner takes LIMIT into account when generating a query plan, so you are very likely to get different plans (yielding different row orders)
depending on what you use for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give
inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL
does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.”

So, if you use Select … Limit … Offset (like in a pagination script) be sure to add one unique column in the sorting:

SELECT * FROM article_vists ORDER BY visits DESC, id ASC LIMIT 0 OFFSET 4

8 COUNT(*) is slower in PostgreSQL

If you use (or plan to use) COUNT on a large database, be sure to find a way to limit the counts . There are few workaraounds for this

9) PostgresSql doesn’t know about “Replace” OR “INSERT … ON UPDATE ” instructions.

Read more about this

10) PostgreSQL give an error, while Mysql truncate longer texts

Inserting a longer text into a varchar field will give an error on PostgresSql, while on Mysql it truncate the text

The solution is to always check the data you send to PostgresSql queries.

No related posts.