If you're going to use a boolean condition in your where clause, you're going to want to make sure you use an operator so that the index is used (if present).

For example, this will not use the index:

db=> SELECT id FROM users WHERE active;

This will use the index:

db=> SELECT id FROM users WHERE active = true;

Create a custom type which describes the structure of the rows returned:

CREATE TYPE func_return_type AS (column_name column_type, ...);

Then create the function:

CREATE OR REPLACE FUNCTION some_func (param_type, ...)
RETURNS SETOF func_return_type AS '
 SELECT columns FROM table WHERE condition;
' LANGUAGE 'SQL';

You've got to build a query, but you're going to need bits of data from previous queries to do it. As you run the smaller queries, note the interesting value returned as a comment on the same line for use later:

select id_job from jobs where name like = '%foo%' -- 41319
select id_jobentry from jobentries where id_job = 41319

Thanks to McG for the tip.

Use the form ::type to ensure that a value is cast to the proper type:

SELECT * FROM table WHERE column = 5::bigint;

In this case, column is indexed and is a bigint. The index will not be used if the query planner doesn't encounter a value of the index type. Note: this only applies in PostgreSQL 7.x.

During an ETL job sometimes bad data will sneak into your OLTP and needs to be searched out. You can adjust the step limit by querying the R_STEP table and then updating the associated row in the R_STEP_ATTRIBUTE table:

To acquire the id_step for the r_step_attribute update:
sql> select id_step from r_step where name = 'your_stepname';

To update the limit (X = step limit, Y = id_step from above):
sql> update r_step_attribute set value_num=X where id_step=Y and code='limit';

Tip based on the ETL toolkit found at http://kettle.pentaho.org/.

Thanks to McG for the tip.

If you've got another service (e.g., tomcat) fighting for port 8080, you can change the port Oracle uses with the following calls:

-- change HTTP port from 8080 to 8083
call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(), '/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()', 8083));

-- change FTP port from 2100 to 2111
call dbms_xdb.cfg_update(updateXML( dbms_xdb.cfg_get(), '/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port/text()' , 2111));

-- refresh settings
exec dbms_xdb.cfg_refresh;

Tip borrowed from an article by red-database-security.com.

Tired of those wide tables that wrap? You can end your query with a \G to get output like this:

mysql> select * from users\G
*************************** 1. row ***************************
     id: 1
   name: xinu
created: 20050115210745
*************************** 2. row ***************************
     id: 2
   name: cygnus
created: 20050115210803
2 rows in set (0.00 sec)

If you need to convert a TEXT column to a BYTEA column, you can use a cast expression to do the work:

ALTER TABLE mytable ALTER COLUMN col TYPE bytea USING
  decode(replace(col, '\\', '\\\\'), 'escape');

Note that altering the column type of a column is only supported beginning with PostgreSQL version 8.0. For more information, see the manual.

To get a count of rows by the first letter of the last name (lname):

sql> select count(1), substr(lname, 1, 1) as first from some_table group by first;

Thanks to McG for the tip.

If you want to create a table that has the same structure as the result set of a query, run:

mydb> CREATE TABLE foobar AS SELECT ...;

If you want to see how a query like \du is being built:

\set ECHO_HIDDEN

If you have a query like this:

SELECT max(some_column) FROM mytable WHERE ...;

and you want to eliminate the sequence scan that kills the query's performance, an alternative is:

SELECT some_column FROM mytable WHERE ... ORDER BY some_column DESC LIMIT 1;

MIN and MAX aggregates can be eliminated this way.

You need to determine if there are any duplicates in a column; here's a quick way to do that with a couple count() functions:

sql> select count(field), count(distinct field) from table where...

If the counts are the same, you're duplicate free!

Thanks to McG for the tip.

If you've munged your template1 database, you can rebuild it from the template0 database.

First, you must set the datistemplate attribute of template1 to false so we can drop it:

xinu=# UPDATE pg_database SET datistemplate = false WHERE datname = 'template1';
UPDATE 1
xinu=# DROP DATABASE template1;
DROP DATABASE

Next, re-create template1 using template0 as a template:

xinu=# CREATE DATABASE template1 WITH TEMPLATE = template0;
CREATE DATABASE

Then restore the datistemplate attribute of template1:

xinu=# UPDATE pg_database SET datistemplate = true WHERE datname = 'template1';
UPDATE 1

If you have a table 'mytable' whose columns all have default values defined and you want to insert a record using all default values, run this query:

INSERT INTO mytable DEFAULT VALUES;

If you have a query that's returning 5 identical rows and you really only need one, you can use SELECT DISTINCT:

sql> SELECT DISTINCT foo, bar, baz FROM bing...

An alternative would be appending LIMIT 1 to the end of the query.

Consider the following schema:

CREATE TABLE loadtest (
    pkey int(11) NOT NULL auto_increment,
    name varchar(20),
    exam int,
    score int,
    time_enter timestamp(14),
    PRIMARY KEY (pkey),
);

And the data you need to load:

'name22999990',2,94
'name22999991',3,93
'name22999992',0,91

Running this query would load the data into the columns name, exam, score:

mysql> LOAD DATA INFILE '/tmp/out.txt' INTO TABLE loadtest
    -> FIELDS TERMINATED BY ',' (name,exam,score);

** Note: This tip borrowed from the Linux Gazette. You can read the complete article at http://www.linuxgazette.com/node/9059.

To create a new database:

$ mysqladmin create <database_name>

To grant permissions to a user, run this:

$ mysql -u root -p
Password: (enter password)
mysql> GRANT ALL ON db_name.* TO username@localhost IDENTIFIED BY "userpasswd";

To flush the privilege tables, run this:

$ mysqladmin flush-privileges

or:

mysql> flush privileges;

To revoke the privileges from a particular user/host pair:

mysql> revoke all privileges, grant option from username;

It is possible to order by an array element as follows:

SELECT * FROM (SELECT ARRAY[1, 2, 3] AS foo) sub ORDER BY foo[1];

You can use this query to find the table information:

SELECT
  a.attnum,
  a.attname AS field,
  t.typname AS type,
  a.attlen AS length,
  a.atttypmod AS lengthvar,
  a.attnotnull AS notnull
FROM
  pg_class c,
  pg_attribute a,
  pg_type t
WHERE
  c.relname = 'your_table_name'
  AND a.attnum > 0
  AND a.attrelid = c.oid
  AND a.atttypid = t.oid
  ORDER BY a.attnum

Use explain:

EXPLAIN SELECT * FROM some_table WHERE condition;

If you want to see how long a query is taking to run, you can either run explain analyze or enable timing for the client by typing \timing.

It is sometimes useful when working with SQL to grab random rowsets, for debugging stored procedures or simply grabbing a random ad for an ad rotator. Either way, different databases use different functions to select this randomization.

• Select a random row with MySQL:

  SELECT * FROM mytable
  ORDER BY RAND()
  LIMIT 1;
  

• Select a random row with PostgreSQL:

  SELECT * FROM mytable
  ORDER BY RANDOM()
  LIMIT 1;
  

• Select a random row with Microsoft SQL Server:

  SELECT TOP 1 column FROM table
  ORDER BY NEWID();
  

Deleting all the rows from the table will leave you adding rows where the last one left off. To bring it back to the beginning, run truncate tablename.

You can use RETURNS SETOF record in your plpgsql functions to return a set of rows. Here is an example of using pgsql to build a query using a table name parameter:

CREATE OR REPLACE FUNCTION test(text) RETURNS SETOF record AS '
DECLARE
  _table ALIAS FOR $1;
  _mycursor refcursor;
  _row record;
BEGIN
  OPEN _mycursor FOR EXECUTE ''SELECT * FROM '' || _table;
  FETCH _mycursor INTO _row;
  WHILE FOUND LOOP
    RETURN NEXT _row;
    FETCH _mycursor INTO _row;
  END LOOP;
  RETURN;
END
' LANGUAGE plpgsql;

Here is an example of calling such a function (the names and types of the result columns are required when returning SETOF record):

mydb=> SELECT * FROM test('mytable') AS (col1 integer, col2 text, col3 date);

Select a random row from a table with the following query:

SELECT * FROM table ORDER BY random() LIMIT 1;

Use the following query to show all tables without primary keys:

SELECT
  nspname AS schema,
  relname AS table
FROM pg_class
  LEFT JOIN pg_constraint ON
    pg_constraint.contype = 'p' AND
    conrelid = pg_class.oid
  JOIN pg_namespace ON
    pg_namespace.oid = pg_class.relnamespace
WHERE
  relkind = 'r' AND contype IS NULL;

Use these queries to change the structure of a DOMAIN constraint:

ALTER DOMAIN foo DROP CONSTRAINT bar;
ALTER DOMAIN foo ADD CONSTRAINT bar CHECK (VALUE = 'baz'::text);

Given a table MYTABLE in namespace MYNAMESPACE, you can use this SQL to retrieve the primary key column name(s) for the table:

SELECT
  attname::text
FROM
  pg_attribute
JOIN
  pg_class ON pg_attribute.attrelid = pg_class.oid
JOIN
  pg_namespace ON pg_namespace.oid = pg_class.relnamespace
LEFT JOIN
  pg_constraint ON conrelid = pg_class.oid AND pg_constraint.contype = 'p'
WHERE
  pg_namespace.nspname = 'MYNAMESPACE' AND
  pg_class.relname = 'MYTABLE' AND
  pg_attribute.attnum = ANY (pg_constraint.conkey)
ORDER BY
  pg_attribute.attnum;

If you have a database mytemplate that you'd like to use as a template, update its datistemplate attribute in the pg_database relation:

UPDATE pg_database SET datistemplate = 't' WHERE datname = 'mytemplate';

Then you can use it as a template when creating other databases:

CREATE DATABASE otherdb TEMPLATE mytemplate;