There’s no doubt that postgresql mailing-list archives at http://archives.postgresql.org serves as a main resources to learn the best of postgresql tips and tricks. However, it’s mean digging into the large amount of archive every time you want to find something. Hence, this page is a collection of what I found on the archives.
* From: Joe Conway <mail ( at ) joeconway ( dot ) com>
* To: Kevin Coyner <kevin ( at ) rustybear ( dot ) com>
* Subject: Re: drop table if exists
* Date: Fri, 14 Mar 2003 09:46:26 -0800
Kevin Coyner wrote:
Is there an equivalent to "IF EXISTS" in Postgres?
No, there is no direct equivalent (and I have often wished there was -- but not enough to try to implement it, at least not yet).
You can fake it with a plpgsql function (very lightly tested):
CREATE OR REPLACE FUNCTION drop_table_if_exists(text, bool) RETURNS bool AS '
DECLARE
opt text;
rec record;
BEGIN
IF $2 THEN
opt := '' CASCADE'';
ELSE
opt := '''';
END IF;
SELECT INTO rec oid FROM pg_class WHERE relname = $1::name;
IF FOUND THEN
EXECUTE ''DROP TABLE '' || $1 || opt;
RETURN true;
END IF;
RETURN false;
END;
' LANGUAGE 'plpgsql';
regression=# SELECT drop_table_if_exists('foo', false);
drop_table_if_exists
----------------------
t
(1 row)
regression=# SELECT drop_table_if_exists('foo', false);
drop_table_if_exists
----------------------
f
(1 row)
HTH,
Joe
A SERIAL column in a table is implemented using a sequence. It is stored as an integer or bigint. A corresponding SEQUENCE is created with the name ‘tablename_columnname_seq’ and the default value of the SERIAL column is set to the result of the function nextval(’tablename_columnname_seq’). The documentation says:
CREATE TABLE tablename (
colname SERIAL
);
is equivalent to specifying:
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);
Suppose you have a table already defined that now requires a SERIAL column. You cannot (yet) use ALTER TABLE to add the SERIAL column because ALTER TABLE does not (yet) support the addition of columns and DEFAULT in the same statement. So you must set up the sequence and set the default yourself. And, if initial values are required, you must initialize the new SERIAL column of the table.
These are the steps you would use to add a SERIAL column to the table person_aliases. This example shows creating the column and corresponding sequence and then adding the default to the column.
ALTER TABLE person_aliases
ADD COLUMN alias_id integer;
CREATE SEQUENCE person_aliases_alias_id_seq;
ALTER TABLE person_aliases
ALTER alias_id
SET DEFAULT nextval('person_aliases_alias_id_seq'::text);
Then it goes further to initialize the value of the column for the entire table. Note that the initialization of the values is in no particular order. Finally, it adds the constraints NOT NULL and UNIQUE on the column. Since 7.3, SERIAL columns are not guaranteed to be UNIQUE and if they should be unique, you must explicitly state that.
UPDATE person_aliases SET alias_id = DEFAULT; ALTER TABLE person_aliases ALTER alias_id SET NOT NULL; ALTER TABLE person_aliases ADD CONSTRAINT person_aliases_ukey UNIQUE(alias_id);
Contributors: Andrew Dunston amdunstan at ncshp.org elein at varlena.com http://www.varlena.com/varlena/GeneralBits/72.php
Tom Lane
<tgl@sss.pgh.pa.us> to Daniel, pgsql-general
More options 5:26 am (6 hours ago)
"Daniel Serodio" <dserodio@gmail.com> writes:
> I've found a thread discussing the use of "text" vs. "varchar"; what
> I'd like to know if there's any performance difference between using
> "varchar(n)" vs. "varchar", ie, should I constrain a "name" column to
> an arbitrary length to improve performance, or will it actually degrade
> performance because of the extra cycles used for checking the length?
The latter.
The general rule of thumb is don't use varchar(n) (or even worse,
char(n)) unless you can point to a specific application requirement
for a maximum field width. In particular, if you find yourself
picking a value for N out of the air, you're doing the wrong thing.
regards, tom lane
