Changes
Jump to navigation
Jump to search
no edit summary
Useful commands are:
\q Quits \i basics.sql Run script basics.sql \dt List tables \COPY Psql's version of copy
==SQL Commands==
There is a list of [http://www.postgresql.org/docs/7.3/static/sql-commands.html SQL commands] that may help.
Make/delete tables and functions with CREATE and DROP:
CREATE TABLE tablename AS
SELECT * FROM tablename WHERE date_prod >= '2002-01-01';
DROP TABLE tablename;
CREATE FUNCTION getreal (text) RETURNS real AS $$
if ($_[0]=~/^\d{1,}\.\d{0,}$/) { return $_[0]; }
return undef;
$$ LANGUAGE plperl;
DROP Function correctyear(int,int);
Populate data with COPY, INSERT and UPDATE:
INSERT INTO tablename VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
COPY tablename FROM '/home/user/weather.txt'; --http://www.postgresql.org/docs/8.4/interactive/sql-copy.html
UPDATE tablename SET kind = 'Dramatic' WHERE kind = 'Drama';
UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id = accounts.sales_id);
Retrieve results with SELECT:
SELECT x.a y.a FROM x,y WHERE x.a=y.a
SELECT * FROM x LEFT OUTER JOIN y ON x.a=y.a;
SELECT * FROM
(SELECT ) AS x
LEFT OUTER JOIN
(SELECT ) AS y
ON x.a=y.a;
SELECT COUNT (*) FROM ( ) AS Temp;
SELECT COUNT (*) FROM Acq;
SELECT x, min(y) FROM tablename GROUP BY x;
SELECT * FROM tablename LIMIT 10;
SELECT
CASE WHEN a > b THEN 1
ELSE 2
END As Colname,
FROM tablename;
Get information on a table:
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'tablename';
Change a table with ALTER:
ALTER TABLE tablename ADD COLUMN colname real;
ALTER TABLE tablename RENAME COLUMN product_no TO product_number;
Find out how a query will be executed with EXPLAIN (a Postgre command):
EXPLAIN ANALYZE SELECT * FROM x;
