Tuesday, May 22, 2012

PostgreSQL sorting NULLS

Here is a handy feature I didn't know existed until today.

You can sort null values to the top or bottom of a list by adding "NULLS FIRST" or "NULLS LAST" at the end of the ORDER BY clause.

For a real world example,
SELECT DISTINCT p.permit_id,city_permit_num,street_no,
street, name, phone1, issue_date,
pl1.notes as work_description,
pl2.notes as work_type
FROM building.permit p
LEFT JOIN public.location l ON p.location_id = l.location_id
LEFT JOIN building.contact_permit cp ON cp.permit_id = p.permit_id
LEFT JOIN public.contact c ON c.contact_id = cp.contact_id
JOIN building.permit_line pl1 ON pl1.permit_id = p.permit_id AND pl1.status = 'A'
JOIN building.item i1 ON pl1.item_id = i1.item_id
  AND i1.description = 'Work Description'
JOIN building.permit_line pl2 ON pl2.permit_id = p.permit_id
  AND pl2.status = 'A'
JOIN building.item i2 ON pl2.item_id = i2.item_id
  AND i2.description ILIKE $1
WHERE street = $2 AND street_no = $3
ORDER BY issue_date DESC NULLS LAST


We get a nice date descending result with null dates at the bottom of the list!.
Yes, this is a real query in one of my apps. Sigh.

Wednesday, May 16, 2012

PostgreSQL Psql

PostgreSQL is a robust and powerful open source database. It has more advanced features than any other open source database and scales well with huge datasets and high traffic loads.

By default, PostgreSQL listens on TCP port 5432.


Dump all databases

pg_dumpall --clean > databases.sql

Dump a database with compression (-Fc)

pg_dump -Fc --file=database.sql --clean database

Dump a database, plain text, one schema only (-n)
pg_dump -Fp --file=filename.sql -n schema --clean database

Dump a single table

Specify the schema with the table name (if applicable) with
pg_dump -t schema.table database

Dump a table definition (no data)

pg_dump -s -t schema.table database

Restore a database from a dump file

pg_restore -Fc database.sql

Restore a single table from a dump file

pg_restore -v -e -Ft -d database -n schema -t tablename dumpfile.tar
note: in this case, the dump file is in tar format, the database to restore to is after the -d switch and the table to restore is after the -t switch.

Copy data from a file into a table (from the psql client)

COPY table-name FROM '/path/to/filename' DELIMITER 'delimiter';
note: the file must be readable by postgresql (chmod 755), the default delimiter is tab.

Copy data from a table to a file (from the psql client)

COPY table-name TO '/path/to/filename' DELIMITER 'delimiter';
note: the directory and file must be writable by postgresql, the default delimiter is tab.

List all schemas
select schema_name from information_schema.schemata

Start the PostgreSQL interactive terminal

psql

Psql - show a list of databases

\l
Lowercase L, not the number 1

Psql - show all users

select * from pg_user;

Psql - show all tables (including system tables)

select * from pg_tables;

Psql - show tables in the current context (database/schema)

\d

Psql - show description of tablename

\d tablename

Psql - show description of tablename, along with constraints, rules, and triggers

\d+ tablename

Psql - change current database

\c database;

Psql - show all schemas in the current database

\dn

Psql - Grant permissions on a schema to a user

GRANT ALL ON myschema TO user;

Psql - quit psql

\q

Psql - show help

\?

Psql - copy a table to a tab delimeted file

COPY table TO 'table.txt';

Psql - load a table from a tab delimeted file

COPY table FROM 'table.txt';

Psql - show permissions on database objects

\z [object]
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
R -- RULE
x -- REFERENCES (foreign keys)
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
T -- TEMPORARY
arwdRxt -- ALL PRIVILEGES (for tables)
* -- grant option for preceding privilege
/yyyy -- user who granted this privilege

Psql - getting or setting sequence values
Get next value of a sequence:
SELECT nextval('this_id_seq');

Set current value of a sequence to 1000:
SELECT setval('this_id_seq', 1000);

Grant access to all tables in a schema


GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO user;


Run the vacuum utility (for version less than 9.0)


vacuumdb --verbose --analyze --all
Note: vacuum reclaims space from deleted records and updates indexes. It should be set up in cron. Newer versions of postgresql may run vacuum automatically.

Increase perfomance with shared memory

One effective performance tuning tip for Postgresql is to increase the shared memory buffers. This might require adding RAM to the server. Many Linux distros default to 32MB of shared memory, controlled by two kernel parameters:
/proc/sys/kernel/shmmax
/proc/sys/kernel/shmall

These values can be changed at run time, but it is better to set them at boot using the /etc/sysctl.conf file. This increases shared memory to 1GB:
# increase shared buffers for postgres at boot
kernel.shmmax=1073741824
kernel.shmall=2097152

Then, tell PostgreSQL to use 768MB of the 1GB available in the /var/lib/pgsql/data/postgresql.conf file:
shared_buffers = 98304 # min 16, at least max_connections*2, 8KB each

Restart PostgreSQL for the change to take effect.