Regarding tables names, case, etc, the prevalent convention is: SQL keywords: UPPER CASE identifiers (names of databases, tables, columns, etc): lower_case_with_underscores For example: UPDATE my_table SET name = 5; This is not written in stone, but the bit about identifiers in lower case is highly recommended, IMO. Postgresql treats identifiers case insensitively when not quoted … Read more
(Updated – Thanks to the people who commented) Modern Versions of PostgreSQL Suppose you have a table named test1, to which you want to add an auto-incrementing, primary-key id (surrogate) column. The following command should be sufficient in recent versions of PostgreSQL: ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY; Older Versions of PostgreSQL … Read more
find the file pg_hba.conf – it may be located, for example in /etc/postgresql-9.1/pg_hba.conf. cd /etc/postgresql-9.1/ Back it up cp pg_hba.conf pg_hba.conf-backup place the following line (as either the first un-commented line, or as the only one): For all occurrence of below (local and host) , except replication section if you don’t have any it has … Read more
You want to use postgresql’s replace function: replace(string text, from text, to text) for instance : UPDATE <table> SET <field> = replace(<field>, ‘cat’, ‘dog’) Be aware, though, that this will be a string-to-string replacement, so ‘category’ will become ‘dogegory’. the regexp_replace function may help you define a stricter match pattern for what you want to … Read more
ALTER DATABASE name OWNER TO new_owner; See the Postgresql manual’s entry on this for more details.
You can prevent future connections: REVOKE CONNECT ON DATABASE thedb FROM public; (and possibly other users/roles; see \l+ in psql) You can then terminate all connections to this db except your own: SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid(); On older versions pid was called procpid so you’ll have … Read more
In PostgreSQL the system determines which table is meant by following a search path, which is a list of schemas to look in. The first matching table in the search path is taken to be the one wanted, otherwise, if there is no match a error is raised, even if matching table names exist in … Read more
No. To do it just once you would simply write the delete statement for the table you want to cascade. DELETE FROM some_child_table WHERE some_fk_field IN (SELECT some_id FROM some_Table); DELETE FROM some_table;
What I did is first check what are the running processes by SELECT * FROM pg_stat_activity WHERE state=”active”; Find the process you want to kill, then type: SELECT pg_cancel_backend(<pid of the process>) This basically “starts” a request to terminate gracefully, which may be satisfied after some time, though the query comes back immediately. If the … Read more
The differences are covered at the PostgreSQL documentation for date/time types. Yes, the treatment of TIME or TIMESTAMP differs between one WITH TIME ZONE or WITHOUT TIME ZONE. It doesn’t affect how the values are stored; it affects how they are interpreted. The effects of time zones on these data types is covered specifically in … Read more