Hot answers tagged postgresql
49
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.
39
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
...
36
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 ...
24
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 ...
23
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.
21
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 ...
18
Your query is pretty much the optimum. Syntax won't get much shorter, query won't get much faster. I only reformat:
SELECT name
FROM spelers
WHERE name ~~ 'B%' OR name ~~ 'D%'
ORDER BY 1;
If you really want to shorten the syntax a bit, use a regular expression with branches:
...
WHERE name ~ '^(B|D).*'
Or slightly faster, yet, with a character ...
16
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' || '.' || ...
16
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, ...
15
All of the platforms you have mentioned can run close to zero data loss configurations. All of them could be deployed in a configuration that will fail.
Platform choice is one part of the puzzle. It will be your implementation of the platform that determines whether or not your requirements are met.
MySQL Cluster
Oracle RAC
PostgreSQL High Availability
...
14
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
...
14
There's nothing wrong with indexing a varchar column if you're going to be doing queries based on it. However please keep in mind that there a limits to some indexes and how much they can index in a single field. Example you can't index a column that can contain an unlimited amount of text. However you should be able to do an index on varchar(256) without ...
14
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
-------------------------------------------------------------------------------------------
...
14
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
...
14
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 ...
14
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 ...
14
Boolean logic - or Three valued logic
IN is shorthand for a series of OR conditions
x NOT IN (1, 2, NULL) is the same as NOT (x = 1 OR x = 2 OR x = NULL)
... is the same as x <> 1 AND x <> 2 AND x <> NULL
... is the same as true AND true AND unknown **
... = unknown **
... which is almost the same as false in this case as it will not pass ...
13
Strictly from a MySQL perspective, that's a very loaded question
CPU frequency relevant for a database server?
While faster CPU and motherboard are great, other bottlenecks can get in the way. Such bottlenecks include:
Disk I/O
Connection Maximums
Network Latency
Query Performance Per Connection
Every little advantage helps, but I have to say No ...
12
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 ...
12
In SQL Server, with a few assumptions like "those fields can't contain NULLs", those queries should give the almost the same plan.
But also consider the type of join you're doing. An IN clause like this is a Semi Join, not an Inner Join. An Inner Join can project onto multiple rows, thereby giving duplicates (compared to using IN or EXISTS). So you may want ...
12
Both excel at a simple task like this. If you end up having big queries where you search for entities that share many attributes ("relational division"), I would expect PostgreSQL at an advantage for its superior index handling.
In particular, multiple joins can be combined with bitmap index scans - a feature that is not present in MySQL. It has an ...
12
You may be able to achieve better performance by searching first in rows with higher frequencies. This can be achieved by 'granulating' the frequencies and then stepping through them procedurally, for example as follows:
--testbed and lexikon dummy data:
begin;
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set ...
12
The physical storage for rows is described in the docs in Database Page Layout. The column contents for the same row are all stored in the same disk page, with the notable exception of TOAST'ed contents (too large to fit in a page). Contents are extracted sequentially within each row, as explained:
To read the data you need to examine each attribute in ...
12
Enhancing Jack Douglas's answer to avoid the need for PL/PgSQL looping and bytea concatenation, you can use:
CREATE OR REPLACE FUNCTION random_bytea(bytea_length integer)
RETURNS bytea AS $body$
SELECT decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,''), 'hex')
FROM generate_series(1, $1);
$body$
LANGUAGE 'sql'
VOLATILE
...
12
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 ...
12
Production ready?
Yes, it's production-ready and widely used. Heroku followers are based on PostgreSQL's built-in async replication for example.
Setup of replication isn't exactly lovely, but tools like repmgr help somewhat with that, and it's improving slowly with each major release. The ability for pg_basebackup to take a copy of the system using ...
11
re 1) Yes and no.
For a query that uses both columns e.g. where (user_id1, user_id2) = (1,2) it doesn't matter which index is created.
For a query that has a condition on only one of the columns e.g. where user_id1 = 1 it does matter because usually only the "leading" columns can be used for a comparison by the optimizer. So where user_id1 = 1 would be ...
11
Schemas in postgres are used mostly for namespacing and sometimes for security.
Namespacing because two objects can have the same name in different schemas, and are then referenced by schema.object notation - especially useful in conjunction with search_path (many contrib modules do this, eg xml2). Security because you can now do grant ... on all tables in ...
11
There is a trick to building fast realtime analytics, regardless of the platform. I've done this with Microsoft Analysis Services, but you can use similar techniques with other platforms as well.
The trick is to have a leading partition that can be populated with near-realtime data and a historical partition (or partitions) that are optimised for fast ...
11
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
