Category Archives: PostgreSQL

PostgreSQL database Backup and Restore from the CLI

Quick note about how to backup and restore a PostgreSQL database from the command line. Things to know: – Name of the database you want to backup; – Username and password of the database user; – your way around a command line – at least how to open it or who to ask to open… Read More »

PostgreSQL – PostGIS versions

Login to your postgresql on some database and go: To get PostgreSQL version: To get versions for PostGIS, GEO, etc.:

How to list and alter table sequences in PostgreSQL

So you screwed up with your ninja manual inserts on some lost and forgotten table. In such a way that when your shiny django backoffice gives you a fullscreen yellow error about duplicate keys. What now? Well, you read and i will show you how to circumvent the system (well, kind of).

PosgreSQL encoding problems

A very simple problem im sure all postgreSQL users/devs have come across one time or the other: So you have your shiny PostgreSQL Database in some nice server somehwre and you want to manually make some changes. Say you have tons of records on some table and want to replace a word with a different… Read More »

How to create an auto increment primary key field in PostgreSQL

So, you want to create a fancy cool table in PostgreSQL, filled with great fields of incomparable values, along with a nice auto increment primary key. You do something like this at table creation time:

How to find out what version of PostGIS is installed

If you want to find out the version of your installed PostGis, you do this: You get a row like this: POSTGIS=”1.5.5″ GEOS=”3.3.5-CAPI-1.7.5″ PROJ=”Rel. 4.6.1, 21 August 2008″ LIBXML=”2.7.8″ USE_STATS Versions displayed will depend on your instalation, of course. External links: – PostGIS website

DELETE all duplicates from TABLE

If we have a long table with thousands of records with some duplicate contents and we want to trim it down to the bare essentials (no duplicates), here’s one (of several) way of doing it: For this, assume we have: – a table called “emails”, with fields: id_email, email – the table has several records… Read More »

Recover/reset PostgreSQL password

Ran into some PostgreSQL problems again (what else is new?), this time regarding the password for the windows user postgres creates. I was replacing postgreSQL x64 for the x86 version to fix some weborb/php/lots of other stuff problems. I uninstalled my x64 version, it left the data folder and user, and when it was reinstalling,… Read More »

XAMPP PHP not working with PostgreSQL

This is a recurring problem i had. For some reason (which i later found out), i couldnt get my php scripts to connect to postgresql. The scripts borked at, for example, pg_connect() (unable to find the function, blah blah). First off, usually php doesnt come configured to run the postgresql dll out of the box… Read More »