PostgreSQL
From WhyNotWiki
Contents |
[edit] Database maintenance
[edit] 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.
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
[edit] Create a new database
http://www.postgresql.org/docs/8.0/interactive/manage-ag-createdb.html
# create database tyler_something owner tyler;
[edit] Grant permissions on an entire database
# grant all on database db_name to tyler;
[edit] Superuser me!
# alter user tyler with createdb; # alter user tyler with createuser;
[edit] 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.
[edit] How do I dump a whole database?
pg_dump my_db
[edit] How do I dump a single table?
pg_dump --inserts --table=my_table my_db
[edit] Data/table manipulation
[edit] 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
[edit] 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.
[edit] Data formatting
[edit] 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
[edit] How to export to a CSV text file
(Which can then be converted into an Excel or other spreadsheet format.)
\a \f ',' \o fileName
[edit] 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)
[edit] 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.
[edit] Reference links
[edit] How to kill a process
pg_cancel_backend(pid);
[edit] How to
[edit] How to add a unique constraint
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
[edit] 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');
[edit] Caveats
[edit] 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
