Tag Info

Hot answers tagged

246

Please note the following commands: \list lists all databases \dt lists 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). See the manual about psql.


61

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;


51

Just turn on timing by entering: \timing


50

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


50

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


39

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.


39

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


37

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 ~~ 'B%' OR name ~~ '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 ~ ...


32

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


30

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


28

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


28

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: event=# SELECT version(); version ------------------------------------------------------------------------------------------- ...


28

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. Additional notes No use for mixed case identifiers ...


27

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


26

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


25

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


23

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


22

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


22

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


21

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


20

If you create a column as serial PostgreSQL automatically creates a sequence for that. The name of the sequence is autogenerated and is always tablename_columnname_seq, in your case the sequence will be names names_id_seq. After inserting into the table, you can call currval() with that sequence name: postgres=> CREATE TABLE names in schema_name (id ...


20

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


20

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


19

For PostgreSQL, CPU power can be very relevant, especially if a fairly high percentage of the active working set of your data fits in RAM. Most of the databases I've worked with have had CPU power as the main bottleneck most of the time. (I just checked vmstat on a server hosting web sites with millions of hits per day hosting over 5TB of database space, ...


19

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


19

I found that I had an extremely similar problem, namely that postgres was opening a socket in /var/pgsql_socket_alt where none of my software expects to look, but the solution to my problem was not only a problem with my $PATH. I had to create the directory /var/pgsql_socket, chown it to myself, and set unix_socket_directory in postgresql.conf (located in ...


19

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.


18

It can be done, using the following template: CREATE TABLE tablename ( ... ); /* for direct invocation */ CREATE FUNCTION propagate_data(newrow tablename) RETURNS void LANGUAGE plpgsql AS $$ BEGIN INSERT INTO other_table VALUES (newrow.a, newrow.b, ...); END: $$; /* trigger function wrapper */ CREATE FUNCTION propagate_data_trg() RETURNS trigger ...


18

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


18

Getting all parents of a specified node: WITH RECURSIVE tree AS ( SELECT id, name, parent_id, 1 as level FROM the_table WHERE name = 'foo' UNION ALL SELECT p.id, p.name, p.parent_id, t.level + 1 FROM the_table p JOIN tree t ON t.parent_id = p.id ) SELECT * FROM tree ...



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