Add new column in PostgreSQL with a subquery and set a NOT NULL constraint

100%

Add new column in PostgreSQL with a subquery and set a NOT NULL constraint

✓ Published0🌍 Public

Last edited Feb 28, 2019

Created on Feb 28, 2019

This example shows how to add a new column to an existing PostgreSQL table and populate it with values from another table using a subquery in an UPDATE statement. The SQL script begins by adding the `exp_id` column as type `text`, then updates it by joining `table1` with `table2` on the `gid` field to copy matching `exp_id` values. Finally, it alters the column to set a `NOT NULL` constraint and wraps all operations in a single transaction with `BEGIN` and `COMMIT` to ensure atomicity. The code relies on standard PostgreSQL DDL and DML commands without external APIs.

AI-generated description

Similar vizzes

Loading thumbnail…

copy to temp table method for updating addressbase

This example demonstrates a PostgreSQL method for efficiently replacing the contents of a large AddressBase database table by loading incoming CSV data into temporary tables, building indexes and constraints there, and then using a transactional rename swap to minimize downtime. The script first copies data from `addressbase_cleaned.csv` and `uprn-to-councils.csv` into empty temp tables, then adds primary keys, GiST spatial indexes on `location`, and B-tree indexes on `postcode` and `uprn`. Within a `BEGIN`/`COMMIT` block, it drops foreign keys, renames the existing tables to `_old`, promotes the temp tables to the main names, and re-adds the foreign key constraints. Timings for each operation illustrate the relative cost of loading, indexing, and swapping.

GGeoWill

49% match

Loading thumbnail…

Gist 5567358

This visualization demonstrates how SQL handles null values in comparison and assignment operations. It shows the distinct results of various null-checking predicates—including `<=>`, `IS NULL`, `IS NOT NULL`, and `ISNULL`—combined with `IF` logic and the `IFNULL` function. The query executes against a single-row dataset containing the value `1`, then uses `GROUP BY ... WITH ROLLUP` to produce aggregated output, highlighting differences between MySQL’s null-safe equality operator and standard null checks. The gallery example relies on standard SQL execution, with no additional charting or graphics libraries involved.

HHoffmannP

45% match

Loading thumbnail…

Gist 66b1a9a31dce76682837c3fd4d9a468b

This example demonstrates how to query JSONB data directly in PostgreSQL and flatten it into a relational result set. The code creates a table with a JSONB column, inserts two sample rows, and then uses two different query approaches: the `->>` operator to extract scalar fields as text, and the `jsonb_to_record` function with a column definition list to cast JSON values into typed columns (text and integer). The example highlights both methods for accessing nested JSON data, showing the explicit extraction syntax versus the more structured record-expansion technique, all within a single SQL file.

JJonasHeylen

44% match

Loading thumbnail…

PostgreSQL command line cheatsheet

This example presents a text-based cheat sheet for PostgreSQL command-line tools, rendering a curated list of psql commands, configuration snippets, and handy SQL queries in a plain markdown document. It shows the core syntax for database management tasks, such as connecting to databases, listing tables and views, managing user roles, and adjusting server logging verbosity, alongside query examples for inspecting indexes, active sessions, and table sizes. The visualization relies on static code blocks and inline formatting, with no interactive graphics or JavaScript libraries; instead, it leverages the raw text content from a gist, emphasizing practical API references like `psql` meta-commands and `pg_stat_activity` queries.

AAngieLiang

44% match

Loading thumbnail…

Gist 1e418bc5c048fed5ef2604a1902eb735

This example demonstrates a spatial correction of a U.S. District Court jurisdiction boundary using PostGIS. It shows how an erroneous polygon (fid 57) is split into two parts, with one part merged into a neighboring jurisdiction (fid 58) and the other retained in place. The SQL uses `st_dump` to extract the polygon components, then `st_union` and `st_multi` to recompute the merged and retained geometries. The `UPDATE` statement with a `CASE` expression applies the corrected shapes back to the table, relying on PostGIS geometry functions for the operation.

CCaged

43% match

Loading thumbnail…

PostgreSQL installation tutorial

This example presents a step-by-step guide for installing PostgreSQL across macOS, Linux, and Windows. It shows the terminal command `psql -U postgres` as the target for verifying a successful setup, then details OS-specific actions, such as using the Postgres.app on macOS, compiling from source via `wget` on Linux, and using the EnterpriseDB installer on Windows. The tutorial also covers checking the `$PATH` variable and verifying the server port with `lsof`.

115Dkatz

42% match

Loading thumbnail…

Gist be32a2ac117700d3dbde6b0824367ba5

This example demonstrates how to create a simple database table visualization from a SQL schema definition. The code defines a `Demo` table with three columns—`ID`, `Key`, and `Value`—and shows how to map those column types and constraints into a visual entity-relationship style diagram. Using the provided `CREATE TABLE` statement as the data source, the visualization renders each column as a node, with the primary key and nullable attributes indicated through distinct visual encodings. The implementation leverages `d3.forceSimulation` for the node layout and `d3.geoPath` for any geometric rendering, while relying on standard DOM elements for interactivity. No additional behavior beyond the table’s structural representation is implied.

HHugoberry

41% match

Loading thumbnail…

Gist faec7eb6c03589ff608bab991699407b

This example demonstrates how to generate and execute a dynamic SQL script in Power Query (M language) to bulk-insert data into a SQL Server table using a table-valued parameter (TVP). It builds a textual TVP declaration and populates it by chunking source rows into sets of 1,000 entries, then constructs a multi-statement script that inserts each chunk and calls a stored procedure. The code relies on M functions like `Table.TransformRows`, `List.Generate`, `Text.Combine`, and `Value.NativeQuery` to programmatically assemble and run the query. The visualization highlights the data flow from a source table to a SQL stored procedure, emphasizing the chunking logic and the use of native query execution.

HHugoberry

41% match