Skip to content

Single line input for psql copy command

I have the following problem:

A database server and another client machine. I have to execute several \copy commands in psql on the client machine to retrieve data and store the output in CSV files. The PostgreSQL COPY command is not an option because it stores the results on the database server, not the client machine. All \copy commands are executed from several files containing SQL commands, which are concated together by a makefile and feeded into psql. So far so good.

My SELECT commands used in the \copy are a bit more complicated and \copy requires to have the entire command on a single line. That can be done in two ways:

- Write the entire command in a single line. But that's hard to debug :-(

- Write the command as i like it and remove the linebreaks before feeding the command into psql.

Continue reading "Single line input for psql copy command"
  • Twitter
  • Bookmark Single line input for psql copy command at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Single line input for psql copy command
  • Bloglines Single line input for psql copy command
  • Technorati Single line input for psql copy command
  • Fark this: Single line input for psql copy command
  • Bookmark Single line input for psql copy command at YahooMyWeb
  • Bookmark Single line input for psql copy command at Furl.net
  • Bookmark Single line input for psql copy command at reddit.com
  • Bookmark Single line input for psql copy command at blinklist.com
  • Bookmark Single line input for psql copy command at Spurl.net
  • Bookmark Single line input for psql copy command at Simpy.com
  • Bookmark Single line input for psql copy command at blogmarks
  • Bookmark Single line input for psql copy command with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

role ... cannot be dropped because some objects depend on it

I want to drop a ROLE (previously USER/GROUPs) which is still referenced by object(s) in my database. But PostgreSQL has objections:

ERROR: role "xyz" cannot be dropped because some objects depend on it
DETAIL: access to schema public

Continue reading "role ... cannot be dropped because some objects depend on it"
  • Twitter
  • Bookmark role ... cannot be dropped because some objects depend on it at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg role ... cannot be dropped because some objects depend on it
  • Bloglines role ... cannot be dropped because some objects depend on it
  • Technorati role ... cannot be dropped because some objects depend on it
  • Fark this: role ... cannot be dropped because some objects depend on it
  • Bookmark role ... cannot be dropped because some objects depend on it at YahooMyWeb
  • Bookmark role ... cannot be dropped because some objects depend on it at Furl.net
  • Bookmark role ... cannot be dropped because some objects depend on it at reddit.com
  • Bookmark role ... cannot be dropped because some objects depend on it at blinklist.com
  • Bookmark role ... cannot be dropped because some objects depend on it at Spurl.net
  • Bookmark role ... cannot be dropped because some objects depend on it at Simpy.com
  • Bookmark role ... cannot be dropped because some objects depend on it at blogmarks
  • Bookmark role ... cannot be dropped because some objects depend on it 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

stop psql on first error

Because i always forget how to stop psql on the first error:


\set ON_ERROR_STOP


You can include this at the top of your script/file, psql will stop right after the first error and will not try to continue any other commands in the chain.

  • Twitter
  • Bookmark stop psql on first error at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg stop psql on first error
  • Bloglines stop psql on first error
  • Technorati stop psql on first error
  • Fark this: stop psql on first error
  • Bookmark stop psql on first error at YahooMyWeb
  • Bookmark stop psql on first error at Furl.net
  • Bookmark stop psql on first error at reddit.com
  • Bookmark stop psql on first error at blinklist.com
  • Bookmark stop psql on first error at Spurl.net
  • Bookmark stop psql on first error at Simpy.com
  • Bookmark stop psql on first error at blogmarks
  • Bookmark stop psql on first error 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