Tag Info

Hot answers tagged

7

First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. Your life gets simpler if you just live with it. To get gap-less numbers, the (often better) alternative is to use a VIEW with row_number(). Example in this related answer: Gap-less sequence where multiple transactions with multiple tables are involved Here ...


5

Since 9.3, you can do this using \gset in psqlrc: select split_part(version(),' ',2) pmt \gset \set PROMPT1 '%:pmt:' Or, as 'the value of the selected prompt variable is printed literally, except where a percent sign (%) is encountered': select split_part(version(),' ',2) "PROMPT1" \gset If your version is <9.3, it's still possible, but much uglier, ...


5

If you can add a column to the table, the following scheme would almost1 work: CREATE TABLE emails ( UserID integer NOT NULL, EmailAddress varchar(254) NOT NULL, IsActive boolean NOT NULL, -- New column ActiveAddress varchar(254) NOT NULL, -- Obvious PK CONSTRAINT PK_emails_UserID_EmailAddress PRIMARY KEY (UserID, ...


4

psycopg2 is a wrapper around libpq, written in C, to expose a Python DB-API compatible API to Python programs. It implements Python objects in C that call libpq functions. It has a thin Python module wrapper around it to load it and provide some of the interface functionality that's easier to write in pure Python. Anything that implements the DB-API to ...


4

The only way to do either of these without schema changes is with a PL/PgSQL trigger. For the "exactly one" case, you can make the references mutual, with one being DEFERRABLE INITIALLY DEFERRED. So A.b_id (FK) references B.b_id (PK) and B.a_id (FK) references A.a_id (PK). Many ORMs etc cannot cope with deferrable constraints though. So in this case you'd ...


3

Following @ypercube 's (justified) critique of my first pass at an answer, this is the (far more thorough) second go! To give you a flavour of JOINs and SQL, I created two tables - Customer and Cust_Order as shown. Note, the Cust_Order table now has a DATETIME (MySQL) or TIMESTAMP (PostgreSQL) column. I then loaded these tables with data (see end of post). ...


3

You don't need triggers or PL/pgSQL at all. You don't even need DEFERRABLE constraints. And you don't need to store any information redundantly. Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the "chicken/egg" problem of inserting a user and his active email, ...


3

This would seem rather simple: INSERT INTO user_flags (user_id, flag) SELECT u.id, 'myflag' -- or whatever to write to this field FROM users u LEFT JOIN user_flags uf ON uf.user_id = u.id WHERE uf.user_id IS NULL; -- to exclude already existing How to .. Select rows which are not present in other table While this is susceptible to race ...


2

Yes, they are. shared_buffers is block-oriented, and stored in the same format as on-disk. This is generally good for performance, since the decompression is very fast, and the compression means that more fits in shared_buffers. The only case it might hurt is if you have enough storage to fit the whole dataset in shared_buffers uncompressed too.


2

Well, apparently this is it: SELECT *, SUM(amount) OVER ( ORDER BY tx_date DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS running_balance FROM transactions ORDER BY tx_date DESC; Output: id | memo | amount | tx_date | running_balance ----+-----------------+--------+------------+----------------- ...


2

You're restoring with pg_restore --format=c ... but the pg_dump was not done with --format=c, it was done with the default, plain format. From pg_dump manpage: -F format, --format=format Selects the format of the output. format can be one of the following: p, plain Output a plain-text SQL script file (the default). ...


2

I don't think there's a fundamental limitation there, and it's something I'd like to see addressed. I agree with you that it's a bit ugly. That said, the convention to just use the postgres database as a maintenance database is so strong that the real world impact is negligible. PostgreSQL has shared catalogs, things like pg_authid and pg_database. It's ...


2

Maybe this is just over-engineered. Have you actually tried using a single full index? Partial indices covering the whole table together do not provide much gain, if any, for index lookups, and from your text I infer that you have indices for all run_ids? There may be some advantages to index scans with partial indices, still I would benchmark the simple ...


2

You can avoid various complications by passing values as values with the USING clause: CREATE OR REPLACE FUNCTION foo(linktable regclass, inttable regclass, verttable regclass) RETURNS void AS $func$ BEGIN EXECUTE format( 'SELECT tdgSetTurnInfo($1, $2, $3, array_agg(t.id)) FROM %s t' ...


2

The actual syntax corresponding to the imaginary SELECT columnname FROM %currenttable% would be, in plpgsql: execute format('SELECT columnname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME); The TG_* built-in variables are documented in Trigger Procedures and the execute and format plpgsql constructs in Basic Statements. The query above is ...


2

I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables TG_TABLE_SCHEMA and TG_TABLE_NAME, or use TG_RELID. These, alongside EXECUTE for dynamic SQL, let you do what you want: BEGIN EXECUTE format('SELECT colname FROM %I', TG_RELID) END; or BEGIN EXECUTE format('SELECT colname FROM %I.%I', ...


1

You can use a CTE to supply the values: with data (street, city, user_id) as ( values ('street1','LA', 2) ) insert into addresses(street, city, user_id) select * from data where exists (select * from users where users.id = data.user_id and users.users.storeaddress = true); Or alternatively a derived table: ...


1

The best way I think would be to use the LAG() or LEAD() functions: SELECT *, - 100.0 * (1 - LEAD(Price) OVER (ORDER BY t.Id) / Price) AS Grown FROM table_name AS t ORDER BY t.Id ; With LEAD(Price) OVER (ORDER BY t.Id) you have access to the next Price when the rows are ordered by Id. It's not clear what the order should be. Based on the data in ...


1

Use pg_restore to list its contents, dump it to SQL, or restore it to a database.


1

Assuming that, for the same trigger invocation, you take all the values from the same row in the table firing your trigger, your trigger function could look like this: CREATE OR REPLACE FUNCTION trfn_tbl_log_any() RETURNS trigger AS $func$ DECLARE _ct int; BEGIN IF NEW.timetype = 'start' THEN EXECUTE format($$ SELECT floor(t.timeidx) + 1 ...


1

Or you can use TG_RELID, but since its data type is plain oid, not regclass, one must cast it to regclass explicitly to get the auto-conversion to a schema-qualified (only if the current search_path requires it), cleanly escaped table name. The documentation: TG_RELID Data type oid; the object ID of the table that caused the trigger invocation. ...


1

work_mem Obviously, the sort operation spills to disk: Sort Method: external merge Disk: 36224kB More work_mem can help the query, like @Kassandry already suggested. Increase the setting until you see Memory instead of Disk in the EXPLAIN output. But it's probably a bad idea to increase the general setting based on one query. Proper setting depends on ...


1

One of the easiest and most effective ways to increase performance on this sort of query is to execute SET work_mem=40MB (because you have ~32MB of temp file for sorting, and a little extra often helps) then run your query, and see if the EXPLAIN ANALYZE plan changes from disk to an in-memory sort. Afterwards, run RESET work_mem to put the value back to the ...


1

Depending on how selective the combined predicates are, I would imagine a good index for this particular query would be: CREATE INDEX index_name ON products (above_revenue_average ASC, start_date DESC) WHERE status > 100 AND category_id <> 5; The SELECT * is potentially problematic because the index above does not contain all columns. ...


1

You can't download a snapshot from RDS you have to use a tool like pg_dump This has already been answered multiple times on regular Stack Overflow: http://stackoverflow.com/questions/14916899/download-rds-snapshot


1

You could always double check your problem by issuing a command such as /usr/bin/pgsql/bin/createdb test You can frequently get better/clearer error messages and/or other information from the command line than from GUI tools such as pgAdmin III. But, in order to change a system parameter, you have to reload the postgresql.conf file after making changes. ...


1

Working with this setup: CREATE TABLE tbl ( tbl_id serial PRIMARY KEY , user_id text NOT NULL , start_time timestamp NOT NULL -- if values could be NULL ... , end_time timestamp NOT NULL -- ... it would be more complicated ); Total duration without redundancy Your numbers indicate you just want the total duration without counting ...


1

While FOREACH is very convenient to loop through a single array, it's not particularly useful to step through multiple arrays in parallel. Use a plain FOR loop with array_lower() / array_upper() instead: FOR i IN 1 .. array_upper(UserResponseList, 1) LOOP RAISE NOTICE '%, %, %', QuestionList[i], UserResponseID_List[i], UserResponseList[i]; END LOOP; ...


1

Given this table: CREATE TABLE usr ( usr_id serial PRIMARY KEY , usr text NOT NULL , tz text -- time zone names ); Use the AT TIME ZONE construct: SELECT *, (now() AT TIME ZONE tz)::time AS local_time FROM usr WHERE (now() AT TIME ZONE tz)::time BETWEEN '13:00'::time AND '23:00'::time; Including upper and lower bounds 1 PM and 11 PM. ...



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