Hot answers tagged

602

Please note the following commands: \list or \l: list all databases \dt: list all tables in the current database You will never see tables in other databases, these tables aren't visible. You have to connect to the correct database to see its tables (and other objects). To switch databases: \connect database_name See the manual about psql.


128

This lists databases: SELECT datname FROM pg_database WHERE datistemplate = false; This lists tables in the current database SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;


117

Just turn on timing by entering: \timing


83

Column order does matter so if (and only if) the column orders match you can for example: insert into items_ver select * from items where item_id=2; Or if they don't match you could for example: insert into items_ver(item_id, item_group, name) select * from items where item_id=2; but relying on column order is a bug waiting to happen (it can change, as ...


81

In addition to the command line \d+ <table_name> you already found, you could also use the Information Schema to look up the column data, using information_schema.columns: SELECT * FROM information_schema.columns WHERE table_schema = 'your_schema' AND table_name = 'your_table' Note: As per the example above, make sure the values are enclosed ...


71

Your query is pretty much the optimum. Syntax won't get much shorter, query won't get much faster: SELECT name FROM spelers WHERE name LIKE 'B%' OR name LIKE 'D%' ORDER BY 1; If you really want to shorten the syntax, use a regular expression with branches: ... WHERE name ~ '^(B|D).*' Or slightly faster, with a character class: ... WHERE name ~ ...


67

It's done with ALTER USER username CREATEDB; See ALTER USER in the doc. To drop a database, either you're superuser (which can be granted with ALTER USER too) or you must own the database.


66

If you can connect to the database with superuser access, then SHOW data_directory; is the shortest way. If the server is not running and you forgot where the data directory was, then you really have to guess yourself. An operating system specific convention or the shell history might provide hints.


65

To lists all schemas, use the (ANSI) standard INFORMATION_SCHEMA select schema_name from information_schema.schemata More details in the manual: http://www.postgresql.org/docs/current/static/information-schema.html alternatively: select nspname from pg_catalog.pg_namespace; More details about pg_catalog in the manual: ...


65

When using the psql command line, you may list all schema with command \dn.


61

No, stored procedures do not prevent SQL injection. Here's an actual example (from an in-house app someone created where I work) of a stored procedure that unfortunately permits SQL injection: CREATE PROCEDURE [dbo].[sp_colunmName2] @columnName as nvarchar(30), @type as nvarchar(30), @searchText as nvarchar(30) AS BEGIN ...


52

Index names in PostgreSQL Index names are unique across a single database schema. Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema. Two tables in the same schema cannot have an index of the same name. (Follows logically.) If you do not care about the name ...


52

First, you have to be able to connect to the database in order to run queries. This can be achieved by REVOKE CONNECT ON DATABASE your_database FROM PUBLIC; GRANT CONNECT ON DATABASE database_name TO user_name; The REVOKE is necessary because The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that ...


50

You can't drop postgres database while clients are connected to it. Quite robust way to work around it, is Make sure noone can connect to this database update pg_database set datallowconn = 'false' where datname = 'mydb'; Force disconnection of all clients connected to this database. For postgres < 9.2: SELECT pg_terminate_backend(procpid) FROM ...


47

\l is also shorthand for \list. There are quite a few slash commands, which you can list in psql by using \?.


47

You can do that in pure SQL. Create a partial unique index in addition to the one you have: CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL; This way you can have (1, 2, 1) and (1, 2, 2) and (1, 2, NULL) for (a, b, c) in your table, but none of these a second time. Or use two partial indexes instead of one complete unique ...


46

In Postgresql these terminal commands list the databases available el@defiant$ /bin/psql -h localhost --username=pgadmin --list Or the command stated more simply: psql -U pgadmin -l Those commands print this on the terminal: List of databases Name | Owner | Encoding | Collate | Ctype | Access ...


43

SQL-Injection attacks are those where untrusted input is directly appended queries, allowing the user to effectively execute arbitrary code, as illustrated in this canonical XKCD comic. Thus, we get the situation: userInput = getFromHTML # "Robert ') Drop table students; --" Query = "Select * from students where studentName = " + userInput Stored ...


41

Q2: way to measure page size PostgreSQL provides a number of Database Object Size Functions, you can use. I packed the most interesting ones in this query and added some Statistics Access Functions. This is going to demonstrate that the various methods to measure the "size of a row" can lead to very different results. It all depends what you want to ...


41

You definitely have to script this via MySQL Stored Procedure Language Here is a Stored Function called GetParentIDByIDto Retrieve a ParentID given an ID to Search For DELIMITER $$ DROP FUNCTION IF EXISTS `junk`.`GetParentIDByID` $$ CREATE FUNCTION `junk`.`GetParentIDByID` (GivenID INT) RETURNS INT DETERMINISTIC BEGIN DECLARE rv INT; SELECT ...


41

Here are the results of querying a table on the second column of a multicolumn index. The effects are easy to reproduce for anybody. Just try it at home. I tested with PostgreSQL 9.0.5 on Debian using a medium sized table of a real-life database with 23322 rows. It implements the n:m relationship between the tables adr (address) and att (attribute), but ...


40

The information schema is the slow and sure way: it is standardized and largely portable to other databases that support it. And it will keep working across major versions. However, views in the information schema often join in many tables from the system catalogs to meet a strictly standardized format - many of which are just dead freight most of the time. ...


39

It certainly is. We discussed that in great detail under this related question. It also depends on the data types of the indexed columns. An index on, say, two integer columns is exactly as big as an index on one. Space is typically allocated in chunks of 8 bytes. For one integer (4 bytes) that's another 4 bytes of padding. The keywords here are data ...


38

Getting the Unix timestamp from a postgres timestamp with time zone like now() is simple, as you say, just: select extract(epoch from now()); Things only get complicated when you have timestamp without time zone field (or just timestamp which is the same thing). When you put timestamp with time zone data like now() into that field, it will be converted to ...


36

To elaborate on @alci's answer: PostgreSQL doesn't care what order you write things in PostgreSQL doesn't care at all about the order of entries in a WHERE clause, and chooses indexes and execution order based on cost and selectivity estimation alone. The order in which joins are written is also ignored up to the configured join_collapse_limit; if there ...


35

Without concurrency Use a subquery in the FROM clause of the UPDATE: UPDATE server_info s SET status = 'active' FROM ( SELECT server_ip -- your pk column or any (set of) unique column(s) FROM server_info WHERE status = 'standby' LIMIT 1 -- arbitrary pick (cheapest) ) sub WHERE s.server_ip = sub.server_ip ...


35

Solution for Postgres 9.1 CREATE INDEX idx_time_limits_inversed ON time_limits (id_phi, start_date_time, end_date_time DESC); In most cases the sort order of an index is hardly relevant. Postgres can scan backwards practically as fast. But for range queries on multiple columns it can make a huge difference. I wrote more in this closely related answer on ...


32

Timing can be turned on with \timing at the psql prompt (as Caleb already said). If you are on 8.4 or above, you can add an optional on/off argument to \timing, which can be helpful if you want to be able to set timing on in .psqlrc - you can then set \timing on explicitly in a script where plain \timing would otherwise toggle it off


32

You have three choices regarding the password prompt: set the PGPASSWORD environment variable. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-envars.html use a .pgpass file to store the password. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-pgpass.html use "trust authentication" for that ...


31

Such a feature does not exist in neither Postgres nor the SQL Standard (AFAIK). I think this is a quite interessting question so I googled a little bit and came accross a interessting article on postgresonline.com. They show an approch that selects the columns directly from the schema: SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || ...



Only top voted, non community-wiki answers of a minimum length are eligible