PostgreSQL

From WhyNotWiki
Revision as of 21:42, 21 August 2007 by Tyler (Talk | contribs)
(diff) ←Older revision | Current revision (diff) | Newer revision→ (diff)
Jump to: navigation, search

Contents

Database maintenance

Create a new user

http://www.postgresql.org/docs/8.0/interactive/auth-methods.html

# create user tyler with password 'password';

http://trac.edgewall.org/wiki/TracEnvironment#DatabaseConnectionStrings. Retrieved on 2007-05-11 11:18.


Generally, the following is sufficient to create a database user named tracuser, and a database named trac.

createuser -U postgres -E -P tracuser
createdb -U postgres -O tracuser -E UTF8 trac

Create a new database

http://www.postgresql.org/docs/8.0/interactive/manage-ag-createdb.html

# create database tyler_something owner tyler;

Grant permissions on an entire database

# grant all on database db_name to tyler;

Superuser me!

# alter user tyler with createdb;
# alter user tyler with createuser;

How do I use pg_dump?

       --data-only
              Dump only the data, not the schema (data definitions).
       --schema-only
              Dump only the object definitions (schema), not data.

       --format=format
              Selects the format of the output.  format can be one of the following:

              p      Output a plain-text SQL script file (default)

              t      Output a tar archive suitable for input into pg_restore. Using this archive format allows reordering and/or exclusion of
                     database  objects  at  the time the database is restored. It is also possible to limit which data is reloaded at restore
                     time.

              c      Output a custom archive suitable for input into pg_restore. This is the most flexible format in that it allows  reorder-
                     ing of loading data as well as object definitions. This format is also compressed by default.

       --clean
              Output commands to clean (drop) database objects prior to (the commands for) creating them.

       --inserts
              Dump  data  as  INSERT  commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps
              that can be loaded into non-PostgreSQL databases. Note that the restore may fail  altogether  if  you  have  rearranged  column
              order.  The --column-inserts option is safer, though even slower.

       --column-inserts
       --attribute-inserts
              Dump  data  as INSERT commands with explicit column names (INSERT INTO table (column, ...) VALUES ...). This will make restora-
              tion very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases.

How do I dump a whole database?

pg_dump my_db

How do I dump a single table?

pg_dump --inserts --table=my_table my_db

Data/table manipulation

How do I copy a table?

If you just want to do a quick backup of data, this would work:

SELECT * INTO old_zips FROM zips;

http://www.postgresql.org/docs/8.0/interactive/sql-selectinto.html

The problem with that, however, is that it doesn't copy all the details of the table (like not null constraints, defaults), so it's not an exact copy of the table.

If you want an exact copy, you can use pg_dump to dump the schema and data and then you can read that back in. But you have to be sure to change the name of the table prior to reading it back in, and that can be a bit error prone (search and replace).

CREATE TABLE films_recent AS
  SELECT * FROM films WHERE date_prod >= '2002-01-01';

http://www.postgresql.org/docs/8.0/interactive/sql-createtableas.html

How do I drop a table only if it exists?

In MySQL, there was the handy drop table if exists table_name feature. What is the equivalent, if any, in PostgreSQL?

# drop table if exists old_zips;
ERROR:  syntax error at or near "exists" at character 15
LINE 1: drop table if exists old_zips;

# drop table old_zips;
ERROR:  table "old_zips" does not exist

Perhaps that error is an exception that can be caught?

The goal here is to have it drop the table if it exists but otherwise to "fail" silently (= don't raise an error!) and continue on with subsequent statements.

Data formatting

Format as date/time

SELECT to_char(time, 'YYYY-MM-DD HH24:MI:SS') as time ...

        time         |
---------------------+
 2006-06-18 15:42:40 |

http://www.postgresql.org/docs/8.0/interactive/functions-formatting.html

How to export to a CSV text file

(Which can then be converted into an Excel or other spreadsheet format.)

\a
\f ','
\o fileName

psql: Formatting

Formatting
  \a             toggle between unaligned and aligned output mode
  \C [STRING]    set table title, or unset if none
  \f [STRING]    show or set field separator for unaligned query output
  \H             toggle HTML output mode (currently off)
  \pset NAME [VALUE]
                 set table output option
                 (NAME := {format|border|expanded|fieldsep|footer|null|
                 recordsep|tuples_only|title|tableattr|pager})
  \t             show only rows (currently off)
  \T [STRING]    set HTML <table> tag attributes, or unset if none
  \x             toggle expanded output (currently off)

To disable part of an SQL script

stuff to execute

\q

stuff to disable

This quit when it gets to the \q, effectively disabling anything that comes afterwards.

This can be easier sometimes than the comment method:

stuff to execute

/*
stuff to disable
*/

This won't work when "stuff to disable" contains comments of the same form, since you can't nest comments.

Reference links

How to kill a process

pg_cancel_backend(pid);

How to

How to add a unique constraint

ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

How to turn a column into a SERIAL (auto-increment) type after the fact

This happened to me once. I wanted the column to be

                                         Table "public.to_do"
   Column   |            Type             |                                 Modifiers
------------+-----------------------------+----------------------------------------------------------------------------
 id         | integer                     | not null default nextval('public.to_do_id_seq'::text)

but it looked like this:

                                         Table "public.to_do"
   Column   |            Type             |                                 Modifiers
------------+-----------------------------+----------------------------------------------------------------------------
 id         | integer                     |

(no NOT NULL constraint and 500,000 rows had NULL as the value)

This didn't work, because it only executed the nextval() once for the whole query, so it set id to 1 for all rows!

update to_do set id = (select nextval('to_do_id_seq'));

The easiest way I found to do this (short of dropping the id column and adding it back, but let's assume you're not allowed to drop the column for some reason) is to create a new SERIAL column -- call it new_id -- and then copy values from it back to the old id column:

alter table to_do add column new_id serial;
update to_do set id = new_id;
alter table to_do drop column new_id;

create sequence to_do_id_seq
        increment by 1
        no minvalue
        no maxvalue
        start with 480000;
alter table to_do alter column id set default nextval('to_do_id_seq');

Caveats

and has higher associativity than or

Proof:

# select 'true' as value where true or true and false;
 value 
-------
 true
(1 row)

# select 'true' as value where true or (true and false);
 value 
-------
 true
(1 row)

# select 'true' as value where (true or true) and false;
 value 
-------

How this bit me:

I had a where clause written something like this:

where
    a or b
  and
    c

I was baffled as to why it was returning rows for which the c condition was false... until I realized that it was not grouping my conditions how I had them formatted; rather, it was grouping (b and c) and then oring that with a.

where
    a
  or 
    b and c

This is not what I would have expected. Most languages that I can think of give and and or equal associativity, and simply evaluate from left-to-right... Not PostgreSQL. Oh well, nothing that a few well-placed parentheses can't fix:

where
    (a or b)
  and
    c
Ads
Personal tools