Home - Postgres

mostly realted to DBIx::Class

doc for DBIx::Class

Matches regular expression, case sensitivehttp://search.cpan.org/~ash/DBIx-Class/

'thomas' ~ '.*thomas.*' ~*http://search.cpan.org/~mstrout/SQL-Abstract-1.24/lib/SQL/Abstract.pm

dump a table

pg_dump -h ccjams001.riken.go.jp -p 5448 -t aggjob_run8 -d BadEvents > aggjob_run8.sql

insert array constant

quote the array with single quotes

'{1,2,3,4,5}'

DBD::Pg dsn

  $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options",
                      $username,
                      $password,
                      {AutoCommit => 0, RaiseError => 1, PrintError => 0}
                     );

generate schema automatically

perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib \
-e 'make_schema_at("SpinDBSchema", { debug => 1 }, [ "dbi:Pg:dbname=spin_scratch"])'

show SQL in server output

set DBIC_TRACE=1

or use this in the code:

$myschema->storage->debug(1);

SQL::Abstract

the perldoc for this class is useful to write 'search' condition for DBIx::Class::ResultSet

  my @albums = $schema->resultset('Album')->search({
    -or => [
      -and => [
        artist => { 'like', '%Smashing Pumpkins%' },
        title  => 'Siamese Dream',
      ],
      artist => 'Starchildren',
    ],
  });

SQL::Abstract is also good for CGI::FormBuilder and HTML::QuickTable

Regex

regular expression

http://www.postgresql.org/docs/7.4/static/functions-matching.html

~ regex match
~* case insensitive
!~ no match
!~* no match, case insensitive

Show user-defined type

SELECT * from pg_type where typname='md5type';

use with Python

http://wiki.python.org/moin/UsingDbApiWithPostgres

automatic timestamp

CREATE TABLE test (time default CURRENT_TIMESTAMP);