Skip to content

SELECT pg_sleep_until('#800Monies');

Pavlo recently pointed out that the pg_sleep() function in PostgreSQL has two alternatives: pg_sleep_for() and pg_sleep_until().

What do they do?

 

Continue reading "SELECT pg_sleep_until('#800Monies');"
  • Twitter
  • Bookmark SELECT pg_sleep_until('#800Monies'); at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg SELECT pg_sleep_until('#800Monies');
  • Bloglines SELECT pg_sleep_until('#800Monies');
  • Technorati SELECT pg_sleep_until('#800Monies');
  • Fark this: SELECT pg_sleep_until('#800Monies');
  • Bookmark SELECT pg_sleep_until('#800Monies'); at YahooMyWeb
  • Bookmark SELECT pg_sleep_until('#800Monies'); at Furl.net
  • Bookmark SELECT pg_sleep_until('#800Monies'); at reddit.com
  • Bookmark SELECT pg_sleep_until('#800Monies'); at blinklist.com
  • Bookmark SELECT pg_sleep_until('#800Monies'); at Spurl.net
  • Bookmark SELECT pg_sleep_until('#800Monies'); at Simpy.com
  • Bookmark SELECT pg_sleep_until('#800Monies'); at blogmarks
  • Bookmark SELECT pg_sleep_until('#800Monies'); with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

create language if not exist

In a customer project i have to setup a database from a Makefile. Part of my problem: on windows the installer may or may not install "plpgsql" into template1 so in consequence this language would be activated in every new database. But that's not predictable. This problem can appear on different Linux/Unix distributions too.

In contrary to some other opinions in #postgresql i dislike filtering error messages and i prefer clean solutions.

Continue reading "create language if not exist"
  • Twitter
  • Bookmark create language if not exist at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg create language if not exist
  • Bloglines create language if not exist
  • Technorati create language if not exist
  • Fark this: create language if not exist
  • Bookmark create language if not exist at YahooMyWeb
  • Bookmark create language if not exist at Furl.net
  • Bookmark create language if not exist at reddit.com
  • Bookmark create language if not exist at blinklist.com
  • Bookmark create language if not exist at Spurl.net
  • Bookmark create language if not exist at Simpy.com
  • Bookmark create language if not exist at blogmarks
  • Bookmark create language if not exist with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Understanding SQL

Just have to port a phpkit database to something else. It seems, the phpkit ppl did not really understand what SQL is for.

The 'phpkit_config' table in this case contains 255(!) columns and a lot of this columns seems to be for extensions.

This is a very good example where you should use normalization: you don't add more and more columns to one table but you have a key/value table and for new entries you add a new key with a value. No need to change the table at all.

  • Twitter
  • Bookmark Understanding SQL at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Understanding SQL
  • Bloglines Understanding SQL
  • Technorati Understanding SQL
  • Fark this: Understanding SQL
  • Bookmark Understanding SQL at YahooMyWeb
  • Bookmark Understanding SQL at Furl.net
  • Bookmark Understanding SQL at reddit.com
  • Bookmark Understanding SQL at blinklist.com
  • Bookmark Understanding SQL at Spurl.net
  • Bookmark Understanding SQL at Simpy.com
  • Bookmark Understanding SQL at blogmarks
  • Bookmark Understanding SQL with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

table size, database size

I have seen this question more than once in the past: "How much storage space do i need for my table?"
PostgreSQL can give you this information.


The storage space (in bytes) for one specific table:

ads=# select pg_relation_size('size_test');
pg_relation_size
------------------
5668864
(1 row)

Same query, but with the result in a human-readable format:

ads=# select pg_size_pretty(pg_relation_size('size_test'));
pg_size_pretty
----------------
5536 kB(1 row)

Remember that this is only the size of the table, not included an index or additional stuff.
The size including any index can be found out with:


ads=# select pg_size_pretty(pg_total_relation_size('size_test'));
pg_size_pretty
----------------
7656 kB
(1 row)


The size of a complete database:

ads=# select pg_size_pretty(pg_database_size('ads'));
pg_size_pretty
----------------
11 MB
(1 row)


You can even find out, how much space a specific value needs:

ads=# select pg_column_size(5::smallint);
pg_column_size
----------------
2
(1 row)

ads=# select pg_column_size(5::int);
pg_column_size
----------------
4
(1 row)

ads=# select pg_column_size(5::bigint);
pg_column_size
----------------
8
(1 row)

ads=# select pg_column_size('This is a string'::varchar);
pg_column_size
----------------
20
(1 row)

ads=# select length('This is a string'::varchar);
length
--------
16
(1 row)

The 4 byte difference between the column size and the string length is the storage overhead needed for varchar columns.

The size for a tablespace can be found out with pg_tablespace_size(), but i don't have a machine with configured tablespace at hand to show an example.
  • Twitter
  • Bookmark table size, database size at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg table size, database size
  • Bloglines table size, database size
  • Technorati table size, database size
  • Fark this: table size, database size
  • Bookmark table size, database size at YahooMyWeb
  • Bookmark table size, database size at Furl.net
  • Bookmark table size, database size at reddit.com
  • Bookmark table size, database size at blinklist.com
  • Bookmark table size, database size at Spurl.net
  • Bookmark table size, database size at Simpy.com
  • Bookmark table size, database size at blogmarks
  • Bookmark table size, database size with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Avoid Unique Key violation

If you have a table with an unique key field, want to insert new values but you don't know, if the value may already be in the table, you could use the following query:

CREATE TABLE uk_test (id SERIAL, col1 VARCHAR(20) NOT NULL UNIQUE, col2 VARCHAR(20) NOT NULL);
INSERT INTO uk_test (col1, col2) SELECT 'content 1', 'content 2' WHERE NOT EXISTS
(SELECT col1 FROM uk_test WHERE col1='content 1');
This does avoid unique key violation errors in a way, that no insert will happen if the value is already in the table.
I would like to thank depesz from #postgresql for the idea.
  • Twitter
  • Bookmark Avoid Unique Key violation at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Avoid Unique Key violation
  • Bloglines Avoid Unique Key violation
  • Technorati Avoid Unique Key violation
  • Fark this: Avoid Unique Key violation
  • Bookmark Avoid Unique Key violation at YahooMyWeb
  • Bookmark Avoid Unique Key violation at Furl.net
  • Bookmark Avoid Unique Key violation at reddit.com
  • Bookmark Avoid Unique Key violation at blinklist.com
  • Bookmark Avoid Unique Key violation at Spurl.net
  • Bookmark Avoid Unique Key violation at Simpy.com
  • Bookmark Avoid Unique Key violation at blogmarks
  • Bookmark Avoid Unique Key violation with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Reverse a text in PostgreSQL

I'm always searching for a reverse function, so here is the link:

http://www.varlena.com/GeneralBits/53.php

code snippet is available in plperl and plpgsql.

  • Twitter
  • Bookmark Reverse a text in PostgreSQL at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Reverse a text in PostgreSQL
  • Bloglines Reverse a text in PostgreSQL
  • Technorati Reverse a text in PostgreSQL
  • Fark this: Reverse a text in PostgreSQL
  • Bookmark Reverse a text in PostgreSQL at YahooMyWeb
  • Bookmark Reverse a text in PostgreSQL at Furl.net
  • Bookmark Reverse a text in PostgreSQL at reddit.com
  • Bookmark Reverse a text in PostgreSQL at blinklist.com
  • Bookmark Reverse a text in PostgreSQL at Spurl.net
  • Bookmark Reverse a text in PostgreSQL at Simpy.com
  • Bookmark Reverse a text in PostgreSQL at blogmarks
  • Bookmark Reverse a text in PostgreSQL with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

find the sequence name

Because i always forget this function name:

pg_get_serial_sequence(table_name, column_name)

Normally it should be: 'table_name' . '__' . 'column_name' . '_' . 'seq'

  • Twitter
  • Bookmark find the sequence name at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg find the sequence name
  • Bloglines find the sequence name
  • Technorati find the sequence name
  • Fark this: find the sequence name
  • Bookmark find the sequence name at YahooMyWeb
  • Bookmark find the sequence name at Furl.net
  • Bookmark find the sequence name at reddit.com
  • Bookmark find the sequence name at blinklist.com
  • Bookmark find the sequence name at Spurl.net
  • Bookmark find the sequence name at Simpy.com
  • Bookmark find the sequence name at blogmarks
  • Bookmark find the sequence name with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca