Tag Info

Hot answers tagged

467

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.


104

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;


97

Just turn on timing by entering: \timing


70

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 ...


63

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 ...


60

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 ~ ...


59

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 ...


58

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.


47

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


46

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: ...


45

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.


41

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


41

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 ...


41

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 ...


39

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 ...


39

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 ...


38

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 ...


35

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 ...


35

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 ...


34

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 ...


32

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 ...


32

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. ...


32

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 measure exactly. Replace schema.tbl ...


32

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 ...


30

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 ...


29

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


28

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' || '.' || ...


27

Production ready? Yes, it's production-ready and widely used. Heroku followers are based on PostgreSQL's built-in async replication for example, as are AWS RDS standbys and read replicas. Streaming replication is used almost universally with PostgreSQL. Setup of replication isn't exactly lovely, but tools like repmgr help somewhat with that, and it's ...


26

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 ...


25

Yes, to some extend. Stored Procedures alone will not prevent SQL Injection. Let me first quote about SQL Injection from OWASP A SQL injection attack consists of insertion or "injection" of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data ...



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