Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Is it possible to reference an inserted column within the same INSERT?

tahishae

This query gives the error:

create table qwe (a bigint, completed bit varying(100000));
CREATE TABLE
insert into qwe (a, completed) values (1000, repeat('0', a));

ERROR:  column "a" does not exist

E
emran last edited by
You probably also saw the hint:

HINT: There is a column named "a" in table "qwe", but it cannot be referenced from this part of the query.

You cannot reference a column value that does not exist, yet.

Instead, spell out the value again. Or use a subselect or CTE to provide a only once:
```
INSERT INTO qwe (a, completed)
VALUES (1000, repeat('0', 1000)::varbit);
INSERT INTO qwe (a, completed)

SELECT a, repeat('0', a)::varbit

FROM  (SELECT 1000) sub(a);
```
db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=05d049a8a94de40483edb02ac4429706

Also, you need an explicit cast to varbit.
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

K

PostgreSQL: Create users that start with number
Software Programming • sql postgresql database • • kalyn

2

0
Votes

2
Posts

0
Views

A

You can create the user using quotes around the name.CREATE USER "12345" WITH ENCRYPTED PASSWORD 'Admin1234'
A

Google Managed PGSQL DB migration to DigitalOcean Managed PGSQL DB
SQL, Database Testing • postgresql migration pg restore google c languageloud sql • • Avante

2

0
Votes

2
Posts

0
Views

C

Install pg_dump and pg_restore or install entire PostgreSQL Binaries on your local machine or EC2, Droplet compute. Create a dump file using pg_dump that contains the data to be loaded from GCP PG: pg_dump -Fc -v --host= --username= --dbname= -f dbname.dump Restore the data into the target Database for PostgreSQL using pg_restore : After you've created the target database, you can use the pg_restore command and the -d, --dbname parameter to restore the data into the target database from the dump file. pg_restore -v --no-owner --host== --port= --username=< dbadmin > --dbname= dbname.dump --no-owner parameter causes all objects created during the restore to be owned by the user specified with --username. For more information , click on pg_restore.
Duplicate data in an SQL table by modifying one of the fields
Software Programming • php sql insert • • Mystic

2

0
Votes

2
Posts

0
Views

Well, you practically have the solution in the same question:INSERT INTO table SELECT NULL id, 'nuevo valor' campo1, campo2, campoN FROM table WHERE id = 2; Only by adding the new value as I have done in the example with campo1, it would already be inserted in that field nuevo valor remaining the rest of the fields with the value they had in the record id=2.It is important that in the field id introduce a NULL to generate the autonumeric without duplicates.
Why primary node disconnect when set automatic failover with PostgreSQL and Pacemaker?
SQL, Database Testing • postgresql high availability failover • • inna

2

0
Votes

2
Posts

0
Views

Whenever something goes wrong, you have to look at the logs. You will find warnings, error, fatal, and panic messages. You can find where your logs are in your postgresql.conf file. Look for the logging_collector setting, if it's on, you will find your server logs in the directory specified in the log_directory setting. If logging_collector is set to off, look at the log_destination setting. If it's syslog you need to look at your syslog settings to find where your logs are. If it's stderr you might find something under /proc//fd/2 where is the PID of your running PostgreSQL server. You might find this page of documentation usefull.
Mysql insert fixed column value for each row during multi-insert
SQL, Database Testing • mysql • • Laycee

2

0
Votes

2
Posts

0
Views

I am surely too late for you, but maybe somebody will have similar question in the future. A way how to accomplish that is by creating temporary table #temptable, and while copying data to actual table, add constant column: # en-US.sql INSERT INTO #temptable (resource_key, resource_value) VALUES ("GEN.YES", "Yes"), ("GEN.NO", "No"), ("GEN.MAYBE", "Maybe") ON DUPLICATE KEY UPDATE `resource_value` = VALUES(`resource_value`); INSERT INTO table (resource_key, resource_value, locale) VALUES (SELECT t.*, "en-US" FROM #temptable AS t);
S

Trigger After Insert is preventing insertion
SQL, Database Testing • sql server t sql trigger insert • • simrah

2

0
Votes

2
Posts

0
Views

The trigger is part of the original transaction. Just because it happens after the original insert does not mean that everything doesn't get rolled back in the event of an error. A rollback does not just roll back the last thing that ran. Take this example: BEGIN TRANSACTION; INSERT dbo.table1 ...; INSERT dbo.table2 ...; ROLLBACK TRANSACTION; Surely you don't expect the rollback to only affect the 2nd insert? You might consider using TRY/CATCH or an INSTEAD OF trigger, which will allow you to handle other stuff without suffering from the errors. Or simply checking first Your use of declaring a bunch of variables, assigning them from a join, and then inserting seems to be both inefficient and problematic. What happens if more than one row matches the join? You know that if you have a multi-row operation, the trigger only fires once, so you'll only end up with one arbitrary row being inserted, right? Why not: INSERT dbo.TBL_USER_LOGS(DEV_NID, LOG_DATE, USE_NID, LOG_MODE) SELECT DEV_NID, LOG_DATE, REL_ENROLLNUMBER, REL_MODE -- table prefixes? FROM dbo.TBL_DEVICE -- always use dbo prefix! INNER JOIN dbo.TBL_READER_LOG ON DEV_ORIGINAL_NID = RDR_NID -- which table do these columns come from? WHERE DEV_TYPE = 1; -- which table does this column come from? As an aside, I notice that your query doesn't seem to take the inserted pseudo-table into account. Shouldn't the insert have something to do with the row(s) actually affected by the trigger?
D

INSERT and UPDATE in Trigger
SQL, Database Testing • oracle select trigger update insert • • Duquan

2

0
Votes

2
Posts

0
Views

A

INSERT INTO table_2 will always insert and never update. If you need different operation depends on which operation(insert or update) actually fired the trigger, you need to check it with IF UPDATING THEN ... END IF; or (IF INSERTING THEN ... END IF; )
K

Select table with external key that can be NULL
Software Programming • sql postgresql select • • Kadyn

2

0
Votes

2
Posts

0
Views

J

SQL always tries for each record from the first table to pick up all the entries from the second table. Usually, we keep him from this desire by conferring two tables. That's when it works. OR readers.speciality_id IS NULL It appears that the condition is true for any record in the second table.For such cases, SQL serves LEFT JOINthat picks up records if they are and returns the record from the first table even if there's nothing in the second.SELECT readers.reader_id, readers.fio, reader_speciality.value FROM readers LEFT JOIN reader_speciality ON readers.speciality_id = reader_speciality.speciality_id
O

Order by one column if not null, otherwise by another column, columns are of not matching types
SQL, Database Testing • postgresql • • obi

2

0
Votes

2
Posts

1
Views

There are a few ways to do this, but essentially, you'll want to calculate a value to be the first sort column. Essentially computing a "sort" value that will figure out the "nearest" order_number for the rows that have a null order_number, then you can sort on that, and use additional columns in your ORDER BY to put the null values in the right order. In this example, I get the non-null order_number of the row that was created most immediately prior to the null row, and use that as the first sort column. This puts things in approximately the right order--then you can use additional ordering columns to control how the null order numbers show up in relation to the "closest order number" that was used for the initial sorting. WITH orders_cte AS ( SELECT *, (SELECT o2.order_number FROM orders AS o2 WHERE o2.created_at
K

Take average of multiple nullable columns
SQL, Database Testing • postgresql null aggregate postgresql 9.5 • • kalyn

2

0
Votes

2
Posts

0
Views

C

The only correct and scalable solution to that problem is to normalize your model. However, you can normalize the data "on the fly" and then use standard SQL aggregation on the result. select name, avg(t.val) from the_table cross join unnest (array["2010","2011","2012"]) with ordinality as t(val) group by name; Online example: https://rextester.com/TGTC30399 But I would strongly recommend to fix your data model by properly normalizing it.
Z

Convert a query results from lines to columns so i can see somes values from a column in columns
SQL, Database Testing • sql server t sql • • zymir

2

0
Votes

2
Posts

0
Views

C

For this example i am using this example query: SELECT * FROM example First you need to define a variable which will hold the results. Because all results will be shown in one column the datatype of this column should be varchar(x) where x is large enough to hold the data you want to show. Of course the number of columns should match the number of columns of your query. In this example i will be using SELECT a,b,c,d FROM example, so my variable will look like: DECLARE @a TABLE(a varchar(20),b varchar(20),c varchar(20),d varchar(20)); Next i will create some sample data: WITH example(a, b, c, d) as ( SELECT 'een', 1, 'one',1.1 union all SELECT 'twee', 2, 'two', 2.2 union all SELECT 'drie', 3, 'three', 3.3 ) select * into #tempTable from example If we select this: select x.name, --x.column_ordinal, CASE x.column_ordinal WHEN 1 THEN a.a WHEN 2 THEN CAST(a.b as varchar(80)) WHEN 3 THEN a.c WHEN 4 THEN CAST(a.d as varchar(80)) END as value from #tempTable as a CROSS APPLY ( SELECT name, column_ordinal FROM sys.dm_exec_describe_first_result_set ('SELECT * FROM #tempTable', NULL, 0) ) x ; The output looks like: name value ---------- ------------ a een b 1 c one d 1.1 a twee b 2 c two d 2.2 a drie b 3 c three d 3.3 Because my query returns 3 records with 4 fields, this query returns 12 records (4 sets of 3 records). see also DBFIDDLE
K

SQL How to copy text from one table (from one column) to another table (to one column), preferably with IF the target column is empty
SQL, Database Testing • mysql table insert c languageopy • • Kadyn

2

0
Votes

2
Posts

0
Views

D

Although previous answer was correct, and I am thankful to @McNets, I found an easier way to do it. UPDATE info i, articles a SET i.Description = a.Article WHERE i.Codename = a.Codename AND i.Description IS NULL;
R

Table design for ticket sales
SQL, Database Testing • postgresql database design • • Raziyah00

2

0
Votes

2
Posts

0
Views

C

I can see no strong reason to avoid having a Ticket table that stores each serial number on a separate row, assuming that makes most sense for the application that will use this database. You should still normalise the overall design to avoid repeated data in this table. "Thousands of rows" is pretty tiny for a relational database: you should easily be able to store all the tickets for thousands of events (~millions of rows) without any performance problems, provided you have clustered and indexed the table appropriately. This of course depends on your server hardware, and on how many events you expect to be servicing. I assume events are short lived, and the record of tickets sold would not usually be needed after the event has happened. If so, you could easily have a TicketHistory table, and archive off rows to there once the event is over so the main table remains small and performant.
T

MySql column value to indicate 2 possible options
SQL, Database Testing • mysql database design • • terrea

2

0
Votes

2
Posts

0
Views

C

You can use the MySQL ENUM type which is stored as a one-byte int internally. From the docs which only hint on implementation, Inserting 1 million rows into this table with a value of 'medium' would require 1 million bytes of storage, as opposed to 6 million bytes if you stored the actual string 'medium' in a VARCHAR column. Though it's documented elsewhere explicitly, 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) MySQL can use two byte enums if you have 255 potential values to enumerate over. It looks like this, CREATE TABLE payment ( id int AUTO_INCREMENT, payment ENUM('money', 'online'), PRIMARY KEY (id) );
P

postgresql error with django
Software Programming • python postgresql django database • • Pearlaqua

2

0
Votes

2
Posts

0
Views

J

The command is python manage.py makemigratiions and do not forget to have active the virtual If the first time you run the migrations, before running the makemigrations executes the command: python manage.py migrate To generate Django default tables

Is it possible to reference an inserted column within the same INSERT?

Suggested Topics

PostgreSQL: Create users that start with number Software Programming • sql postgresql database • • kalyn

Google Managed PGSQL DB migration to DigitalOcean Managed PGSQL DB SQL, Database Testing • postgresql migration pg restore google c languageloud sql • • Avante

Duplicate data in an SQL table by modifying one of the fields Software Programming • php sql insert • • Mystic

Why primary node disconnect when set automatic failover with PostgreSQL and Pacemaker? SQL, Database Testing • postgresql high availability failover • • inna

Mysql insert fixed column value for each row during multi-insert SQL, Database Testing • mysql • • Laycee

Trigger After Insert is preventing insertion SQL, Database Testing • sql server t sql trigger insert • • simrah

INSERT and UPDATE in Trigger SQL, Database Testing • oracle select trigger update insert • • Duquan

Select table with external key that can be NULL Software Programming • sql postgresql select • • Kadyn

Order by one column if not null, otherwise by another column, columns are of not matching types SQL, Database Testing • postgresql • • obi

Take average of multiple nullable columns SQL, Database Testing • postgresql null aggregate postgresql 9.5 • • kalyn

Convert a query results from lines to columns so i can see somes values from a column in columns SQL, Database Testing • sql server t sql • • zymir

SQL How to copy text from one table (from one column) to another table (to one column), preferably with IF the target column is empty SQL, Database Testing • mysql table insert c languageopy • • Kadyn

Table design for ticket sales SQL, Database Testing • postgresql database design • • Raziyah00

MySql column value to indicate 2 possible options SQL, Database Testing • mysql database design • • terrea

postgresql error with django Software Programming • python postgresql django database • • Pearlaqua

PostgreSQL: Create users that start with number
Software Programming • sql postgresql database • • kalyn

Google Managed PGSQL DB migration to DigitalOcean Managed PGSQL DB
SQL, Database Testing • postgresql migration pg restore google c languageloud sql • • Avante

Duplicate data in an SQL table by modifying one of the fields
Software Programming • php sql insert • • Mystic

Why primary node disconnect when set automatic failover with PostgreSQL and Pacemaker?
SQL, Database Testing • postgresql high availability failover • • inna

Mysql insert fixed column value for each row during multi-insert
SQL, Database Testing • mysql • • Laycee

Trigger After Insert is preventing insertion
SQL, Database Testing • sql server t sql trigger insert • • simrah

INSERT and UPDATE in Trigger
SQL, Database Testing • oracle select trigger update insert • • Duquan

Select table with external key that can be NULL
Software Programming • sql postgresql select • • Kadyn

Order by one column if not null, otherwise by another column, columns are of not matching types
SQL, Database Testing • postgresql • • obi

Take average of multiple nullable columns
SQL, Database Testing • postgresql null aggregate postgresql 9.5 • • kalyn

Convert a query results from lines to columns so i can see somes values from a column in columns
SQL, Database Testing • sql server t sql • • zymir

SQL How to copy text from one table (from one column) to another table (to one column), preferably with IF the target column is empty
SQL, Database Testing • mysql table insert c languageopy • • Kadyn

Table design for ticket sales
SQL, Database Testing • postgresql database design • • Raziyah00

MySql column value to indicate 2 possible options
SQL, Database Testing • mysql database design • • terrea

postgresql error with django
Software Programming • python postgresql django database • • Pearlaqua