Search Results
| Search type | Search syntax |
|---|---|
| Tags | [tag] |
| Exact | "words here" |
| Author |
user:1234 user:me (yours) |
| Score |
score:3 (3+) score:0 (none) |
| Answers |
answers:3 (3+) answers:0 (none) isaccepted:yes hasaccepted:no inquestion:1234 |
| Views | views:250 |
| Code | code:"if (foo != bar)" |
| Sections |
title:apples body:"apples oranges" |
| URL | url:"*.example.com" |
| Saves | in:saves |
| Status |
closed:yes duplicate:no migrated:no wiki:no |
| Types |
is:question is:answer |
| Exclude |
-[tag] -apples |
| For more details on advanced search visit our help page | |
Results tagged with ddl
Search options answers only
not deleted
user 3684
Data Definition Language; defining database structures but not their contents.
0
votes
How can I add a table column at the first position?
New columns are added at the end of the column list with ALTER TABLE. You need to recreate the table to get the desired order of columns.
Or UPDATE the existing column in place, which is slghtly trick …
- 182k
69
votes
Accepted
Best way to populate a new column in a large table?
It very much depends on details of your setup and requirements.
Note that since Postgres 11, only adding a column with a volatile DEFAULT still triggers a table rewrite. Unfortunately, this is your c …
- 182k
1
vote
Is this an example of an Enum (Enumerated Type)
You can find out for sure with this query to the system catalog table pg_type:
SELECT typtype
FROM pg_type
WHERE oid = 'finance.fund_part_status'::regtype
I quote the manual about pg_type:
t …
- 182k
3
votes
Accepted
Making a domain conditional on other values in the table
I wouldn't try to solve that with a DOMAIN since that cannot reference other columns. Use a CHECK constraint instead:
ALTER TABLE foo ADD CONSTRAINT foo_min_salary
CHECK (salary > 1000 OR hired < '20 …
- 182k
2
votes
SELECT INTO with regexp_replace() doesn't write changes into newly generated table
Your regexp_replace() statement is invalid. It would work like this:
SELECT regexp_replace(tbl, '('''')$', '' , 'g')
FROM (
VALUES
('6''''')
,('6''''')
,('6''''')
,('20''''')
,('12''''')
,('1 …
- 182k
4
votes
Accepted
Convert varchar column to inet
You can do it all in a single DDL command to minimize friction with concurrent transactions (if that should matter):
ALTER TABLE request_logs
ALTER COLUMN sign_up_ip DROP default
, ALTER COLUMN sign_up_ip …
- 182k
2
votes
Accepted
Default values in SQLite3
SQLite only implements a very limited subset of ALTER TABLE statements. You'll have to drop and recreate the table, preferably in one transaction:
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE tbl_tmp(co …
- 182k
6
votes
Accepted
Create a function from another function
Yes, totally possible. You just have some random syntax errors.
And I took the freedom to replace the term "stored procedure" in your query with "function", since Postgres does not have stored proced …
- 182k
6
votes
Accepted
Drop primary key without dropping an index
I don't think there is a supported way to do this. Consider the comments so far.
If you are concerned about effects on concurrent transactions, there is a code example in the manual:
To recreate …
- 182k
2
votes
Accepted
Retrieve Names of Check Constraints Specific to a Column
how can I select all of the check constraints associated with that column?
pg_constraint.conkey is an array column, so we join on a.attnum = ANY(c.conkey) and the oid of the relation additionally. L …
- 182k
15
votes
Accepted
Use composite type to create new table
You can use a typed table:
CREATE TYPE mytype AS (some_id int, some_col text);
CREATE TABLE example OF mytype (PRIMARY KEY (some_id));
I added a PK constraint (which you did not ask for).
The syntax …
- 182k
7
votes
Accepted
Basic transactional DDL script in PostgreSQL
BEGIN;
CREATE TABLE IF NOT EXISTS blah ( ... );
-- much more DDL
COMMIT;
Before Postgres 11, one could not start, commit or roll back transactions inside PL/pgSQL code blocks at all, which is always run …
- 182k
11
votes
How to safely replace the old table with its cleaned copy?
By default, the error message tells you more. Like:
ERROR: cannot drop table tbl because other objects depend on it
SQL state: 2BP01
Detail: view v_tbl depends on table tbl
Hint: Use DROP ... CASCADE …
- 182k
3
votes
Is it possible to add a GENERATED COLUMN to a large table without locking/downtime on postgres?
Not possible with the currently implemented STORED generated columns. It would probably work for VIRTUAL generated columns, which are not implemented, yet (as of pg 15).
Depending on your undisclosed …
- 182k
1
vote
Create table that has a sequence column counting from 1 for every different foreign key
A serial (or IDENTITY) column is not the right tool for your purpose. Those are meant to handle concurrent write access gracefully, and expressly not to produce a gapless sequence of row numbers, much …
- 182k